View Issue Details

IDProjectCategoryView StatusLast Update
0003251SymmetricDSBugpublic2018-12-05 14:58
Reporterivan.konevAssigned To 
Prioritylow 
Status confirmedResolutionopen 
Product Version3.8.24 
Target Version3.10.0Fixed in Version 
Summary0003251: Mysql BIGINT UNSIGNED transform always processed as signed and giver error on larger than 63 bits numbers.
DescriptionSetup - mysql 5.5-5.7, i'm trying to replicate table with BIGINT(32) UNSIGNED field. It gives error when field value exceeds BIGINT SIGNED value.

The outgoing batch 001-122539 failed: [22001,1264] Data truncation: Out of range value for column 'intval' at row 1
Steps To Reproduce2 tables , 2 nodes. Table test replicated w/o any transforms using default values in trigger+trigger_router.

CREATE TABLE `test` (
    `id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `intval` BIGINT(32) UNSIGNED NOT NULL DEFAULT '0',
    PRIMARY KEY (`id`)
)
COLLATE='utf8mb4_bin' ENGINE=InnoDB AUTO_INCREMENT=4;

Insert some data:

INSERT IGNORE INTO `test` (`id`, `intval`) VALUES
    (2, 1),
    (3, 8643130000000077777);

Try to update value to larget than SIGNED BIGINT, like 9643130000000077777 and it will fail replication with error above on DESTINATION node.

2017-09-15 20:01:39,666 ERROR [test-001] [DataLoaderService] [dataloader-home-000-1] Failed to load batch 000-122539 StackTraceKey [SqlException:1431669847]
2017-09-15 20:02:39,630 INFO [test-001] [IncomingBatchService] [dataloader-home-000-1] Retrying batch 000-122539
2017-09-15 20:02:39,633 INFO [test-001] [DefaultDatabaseWriter] [dataloader-home-000-1] Failed to process update event in batch 122539.
Failed sql was: update `monitor`.`test` set `intval` = ? where `id` = ?
Failed sql parameters: [9643130000000077777, 3]
Failed sql parameters types: [-5, 4]
Failed sql state and code: 22001 (1264)
Failed pk data was: "3"
Failed row data was: "3","9643130000000077777"
Failed old data was: "3","8643130000000077777"
 StackTraceKey [SqlException:1431669847]

Activities

hanes

2018-10-17 10:28

developer   ~0001278

Issue still exists in 3.9.14. SQLExplorer (in Pro) can't select the value out of the table. And when attempting to sync the row, the data is captured as the full value (9643130000000077777) but insert is failing on the target MySQL database (see below). It looks like SymmetricDS isn't recognizing that the type is BIGINT Unsigned.

2018-10-17 10:24:45,849 INFO [store-001] [DefaultDatabaseWriter] [store-001-dataloader-6] Failed to process insert event in batch 000-43 on channel 'default'.

Failed sql was: insert into `symmetricclient`.`test` (`id`, `intval`) values (?,?)
Failed sql parameters: [4, '9643130000000077777']
Failed sql parameters types: [INTEGER, BIGINT]
Failed sql state and code: 22001 (1264)
Failed row data was: "4","9643130000000077777"
 StackTraceKey.init [SqlException:1051950945]
org.jumpmind.db.sql.SqlException: Data truncation: Out of range value for column 'intval' at row 1
     at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:302)
     at org.jumpmind.db.sql.AbstractSqlTemplate.translate(AbstractSqlTemplate.java:291)
     at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:466)
     at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.execute(DefaultDatabaseWriter.java:896)
     at org.jumpmind.symmetric.io.data.writer.DefaultDatabaseWriter.insert(DefaultDatabaseWriter.java:203)
     at org.jumpmind.symmetric.io.data.writer.AbstractDatabaseWriter.write(AbstractDatabaseWriter.java:191)
     at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64)
     at org.jumpmind.symmetric.model.ProcessInfoDataWriter.write(ProcessInfoDataWriter.java:84)
     at org.jumpmind.symmetric.io.data.writer.NestedDataWriter.write(NestedDataWriter.java:64)
     at org.jumpmind.symmetric.io.data.writer.TransformWriter.write(TransformWriter.java:201)
     at org.jumpmind.symmetric.io.data.DataProcessor.forEachDataInTable(DataProcessor.java:210)
     at org.jumpmind.symmetric.io.data.DataProcessor.forEachTableInBatch(DataProcessor.java:177)
     at org.jumpmind.symmetric.io.data.DataProcessor.process(DataProcessor.java:123)
     at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1039)
     at org.jumpmind.symmetric.service.impl.DataLoaderService$LoadIntoDatabaseOnArrivalListener$2.call(DataLoaderService.java:1016)
     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: Out of range value for column 'intval' 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.sendCommand(MysqlIO.java:2527)
     at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2680)
     at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2484)
     at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858)
     at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079)
     at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2013)
     at com.mysql.jdbc.PreparedStatement.executeLargeUpdate(PreparedStatement.java:5104)
     at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1998)
     at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
     at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
     at org.jumpmind.db.sql.JdbcSqlTransaction.executePreparedUpdate(JdbcSqlTransaction.java:485)
     at org.jumpmind.db.sql.JdbcSqlTransaction.addRow(JdbcSqlTransaction.java:463)
     ... 16 more
     

Issue History

Date Modified Username Field Change
2017-09-15 13:06 ivan.konev New Issue
2017-09-15 13:06 ivan.konev Tag Attached: extract
2018-10-17 10:28 hanes Status new => confirmed
2018-10-17 10:28 hanes Note Added: 0001278
2018-10-17 14:28 hanes Target Version => 3.10.0
2018-10-25 15:20 hanes Summary Mysql BIGINT UNSIGNED transform always processed as signed and giver error on larger thant 63 bits numbers. => Mysql BIGINT UNSIGNED transform always processed as signed and giver error on larger than 63 bits numbers.
2018-12-05 14:58 hanes Priority normal => low