SET GLOBAL debug = "+d,ddl_buf_add_two"; # # Test#1 : Try and fail to rebuild the table's PK from 2 columns to 1 column with duplicates. # CREATE TABLE t1 ( id0 int, id1 int, PRIMARY KEY (id0, id1) ) ENGINE=InnoDB; INSERT INTO t1(id0, id1) VALUES (1, 1), (2, 2), (2, 3); ALTER TABLE t1 DROP PRIMARY KEY, ADD PRIMARY KEY(id0), ALGORITHM = INPLACE; ERROR 23000: Duplicate entry '2' for key 't1.PRIMARY' # Check that records are present. SELECT * FROM t1 ORDER BY id0, id1; id0 id1 1 1 2 2 2 3 DROP TABLE t1; # # Test#2 : Try and fail to rebuild the table's PK from 2 columns to 1 column with duplicates. # Then succeed doing it on second set of two columns with unique rows. # CREATE TABLE t2 ( id0 int, id1 int, id2 int, PRIMARY KEY (id0, id2) ) ENGINE=InnoDB; INSERT INTO t2(id0, id1, id2) VALUES (1, 2, 3), (1, 2, 4), (1, 3, 5); ALTER TABLE t2 DROP PRIMARY KEY, ADD PRIMARY KEY(id1), ALGORITHM = INPLACE; ERROR 23000: Duplicate entry '2' for key 't2.PRIMARY' ALTER TABLE t2 DROP PRIMARY KEY, ADD PRIMARY KEY(id1, id2), ALGORITHM = INPLACE; # Check that records are present. SELECT * FROM t2 ORDER BY id0, id1, id2; id0 id1 id2 1 2 3 1 2 4 1 3 5 DROP TABLE t2; # # Test#3 : Try and fail to rebuild the table's PK from unique 1 column to # set of 2 columns with duplicate rows. # CREATE TABLE t3 ( id0 int, id1 char(2), id2 varchar(3), id3 date, id4 bool, PRIMARY KEY (id0) ) ENGINE=InnoDB; INSERT INTO t3(id0, id1, id2, id3, id4) VALUES (1, '22', '3', '2020-01-01', true), (2, '33', '4', '2020-02-01', false), (3, '22', '3', '2020-01-01', true); ALTER TABLE t3 DROP PRIMARY KEY, ADD PRIMARY KEY(id1, id2), ALGORITHM = INPLACE; ERROR 23000: Duplicate entry '22-3' for key 't3.PRIMARY' # # Test#4 : Change primary key to all 5 columns. Then fail to change to # last 4 ones due to duplicates. # ALTER TABLE t3 DROP PRIMARY KEY, ADD PRIMARY KEY(id0, id1, id2, id3, id4), ALGORITHM = INPLACE; ALTER TABLE t3 DROP PRIMARY KEY, ADD PRIMARY KEY(id1, id2, id3, id4), ALGORITHM = INPLACE; ERROR 23000: Duplicate entry '22-3-2020-01-01-1' for key 't3.PRIMARY' # Check that records are present. SELECT * FROM t3 ORDER BY id0, id1, id2, id3, id4; id0 id1 id2 id3 id4 1 22 3 2020-01-01 1 2 33 4 2020-02-01 0 3 22 3 2020-01-01 1 DROP TABLE t3; SET GLOBAL debug = "-d,ddl_buf_add_two";