Uploaded image for project: 'Percona Toolkit'
  1. Percona Toolkit
  2. PT-272

LP #1613915: pt-online-schema-change misses data when table has a compound primary key

    Details

      Description

      **Reported in Launchpad by Will Gunty last update 09-12-2016 15:23:55

      When a table has a compound primary key, and the first item of that key is also not unique (either enforced through a constraint or not-enforced, but still unique due to other methods), there is a chance for data to be lost when copying to the new table.

      This issue exists with 2.2.13+. It may exist with earlier versions, but I have not tested.

      Consider the following schema:

      CREATE TABLE `orgFeatures` (
      `orgId` char(20) NOT NULL,
      `instanceId` char(20) NOT NULL,
      `feature` enum('FOO','BAR','BAT','BAZ','CAT','DOG','DERP','HERP','VANILLA','CHOCOLATE','MINT') NOT NULL DEFAULT 'FOO',
      `isSupported` bit(1) NOT NULL,
      `isEnabled` bit(1) NOT NULL,
      PRIMARY KEY (`instanceId`,`feature`)
      ) ENGINE=InnoDB DEFAULT CHARSET=utf8;

      Altering this table using pt-osc results in a small amount of data loss.

      Here is some debug output. Notice in the lines that I have commented, the lower bound does not match the upper bound of the line above it.

      INSERT LOW_PRIORITY IGNORE INTO `testing`.`_orgFeatures_new` (`orgid`, `instanceid`, `feature`, `issupported`, `isenabled`) SELECT `orgid`, `instanceid`, `feature`, `issupported`, `isenabled` FROM `testing`.`orgFeatures` FORCE INDEX(`PRIMARY`) WHERE ((`instanceid` > ?) OR (`instanceid` = ? AND `feature` >= ?)) AND ((`instanceid` < ?) OR (`instanceid` = ? AND `feature` <= ?)) LOCK IN SHARE MODE /pt-online-schema-change 29182 copy nibble/ lower boundary: 0oa1cych5vs3jdto20x7 0oa1cych5vs3jdto20x7 FOO upper boundary: 0oa1kcu1rZUONQAMHFKX 0oa1kcu1rZUONQAMHFKX FOO

      1. INSERT LOW_PRIORITY IGNORE INTO `testing`.`_orgFeatures_new` (`orgid`, `instanceid`, `feature`, `issupported`, `isenabled`) SELECT `orgid`, `instanceid`, `feature`, `issupported`, `isenabled` FROM `testing`.`orgFeatures` FORCE INDEX(`PRIMARY`) WHERE ((`instanceid` > ?) OR (`instanceid` = ? AND `feature` >= ?)) AND ((`instanceid` < ?) OR (`instanceid` = ? AND `feature` <= ?)) LOCK IN SHARE MODE /pt-online-schema-change 29182 copy nibble/ params: 0oa1kcu3bCTCTPDWEVLB, 0oa1kcu3bCTCTPDWEVLB, BAR, 0oa1t5f0ulqpCp1z80x7, 0oa1t5f0ulqpCp1z80x7, BAR
      2. INSERT LOW_PRIORITY IGNORE INTO `testing`.`_orgFeatures_new` (`orgid`, `instanceid`, `feature`, `issupported`, `isenabled`) SELECT `orgid`, `instanceid`, `feature`, `issupported`, `isenabled` FROM `testing`.`orgFeatures` FORCE INDEX(`PRIMARY`) WHERE ((`instanceid` > ?) OR (`instanceid` = ? AND `feature` >= ?)) AND ((`instanceid` < ?) OR (`instanceid` = ? AND `feature` <= ?)) LOCK IN SHARE MODE /pt-online-schema-change 29182 copy nibble/ lower boundary: 0oa1kcu3bCTCTPDWEVLB 0oa1kcu3bCTCTPDWEVLB BAR upper boundary: 0oa1t5f0ulqpCp1z80x7 0oa1t5f0ulqpCp1z80x7 BAR
        INSERT LOW_PRIORITY IGNORE INTO `testing`.`_orgFeatures_new` (`orgid`, `instanceid`, `feature`, `issupported`, `isenabled`) SELECT `orgid`, `instanceid`, `feature`, `issupported`, `isenabled` FROM `testing`.`orgFeatures` FORCE INDEX(`PRIMARY`) WHERE ((`instanceid` > ?) OR (`instanceid` = ? AND `feature` >= ?)) AND ((`instanceid` < ?) OR (`instanceid` = ? AND `feature` <= ?)) LOCK IN SHARE MODE /pt-online-schema-change 29182 copy nibble/ params: 0oa1t5f0ulqpCp1z80x7, 0oa1t5f0ulqpCp1z80x7, BAZ, 0oa26z4oytDgBtxhM0x7, 0oa26z4oytDgBtxhM0x7, BAT
        INSERT LOW_PRIORITY IGNORE INTO `testing`.`_orgFeatures_new` (`orgid`, `instanceid`, `feature`, `issupported`, `isenabled`) SELECT `orgid`, `instanceid`, `feature`, `issupported`, `isenabled` FROM `testing`.`orgFeatures` FORCE INDEX(`PRIMARY`) WHERE ((`instanceid` > ?) OR (`instanceid` = ? AND `feature` >= ?)) AND ((`instanceid` < ?) OR (`instanceid` = ? AND `feature` <= ?)) LOCK IN SHARE MODE /pt-online-schema-change 29182 copy nibble/ lower boundary: 0oa1t5f0ulqpCp1z80x7 0oa1t5f0ulqpCp1z80x7 BAZ upper boundary: 0oa26z4oytDgBtxhM0x7 0oa26z4oytDgBtxhM0x7 BAT
      3. INSERT LOW_PRIORITY IGNORE INTO `testing`.`_orgFeatures_new` (`orgid`, `instanceid`, `feature`, `issupported`, `isenabled`) SELECT `orgid`, `instanceid`, `feature`, `issupported`, `isenabled` FROM `testing`.`orgFeatures` FORCE INDEX(`PRIMARY`) WHERE ((`instanceid` > ?) OR (`instanceid` = ? AND `feature` >= ?)) AND ((`instanceid` < ?) OR (`instanceid` = ? AND `feature` <= ?)) LOCK IN SHARE MODE /pt-online-schema-change 29182 copy nibble/ params: 0oa26z56wQCYIZUODNAS, 0oa26z56wQCYIZUODNAS, FOO, 0oa2mruemwNu34cwH0x7, 0oa2mruemwNu34cwH0x7, CAT
      4. INSERT LOW_PRIORITY IGNORE INTO `testing`.`_orgFeatures_new` (`orgid`, `instanceid`, `feature`, `issupported`, `isenabled`) SELECT `orgid`, `instanceid`, `feature`, `issupported`, `isenabled` FROM `testing`.`orgFeatures` FORCE INDEX(`PRIMARY`) WHERE ((`instanceid` > ?) OR (`instanceid` = ? AND `feature` >= ?)) AND ((`instanceid` < ?) OR (`instanceid` = ? AND `feature` <= ?)) LOCK IN SHARE MODE /pt-online-schema-change 29182 copy nibble/ lower boundary: 0oa26z56wQCYIZUODNAS 0oa26z56wQCYIZUODNAS FOO upper boundary: 0oa2mruemwNu34cwH0x7 0oa2mruemwNu34cwH0x7 CAT
        INSERT LOW_PRIORITY IGNORE INTO `testing`.`_orgFeatures_new` (`orgid`, `instanceid`, `feature`, `issupported`, `isenabled`) SELECT `orgid`, `instanceid`, `feature`, `issupported`, `isenabled` FROM `testing`.`orgFeatures` FORCE INDEX(`PRIMARY`) WHERE ((`instanceid` > ?) OR (`instanceid` = ? AND `feature` >= ?)) AND ((`instanceid` < ?) OR (`instanceid` = ? AND `feature` <= ?)) LOCK IN SHARE MODE /pt-online-schema-change 29182 copy nibble/ params: 0oa2mruemwNu34cwH0x7, 0oa2mruemwNu34cwH0x7, DOG, 0oa34pwo3dGMtIvOp0x7, 0oa34pwo3dGMtIvOp0x7, VANILLA

        Smart Checklist

          Attachments

            Issue Links

              Activity

                People

                • Assignee:
                  Unassigned
                  Reporter:
                  lpjirasync lpjirasync (Inactive)
                • Votes:
                  0 Vote for this issue
                  Watchers:
                  4 Start watching this issue

                  Dates

                  • Created:
                    Updated:
                    Resolved: