cancel
Showing results for 
Search instead for 
Did you mean: 

Web Reporter Migrate MySQL DB to MS SQL 2012 DB

Jump to solution

hi, we've migrated the internal mysql db to an external ms sql and since then web reporter is not able to connect to the new db..only the connection test is successful, but I get a lot of error messages in der error log:

2014-06-27 18:31:27,433 ERROR [securecomputing.smartfilter.reportdbbuilder.ReportDbBuilder] Failed to create Session Factory, database unavailable.

org.hibernate.HibernateException: Wrong column type: hour_of_day, expected: tinyint

at org.hibernate.mapping.Table.validateColumns(Table.java:261)

at org.hibernate.cfg.Configuration.validateSchema(Configuration.java:1083)

at org.hibernate.tool.hbm2ddl.SchemaValidator.validate(SchemaValidator.java:116)

at org.hibernate.impl.SessionFactoryImpl.<init>(SessionFactoryImpl.java:317)

at org.hibernate.cfg.Configuration.buildSessionFactory(Configuration.java:1294)

at securecomputing.smartfilter.reportdbbuilder.ReportDbBuilder.createSessionFactory(ReportDbBuilder.java:1536)

at securecomputing.smartfilter.reportdbbuilder.ReportDbBuilder.setReportingDatabase(ReportDbBuilder.java:929)

at securecomputing.smartfilter.reportdbbuilder.ReportDbBuilder.setReportingDatabase(ReportDbBuilder.java:642)

at securecomputing.smartfilter.server.project.services.reportDb.SetReportDatabaseTask.runit(SetReportDatabaseTask.java:166)

at securecomputing.smartfilter.server.project.ScheduledTask.run(ScheduledTask.java:67)

at java.util.concurrent.ThreadPoolExecutor$Worker.runTask(ThreadPoolExecutor.java:886)

at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:908)

at java.lang.Thread.run(Thread.java:619)

did something went wrong? Basically we've created a database and migrated the whole schema from the old mysql db to the new ms sql db...

Thanks!

Kind regards,

Renata

1 Solution

Accepted Solutions
sroering
Level 13

Re: Web Reporter Migrate MySQL DB to MS SQL 2012 DB

Jump to solution

First of alll, a disclaimer. What you are doing is not supported. Making mistakes can mean that your data will become corrupted, and report results too.

The error is quite obvious of the problem. The SQL Server column (scr_fct_web.hour_of_day) is not the correct type, so when you created the MSSQL tables, you didn't create the correct schema.

If I were to try and migrate the DB, this is how I would do it.

1) Make sure log sources are disabled to prevent any incoming data.

2) Wait for any existing log parsing jobs to finish.

3) Connect to the MS-SQL DB. This will create the correct schema. then in WR, make the DB offline.

4) copy the data from your mysql to the MS-SQL DB, replacing any existing values in the MS DB.  Some tables are prepoulated with default values.

5) In WR, put the DB online, then run some reports to make sure everything is OK. check the server.log for errors to be sure.

6) enable the log sources and begin accepting new data.

Step 4 is the tricky one.  I suspect you tried to recreate the table schema on your own(step 3), which is resulting in the error.

Good luck.

8 Replies
sroering
Level 13

Re: Web Reporter Migrate MySQL DB to MS SQL 2012 DB

Jump to solution

First of alll, a disclaimer. What you are doing is not supported. Making mistakes can mean that your data will become corrupted, and report results too.

The error is quite obvious of the problem. The SQL Server column (scr_fct_web.hour_of_day) is not the correct type, so when you created the MSSQL tables, you didn't create the correct schema.

If I were to try and migrate the DB, this is how I would do it.

1) Make sure log sources are disabled to prevent any incoming data.

2) Wait for any existing log parsing jobs to finish.

3) Connect to the MS-SQL DB. This will create the correct schema. then in WR, make the DB offline.

4) copy the data from your mysql to the MS-SQL DB, replacing any existing values in the MS DB.  Some tables are prepoulated with default values.

5) In WR, put the DB online, then run some reports to make sure everything is OK. check the server.log for errors to be sure.

6) enable the log sources and begin accepting new data.

Step 4 is the tricky one.  I suspect you tried to recreate the table schema on your own(step 3), which is resulting in the error.

Good luck.

Re: Web Reporter Migrate MySQL DB to MS SQL 2012 DB

Jump to solution

Hi, I did it your way and migration worked fine! i can also see all entries in the new db, BUT I cannot run reports for logs older than 30.6.. 30.6 was the migration date... do you think indexing would help or why is reporter not able to search for older logs even if they are in the database??

Thanks very much!

0 Kudos
mekafir
Level 7

Re: Web Reporter Migrate MySQL DB to MS SQL 2012 DB

Jump to solution

, Is there any KB article for the migration process in detail ? Because I need to igrate the MySQL database to SQL Server box due to the fact that I cannot query / create report with the data older than 3 weeks.

so in this case, on the Steps #4 how did you copy the builtin MySQL database to the SQL Server database ?

0 Kudos
mbagheryan
Level 12

Re: Web Reporter Migrate MySQL DB to MS SQL 2012 DB

Jump to solution

Perfect Idea

mekafir
Level 7

Re: Web Reporter Migrate MySQL DB to MS SQL 2012 DB

Jump to solution

Migrating from My SQL to SQL Server database, is this supported by McAfee ?

0 Kudos
mbagheryan
Level 12

Re: Web Reporter Migrate MySQL DB to MS SQL 2012 DB

Jump to solution

Migrating is not on McAfee but about Web Reporter I can say it is Supporting MSSQL 2008 r2 sp1 and above.

mekafir
Level 7

Re: Web Reporter Migrate MySQL DB to MS SQL 2012 DB

Jump to solution

So in my case here, can I just change the highlighted values below in my production server to point to the new SQL Database from MySQL ?

DB.JPGDB2.JPG

The past performance or statistics data cen be forfeited, just wanted to  confirm if that's the correct location ?

0 Kudos
mbagheryan
Level 12

Re: Web Reporter Migrate MySQL DB to MS SQL 2012 DB

Jump to solution

Yes.

0 Kudos