search

nuodb / migration-tools

Migration tools for NuoDB
http://nuodb.github.com/migration-tools/
BSD 3-Clause "New" or "Revised" License
27 stars 10 forks source link

failure to dump timestamp '0000-00-00 00:00:00' #27

Closed acharis closed 11 years ago

acharis commented 11 years ago

this is spit out by the 1.1.1-174 release package (if it matters, this is on AWS , with yum install mysql-connector-java.noarch)

CLASSPATH=/usr/share/java/mysql-connector-java.jar bin/nuodb-migrator dump --source.driver=com.mysql.jdbc.Driver --source.url=jdbc:mysql://localhost:3306/DATABASE_NAME_OBSCURED --source.username=root --output.type=csv Jul 22, 2013 9:27:15 PM com.nuodb.migrator.bootstrap.Bootstrap main SEVERE: Execution failed com.nuodb.migrator.resultset.format.FormatOutputException: Failed to dump row #93911: Can't get table OBSCURED.OBSCURED OBSCURED DATETIME column value at com.nuodb.migrator.resultset.format.FormatOutputBase.onWriteRowFailure(FormatOutputBase.java:166) at com.nuodb.migrator.resultset.format.FormatOutputBase.writeRow(FormatOutputBase.java:139) at com.nuodb.migrator.dump.DumpJob.dump(DumpJob.java:223) at com.nuodb.migrator.dump.DumpJob$2.execute(DumpJob.java:192) at com.nuodb.migrator.dump.DumpJob$2.execute(DumpJob.java:189) at com.nuodb.migrator.jdbc.query.StatementTemplate.execute(StatementTemplate.java:50) at com.nuodb.migrator.dump.DumpJob.dump(DumpJob.java:179) at com.nuodb.migrator.dump.DumpJob.doExecute(DumpJob.java:146) at com.nuodb.migrator.dump.DumpJob.doExecute(DumpJob.java:87) at com.nuodb.migrator.job.decorate.DecoratingJobBase.execute(DecoratingJobBase.java:50) at com.nuodb.migrator.job.SimpleJobExecutor.execute(SimpleJobExecutor.java:105) at com.nuodb.migrator.cli.run.CliRunJob.run(CliRunJob.java:57) at com.nuodb.migrator.cli.run.CliRunJob.run(CliRunJob.java:50) at com.nuodb.migrator.cli.CliHandler.handleRun(CliHandler.java:200) at com.nuodb.migrator.cli.CliHandler.handleOptionSet(CliHandler.java:148) at com.nuodb.migrator.cli.CliHandler.boot(CliHandler.java:74) at com.nuodb.migrator.bootstrap.Bootstrap.boot(Bootstrap.java:74) at com.nuodb.migrator.bootstrap.Bootstrap.main(Bootstrap.java:109) Caused by: com.nuodb.migrator.resultset.format.value.ValueFormatException: Can't get table OBSCURED.OBSCURED OBSCURED DATETIME column value at com.nuodb.migrator.resultset.format.value.ValueFormatBase.newGetValueFailure(ValueFormatBase.java:61) at com.nuodb.migrator.resultset.format.value.ValueFormatBase.getValue(ValueFormatBase.java:52) at com.nuodb.migrator.resultset.format.FormatOutputBase.getValues(FormatOutputBase.java:150) at com.nuodb.migrator.resultset.format.FormatOutputBase.writeRow(FormatOutputBase.java:137) ... 16 more Caused by: java.sql.SQLException: Cannot convert value '0000-00-00 00:00:00' from column 2 to TIMESTAMP. at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929) at com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:1339) at com.mysql.jdbc.BufferRow.getTimestampFast(BufferRow.java:578) at com.mysql.jdbc.ResultSetImpl.getTimestampInternal(ResultSetImpl.java:6755) at com.mysql.jdbc.ResultSetImpl.getTimestamp(ResultSetImpl.java:6074) at org.apache.commons.dbcp.DelegatingResultSet.getTimestamp(DelegatingResultSet.java:250) at org.apache.commons.dbcp.DelegatingResultSet.getTimestamp(DelegatingResultSet.java:250) at com.nuodb.migrator.jdbc.type.jdbc2.JdbcTimestampType.getValue(JdbcTimestampType.java:55) at com.nuodb.migrator.jdbc.type.jdbc2.JdbcTimestampType.getValue(JdbcTimestampType.java:40) at com.nuodb.migrator.jdbc.type.access.JdbcTypeValueAccessProvider$JdbcTypeValueGetterImpl.getValue(JdbcTypeValueAccessProvider.java:154) at com.nuodb.migrator.jdbc.type.access.JdbcTypeValueAccessProvider$JdbcTypeValueAccessImpl.getValue(JdbcTypeValueAccessProvider.java:241) at com.nuodb.migrator.resultset.format.value.JdbcTimestampValueFormat.doGetValue(JdbcTimestampValueFormat.java:55) at com.nuodb.migrator.resultset.format.value.ValueFormatBase.getValue(ValueFormatBase.java:48) ... 18 more Caused by: java.sql.SQLException: Value '[B@1b0deb5f' can not be represented as java.sql.Timestamp at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1075) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:989) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:984) at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:929) at com.mysql.jdbc.ResultSetRow.getTimestampFast(ResultSetRow.java:1038) ... 29 more

i'll try to work around this as best i can.

ajabrevaya commented 11 years ago

Alex, can you use the zeroDateTimeBehavior=convertToNull property? Described in this techblog post: http://www.nuodb.com/techblog/2013/06/26/migrating-a-mysql-application-to-nuodb/

$ /opt/nuodb/tools/migrator/bin/nuodb-migrator dump --source.driver=com.mysql.jdbc.Driver --source.url=jdbc:mysql://localhost:3306/foodmart?zeroDateTimeBehavior=convertToNull --source.username=root --source.password=root --output.type=csv --output.path=/tmp/foodmart.cat

philip-stoev commented 11 years ago

Alex, note that 0000-00-00 is a MySQL-ism that is not standard and not supported by other databases.

tazija commented 11 years ago

com.nuodb.migrator.jdbc.url.MySQLJdbcUrlParser sets zeroDateTimeBehavior parameter to convertToNull value by default, unless a user provides other value (round | exception) for zeroDateTimeBehavior on the command line.

i.e. to override zero'd date conversion from convertToNull you can use --source.url=jdbc:mysql://localhost:3306/database?zeroDateTimeBehavior=exception

tazija commented 11 years ago

Fix is available in the master branch