Critical For a given example table:
CREATE TABLE `upsert` ( `col1` varbinary(100) NOT NULL, `col2` varbinary(100) NOT NULL, `col3` bigint(20) unsigned NOT NULL AUTO_INCREMENT, PRIMARY KEY (`col3`), UNIQUE KEY `col2` (`col2`), UNIQUE KEY `col1` (`col1`) ) ENGINE=InnoDB
During multi concurrent insert test, where primary, auto-inc column is unset and other unique columns have always unique values, occasionally, on cluster resize (like when nodes leaving/joining), there are conflicting auto_increment values assigned occasionally.
With INSERT INTO ... ON DUPLICATE KEY UPDATE, this leads to overwriting unique rows, hence loosing data!
Example binary log event:
#180604 14:43:14 server id 1 end_log_pos 236257 CRC32 0xae59e3b1 Write_rows: table id 844 flags: STMT_END_F ### INSERT INTO `upsert`.`upsert` ### SET ### @1='8103' ### @2='8103' ### @3=4162 # at 236257 #180604 14:43:14 server id 1 end_log_pos 236288 CRC32 0xd7ea9392 Xid = 1441269
#180604 14:43:14 server id 1 end_log_pos 238563 CRC32 0x32798862 Update_rows: table id 844 flags: STMT_END_F ### UPDATE `upsert`.`upsert` ### WHERE ### @1='8103' ### @2='8103' ### @3=4162 ### SET ### @1='7101' ### @2='7101' ### @3=4162 # at 238563 #180604 14:43:14 server id 1 end_log_pos 238594 CRC32 0xc5687490 Xid = 1441277
Example scripts for test case, error log and binlog attached.
Reproduced on both PXC 5.7.19 and 5.7.21, in 3-node cluster and 5-node cluster.
- mentioned in
-
Page Loading...
