View Issue Details

Related ChangesetsJump to NotesJump to History
IDProjectCategoryView StatusDate SubmittedLast Update
0003251SymmetricDSBugpublic2017-09-15 17:062022-10-04 15:15
Reporterivan.konev Assigned Toemiller  
Prioritylow 
Status closedResolutionfixed 
Product Version3.8.24 
Target Version3.14.2Fixed in Version3.14.2 
Summary0003251: Mysql BIGINT UNSIGNED processed as signed and gives 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]
Tagsextract

Activities

hanes

2018-10-17 14: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
     

Related Changesets

SymmetricDS: 3.14 73ae7819

2022-09-02 15:08:09

evan-miller-jumpmind

Details Diff 		0003251: Fixed inserts and updates for MySQL unsigned bigints larger than Long.MAX_VALUE Affected Issues
0003251
mod - symmetric-jdbc/src/main/java/org/jumpmind/db/platform/mysql/MySqlJdbcSqlTemplate.java Diff File

Issue History

Date Modified Username Field Change
2017-09-15 17:06 ivan.konev New Issue
2017-09-15 17:06 ivan.konev Tag Attached: extract
2018-10-17 14:28 hanes Status new => confirmed
2018-10-17 14:28 hanes Note Added: 0001278
2018-10-17 18:28 hanes Target Version => 3.10.0
2018-10-25 19: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 19:58 hanes Priority normal => low
2019-03-14 12:39 admin Target Version 3.10.0 => 3.10.1
2019-04-23 13:46 elong Target Version 3.10.1 => 3.10.2
2019-04-26 15:17 elong Summary Mysql BIGINT UNSIGNED transform always processed as signed and giver error on larger than 63 bits numbers. => Mysql BIGINT UNSIGNED processed as signed and gives error on larger than 63 bits numbers.
2019-05-08 12:51 admin Target Version 3.10.2 => 3.10.3
2019-06-24 17:45 elong Target Version 3.10.3 => 3.10.4
2019-08-09 19:41 elong Target Version 3.10.4 => 3.10.5
2019-10-31 14:59 admin Target Version 3.10.5 => 3.10.6
2019-11-14 19:47 elong Target Version 3.10.6 => 3.10.7
2019-12-09 15:25 admin Target Version 3.10.7 => 3.10.8
2020-01-02 14:40 admin Target Version 3.10.8 => 3.10.9
2020-01-22 19:03 elong Target Version 3.10.9 => 3.10.10
2020-02-14 13:46 elong Target Version 3.10.10 => 3.10.11
2020-03-16 20:14 admin Target Version 3.10.11 => 3.10.12
2020-05-26 12:28 elong Target Version 3.10.12 => 3.11.10
2020-06-24 20:14 elong Target Version 3.11.10 => 3.12.2
2020-07-20 13:06 elong Target Version 3.12.2 => 3.12.3
2020-08-31 19:47 elong Target Version 3.12.3 => 3.12.4
2020-09-23 15:32 admin Target Version 3.12.4 => 3.12.5
2020-11-09 13:45 admin Target Version 3.12.5 => 3.12.6
2021-01-08 20:42 admin Target Version 3.12.6 => 3.12.7
2021-02-25 13:01 admin Target Version 3.12.7 => 3.12.8
2021-03-30 19:13 admin Target Version 3.12.8 => 3.12.9
2021-04-28 14:41 admin Target Version 3.12.9 => 3.12.10
2021-05-28 11:54 admin Target Version 3.12.10 => 3.12.11
2021-07-13 12:43 admin Target Version 3.12.11 => 3.12.12
2021-11-03 12:11 elong Target Version 3.12.12 => 3.12.13
2021-12-11 17:11 admin Target Version 3.12.13 => 3.12.14
2021-12-20 13:55 admin Target Version 3.12.14 => 3.12.15
2022-01-13 13:34 elong Target Version 3.12.15 => 3.14.0
2022-07-25 16:21 elong Target Version 3.14.0 =>
2022-09-02 15:08 emiller Assigned To => emiller
2022-09-02 15:08 emiller Status confirmed => resolved
2022-09-02 15:08 emiller Resolution open => fixed
2022-09-02 15:08 emiller Fixed in Version => 3.14.2
2022-09-02 16:00 Changeset attached => SymmetricDS 3.14 73ae7819
2022-10-04 15:15 admin Status resolved => closed