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

Bug #1545129 reported by Daniël van Eeden on 2016-02-12
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona Toolkit moved to
Fix Released
Carlos Salguero

Bug Description


  id tinyint unsigned auto_increment primary key,
  notunique varchar(200) NOT NULL
INSERT INTO t1(notunique) VALUES('test01'),('test01'),('test02');


pt-online-schema-change --alter="ADD UNIQUE INDEX unique_1 (notunique)" --statistics --execute h=,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+)


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?)


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

description: updated
Changed in percona-toolkit:
importance: Undecided → Critical
assignee: nobody → Carlos Salguero (carlos-salguero)
Miklos Szel (miklos-szel) wrote :

I can confirm that the issue still persists in the latest stable (2.2.20) and it caused us unexpected data loss.

Miklos Szel (miklos-szel) wrote :

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.

# 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
# pt_online_schema_change:10925 41234 SHOW WARNINGS

Jericho Rivera (jericho-rivera) wrote :

Confirmed affects PT 3.0.2 as well.

Changed in percona-toolkit:
status: New → Confirmed
tags: added: pt116

Added --use-insert-ignore to force/prevent using IGNORE on INSERTS.
If the program detects you are trying to add an UNIQUE KEY, you MUST specify --[no]use-insert-ignore
All tests
All tests successful.
Files=21, Tests=140, 522 wallclock secs ( 0.25 usr 0.04 sys + 42.16 cusr 9.51 csys = 51.96 CPU)
Result: PASS
New tests
prove -v -w t/pt-online-schema-change/pt-116.t
t/pt-online-schema-change/pt-116.t ..
ok 1 - Need to specify use-insert-ignore
ok 2 - Error adding unique index not using insert ignore
ok 3 - Got error adding unique index (exit status != 0)
ok 4 - ALTER ADD UNIQUE key on a field having duplicated values
ok 5 - Error adding unique index not using insert ignore
ok 6 - Added unique index and some rows got lost (exit status = 0)
ok 7 - Added unique index and some rows got lost (row count = original - 1)
ok 8 - Sandbox servers
All tests successful.
Files=1, Tests=8, 5 wallclock secs ( 0.06 usr 0.01 sys + 1.00 cusr 0.18 csys = 1.25 CPU)
Result: PASS

Changed in percona-toolkit:
status: Confirmed → Fix Committed
milestone: none → 3.0.3
Changed in percona-toolkit:
status: Fix Committed → Fix Released

Percona now uses JIRA for bug reports so this bug report is migrated to:

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers