Mysql GenerateChangelog of data in xml format error

Liquibase Development
1

Hi All,
Ran the following command, but not sure how to pinpoint what is going wrong.
Running against a local Mysql database.

C:\Dev\liquibase-3.0.7-bin>liquibase --driver=com.mysql.jdbc.Driver --url=jdbc:mysql://localhost:3306/kmmdemo --username=kmmdemo --password=kmmdemo --changeLogFile=output2.xml --diffTypes=data generateChangeLog --logLevel=DEBUG

Liquibase generateChangeLog Failed: java.sql.SQLException: Cannot convert value ‘0000-00-00 00:00:00’ from column 6 to TIMESTAMP.

Anybody have idea on how I can figure out which table it has issue with?
Thought maybe logLevel would give more info, but apparently not.
Thanks
Adrian

2

Are you getting any stacktrace in the debug output?

Nathan

3

Would the debug output also be generated in the command line?
Or is it output to a file? Sorry I’m new to liquibase.

Only got that oneliner in my console output.

4

It should go to output. I think the problem is that the --logLevel=debug flag goes before the generateChangeLog command, not after.


Nathan

5

Yeah I moved the loglevel
Following output now:

  1. C:\Users\Carbon>liquibase --driver=com.mysql.jdbc.Driver --url=jdbc:mysql://localhost:3306/kmmdemo --username=kmmdemo --password=kmmdemo --changeLogFile=output2.xml --logLevel=DEBUG --diffTypes=data generateChangeLog
  2. DEBUG 11/21/13 8:37 AM:liquibase: Connected to kmmdemo@localhost@jdbc:mysql://localhost:3306/kmmdemo
  3. DEBUG 11/21/13 8:37 AM:liquibase: Setting auto commit to false from true
  4. DEBUG 11/21/13 8:38 AM:liquibase: Executing QUERY database command: select view_definition from information_schema.views where table_name=‘mm_catalog_item_live_v’ and table_schema='kmmdemo’
  5. DEBUG 11/21/13 8:38 AM:liquibase: Executing QUERY database command: select view_definition from information_schema.views where table_name=‘mm_customer_return_lines_v’ and table_schema='kmmdemo’
  6. DEBUG 11/21/13 8:38 AM:liquibase: Executing QUERY database command: select view_definition from information_schema.views where table_name=‘sbval_v’ and table_schema='kmmdemo’
  7. DEBUG 11/21/13 8:38 AM:liquibase: Executing QUERY database command: select view_definition from information_schema.views where table_name=‘mm_stock_balance_v’ and table_schema='kmmdemo’
  8. DEBUG 11/21/13 8:38 AM:liquibase: Executing QUERY database command: select view_definition from information_schema.views where table_name=‘qtytbl_v’ and table_schema='kmmdemo’
  9. DEBUG 11/21/13 8:38 AM:liquibase: Executing QUERY database command: select view_definition from information_schema.views where table_name=‘mm_awaiting_shipment_v’ and table_schema='kmmdemo’
  10. DEBUG 11/21/13 8:38 AM:liquibase: Executing QUERY database command: select view_definition from information_schema.views where table_name=‘mm_stock_reordered_v’ and table_schema='kmmdemo’
  11. DEBUG 11/21/13 8:38 AM:liquibase: Executing QUERY database command: select view_definition from information_schema.views where table_name=‘reord_v’ and table_schema='kmmdemo’
  12. DEBUG 11/21/13 8:38 AM:liquibase: Executing QUERY database command: select view_definition from information_schema.views where table_name=‘mm_reorder_summary_v’ and table_schema='kmmdemo’
  13. DEBUG 11/21/13 8:38 AM:liquibase: Executing QUERY database command: select view_definition from information_schema.views where table_name=‘mm_checkin_correction_v’ and table_schema='kmmdemo’
  14. DEBUG 11/21/13 8:38 AM:liquibase: Executing QUERY database command: select view_definition from information_schema.views where table_name=‘mm_checkin_receivable_v’ and table_schema='kmmdemo’
  15. DEBUG 11/21/13 8:38 AM:liquibase: Executing QUERY database command: select view_definition from information_schema.views where table_name=‘mm_mass_maint_stock_v’ and table_schema='kmmdemo’
  16. DEBUG 11/21/13 8:38 AM:liquibase: Executing QUERY database command: select view_definition from information_schema.views where table_name=‘mm_stock_ordered_v’ and table_schema='kmmdemo’
  17. DEBUG 11/21/13 8:38 AM:liquibase: Executing QUERY database command: select view_definition from information_schema.views where table_name=‘mm_vendor_return_lines_v’ and table_schema='kmmdemo’
  18. DEBUG 11/21/13 8:38 AM:liquibase: Executing QUERY database command: select view_definition from information_schema.views where table_name=‘odett_v’ and table_schema='kmmdemo’
  19. INFO 11/21/13 8:38 AM:liquibase: output2.xml exists, appending
  20. DEBUG 11/21/13 8:38 AM:liquibase: MissingObjectChangeGenerator type order:     liquibase.structure.core.Schema    liquibase.structure.core.Table    liquibase.structure.core.Data    liquibase.structure.core.ForeignKey    liquibase.structure.core.Index    liquibase.structure.core.PrimaryKey    liquibase.structure.core.View
  21. Liquibase generateChangeLog Failed: java.sql.SQLException: Cannot convert value ‘0000-00-00 00:00:00’ from column 6 to TIMESTAMP.
  22. SEVERE 11/21/13 8:38 AM:liquibase: java.sql.SQLException: Cannot convert value ‘0000-00-00 00:00:00’ from column 6 to TIMESTAMP.
  23. liquibase.exception.UnexpectedLiquibaseException: java.sql.SQLException: Cannot convert value ‘0000-00-00 00:00:00’ from column 6 to TIMESTAMP.
  24.         at liquibase.diff.output.changelog.core.MissingDataChangeGenerator.fixMissing(MissingDataChangeGenerator.java:206)
  25.         at liquibase.diff.output.changelog.ChangeGeneratorChain.fixMissing(ChangeGeneratorChain.java:44)
  26.         at liquibase.diff.output.changelog.ChangeGeneratorFactory.fixMissing(ChangeGeneratorFactory.java:95)
  27.         at liquibase.diff.output.changelog.DiffToChangeLog.generateChangeSets(DiffToChangeLog.java:140)
  28.         at liquibase.diff.output.changelog.DiffToChangeLog.print(DiffToChangeLog.java:121)
  29.         at liquibase.diff.output.changelog.DiffToChangeLog.print(DiffToChangeLog.java:67)
  30.         at liquibase.diff.output.changelog.DiffToChangeLog.print(DiffToChangeLog.java:50)
  31.         at liquibase.integration.commandline.CommandLineUtils.doGenerateChangeLog(CommandLineUtils.java:173)
  32.         at liquibase.integration.commandline.Main.doMigration(Main.java:791)
  33.         at liquibase.integration.commandline.Main.main(Main.java:133)
  34. Caused by: java.sql.SQLException: Cannot convert value ‘0000-00-00 00:00:00’ from column 6 to TIMESTAMP.
  35.         at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
  36.         at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
  37.         at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
  38.         at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
  39.         at com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:1339)
  40.         at com.mysql.jdbc.BufferRow.getTimestampFast(BufferRow.java:578)
  41.         at com.mysql.jdbc.ResultSetImpl.getTimestampInternal(ResultSetImpl.java:6754)
  42.         at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:6073)
  43.         at com.mysql.jdbc.ResultSetImpl.getObject(ResultSetImpl.java:5029)
  44.         at liquibase.util.JdbcUtils.getResultSetValue(JdbcUtils.java:83)
  45.         at liquibase.diff.output.changelog.core.MissingDataChangeGenerator.fixMissing(MissingDataChangeGenerator.java:180)
  46.         … 9 more
  47. Caused by: java.sql.SQLException: Value ‘[B@4c272961’ can not be represented as java.sql.Timestamp
  48.         at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075)
  49.         at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989)
  50.         at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984)
  51.         at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929)
  52.         at com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:1037)
  53.         … 15 more


For more information, use the --logLevel flag

6

Looks like we’re running into http://stackoverflow.com/questions/11133759/0000-00-00-000000-can-not-be-represented-as-java-sql-timestamp-error


Adding the parameter would get you past the error, but you will have to reset the correct zero date back. It looks like the problem is with rows with a zero timestamp value. Do you know how many rows that is in your database?

Nathan