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

[Daniël van Eeden]

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)

[Miklos Szel (miklos-szel)]

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

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
# 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)]

Confirmed affects PT 3.0.2 as well.

[Carlos Salguero (carlos-salguero)]

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 ..
1..8
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
ok
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

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-271