Uploaded image for project: 'Percona XtraDB Cluster'
  1. Percona XtraDB Cluster
  2. PXC-2128

Duplicate auto_increment values set for concurrent sessions on cluster reconfiguration

    Details

    • Type: Bug
    • Status: Done
    • Priority: Critical
    • Resolution: Fixed
    • Affects Version/s: 5.7.19-29.22, 5.7.21-29.26
    • Fix Version/s: 5.6.40, 5.7.22
    • Component/s: None
    • Security Level: Public Bug
    • Labels:

      Description

      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.

       

       

        Attachments

        1. auto-inc_test.php
          0.9 kB
        2. pxc01_1.err.gz
          1.57 MB
        3. pxc01-bin.000933.sql.gz
          578 kB
        4. test.sh
          1 kB

          Issue Links

            Expenses

              Activity

                People

                • Assignee:
                  krunal.bauskar Krunal Bauskar
                  Reporter:
                  przemyslaw.malkowski@percona.com Przemyslaw Malkowski
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  4 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved:

                    Time Tracking

                    Estimated:
                    Original Estimate - Not Specified
                    Not Specified
                    Remaining:
                    Remaining Estimate - 0 minutes
                    0m
                    Logged:
                    Time Spent - 3 days, 4 hours, 4 minutes
                    3d 4h 4m