pt-online-schema change eats data on adding a unique index
General
Escalation
General
Escalation
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+)
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
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
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
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
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
Launchpad: https://bugs.launchpad.net/percona-toolkit/+bug/1545129