mysql bulk loader fails on date values with timezone from postgresql

[jcarrig]

I am trying to run an initial load from PostgreSQL to MySQL, using a channel with a bulk loader and the bulk load fails because the date columns in PostgreSQL are timestamp(3) with timezone and in MySQL timestamps do not have a timezone.

Here is an example of the error message:

2021-04-16 15:53:52,305 INFO [target-001] [ManageIncomingBatchListener] [target-001-dataloader-8] Bulk loading failed for this batch 14, falling back to default loading. (org.jumpmind.db.sql.SqlException: Data truncation: Incorrect datetime value: '2021-04-16 15:49:44.561000 -00:00' for column 'created_at' at row 1)
2021-04-16 15:53:52,306 INFO [target-001] [DataLoaderService] [target-001-dataloader-8] Bulk loader failed in class org.jumpmind.db.sql.SqlException with message: Data truncation: Incorrect datetime value: '2021-04-16 15:49:44.561000 -00:00' for column 'created_at' at row 1

Is this a bug in MySqlBulkDatabaseWriter, should it know timestamps don't have timezones? Or is there a way I can remove the timezone before it gets to loading the data in bulk? Any help is appreciated.

Thanks!

[jcarrig]

2021-04-16 17:46:27,869 DEBUG [target-001] [ManageIncomingBatchListener] [target-001-dataloader-4] Bulk loading error. org.jumpmind.db.sql.SqlException: Data truncation: Incorrect datetime value: '2021-04-16 15:49:54.274000 -00:00' for column 'created_at' at row 1
    at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:306)
    at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:295)
    at org.jumpmind.symmetric.io.MySqlBulkDatabaseWriter.flush(MySqlBulkDatabaseWriter.java:207)
    at org.jumpmind.symmetric.io.MySqlBulkDatabaseWriter.end(MySqlBulkDatabaseWriter.java:106)
    at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.end(NestedDataWriter.java:68)
    at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.end(NestedDataWriter.java:68)
    at org.jumpmind.symmetric.io.data.writer.TransformWriter.end(TransformWriter.java:468)
    at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:184)
    at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:124)
    at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1088)
    at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1064)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.jdbc.MysqlDataTruncation: Data truncation: Incorrect datetime value: '2021-04-16 15:49:54.274000 -00:00' for column 'created_at' at row 1
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3971)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3909)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:873)
    at com.mysql.jdbc.MysqlIO.sendFileToServer(MysqlIO.java:3876)
    at com.mysql.jdbc.MysqlIO.readResultsForQueryOrUpdate(MysqlIO.java:3110)
    at com.mysql.jdbc.MysqlIO.readAllResults(MysqlIO.java:2341)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2736)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2480)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2438)
    at com.mysql.jdbc.StatementImpl.executeInternal(StatementImpl.java:845)
    at com.mysql.jdbc.StatementImpl.execute(StatementImpl.java:745)
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
    at org.apache.commons.dbcp.DelegatingStatement.execute(DelegatingStatement.java:264)
    at org.jumpmind.symmetric.io.MySqlBulkDatabaseWriter.flush(MySqlBulkDatabaseWriter.java:203)
    ... 12 more

[jcarrig]

The workaround I implemented for this issue is to use Left Transform that removes the timezone from the date string for all date columns on EXTRACT. So the data is in the proper format by the time it reaches the bulk loader...

[Josh Hicks]

That sounds like a viable solution thanks for sharing. If you enter an issue we can add it to a future roadmap.