| << Back to previous view |
|
[QB-2102] database restore into mySQL fails with SQL Exception 'Data truncation: Incorrect datetime value: '' fo r column 'QB_STATUS_DATE' at row 1'
|
|
| Status: | Closed |
| Project: | QuickBuild |
| Component/s: | None |
| Affects Version/s: | 5.0.3 |
| Fix Version/s: | None |
| Type: | Bug | Priority: | Major |
| Reporter: | Alex Chatziparaskewas | Assigned To: | Robin Shen |
| Resolution: | Fixed | Votes: | 0 |
| Remaining Estimate: | Unknown | Time Spent: | Unknown |
| Original Estimate: | Unknown | ||
| Environment: | Windows 2012 64bit | ||
| Description |
|
Hello
We are trying to migrate from the embedded H2 database to mySQL. However, following the documentation results in an SQL Exception 'Data truncation: Incorrect datetime value: '' for column 'QB_STATUS_DATE' at row 1' upon restoring the database backup. The full stack trace is attached below. Additional information: The restore fails to create entries from the qb_configuration table in H2. I have looked into the H2 database and checked if any of the 'qb_status_date' entries is empty (null) or otherwise dodgy. Nothing. The configuration.xml from the backup was checked as well, but nothing obvious was visible. I have tried to removing the 'offending' configuration from the XML (the last one where it says import), but still it fails in the same way. Additionally, the errors message says 'row 1' so my guess is that this is a general problem on all configurations. Thanks & Regards Alex ------------------------------------------ STACK TRACE ---------------------------- 2014-06-21 13:12:35,546 INFO - Importing configuration 'root/M+H Control Centre /b_PI_PLATFORM_D45/fix build'... 2014-06-21 13:12:35,562 INFO - Importing configuration 'root/M+H Control Centre /Archived'... 2014-06-21 13:12:35,843 WARN - SQL Error: 0, SQLState: 22001 2014-06-21 13:12:35,843 ERROR - Data truncation: Incorrect datetime value: '' fo r column 'QB_STATUS_DATE' at row 1 2014-06-21 13:12:35,843 ERROR - Could not synchronize database state with sessio n org.hibernate.exception.DataException: could not insert: [com.pmease.quickbuild. model.Configuration] at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.j ava:102) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelp er.java:66) at org.hibernate.persister.entity.AbstractEntityPersister.insert(Abstrac tEntityPersister.java:2454) at org.hibernate.persister.entity.AbstractEntityPersister.insert(Abstrac tEntityPersister.java:2874) at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.ja va:79) at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:273) at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:265) at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:184) at org.hibernate.event.def.AbstractFlushingEventListener.performExecutio ns(AbstractFlushingEventListener.java:321) at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlus hEventListener.java:51) at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1216) at com.pmease.quickbuild.entitymanager.impl.DefaultDataManager.importDat a(DefaultDataManager.java:742) at com.pmease.quickbuild.Quickbuild.importData(Quickbuild.java:866) at com.pmease.quickbuild.bootstrap.Restore$1.execute(Restore.java:71) at com.pmease.quickbuild.Quickbuild$3.run(Quickbuild.java:587) at java.lang.Thread.run(Unknown Source) Caused by: java.sql.BatchUpdateException: Data truncation: Incorrect datetime va lue: '' for column 'QB_STATUS_DATE' at row 1 at com.mysql.jdbc.ServerPreparedStatement.executeBatch(ServerPreparedSta tement.java:647) at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.jav a:70) at org.hibernate.jdbc.BatchingBatcher.addToBatch(BatchingBatcher.java:56 ) at org.hibernate.persister.entity.AbstractEntityPersister.insert(Abstrac tEntityPersister.java:2434) ... 13 more 2014-06-21 13:12:35,874 INFO - Stopping plugin manager... 2014-06-21 13:12:35,889 INFO - Stopping QuickBuild server... 2014-06-21 13:12:35,889 INFO - Stopping plugins... 2014-06-21 13:12:35,889 INFO - Stopping task scheduler... 2014-06-21 13:12:35,889 INFO - Closing session factory... 2014-06-21 13:12:35,974 INFO - QuickBuild server stopped. Exception in thread "main" org.hibernate.exception.DataException: could not inse rt: [com.pmease.quickbuild.model.Configuration] at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.j ava:102) at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelp er.java:66) at org.hibernate.persister.entity.AbstractEntityPersister.insert(Abstrac tEntityPersister.java:2454) at org.hibernate.persister.entity.AbstractEntityPersister.insert(Abstrac tEntityPersister.java:2874) at org.hibernate.action.EntityInsertAction.execute(EntityInsertAction.ja va:79) at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:273) at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:265) at org.hibernate.engine.ActionQueue.executeActions(ActionQueue.java:184) at org.hibernate.event.def.AbstractFlushingEventListener.performExecutio ns(AbstractFlushingEventListener.java:321) at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlus hEventListener.java:51) at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:1216) at com.pmease.quickbuild.entitymanager.impl.DefaultDataManager.importDat a(DefaultDataManager.java:742) at com.pmease.quickbuild.Quickbuild.importData(Quickbuild.java:866) at com.pmease.quickbuild.bootstrap.Restore$1.execute(Restore.java:71) at com.pmease.quickbuild.Quickbuild$3.run(Quickbuild.java:587) at java.lang.Thread.run(Unknown Source) Caused by: java.sql.BatchUpdateException: Data truncation: Incorrect datetime va lue: '' for column 'QB_STATUS_DATE' at row 1 at com.mysql.jdbc.ServerPreparedStatement.executeBatch(ServerPreparedSta tement.java:647) at org.hibernate.jdbc.BatchingBatcher.doExecuteBatch(BatchingBatcher.jav a:70) at org.hibernate.jdbc.BatchingBatcher.addToBatch(BatchingBatcher.java:56 ) at org.hibernate.persister.entity.AbstractEntityPersister.insert(Abstrac tEntityPersister.java:2434) ... 13 more Press any key to continue . . . |
| Comments |
| Comment by Alex Chatziparaskewas [ 21/Jun/14 11:45 AM ] |
|
Some additional information:
* mySQL version: 5.6 * JDBC driver: mysql-connector-java-3.1.12-bin.jar * restoring the backup back into the H2 database works flawlessly |
| Comment by Robin Shen [ 21/Jun/14 12:09 PM ] |
| Please use mysql-connector-5.1.31 instead of 3.1.12. |
| Comment by Alex Chatziparaskewas [ 21/Jun/14 04:35 PM ] |
|
Thanks for the hint, I somehow assumed that 3.1.12, because it is mentioned int the hibernate.properties file is the desired version.
I upgraded to 5.1.31, set the hibernate.dialect to org.hibernate.dialect.MySQL5Dialect and it worked. Another error with the embedded H2 database (artifact & build cleanup) vanished as well. Maybe the documentation or property file should be updated. |
| Comment by Robin Shen [ 21/Jun/14 11:20 PM ] |
| Will update the document to reflect this. |