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

pt-online-schema change eats data on adding a unique index

    Details

    • Type: Bug
    • Status: Done
    • Priority: High
    • Resolution: Fixed
    • Affects Version/s: 3.0.2
    • Fix Version/s: 3.0.3
    • Component/s: None
    • Labels:
      None

      Description

      Setup
      =====

      DROP TABLE IF EXISTS t1;
      CREATE TABLE t1 (
      id tinyint unsigned auto_increment primary key,
      notunique varchar(200) NOT NULL
      );
      INSERT INTO t1(notunique) VALUES('test01'),('test01'),('test02');
      SELECT * FROM t1;

      Action
      =====

      pt-online-schema-change --alter="ADD UNIQUE INDEX unique_1 (notunique)" --statistics --execute h=127.0.0.1,P=5711,D=test,t=t1,u=msandbox,p=msandbox

      Expected/Desired result
      ===================

      The osc aborts because the data for the unique index is not unique

      Actual result
      ==========

      On MySQL 5.6 data with duplicate entries are dropped silently
      On MySQL 5.7 data with duplicate entries are dropped with a warning 1062 in the stats (probably also for MariaDB 5.5+)

      Related
      ======

      https://bugs.launchpad.net/percona-toolkit/+bug/1099836

      Suggested fix
      ===========

      • Abort on warning 1062 if possible and/or require a --force-unique flag when adding adding an unique index.
      • Don't swap/drop if the original table has less rows than the original table (count inserts? stats?)

      Versions
      =======

      • MySQL 5.7.11
      • pt-online-schema-change 2.2.16 (git b1ca211)

      The cause of the unexpected behavior is that pt-osc uses INSERT LOW_PRIORITY IGNORE to copy chunks, which only raises a warning instead of an error when there is a UNIQUE constraint violation (Percona and Oracle 5.6.35) so pt-osc silently ignores those rows.

      example

      1. pt_online_schema_change:10906 41234 INSERT LOW_PRIORITY IGNORE INTO `xy`.`_table_new` () SELECT ) WHERE ((`user_id` >= ?)) AND ((`user_id` <= ?)) LOCK IN SHARE MODE /pt-online-schema-change 41234 copy nibble/ lower boundary: 18757143 upper boundary: 18773685
      2. pt_online_schema_change:10925 41234 SHOW WARNINGS

      Launchpad: https://bugs.launchpad.net/percona-toolkit/+bug/1545129

        Smart Checklist

          Attachments

            Activity

              People

              • Assignee:
                carlos.salguero Carlos Salguero
                Reporter:
                carlos.salguero Carlos Salguero
              • Votes:
                0 Vote for this issue
                Watchers:
                1 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 - 2 days
                  2d