# Establish another connection (user=root) for concurrent DML # Test suite/xengine/t/online_ddl_prefix_composite_key.inc with latin1 and latin1_bin ################################################################### case 1 non-unique secondary key ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET latin1 COLLATE latin1_bin; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### ALTER TABLE t1 ADD KEY pre_sk(b(6)), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); #### composite index #### ALTER TABLE t1 ADD KEY com_sk(c, d), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(4, '4444444', 8, '4d'); #### composite index with prefix #### ALTER TABLE t1 ADD KEY pre_com_sk(b(4), c), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(5, '5555555', 10, '5e'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE latin1_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`id`), KEY `pre_sk` (`b`(6)), KEY `com_sk` (`c`,`d`), KEY `pre_com_sk` (`b`(4),`c`) ) ENGINE=XENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_bin ALTER TABLE t1 DROP KEY pre_sk, DROP KEY com_sk, DROP KEY pre_com_sk, ALGORITHM=INPLACE, LOCK=DEFAULT; DROP TABLE t1; ################################################################### case 2 unique secondary key ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET latin1 COLLATE latin1_bin; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(3, '2222333', 6, '3c'); ERROR 23000: Duplicate entry '2222' for key 'pre_uk' INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); #### composite index #### ALTER TABLE t1 ADD UNIQUE KEY com_uk(c, d), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(4, '4444444', 6, '3c'); ERROR 23000: Duplicate entry '6-3c' for key 'com_uk' INSERT INTO t1 VALUES(4, '4444444', 8, '4d'); #### composite index with prefix #### ALTER TABLE t1 ADD KEY pre_com_uk(b(6), c), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(5, '4444445', 8, '5e'); ERROR 23000: Duplicate entry '4444' for key 'pre_uk' INSERT INTO t1 VALUES(5, '5555555', 10, '5e'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE latin1_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `com_uk` (`c`,`d`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_com_uk` (`b`(6),`c`) ) ENGINE=XENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_bin ALTER TABLE t1 DROP KEY pre_uk, DROP KEY com_uk, DROP KEY pre_com_uk, ALGORITHM=INPLACE, LOCK=DEFAULT; DROP TABLE t1; ################################################################### case 3 unique secondary key with concurrent DML ################################################################### case 3.1 has unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET latin1 COLLATE latin1_bin; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(6)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(4, '2222333', 8, '4d'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '2222' for key 'pre_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE latin1_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_bin INSERT INTO t1 VALUES(4, 'xxxxxxx', 8, '4d'); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d #### composite index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY com_uk(c, d), ADD KEY com_sk(b, c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(5, '5555555', 10, '5e'); INSERT INTO t1 VALUES(6, '6666666', 10, '5e'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '10-5e' for key 'com_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE latin1_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_bin INSERT INTO t1 VALUES(5, 'yyyyyyy', 10, '5eeee'); ERROR 23000: Duplicate entry '5' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e #### composite index with prefix #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_com_uk(c, d(4)), ADD KEY pre_com_sk(b(4), c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(7, '7777777', 14, '7e7e7e7e'); INSERT INTO t1 VALUES(8, '8888888', 14, '7e7e8f8f'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e 7 7777777 14 7e7e7e7e 8 8888888 14 7e7e8f8f SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '10-5e' for key 'pre_com_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE latin1_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_bin INSERT INTO t1 VALUES(5, 'yyyyyyy', 10, '5e'); ERROR 23000: Duplicate entry '5' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e 7 7777777 14 7e7e7e7e 8 8888888 14 7e7e8f8f DROP TABLE t1; ################################################################### case 3.2 no unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET latin1 COLLATE latin1_bin; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(2, '3333222', 8, '3c'); ERROR 23000: Duplicate entry '2' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE latin1_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_sk` (`d`(4)) ) ENGINE=XENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_bin INSERT INTO t1 VALUES(4, '3333333', 8, '3c'); ERROR 23000: Duplicate entry '3333' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c #### composite index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY com_uk(c, d), ADD KEY com_sk(b, c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(4, '4444444', 8, '4d'); INSERT INTO t1 VALUES(5, '4444555', 8, '5d'); ERROR 23000: Duplicate entry '4444' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE latin1_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `com_uk` (`c`,`d`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_sk` (`d`(4)), KEY `com_sk` (`b`,`c`) ) ENGINE=XENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_bin INSERT INTO t1 VALUES(4, 'yyyyyyy', 8, '8c'); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d ALTER TABLE t1 DROP KEY com_uk, DROP KEY com_sk, ALGORITHM=INPLACE, LOCK=DEFAULT; #### composite index with prefix #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_com_uk(c, d(4)), ADD KEY pre_com_sk(b(4), c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(5, '5555555', 10, '5e5e5e5e'); INSERT INTO t1 VALUES(6, '5555666', 10, '5e5e6f6f'); ERROR 23000: Duplicate entry '5555' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e5e5e5e SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE latin1_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pre_uk` (`b`(4)), UNIQUE KEY `pre_com_uk` (`c`,`d`(4)), KEY `pre_sk` (`d`(4)), KEY `pre_com_sk` (`b`(4),`c`) ) ENGINE=XENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_bin INSERT INTO t1 VALUES(5, 'yyyyyyy', 10, '8c'); ERROR 23000: Duplicate entry '5' for key 'PRIMARY' INSERT INTO t1 VALUES(6, 'yyyyyyy', 10, '8c'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e5e5e5e 6 yyyyyyy 10 8c ALTER TABLE t1 DROP KEY pre_uk, DROP KEY pre_com_uk, DROP KEY pre_sk, DROP KEY pre_com_sk, ALGORITHM=INPLACE, LOCK=DEFAULT; DROP TABLE t1; ################################################################### case 4 prefix key on blob field ################################################################### case 4.1 has unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b TEXT, c int, d CHAR(10))CHARSET latin1 COLLATE latin1_bin; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(6)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(4, '2222333', 8, '4d'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '2222' for key 'pre_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` text COLLATE latin1_bin, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_bin INSERT INTO t1 VALUES(4, 'xxxxxxx', 8, '4d'); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d DROP TABLE t1; ################################################################### case 4.2 no unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b TEXT, c int, d CHAR(10))CHARSET latin1 COLLATE latin1_bin; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(2, '3333222', 8, '3c'); ERROR 23000: Duplicate entry '2' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` text COLLATE latin1_bin, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE latin1_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_sk` (`d`(4)) ) ENGINE=XENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_bin INSERT INTO t1 VALUES(4, '3333333', 8, '3c'); ERROR 23000: Duplicate entry '3333' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c DROP TABLE t1; ################################################################### case 4.3 has unique conflict on PRIMARY KEY ################################################################### CREATE TABLE t1(b TEXT NOT NULL, c int, d CHAR(10))CHARSET latin1 COLLATE latin1_bin; INSERT INTO t1 VALUES('1111111', 2, '1a'); INSERT INTO t1 VALUES('2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_copy_ddl_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD PRIMARY KEY(b(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES('3333333', 6, '3c'); INSERT INTO t1 VALUES('3333444', 8, '4d'); SELECT * FROM t1; b c d 1111111 2 1a 2222222 4 2b 3333333 6 3c 3333444 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '3333' for key 'PRIMARY' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `b` text COLLATE latin1_bin NOT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE latin1_bin DEFAULT NULL ) ENGINE=XENGINE DEFAULT CHARSET=latin1 COLLATE=latin1_bin SELECT * FROM t1; b c d 1111111 2 1a 2222222 4 2b 3333333 6 3c 3333444 8 4d DROP TABLE t1; # Test suite/xengine/t/online_ddl_prefix_composite_key.inc with gbk and gbk_bin ################################################################### case 1 non-unique secondary key ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET gbk COLLATE gbk_bin; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### ALTER TABLE t1 ADD KEY pre_sk(b(6)), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); #### composite index #### ALTER TABLE t1 ADD KEY com_sk(c, d), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(4, '4444444', 8, '4d'); #### composite index with prefix #### ALTER TABLE t1 ADD KEY pre_com_sk(b(4), c), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(5, '5555555', 10, '5e'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE gbk_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE gbk_bin DEFAULT NULL, PRIMARY KEY (`id`), KEY `pre_sk` (`b`(6)), KEY `com_sk` (`c`,`d`), KEY `pre_com_sk` (`b`(4),`c`) ) ENGINE=XENGINE DEFAULT CHARSET=gbk COLLATE=gbk_bin ALTER TABLE t1 DROP KEY pre_sk, DROP KEY com_sk, DROP KEY pre_com_sk, ALGORITHM=INPLACE, LOCK=DEFAULT; DROP TABLE t1; ################################################################### case 2 unique secondary key ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET gbk COLLATE gbk_bin; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(3, '2222333', 6, '3c'); ERROR 23000: Duplicate entry '2222' for key 'pre_uk' INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); #### composite index #### ALTER TABLE t1 ADD UNIQUE KEY com_uk(c, d), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(4, '4444444', 6, '3c'); ERROR 23000: Duplicate entry '6-3c' for key 'com_uk' INSERT INTO t1 VALUES(4, '4444444', 8, '4d'); #### composite index with prefix #### ALTER TABLE t1 ADD KEY pre_com_uk(b(6), c), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(5, '4444445', 8, '5e'); ERROR 23000: Duplicate entry '4444' for key 'pre_uk' INSERT INTO t1 VALUES(5, '5555555', 10, '5e'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE gbk_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE gbk_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `com_uk` (`c`,`d`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_com_uk` (`b`(6),`c`) ) ENGINE=XENGINE DEFAULT CHARSET=gbk COLLATE=gbk_bin ALTER TABLE t1 DROP KEY pre_uk, DROP KEY com_uk, DROP KEY pre_com_uk, ALGORITHM=INPLACE, LOCK=DEFAULT; DROP TABLE t1; ################################################################### case 3 unique secondary key with concurrent DML ################################################################### case 3.1 has unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET gbk COLLATE gbk_bin; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(6)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(4, '2222333', 8, '4d'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '2222' for key 'pre_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE gbk_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE gbk_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=gbk COLLATE=gbk_bin INSERT INTO t1 VALUES(4, 'xxxxxxx', 8, '4d'); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d #### composite index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY com_uk(c, d), ADD KEY com_sk(b, c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(5, '5555555', 10, '5e'); INSERT INTO t1 VALUES(6, '6666666', 10, '5e'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '10-5e' for key 'com_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE gbk_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE gbk_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=gbk COLLATE=gbk_bin INSERT INTO t1 VALUES(5, 'yyyyyyy', 10, '5eeee'); ERROR 23000: Duplicate entry '5' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e #### composite index with prefix #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_com_uk(c, d(4)), ADD KEY pre_com_sk(b(4), c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(7, '7777777', 14, '7e7e7e7e'); INSERT INTO t1 VALUES(8, '8888888', 14, '7e7e8f8f'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e 7 7777777 14 7e7e7e7e 8 8888888 14 7e7e8f8f SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '10-5e' for key 'pre_com_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE gbk_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE gbk_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=gbk COLLATE=gbk_bin INSERT INTO t1 VALUES(5, 'yyyyyyy', 10, '5e'); ERROR 23000: Duplicate entry '5' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e 7 7777777 14 7e7e7e7e 8 8888888 14 7e7e8f8f DROP TABLE t1; ################################################################### case 3.2 no unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET gbk COLLATE gbk_bin; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(2, '3333222', 8, '3c'); ERROR 23000: Duplicate entry '2' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE gbk_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE gbk_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_sk` (`d`(4)) ) ENGINE=XENGINE DEFAULT CHARSET=gbk COLLATE=gbk_bin INSERT INTO t1 VALUES(4, '3333333', 8, '3c'); ERROR 23000: Duplicate entry '3333' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c #### composite index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY com_uk(c, d), ADD KEY com_sk(b, c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(4, '4444444', 8, '4d'); INSERT INTO t1 VALUES(5, '4444555', 8, '5d'); ERROR 23000: Duplicate entry '4444' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE gbk_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE gbk_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `com_uk` (`c`,`d`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_sk` (`d`(4)), KEY `com_sk` (`b`,`c`) ) ENGINE=XENGINE DEFAULT CHARSET=gbk COLLATE=gbk_bin INSERT INTO t1 VALUES(4, 'yyyyyyy', 8, '8c'); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d ALTER TABLE t1 DROP KEY com_uk, DROP KEY com_sk, ALGORITHM=INPLACE, LOCK=DEFAULT; #### composite index with prefix #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_com_uk(c, d(4)), ADD KEY pre_com_sk(b(4), c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(5, '5555555', 10, '5e5e5e5e'); INSERT INTO t1 VALUES(6, '5555666', 10, '5e5e6f6f'); ERROR 23000: Duplicate entry '5555' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e5e5e5e SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE gbk_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE gbk_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pre_uk` (`b`(4)), UNIQUE KEY `pre_com_uk` (`c`,`d`(4)), KEY `pre_sk` (`d`(4)), KEY `pre_com_sk` (`b`(4),`c`) ) ENGINE=XENGINE DEFAULT CHARSET=gbk COLLATE=gbk_bin INSERT INTO t1 VALUES(5, 'yyyyyyy', 10, '8c'); ERROR 23000: Duplicate entry '5' for key 'PRIMARY' INSERT INTO t1 VALUES(6, 'yyyyyyy', 10, '8c'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e5e5e5e 6 yyyyyyy 10 8c ALTER TABLE t1 DROP KEY pre_uk, DROP KEY pre_com_uk, DROP KEY pre_sk, DROP KEY pre_com_sk, ALGORITHM=INPLACE, LOCK=DEFAULT; DROP TABLE t1; ################################################################### case 4 prefix key on blob field ################################################################### case 4.1 has unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b TEXT, c int, d CHAR(10))CHARSET gbk COLLATE gbk_bin; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(6)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(4, '2222333', 8, '4d'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '2222' for key 'pre_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` text COLLATE gbk_bin, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE gbk_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=gbk COLLATE=gbk_bin INSERT INTO t1 VALUES(4, 'xxxxxxx', 8, '4d'); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d DROP TABLE t1; ################################################################### case 4.2 no unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b TEXT, c int, d CHAR(10))CHARSET gbk COLLATE gbk_bin; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(2, '3333222', 8, '3c'); ERROR 23000: Duplicate entry '2' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` text COLLATE gbk_bin, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE gbk_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_sk` (`d`(4)) ) ENGINE=XENGINE DEFAULT CHARSET=gbk COLLATE=gbk_bin INSERT INTO t1 VALUES(4, '3333333', 8, '3c'); ERROR 23000: Duplicate entry '3333' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c DROP TABLE t1; ################################################################### case 4.3 has unique conflict on PRIMARY KEY ################################################################### CREATE TABLE t1(b TEXT NOT NULL, c int, d CHAR(10))CHARSET gbk COLLATE gbk_bin; INSERT INTO t1 VALUES('1111111', 2, '1a'); INSERT INTO t1 VALUES('2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_copy_ddl_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD PRIMARY KEY(b(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES('3333333', 6, '3c'); INSERT INTO t1 VALUES('3333444', 8, '4d'); SELECT * FROM t1; b c d 1111111 2 1a 2222222 4 2b 3333333 6 3c 3333444 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '3333' for key 'PRIMARY' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `b` text COLLATE gbk_bin NOT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE gbk_bin DEFAULT NULL ) ENGINE=XENGINE DEFAULT CHARSET=gbk COLLATE=gbk_bin SELECT * FROM t1; b c d 1111111 2 1a 2222222 4 2b 3333333 6 3c 3333444 8 4d DROP TABLE t1; # Test suite/xengine/t/online_ddl_prefix_composite_key.inc with gbk and gbk_chinese_ci ################################################################### case 1 non-unique secondary key ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET gbk COLLATE gbk_chinese_ci; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### ALTER TABLE t1 ADD KEY pre_sk(b(6)), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); #### composite index #### ALTER TABLE t1 ADD KEY com_sk(c, d), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(4, '4444444', 8, '4d'); #### composite index with prefix #### ALTER TABLE t1 ADD KEY pre_com_sk(b(4), c), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(5, '5555555', 10, '5e'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `pre_sk` (`b`(6)), KEY `com_sk` (`c`,`d`), KEY `pre_com_sk` (`b`(4),`c`) ) ENGINE=XENGINE DEFAULT CHARSET=gbk ALTER TABLE t1 DROP KEY pre_sk, DROP KEY com_sk, DROP KEY pre_com_sk, ALGORITHM=INPLACE, LOCK=DEFAULT; DROP TABLE t1; ################################################################### case 2 unique secondary key ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET gbk COLLATE gbk_chinese_ci; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(3, '2222333', 6, '3c'); ERROR 23000: Duplicate entry '2222' for key 'pre_uk' INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); #### composite index #### ALTER TABLE t1 ADD UNIQUE KEY com_uk(c, d), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(4, '4444444', 6, '3c'); ERROR 23000: Duplicate entry '6-3c' for key 'com_uk' INSERT INTO t1 VALUES(4, '4444444', 8, '4d'); #### composite index with prefix #### ALTER TABLE t1 ADD KEY pre_com_uk(b(6), c), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(5, '4444445', 8, '5e'); ERROR 23000: Duplicate entry '4444' for key 'pre_uk' INSERT INTO t1 VALUES(5, '5555555', 10, '5e'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `com_uk` (`c`,`d`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_com_uk` (`b`(6),`c`) ) ENGINE=XENGINE DEFAULT CHARSET=gbk ALTER TABLE t1 DROP KEY pre_uk, DROP KEY com_uk, DROP KEY pre_com_uk, ALGORITHM=INPLACE, LOCK=DEFAULT; DROP TABLE t1; ################################################################### case 3 unique secondary key with concurrent DML ################################################################### case 3.1 has unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET gbk COLLATE gbk_chinese_ci; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(6)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(4, '2222333', 8, '4d'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '2222' for key 'pre_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=gbk INSERT INTO t1 VALUES(4, 'xxxxxxx', 8, '4d'); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d #### composite index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY com_uk(c, d), ADD KEY com_sk(b, c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(5, '5555555', 10, '5e'); INSERT INTO t1 VALUES(6, '6666666', 10, '5e'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '10-5e' for key 'com_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=gbk INSERT INTO t1 VALUES(5, 'yyyyyyy', 10, '5eeee'); ERROR 23000: Duplicate entry '5' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e #### composite index with prefix #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_com_uk(c, d(4)), ADD KEY pre_com_sk(b(4), c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(7, '7777777', 14, '7e7e7e7e'); INSERT INTO t1 VALUES(8, '8888888', 14, '7e7e8f8f'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e 7 7777777 14 7e7e7e7e 8 8888888 14 7e7e8f8f SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '10-5e' for key 'pre_com_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=gbk INSERT INTO t1 VALUES(5, 'yyyyyyy', 10, '5e'); ERROR 23000: Duplicate entry '5' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e 7 7777777 14 7e7e7e7e 8 8888888 14 7e7e8f8f DROP TABLE t1; ################################################################### case 3.2 no unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET gbk COLLATE gbk_chinese_ci; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(2, '3333222', 8, '3c'); ERROR 23000: Duplicate entry '2' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_sk` (`d`(4)) ) ENGINE=XENGINE DEFAULT CHARSET=gbk INSERT INTO t1 VALUES(4, '3333333', 8, '3c'); ERROR 23000: Duplicate entry '3333' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c #### composite index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY com_uk(c, d), ADD KEY com_sk(b, c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(4, '4444444', 8, '4d'); INSERT INTO t1 VALUES(5, '4444555', 8, '5d'); ERROR 23000: Duplicate entry '4444' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `com_uk` (`c`,`d`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_sk` (`d`(4)), KEY `com_sk` (`b`,`c`) ) ENGINE=XENGINE DEFAULT CHARSET=gbk INSERT INTO t1 VALUES(4, 'yyyyyyy', 8, '8c'); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d ALTER TABLE t1 DROP KEY com_uk, DROP KEY com_sk, ALGORITHM=INPLACE, LOCK=DEFAULT; #### composite index with prefix #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_com_uk(c, d(4)), ADD KEY pre_com_sk(b(4), c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(5, '5555555', 10, '5e5e5e5e'); INSERT INTO t1 VALUES(6, '5555666', 10, '5e5e6f6f'); ERROR 23000: Duplicate entry '5555' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e5e5e5e SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pre_uk` (`b`(4)), UNIQUE KEY `pre_com_uk` (`c`,`d`(4)), KEY `pre_sk` (`d`(4)), KEY `pre_com_sk` (`b`(4),`c`) ) ENGINE=XENGINE DEFAULT CHARSET=gbk INSERT INTO t1 VALUES(5, 'yyyyyyy', 10, '8c'); ERROR 23000: Duplicate entry '5' for key 'PRIMARY' INSERT INTO t1 VALUES(6, 'yyyyyyy', 10, '8c'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e5e5e5e 6 yyyyyyy 10 8c ALTER TABLE t1 DROP KEY pre_uk, DROP KEY pre_com_uk, DROP KEY pre_sk, DROP KEY pre_com_sk, ALGORITHM=INPLACE, LOCK=DEFAULT; DROP TABLE t1; ################################################################### case 4 prefix key on blob field ################################################################### case 4.1 has unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b TEXT, c int, d CHAR(10))CHARSET gbk COLLATE gbk_chinese_ci; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(6)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(4, '2222333', 8, '4d'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '2222' for key 'pre_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` text, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=gbk INSERT INTO t1 VALUES(4, 'xxxxxxx', 8, '4d'); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d DROP TABLE t1; ################################################################### case 4.2 no unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b TEXT, c int, d CHAR(10))CHARSET gbk COLLATE gbk_chinese_ci; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(2, '3333222', 8, '3c'); ERROR 23000: Duplicate entry '2' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` text, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_sk` (`d`(4)) ) ENGINE=XENGINE DEFAULT CHARSET=gbk INSERT INTO t1 VALUES(4, '3333333', 8, '3c'); ERROR 23000: Duplicate entry '3333' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c DROP TABLE t1; ################################################################### case 4.3 has unique conflict on PRIMARY KEY ################################################################### CREATE TABLE t1(b TEXT NOT NULL, c int, d CHAR(10))CHARSET gbk COLLATE gbk_chinese_ci; INSERT INTO t1 VALUES('1111111', 2, '1a'); INSERT INTO t1 VALUES('2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_copy_ddl_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD PRIMARY KEY(b(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES('3333333', 6, '3c'); INSERT INTO t1 VALUES('3333444', 8, '4d'); SELECT * FROM t1; b c d 1111111 2 1a 2222222 4 2b 3333333 6 3c 3333444 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '3333' for key 'PRIMARY' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `b` text NOT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL ) ENGINE=XENGINE DEFAULT CHARSET=gbk SELECT * FROM t1; b c d 1111111 2 1a 2222222 4 2b 3333333 6 3c 3333444 8 4d DROP TABLE t1; # Test suite/xengine/t/online_ddl_prefix_composite_key.inc with utf8 and utf8_bin ################################################################### case 1 non-unique secondary key ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET utf8 COLLATE utf8_bin; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warning 3778 'utf8_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead. INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### ALTER TABLE t1 ADD KEY pre_sk(b(6)), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); #### composite index #### ALTER TABLE t1 ADD KEY com_sk(c, d), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(4, '4444444', 8, '4d'); #### composite index with prefix #### ALTER TABLE t1 ADD KEY pre_com_sk(b(4), c), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(5, '5555555', 10, '5e'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE utf8_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`), KEY `pre_sk` (`b`(6)), KEY `com_sk` (`c`,`d`), KEY `pre_com_sk` (`b`(4),`c`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8 COLLATE=utf8_bin ALTER TABLE t1 DROP KEY pre_sk, DROP KEY com_sk, DROP KEY pre_com_sk, ALGORITHM=INPLACE, LOCK=DEFAULT; DROP TABLE t1; ################################################################### case 2 unique secondary key ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET utf8 COLLATE utf8_bin; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warning 3778 'utf8_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead. INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(3, '2222333', 6, '3c'); ERROR 23000: Duplicate entry '2222' for key 'pre_uk' INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); #### composite index #### ALTER TABLE t1 ADD UNIQUE KEY com_uk(c, d), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(4, '4444444', 6, '3c'); ERROR 23000: Duplicate entry '6-3c' for key 'com_uk' INSERT INTO t1 VALUES(4, '4444444', 8, '4d'); #### composite index with prefix #### ALTER TABLE t1 ADD KEY pre_com_uk(b(6), c), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(5, '4444445', 8, '5e'); ERROR 23000: Duplicate entry '4444' for key 'pre_uk' INSERT INTO t1 VALUES(5, '5555555', 10, '5e'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE utf8_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `com_uk` (`c`,`d`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_com_uk` (`b`(6),`c`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8 COLLATE=utf8_bin ALTER TABLE t1 DROP KEY pre_uk, DROP KEY com_uk, DROP KEY pre_com_uk, ALGORITHM=INPLACE, LOCK=DEFAULT; DROP TABLE t1; ################################################################### case 3 unique secondary key with concurrent DML ################################################################### case 3.1 has unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET utf8 COLLATE utf8_bin; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warning 3778 'utf8_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead. INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(6)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(4, '2222333', 8, '4d'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '2222' for key 'pre_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE utf8_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8 COLLATE=utf8_bin INSERT INTO t1 VALUES(4, 'xxxxxxx', 8, '4d'); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d #### composite index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY com_uk(c, d), ADD KEY com_sk(b, c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(5, '5555555', 10, '5e'); INSERT INTO t1 VALUES(6, '6666666', 10, '5e'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '10-5e' for key 'com_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE utf8_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8 COLLATE=utf8_bin INSERT INTO t1 VALUES(5, 'yyyyyyy', 10, '5eeee'); ERROR 23000: Duplicate entry '5' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e #### composite index with prefix #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_com_uk(c, d(4)), ADD KEY pre_com_sk(b(4), c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(7, '7777777', 14, '7e7e7e7e'); INSERT INTO t1 VALUES(8, '8888888', 14, '7e7e8f8f'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e 7 7777777 14 7e7e7e7e 8 8888888 14 7e7e8f8f SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '10-5e' for key 'pre_com_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE utf8_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8 COLLATE=utf8_bin INSERT INTO t1 VALUES(5, 'yyyyyyy', 10, '5e'); ERROR 23000: Duplicate entry '5' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e 7 7777777 14 7e7e7e7e 8 8888888 14 7e7e8f8f DROP TABLE t1; ################################################################### case 3.2 no unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET utf8 COLLATE utf8_bin; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warning 3778 'utf8_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead. INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(2, '3333222', 8, '3c'); ERROR 23000: Duplicate entry '2' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE utf8_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_sk` (`d`(4)) ) ENGINE=XENGINE DEFAULT CHARSET=utf8 COLLATE=utf8_bin INSERT INTO t1 VALUES(4, '3333333', 8, '3c'); ERROR 23000: Duplicate entry '3333' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c #### composite index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY com_uk(c, d), ADD KEY com_sk(b, c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(4, '4444444', 8, '4d'); INSERT INTO t1 VALUES(5, '4444555', 8, '5d'); ERROR 23000: Duplicate entry '4444' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE utf8_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `com_uk` (`c`,`d`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_sk` (`d`(4)), KEY `com_sk` (`b`,`c`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8 COLLATE=utf8_bin INSERT INTO t1 VALUES(4, 'yyyyyyy', 8, '8c'); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d ALTER TABLE t1 DROP KEY com_uk, DROP KEY com_sk, ALGORITHM=INPLACE, LOCK=DEFAULT; #### composite index with prefix #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_com_uk(c, d(4)), ADD KEY pre_com_sk(b(4), c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(5, '5555555', 10, '5e5e5e5e'); INSERT INTO t1 VALUES(6, '5555666', 10, '5e5e6f6f'); ERROR 23000: Duplicate entry '5555' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e5e5e5e SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE utf8_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pre_uk` (`b`(4)), UNIQUE KEY `pre_com_uk` (`c`,`d`(4)), KEY `pre_sk` (`d`(4)), KEY `pre_com_sk` (`b`(4),`c`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8 COLLATE=utf8_bin INSERT INTO t1 VALUES(5, 'yyyyyyy', 10, '8c'); ERROR 23000: Duplicate entry '5' for key 'PRIMARY' INSERT INTO t1 VALUES(6, 'yyyyyyy', 10, '8c'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e5e5e5e 6 yyyyyyy 10 8c ALTER TABLE t1 DROP KEY pre_uk, DROP KEY pre_com_uk, DROP KEY pre_sk, DROP KEY pre_com_sk, ALGORITHM=INPLACE, LOCK=DEFAULT; DROP TABLE t1; ################################################################### case 4 prefix key on blob field ################################################################### case 4.1 has unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b TEXT, c int, d CHAR(10))CHARSET utf8 COLLATE utf8_bin; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warning 3778 'utf8_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead. INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(6)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(4, '2222333', 8, '4d'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '2222' for key 'pre_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` text COLLATE utf8_bin, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8 COLLATE=utf8_bin INSERT INTO t1 VALUES(4, 'xxxxxxx', 8, '4d'); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d DROP TABLE t1; ################################################################### case 4.2 no unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b TEXT, c int, d CHAR(10))CHARSET utf8 COLLATE utf8_bin; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warning 3778 'utf8_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead. INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(2, '3333222', 8, '3c'); ERROR 23000: Duplicate entry '2' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` text COLLATE utf8_bin, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_sk` (`d`(4)) ) ENGINE=XENGINE DEFAULT CHARSET=utf8 COLLATE=utf8_bin INSERT INTO t1 VALUES(4, '3333333', 8, '3c'); ERROR 23000: Duplicate entry '3333' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c DROP TABLE t1; ################################################################### case 4.3 has unique conflict on PRIMARY KEY ################################################################### CREATE TABLE t1(b TEXT NOT NULL, c int, d CHAR(10))CHARSET utf8 COLLATE utf8_bin; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warning 3778 'utf8_bin' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead. INSERT INTO t1 VALUES('1111111', 2, '1a'); INSERT INTO t1 VALUES('2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_copy_ddl_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD PRIMARY KEY(b(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES('3333333', 6, '3c'); INSERT INTO t1 VALUES('3333444', 8, '4d'); SELECT * FROM t1; b c d 1111111 2 1a 2222222 4 2b 3333333 6 3c 3333444 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '3333' for key 'PRIMARY' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `b` text COLLATE utf8_bin NOT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8_bin DEFAULT NULL ) ENGINE=XENGINE DEFAULT CHARSET=utf8 COLLATE=utf8_bin SELECT * FROM t1; b c d 1111111 2 1a 2222222 4 2b 3333333 6 3c 3333444 8 4d DROP TABLE t1; # Test suite/xengine/t/online_ddl_prefix_composite_key.inc with utf8 and utf8_general_ci ################################################################### case 1 non-unique secondary key ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET utf8 COLLATE utf8_general_ci; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warning 3778 'utf8_general_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead. INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### ALTER TABLE t1 ADD KEY pre_sk(b(6)), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); #### composite index #### ALTER TABLE t1 ADD KEY com_sk(c, d), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(4, '4444444', 8, '4d'); #### composite index with prefix #### ALTER TABLE t1 ADD KEY pre_com_sk(b(4), c), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(5, '5555555', 10, '5e'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `pre_sk` (`b`(6)), KEY `com_sk` (`c`,`d`), KEY `pre_com_sk` (`b`(4),`c`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8 ALTER TABLE t1 DROP KEY pre_sk, DROP KEY com_sk, DROP KEY pre_com_sk, ALGORITHM=INPLACE, LOCK=DEFAULT; DROP TABLE t1; ################################################################### case 2 unique secondary key ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET utf8 COLLATE utf8_general_ci; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warning 3778 'utf8_general_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead. INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(3, '2222333', 6, '3c'); ERROR 23000: Duplicate entry '2222' for key 'pre_uk' INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); #### composite index #### ALTER TABLE t1 ADD UNIQUE KEY com_uk(c, d), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(4, '4444444', 6, '3c'); ERROR 23000: Duplicate entry '6-3c' for key 'com_uk' INSERT INTO t1 VALUES(4, '4444444', 8, '4d'); #### composite index with prefix #### ALTER TABLE t1 ADD KEY pre_com_uk(b(6), c), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(5, '4444445', 8, '5e'); ERROR 23000: Duplicate entry '4444' for key 'pre_uk' INSERT INTO t1 VALUES(5, '5555555', 10, '5e'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `com_uk` (`c`,`d`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_com_uk` (`b`(6),`c`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8 ALTER TABLE t1 DROP KEY pre_uk, DROP KEY com_uk, DROP KEY pre_com_uk, ALGORITHM=INPLACE, LOCK=DEFAULT; DROP TABLE t1; ################################################################### case 3 unique secondary key with concurrent DML ################################################################### case 3.1 has unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET utf8 COLLATE utf8_general_ci; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warning 3778 'utf8_general_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead. INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(6)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(4, '2222333', 8, '4d'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '2222' for key 'pre_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8 INSERT INTO t1 VALUES(4, 'xxxxxxx', 8, '4d'); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d #### composite index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY com_uk(c, d), ADD KEY com_sk(b, c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(5, '5555555', 10, '5e'); INSERT INTO t1 VALUES(6, '6666666', 10, '5e'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '10-5e' for key 'com_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8 INSERT INTO t1 VALUES(5, 'yyyyyyy', 10, '5eeee'); ERROR 23000: Duplicate entry '5' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e #### composite index with prefix #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_com_uk(c, d(4)), ADD KEY pre_com_sk(b(4), c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(7, '7777777', 14, '7e7e7e7e'); INSERT INTO t1 VALUES(8, '8888888', 14, '7e7e8f8f'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e 7 7777777 14 7e7e7e7e 8 8888888 14 7e7e8f8f SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '10-5e' for key 'pre_com_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8 INSERT INTO t1 VALUES(5, 'yyyyyyy', 10, '5e'); ERROR 23000: Duplicate entry '5' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e 7 7777777 14 7e7e7e7e 8 8888888 14 7e7e8f8f DROP TABLE t1; ################################################################### case 3.2 no unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET utf8 COLLATE utf8_general_ci; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warning 3778 'utf8_general_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead. INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(2, '3333222', 8, '3c'); ERROR 23000: Duplicate entry '2' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_sk` (`d`(4)) ) ENGINE=XENGINE DEFAULT CHARSET=utf8 INSERT INTO t1 VALUES(4, '3333333', 8, '3c'); ERROR 23000: Duplicate entry '3333' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c #### composite index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY com_uk(c, d), ADD KEY com_sk(b, c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(4, '4444444', 8, '4d'); INSERT INTO t1 VALUES(5, '4444555', 8, '5d'); ERROR 23000: Duplicate entry '4444' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `com_uk` (`c`,`d`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_sk` (`d`(4)), KEY `com_sk` (`b`,`c`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8 INSERT INTO t1 VALUES(4, 'yyyyyyy', 8, '8c'); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d ALTER TABLE t1 DROP KEY com_uk, DROP KEY com_sk, ALGORITHM=INPLACE, LOCK=DEFAULT; #### composite index with prefix #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_com_uk(c, d(4)), ADD KEY pre_com_sk(b(4), c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(5, '5555555', 10, '5e5e5e5e'); INSERT INTO t1 VALUES(6, '5555666', 10, '5e5e6f6f'); ERROR 23000: Duplicate entry '5555' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e5e5e5e SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pre_uk` (`b`(4)), UNIQUE KEY `pre_com_uk` (`c`,`d`(4)), KEY `pre_sk` (`d`(4)), KEY `pre_com_sk` (`b`(4),`c`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8 INSERT INTO t1 VALUES(5, 'yyyyyyy', 10, '8c'); ERROR 23000: Duplicate entry '5' for key 'PRIMARY' INSERT INTO t1 VALUES(6, 'yyyyyyy', 10, '8c'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e5e5e5e 6 yyyyyyy 10 8c ALTER TABLE t1 DROP KEY pre_uk, DROP KEY pre_com_uk, DROP KEY pre_sk, DROP KEY pre_com_sk, ALGORITHM=INPLACE, LOCK=DEFAULT; DROP TABLE t1; ################################################################### case 4 prefix key on blob field ################################################################### case 4.1 has unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b TEXT, c int, d CHAR(10))CHARSET utf8 COLLATE utf8_general_ci; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warning 3778 'utf8_general_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead. INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(6)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(4, '2222333', 8, '4d'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '2222' for key 'pre_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` text, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8 INSERT INTO t1 VALUES(4, 'xxxxxxx', 8, '4d'); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d DROP TABLE t1; ################################################################### case 4.2 no unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b TEXT, c int, d CHAR(10))CHARSET utf8 COLLATE utf8_general_ci; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warning 3778 'utf8_general_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead. INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(2, '3333222', 8, '3c'); ERROR 23000: Duplicate entry '2' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` text, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_sk` (`d`(4)) ) ENGINE=XENGINE DEFAULT CHARSET=utf8 INSERT INTO t1 VALUES(4, '3333333', 8, '3c'); ERROR 23000: Duplicate entry '3333' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c DROP TABLE t1; ################################################################### case 4.3 has unique conflict on PRIMARY KEY ################################################################### CREATE TABLE t1(b TEXT NOT NULL, c int, d CHAR(10))CHARSET utf8 COLLATE utf8_general_ci; Warnings: Warning 3719 'utf8' is currently an alias for the character set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous. Warning 3778 'utf8_general_ci' is a collation of the deprecated character set UTF8MB3. Please consider using UTF8MB4 with an appropriate collation instead. INSERT INTO t1 VALUES('1111111', 2, '1a'); INSERT INTO t1 VALUES('2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_copy_ddl_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD PRIMARY KEY(b(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES('3333333', 6, '3c'); INSERT INTO t1 VALUES('3333444', 8, '4d'); SELECT * FROM t1; b c d 1111111 2 1a 2222222 4 2b 3333333 6 3c 3333444 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '3333' for key 'PRIMARY' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `b` text NOT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL ) ENGINE=XENGINE DEFAULT CHARSET=utf8 SELECT * FROM t1; b c d 1111111 2 1a 2222222 4 2b 3333333 6 3c 3333444 8 4d DROP TABLE t1; # Test suite/xengine/t/online_ddl_prefix_composite_key.inc with utf8mb4 and utf8mb4_bin ################################################################### case 1 non-unique secondary key ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET utf8mb4 COLLATE utf8mb4_bin; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### ALTER TABLE t1 ADD KEY pre_sk(b(6)), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); #### composite index #### ALTER TABLE t1 ADD KEY com_sk(c, d), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(4, '4444444', 8, '4d'); #### composite index with prefix #### ALTER TABLE t1 ADD KEY pre_com_sk(b(4), c), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(5, '5555555', 10, '5e'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`id`), KEY `pre_sk` (`b`(6)), KEY `com_sk` (`c`,`d`), KEY `pre_com_sk` (`b`(4),`c`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ALTER TABLE t1 DROP KEY pre_sk, DROP KEY com_sk, DROP KEY pre_com_sk, ALGORITHM=INPLACE, LOCK=DEFAULT; DROP TABLE t1; ################################################################### case 2 unique secondary key ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET utf8mb4 COLLATE utf8mb4_bin; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(3, '2222333', 6, '3c'); ERROR 23000: Duplicate entry '2222' for key 'pre_uk' INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); #### composite index #### ALTER TABLE t1 ADD UNIQUE KEY com_uk(c, d), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(4, '4444444', 6, '3c'); ERROR 23000: Duplicate entry '6-3c' for key 'com_uk' INSERT INTO t1 VALUES(4, '4444444', 8, '4d'); #### composite index with prefix #### ALTER TABLE t1 ADD KEY pre_com_uk(b(6), c), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(5, '4444445', 8, '5e'); ERROR 23000: Duplicate entry '4444' for key 'pre_uk' INSERT INTO t1 VALUES(5, '5555555', 10, '5e'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `com_uk` (`c`,`d`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_com_uk` (`b`(6),`c`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin ALTER TABLE t1 DROP KEY pre_uk, DROP KEY com_uk, DROP KEY pre_com_uk, ALGORITHM=INPLACE, LOCK=DEFAULT; DROP TABLE t1; ################################################################### case 3 unique secondary key with concurrent DML ################################################################### case 3.1 has unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET utf8mb4 COLLATE utf8mb4_bin; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(6)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(4, '2222333', 8, '4d'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '2222' for key 'pre_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin INSERT INTO t1 VALUES(4, 'xxxxxxx', 8, '4d'); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d #### composite index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY com_uk(c, d), ADD KEY com_sk(b, c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(5, '5555555', 10, '5e'); INSERT INTO t1 VALUES(6, '6666666', 10, '5e'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '10-5e' for key 'com_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin INSERT INTO t1 VALUES(5, 'yyyyyyy', 10, '5eeee'); ERROR 23000: Duplicate entry '5' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e #### composite index with prefix #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_com_uk(c, d(4)), ADD KEY pre_com_sk(b(4), c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(7, '7777777', 14, '7e7e7e7e'); INSERT INTO t1 VALUES(8, '8888888', 14, '7e7e8f8f'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e 7 7777777 14 7e7e7e7e 8 8888888 14 7e7e8f8f SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '10-5e' for key 'pre_com_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin INSERT INTO t1 VALUES(5, 'yyyyyyy', 10, '5e'); ERROR 23000: Duplicate entry '5' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e 7 7777777 14 7e7e7e7e 8 8888888 14 7e7e8f8f DROP TABLE t1; ################################################################### case 3.2 no unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET utf8mb4 COLLATE utf8mb4_bin; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(2, '3333222', 8, '3c'); ERROR 23000: Duplicate entry '2' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_sk` (`d`(4)) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin INSERT INTO t1 VALUES(4, '3333333', 8, '3c'); ERROR 23000: Duplicate entry '3333' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c #### composite index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY com_uk(c, d), ADD KEY com_sk(b, c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(4, '4444444', 8, '4d'); INSERT INTO t1 VALUES(5, '4444555', 8, '5d'); ERROR 23000: Duplicate entry '4444' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `com_uk` (`c`,`d`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_sk` (`d`(4)), KEY `com_sk` (`b`,`c`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin INSERT INTO t1 VALUES(4, 'yyyyyyy', 8, '8c'); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d ALTER TABLE t1 DROP KEY com_uk, DROP KEY com_sk, ALGORITHM=INPLACE, LOCK=DEFAULT; #### composite index with prefix #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_com_uk(c, d(4)), ADD KEY pre_com_sk(b(4), c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(5, '5555555', 10, '5e5e5e5e'); INSERT INTO t1 VALUES(6, '5555666', 10, '5e5e6f6f'); ERROR 23000: Duplicate entry '5555' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e5e5e5e SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pre_uk` (`b`(4)), UNIQUE KEY `pre_com_uk` (`c`,`d`(4)), KEY `pre_sk` (`d`(4)), KEY `pre_com_sk` (`b`(4),`c`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin INSERT INTO t1 VALUES(5, 'yyyyyyy', 10, '8c'); ERROR 23000: Duplicate entry '5' for key 'PRIMARY' INSERT INTO t1 VALUES(6, 'yyyyyyy', 10, '8c'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e5e5e5e 6 yyyyyyy 10 8c ALTER TABLE t1 DROP KEY pre_uk, DROP KEY pre_com_uk, DROP KEY pre_sk, DROP KEY pre_com_sk, ALGORITHM=INPLACE, LOCK=DEFAULT; DROP TABLE t1; ################################################################### case 4 prefix key on blob field ################################################################### case 4.1 has unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b TEXT, c int, d CHAR(10))CHARSET utf8mb4 COLLATE utf8mb4_bin; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(6)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(4, '2222333', 8, '4d'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '2222' for key 'pre_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` text COLLATE utf8mb4_bin, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin INSERT INTO t1 VALUES(4, 'xxxxxxx', 8, '4d'); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d DROP TABLE t1; ################################################################### case 4.2 no unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b TEXT, c int, d CHAR(10))CHARSET utf8mb4 COLLATE utf8mb4_bin; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(2, '3333222', 8, '3c'); ERROR 23000: Duplicate entry '2' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` text COLLATE utf8mb4_bin, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8mb4_bin DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_sk` (`d`(4)) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin INSERT INTO t1 VALUES(4, '3333333', 8, '3c'); ERROR 23000: Duplicate entry '3333' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c DROP TABLE t1; ################################################################### case 4.3 has unique conflict on PRIMARY KEY ################################################################### CREATE TABLE t1(b TEXT NOT NULL, c int, d CHAR(10))CHARSET utf8mb4 COLLATE utf8mb4_bin; INSERT INTO t1 VALUES('1111111', 2, '1a'); INSERT INTO t1 VALUES('2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_copy_ddl_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD PRIMARY KEY(b(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES('3333333', 6, '3c'); INSERT INTO t1 VALUES('3333444', 8, '4d'); SELECT * FROM t1; b c d 1111111 2 1a 2222222 4 2b 3333333 6 3c 3333444 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '3333' for key 'PRIMARY' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `b` text COLLATE utf8mb4_bin NOT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8mb4_bin DEFAULT NULL ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin SELECT * FROM t1; b c d 1111111 2 1a 2222222 4 2b 3333333 6 3c 3333444 8 4d DROP TABLE t1; # Test suite/xengine/t/online_ddl_prefix_composite_key.inc with utf8mb4 and utf8mb4_general_ci ################################################################### case 1 non-unique secondary key ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET utf8mb4 COLLATE utf8mb4_general_ci; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### ALTER TABLE t1 ADD KEY pre_sk(b(6)), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); #### composite index #### ALTER TABLE t1 ADD KEY com_sk(c, d), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(4, '4444444', 8, '4d'); #### composite index with prefix #### ALTER TABLE t1 ADD KEY pre_com_sk(b(4), c), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(5, '5555555', 10, '5e'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`id`), KEY `pre_sk` (`b`(6)), KEY `com_sk` (`c`,`d`), KEY `pre_com_sk` (`b`(4),`c`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ALTER TABLE t1 DROP KEY pre_sk, DROP KEY com_sk, DROP KEY pre_com_sk, ALGORITHM=INPLACE, LOCK=DEFAULT; DROP TABLE t1; ################################################################### case 2 unique secondary key ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET utf8mb4 COLLATE utf8mb4_general_ci; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(3, '2222333', 6, '3c'); ERROR 23000: Duplicate entry '2222' for key 'pre_uk' INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); #### composite index #### ALTER TABLE t1 ADD UNIQUE KEY com_uk(c, d), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(4, '4444444', 6, '3c'); ERROR 23000: Duplicate entry '6-3c' for key 'com_uk' INSERT INTO t1 VALUES(4, '4444444', 8, '4d'); #### composite index with prefix #### ALTER TABLE t1 ADD KEY pre_com_uk(b(6), c), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(5, '4444445', 8, '5e'); ERROR 23000: Duplicate entry '4444' for key 'pre_uk' INSERT INTO t1 VALUES(5, '5555555', 10, '5e'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `com_uk` (`c`,`d`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_com_uk` (`b`(6),`c`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ALTER TABLE t1 DROP KEY pre_uk, DROP KEY com_uk, DROP KEY pre_com_uk, ALGORITHM=INPLACE, LOCK=DEFAULT; DROP TABLE t1; ################################################################### case 3 unique secondary key with concurrent DML ################################################################### case 3.1 has unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET utf8mb4 COLLATE utf8mb4_general_ci; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(6)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(4, '2222333', 8, '4d'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '2222' for key 'pre_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci INSERT INTO t1 VALUES(4, 'xxxxxxx', 8, '4d'); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d #### composite index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY com_uk(c, d), ADD KEY com_sk(b, c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(5, '5555555', 10, '5e'); INSERT INTO t1 VALUES(6, '6666666', 10, '5e'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '10-5e' for key 'com_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci INSERT INTO t1 VALUES(5, 'yyyyyyy', 10, '5eeee'); ERROR 23000: Duplicate entry '5' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e #### composite index with prefix #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_com_uk(c, d(4)), ADD KEY pre_com_sk(b(4), c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(7, '7777777', 14, '7e7e7e7e'); INSERT INTO t1 VALUES(8, '8888888', 14, '7e7e8f8f'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e 7 7777777 14 7e7e7e7e 8 8888888 14 7e7e8f8f SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '10-5e' for key 'pre_com_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci INSERT INTO t1 VALUES(5, 'yyyyyyy', 10, '5e'); ERROR 23000: Duplicate entry '5' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e 7 7777777 14 7e7e7e7e 8 8888888 14 7e7e8f8f DROP TABLE t1; ################################################################### case 3.2 no unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET utf8mb4 COLLATE utf8mb4_general_ci; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(2, '3333222', 8, '3c'); ERROR 23000: Duplicate entry '2' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_sk` (`d`(4)) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci INSERT INTO t1 VALUES(4, '3333333', 8, '3c'); ERROR 23000: Duplicate entry '3333' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c #### composite index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY com_uk(c, d), ADD KEY com_sk(b, c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(4, '4444444', 8, '4d'); INSERT INTO t1 VALUES(5, '4444555', 8, '5d'); ERROR 23000: Duplicate entry '4444' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `com_uk` (`c`,`d`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_sk` (`d`(4)), KEY `com_sk` (`b`,`c`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci INSERT INTO t1 VALUES(4, 'yyyyyyy', 8, '8c'); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d ALTER TABLE t1 DROP KEY com_uk, DROP KEY com_sk, ALGORITHM=INPLACE, LOCK=DEFAULT; #### composite index with prefix #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_com_uk(c, d(4)), ADD KEY pre_com_sk(b(4), c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(5, '5555555', 10, '5e5e5e5e'); INSERT INTO t1 VALUES(6, '5555666', 10, '5e5e6f6f'); ERROR 23000: Duplicate entry '5555' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e5e5e5e SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) COLLATE utf8mb4_general_ci DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pre_uk` (`b`(4)), UNIQUE KEY `pre_com_uk` (`c`,`d`(4)), KEY `pre_sk` (`d`(4)), KEY `pre_com_sk` (`b`(4),`c`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci INSERT INTO t1 VALUES(5, 'yyyyyyy', 10, '8c'); ERROR 23000: Duplicate entry '5' for key 'PRIMARY' INSERT INTO t1 VALUES(6, 'yyyyyyy', 10, '8c'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e5e5e5e 6 yyyyyyy 10 8c ALTER TABLE t1 DROP KEY pre_uk, DROP KEY pre_com_uk, DROP KEY pre_sk, DROP KEY pre_com_sk, ALGORITHM=INPLACE, LOCK=DEFAULT; DROP TABLE t1; ################################################################### case 4 prefix key on blob field ################################################################### case 4.1 has unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b TEXT, c int, d CHAR(10))CHARSET utf8mb4 COLLATE utf8mb4_general_ci; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(6)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(4, '2222333', 8, '4d'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '2222' for key 'pre_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` text COLLATE utf8mb4_general_ci, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci INSERT INTO t1 VALUES(4, 'xxxxxxx', 8, '4d'); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d DROP TABLE t1; ################################################################### case 4.2 no unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b TEXT, c int, d CHAR(10))CHARSET utf8mb4 COLLATE utf8mb4_general_ci; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(2, '3333222', 8, '3c'); ERROR 23000: Duplicate entry '2' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` text COLLATE utf8mb4_general_ci, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8mb4_general_ci DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_sk` (`d`(4)) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci INSERT INTO t1 VALUES(4, '3333333', 8, '3c'); ERROR 23000: Duplicate entry '3333' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c DROP TABLE t1; ################################################################### case 4.3 has unique conflict on PRIMARY KEY ################################################################### CREATE TABLE t1(b TEXT NOT NULL, c int, d CHAR(10))CHARSET utf8mb4 COLLATE utf8mb4_general_ci; INSERT INTO t1 VALUES('1111111', 2, '1a'); INSERT INTO t1 VALUES('2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_copy_ddl_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD PRIMARY KEY(b(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES('3333333', 6, '3c'); INSERT INTO t1 VALUES('3333444', 8, '4d'); SELECT * FROM t1; b c d 1111111 2 1a 2222222 4 2b 3333333 6 3c 3333444 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '3333' for key 'PRIMARY' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `b` text COLLATE utf8mb4_general_ci NOT NULL, `c` int(11) DEFAULT NULL, `d` char(10) COLLATE utf8mb4_general_ci DEFAULT NULL ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci SELECT * FROM t1; b c d 1111111 2 1a 2222222 4 2b 3333333 6 3c 3333444 8 4d DROP TABLE t1; # Test suite/xengine/t/online_ddl_prefix_composite_key.inc with utf8mb4 and utf8mb4_0900_ai_ci ################################################################### case 1 non-unique secondary key ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### ALTER TABLE t1 ADD KEY pre_sk(b(6)), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); #### composite index #### ALTER TABLE t1 ADD KEY com_sk(c, d), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(4, '4444444', 8, '4d'); #### composite index with prefix #### ALTER TABLE t1 ADD KEY pre_com_sk(b(4), c), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(5, '5555555', 10, '5e'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `pre_sk` (`b`(6)), KEY `com_sk` (`c`,`d`), KEY `pre_com_sk` (`b`(4),`c`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ALTER TABLE t1 DROP KEY pre_sk, DROP KEY com_sk, DROP KEY pre_com_sk, ALGORITHM=INPLACE, LOCK=DEFAULT; DROP TABLE t1; ################################################################### case 2 unique secondary key ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(3, '2222333', 6, '3c'); ERROR 23000: Duplicate entry '2222' for key 'pre_uk' INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); #### composite index #### ALTER TABLE t1 ADD UNIQUE KEY com_uk(c, d), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(4, '4444444', 6, '3c'); ERROR 23000: Duplicate entry '6-3c' for key 'com_uk' INSERT INTO t1 VALUES(4, '4444444', 8, '4d'); #### composite index with prefix #### ALTER TABLE t1 ADD KEY pre_com_uk(b(6), c), ALGORITHM=INPLACE, LOCK=DEFAULT; INSERT INTO t1 VALUES(5, '4444445', 8, '5e'); ERROR 23000: Duplicate entry '4444' for key 'pre_uk' INSERT INTO t1 VALUES(5, '5555555', 10, '5e'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `com_uk` (`c`,`d`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_com_uk` (`b`(6),`c`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci ALTER TABLE t1 DROP KEY pre_uk, DROP KEY com_uk, DROP KEY pre_com_uk, ALGORITHM=INPLACE, LOCK=DEFAULT; DROP TABLE t1; ################################################################### case 3 unique secondary key with concurrent DML ################################################################### case 3.1 has unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(6)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(4, '2222333', 8, '4d'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '2222' for key 'pre_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci INSERT INTO t1 VALUES(4, 'xxxxxxx', 8, '4d'); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d #### composite index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY com_uk(c, d), ADD KEY com_sk(b, c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(5, '5555555', 10, '5e'); INSERT INTO t1 VALUES(6, '6666666', 10, '5e'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '10-5e' for key 'com_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci INSERT INTO t1 VALUES(5, 'yyyyyyy', 10, '5eeee'); ERROR 23000: Duplicate entry '5' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e #### composite index with prefix #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_com_uk(c, d(4)), ADD KEY pre_com_sk(b(4), c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(7, '7777777', 14, '7e7e7e7e'); INSERT INTO t1 VALUES(8, '8888888', 14, '7e7e8f8f'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e 7 7777777 14 7e7e7e7e 8 8888888 14 7e7e8f8f SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '10-5e' for key 'pre_com_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci INSERT INTO t1 VALUES(5, 'yyyyyyy', 10, '5e'); ERROR 23000: Duplicate entry '5' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d 5 5555555 10 5e 6 6666666 10 5e 7 7777777 14 7e7e7e7e 8 8888888 14 7e7e8f8f DROP TABLE t1; ################################################################### case 3.2 no unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b VARCHAR(10), c int, d CHAR(10))CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(2, '3333222', 8, '3c'); ERROR 23000: Duplicate entry '2' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_sk` (`d`(4)) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci INSERT INTO t1 VALUES(4, '3333333', 8, '3c'); ERROR 23000: Duplicate entry '3333' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c #### composite index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY com_uk(c, d), ADD KEY com_sk(b, c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(4, '4444444', 8, '4d'); INSERT INTO t1 VALUES(5, '4444555', 8, '5d'); ERROR 23000: Duplicate entry '4444' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `com_uk` (`c`,`d`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_sk` (`d`(4)), KEY `com_sk` (`b`,`c`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci INSERT INTO t1 VALUES(4, 'yyyyyyy', 8, '8c'); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d ALTER TABLE t1 DROP KEY com_uk, DROP KEY com_sk, ALGORITHM=INPLACE, LOCK=DEFAULT; #### composite index with prefix #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_com_uk(c, d(4)), ADD KEY pre_com_sk(b(4), c), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(5, '5555555', 10, '5e5e5e5e'); INSERT INTO t1 VALUES(6, '5555666', 10, '5e5e6f6f'); ERROR 23000: Duplicate entry '5555' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e5e5e5e SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` varchar(10) DEFAULT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pre_uk` (`b`(4)), UNIQUE KEY `pre_com_uk` (`c`,`d`(4)), KEY `pre_sk` (`d`(4)), KEY `pre_com_sk` (`b`(4),`c`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci INSERT INTO t1 VALUES(5, 'yyyyyyy', 10, '8c'); ERROR 23000: Duplicate entry '5' for key 'PRIMARY' INSERT INTO t1 VALUES(6, 'yyyyyyy', 10, '8c'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 4444444 8 4d 5 5555555 10 5e5e5e5e 6 yyyyyyy 10 8c ALTER TABLE t1 DROP KEY pre_uk, DROP KEY pre_com_uk, DROP KEY pre_sk, DROP KEY pre_com_sk, ALGORITHM=INPLACE, LOCK=DEFAULT; DROP TABLE t1; ################################################################### case 4 prefix key on blob field ################################################################### case 4.1 has unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b TEXT, c int, d CHAR(10))CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(6)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(4, '2222333', 8, '4d'); SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '2222' for key 'pre_uk' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` text, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci INSERT INTO t1 VALUES(4, 'xxxxxxx', 8, '4d'); ERROR 23000: Duplicate entry '4' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c 4 2222333 8 4d DROP TABLE t1; ################################################################### case 4.2 no unique conflict ################################################################### CREATE TABLE t1(id INT PRIMARY KEY, b TEXT, c int, d CHAR(10))CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci; INSERT INTO t1 VALUES(1, '1111111', 2, '1a'); INSERT INTO t1 VALUES(2, '2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_create_sk_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD UNIQUE KEY pre_uk(b(4)), ADD KEY pre_sk(d(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES(3, '3333333', 6, '3c'); INSERT INTO t1 VALUES(2, '3333222', 8, '3c'); ERROR 23000: Duplicate entry '2' for key 'PRIMARY' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `id` int(11) NOT NULL, `b` text, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `pre_uk` (`b`(4)), KEY `pre_sk` (`d`(4)) ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci INSERT INTO t1 VALUES(4, '3333333', 8, '3c'); ERROR 23000: Duplicate entry '3333' for key 'pre_uk' SELECT * FROM t1; id b c d 1 1111111 2 1a 2 2222222 4 2b 3 3333333 6 3c DROP TABLE t1; ################################################################### case 4.3 has unique conflict on PRIMARY KEY ################################################################### CREATE TABLE t1(b TEXT NOT NULL, c int, d CHAR(10))CHARSET utf8mb4 COLLATE utf8mb4_0900_ai_ci; INSERT INTO t1 VALUES('1111111', 2, '1a'); INSERT INTO t1 VALUES('2222222', 4, '2b'); #### prefix index #### SET DEBUG_SYNC= 'xengine.inplace_copy_ddl_scan_base_begin SIGNAL start_dml WAIT_FOR dml_done'; ALTER TABLE t1 ADD PRIMARY KEY(b(4)), ALGORITHM=INPLACE, LOCK=DEFAULT; # Switch to dml connection SET DEBUG_SYNC= 'now WAIT_FOR start_dml'; INSERT INTO t1 VALUES('3333333', 6, '3c'); INSERT INTO t1 VALUES('3333444', 8, '4d'); SELECT * FROM t1; b c d 1111111 2 1a 2222222 4 2b 3333333 6 3c 3333444 8 4d SET DEBUG_SYNC= 'now SIGNAL dml_done'; # Switch to connection default ERROR 23000: Duplicate entry '3333' for key 'PRIMARY' CHECK TABLE t1; Table Op Msg_type Msg_text test.t1 check status OK SHOW CREATE TABLE t1; Table Create Table t1 CREATE TABLE `t1` ( `b` text NOT NULL, `c` int(11) DEFAULT NULL, `d` char(10) DEFAULT NULL ) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci SELECT * FROM t1; b c d 1111111 2 1a 2222222 4 2b 3333333 6 3c 3333444 8 4d DROP TABLE t1;