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
Versions
=======
- MySQL 5.7.11
- pt-online-schema-change 2.2.16 (git b1ca211)
Setup
=====
DROP TABLE IF EXISTS t1; 'test01' ),('test01' ),('test02' );
CREATE TABLE t1 (
id tinyint unsigned auto_increment primary key,
notunique varchar(200) NOT NULL
);
INSERT INTO t1(notunique) VALUES(
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
Versions
=======
- MySQL 5.7.11 schema- change 2.2.16 (git b1ca211)
- pt-online-