370 lines
14 KiB
Plaintext
370 lines
14 KiB
Plaintext
##########
|
|
# Test online ddl without rebuild and with rebuild
|
|
##########
|
|
##########
|
|
# case 1 Test online inplace DDL no rebuild, which means all old subtable should be kept as same
|
|
##########
|
|
# BUG#23983934
|
|
# https://work.aone.alibaba-inc.com/issue/23983934
|
|
#############################################################
|
|
# case 1.1 add new key, INDEX_ID of old key should be kept as same
|
|
#############################################################
|
|
CREATE TABLE t(id INT PRIMARY KEY, name CHAR(30), room INT, code INT, score FLOAT, UNIQUE KEY uk(code)) ENGINE=XENGINE;
|
|
INSERT INTO t VALUES (1, '张三', 1, 128, 80), (2, '李四', 2, 256, 90);
|
|
EXPLAIN SELECT code from t;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t NULL index NULL uk 5 NULL # 100.00 Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t`.`code` AS `code` from `test`.`t`
|
|
SELECT code from t;
|
|
code
|
|
128
|
|
256
|
|
# add new secondary key
|
|
ALTER TABLE t ADD INDEX sk(score), ADD UNIQUE INDEX idx_room(room);
|
|
EXPLAIN SELECT code from t;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t NULL index NULL uk 5 NULL # 100.00 Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t`.`code` AS `code` from `test`.`t`
|
|
SELECT code from t;
|
|
code
|
|
128
|
|
256
|
|
include/assert.inc [subtable of of pk and uk should not be changed after adding new key]
|
|
DROP TABLE t;
|
|
#############################################################
|
|
# case 1.2 drop secondary key, INDEX_ID of PRIMARY key should be kept as same
|
|
#############################################################
|
|
CREATE TABLE t(id INT PRIMARY KEY, name CHAR(30), code INT, UNIQUE KEY uk(code)) ENGINE=XENGINE;
|
|
INSERT INTO t VALUES (1, '张三', 128), (2, '李四', 256);
|
|
SELECT * from t;
|
|
id name code
|
|
1 张三 128
|
|
2 李四 256
|
|
# drop secondary key
|
|
ALTER TABLE t DROP INDEX uk;
|
|
SELECT * from t;
|
|
id name code
|
|
1 张三 128
|
|
2 李四 256
|
|
include/assert.inc [subtable of pk should not be changed after dropping secondery key]
|
|
DROP TABLE t;
|
|
#############################################################
|
|
# case 1.3 rename secondary key, INDEX_ID of the key should be kept as same
|
|
#############################################################
|
|
CREATE TABLE t(id INT PRIMARY KEY, name CHAR(30), code INT, UNIQUE KEY uk(code)) ENGINE=XENGINE;
|
|
INSERT INTO t VALUES (1, '张三', 128), (2, '李四', 256);
|
|
EXPLAIN SELECT code from t;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t NULL index NULL uk 5 NULL # 100.00 Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t`.`code` AS `code` from `test`.`t`
|
|
SELECT code from t;
|
|
code
|
|
128
|
|
256
|
|
# rename secondary key
|
|
ALTER TABLE t RENAME INDEX uk TO uk_c;
|
|
EXPLAIN SELECT code from t;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t NULL index NULL uk_c 5 NULL # 100.00 Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t`.`code` AS `code` from `test`.`t`
|
|
SELECT code from t;
|
|
code
|
|
128
|
|
256
|
|
include/assert.inc [subtable of pk and uk should not be changed after rename secondary key]
|
|
# change index visibility
|
|
ALTER TABLE t ALTER INDEX uk_c INVISIBLE;
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`id` int(11) NOT NULL,
|
|
`name` char(30) COLLATE utf8mb4_general_ci DEFAULT NULL,
|
|
`code` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk_c` (`code`) /*!80000 INVISIBLE */
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
EXPLAIN SELECT code from t;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t NULL ALL NULL NULL NULL NULL # 100.00 NULL
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t`.`code` AS `code` from `test`.`t`
|
|
SELECT code from t;
|
|
code
|
|
128
|
|
256
|
|
include/assert.inc [subtable of pk and uk should not be changed after changine visibility of index]
|
|
DROP TABLE t;
|
|
#############################################################
|
|
# case 1.4 rename column of index, INDEX_ID of the index should be kept as same
|
|
#############################################################
|
|
CREATE TABLE t(i INT PRIMARY KEY, name CHAR(30), code INT, KEY sk(code)) ENGINE=XENGINE;
|
|
INSERT INTO t VALUES (1, '张三', 128), (2, '李四', 256);
|
|
SELECT * FROM t;
|
|
i name code
|
|
1 张三 128
|
|
2 李四 256
|
|
# rename column of PRIMARY key
|
|
ALTER TABLE t RENAME COLUMN i TO id, ALGORITHM=INPLACE;
|
|
SELECT * FROM t;
|
|
id name code
|
|
1 张三 128
|
|
2 李四 256
|
|
include/assert.inc [subtable of pk and sk should not be changed after renaming column of pk]
|
|
# rename column of secondary key
|
|
ALTER TABLE t RENAME COLUMN code TO cd, ALGORITHM=INPLACE;
|
|
EXPLAIN SELECT cd FROM t;
|
|
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
|
|
1 SIMPLE t NULL index NULL sk 5 NULL # 100.00 Using index
|
|
Warnings:
|
|
Note 1003 /* select#1 */ select `test`.`t`.`cd` AS `cd` from `test`.`t`
|
|
SELECT cd FROM t;
|
|
cd
|
|
128
|
|
256
|
|
include/assert.inc [subtable of pk and sk should not be changed after renaming name of sk]
|
|
DROP TABLE t;
|
|
#############################################################
|
|
# case 1.5 extend varchar size within same pack length, INDEX_ID of the index should be kept as same
|
|
#############################################################
|
|
CREATE TABLE t(id INT PRIMARY KEY, name VARCHAR(70), KEY sk_nm(name)) ENGINE=XENGINE;
|
|
INSERT INTO t VALUES (1, '张三'), (2, '李四');
|
|
SELECT * FROM t;
|
|
id name
|
|
1 张三
|
|
2 李四
|
|
# extend varchar column
|
|
ALTER TABLE t MODIFY COLUMN name VARCHAR(120), ALGORITHM=INPLACE;
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`id` int(11) NOT NULL,
|
|
`name` varchar(120) COLLATE utf8mb4_general_ci DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `sk_nm` (`name`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
include/assert.inc [subtable of pk and sk should not be changed after extending varchar column]
|
|
DROP TABLE t;
|
|
#############################################################
|
|
# case 1.6 change comment of key, INDEX_ID of the index should be kept as same
|
|
#############################################################
|
|
CREATE TABLE t(id INT PRIMARY KEY, name VARCHAR(70), KEY sk_nm(name)) ENGINE=XENGINE;
|
|
INSERT INTO t VALUES (1, '张三'), (2, '李四');
|
|
SELECT * FROM t;
|
|
id name
|
|
1 张三
|
|
2 李四
|
|
# extend varchar column
|
|
ALTER TABLE t DROP KEY sk_nm, ADD KEY sk_nm(name) COMMENT 'key on name', ALGORITHM=INPLACE;
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`id` int(11) NOT NULL,
|
|
`name` varchar(70) COLLATE utf8mb4_general_ci DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
KEY `sk_nm` (`name`) COMMENT 'key on name'
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
include/assert.inc [subtable of pk and sk should not be changed after changing comment of index]
|
|
DROP TABLE t;
|
|
##########
|
|
# case 2 Test online inplace DDL with rebuild, which means index number of all old subtable should be channged
|
|
##########
|
|
#############################################################
|
|
# case 2.1 add new primary key, INDEX_ID of all existing keys should be changed
|
|
#############################################################
|
|
CREATE TABLE t(id INT NOT NULL, name VARCHAR(40), code INT, KEY sk(name), UNIQUE KEY uk(code)) ENGINE=XENGINE;
|
|
INSERT INTO t VALUES (1, '张三', 128), (2, '李四', 256);
|
|
ALTER TABLE t ADD PRIMARY KEY(id), ALGORITHM=INPLACE;
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`id` int(11) NOT NULL,
|
|
`name` varchar(40) COLLATE utf8mb4_general_ci DEFAULT NULL,
|
|
`code` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk` (`code`),
|
|
KEY `sk` (`name`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT name FROM t;
|
|
name
|
|
张三
|
|
李四
|
|
SELECT code FROM t;
|
|
code
|
|
128
|
|
256
|
|
include/assert.inc [subtable of uk and sk should not be changed after changing comment of index]
|
|
DROP TABLE t;
|
|
#############################################################
|
|
# case 2.2 modify primary key, INDEX_ID of all existing keys should be changed
|
|
#############################################################
|
|
CREATE TABLE t(id INT NOT NULL, col INT PRIMARY KEY, name VARCHAR(40), code INT, KEY sk(name), UNIQUE KEY uk(code)) ENGINE=XENGINE;
|
|
INSERT INTO t VALUES (1, 1, '张三', 128), (2, 4, '李四', 256);
|
|
ALTER TABLE t DROP COLUMN col, ADD PRIMARY KEY(id), ALGORITHM=INPLACE;
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`id` int(11) NOT NULL,
|
|
`name` varchar(40) COLLATE utf8mb4_general_ci DEFAULT NULL,
|
|
`code` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk` (`code`),
|
|
KEY `sk` (`name`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT name FROM t;
|
|
name
|
|
张三
|
|
李四
|
|
SELECT code FROM t;
|
|
code
|
|
128
|
|
256
|
|
include/assert.inc [subtable of uk and sk should not be changed after changing comment of index]
|
|
DROP TABLE t;
|
|
#############################################################
|
|
# case 2.3 set non-nullable column nullable, INDEX_ID of all existing keys should be changed
|
|
#############################################################
|
|
CREATE TABLE t(id INT PRIMARY KEY, col INT NOT NULL, name VARCHAR(40), code INT, KEY sk(name), UNIQUE KEY uk(code)) ENGINE=XENGINE;
|
|
INSERT INTO t VALUES (1, 1, '张三', 128), (2, 4, '李四', 256);
|
|
ALTER TABLE t MODIFY COLUMN col INT, ALGORITHM=INPLACE;
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`id` int(11) NOT NULL,
|
|
`col` int(11) DEFAULT NULL,
|
|
`name` varchar(40) COLLATE utf8mb4_general_ci DEFAULT NULL,
|
|
`code` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk` (`code`),
|
|
KEY `sk` (`name`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT name FROM t;
|
|
name
|
|
张三
|
|
李四
|
|
SELECT code FROM t;
|
|
code
|
|
128
|
|
256
|
|
include/assert.inc [subtable of pk, uk and sk should not be changed after changing comment of index]
|
|
DROP TABLE t;
|
|
#############################################################
|
|
# case 2.4 set nullable column non-nullable, INDEX_ID of all existing keys should be changed
|
|
#############################################################
|
|
SET @save_sql_mode = @@sql_mode;
|
|
SET SQL_MODE = "STRICT_TRANS_TABLES";
|
|
Warnings:
|
|
Warning 3135 'NO_ZERO_DATE', 'NO_ZERO_IN_DATE' and 'ERROR_FOR_DIVISION_BY_ZERO' sql modes should be used with strict mode. They will be merged with strict mode in a future release.
|
|
CREATE TABLE t(id INT PRIMARY KEY, col INT, name VARCHAR(40), code INT, KEY sk(name), UNIQUE KEY uk(code)) ENGINE=XENGINE;
|
|
INSERT INTO t VALUES (1, 1, '张三', 128), (2, 4, '李四', 256);
|
|
ALTER TABLE t MODIFY COLUMN col INT NOT NULL, ALGORITHM=INPLACE;
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`id` int(11) NOT NULL,
|
|
`col` int(11) NOT NULL,
|
|
`name` varchar(40) COLLATE utf8mb4_general_ci DEFAULT NULL,
|
|
`code` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk` (`code`),
|
|
KEY `sk` (`name`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT name FROM t;
|
|
name
|
|
张三
|
|
李四
|
|
SELECT code FROM t;
|
|
code
|
|
128
|
|
256
|
|
include/assert.inc [subtable of pk, uk and sk should not be changed after changing comment of index]
|
|
DROP TABLE t;
|
|
SET SQL_MODE = @save_sql_mode;
|
|
#############################################################
|
|
# case 2.5 insert column, INDEX_ID of all existing keys should be changed
|
|
#############################################################
|
|
CREATE TABLE t(id INT PRIMARY KEY, name VARCHAR(40), code INT, KEY sk(name), UNIQUE KEY uk(code)) ENGINE=XENGINE;
|
|
INSERT INTO t VALUES (1, '张三', 128), (2, '李四', 256);
|
|
ALTER TABLE t ADD COLUMN col INT AFTER id, ALGORITHM=INPLACE;
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`id` int(11) NOT NULL,
|
|
`col` int(11) DEFAULT NULL,
|
|
`name` varchar(40) COLLATE utf8mb4_general_ci DEFAULT NULL,
|
|
`code` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk` (`code`),
|
|
KEY `sk` (`name`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT name FROM t;
|
|
name
|
|
张三
|
|
李四
|
|
SELECT code FROM t;
|
|
code
|
|
128
|
|
256
|
|
include/assert.inc [subtable of pk, uk and sk should not be changed after changing comment of index]
|
|
DROP TABLE t;
|
|
#############################################################
|
|
# case 2.6 reorder a column, INDEX_ID of all existing keys should be changed
|
|
#############################################################
|
|
CREATE TABLE t(id INT PRIMARY KEY, col INT, name VARCHAR(40), code INT, KEY sk(name), UNIQUE KEY uk(code)) ENGINE=XENGINE;
|
|
INSERT INTO t VALUES (1, 1, '张三', 128), (2, 4, '李四', 256);
|
|
ALTER TABLE t MODIFY COLUMN col INT AFTER name, ALGORITHM=INPLACE;
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`id` int(11) NOT NULL,
|
|
`name` varchar(40) COLLATE utf8mb4_general_ci DEFAULT NULL,
|
|
`col` int(11) DEFAULT NULL,
|
|
`code` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk` (`code`),
|
|
KEY `sk` (`name`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT name FROM t;
|
|
name
|
|
张三
|
|
李四
|
|
SELECT code FROM t;
|
|
code
|
|
128
|
|
256
|
|
SELECT * FROM t;
|
|
id name col code
|
|
1 张三 1 128
|
|
2 李四 4 256
|
|
include/assert.inc [subtable of pk, uk and sk should not be changed after changing comment of index]
|
|
DROP TABLE t;
|
|
#############################################################
|
|
# case 2.7 drop a column, INDEX_ID of all existing keys should be changed
|
|
#############################################################
|
|
CREATE TABLE t(id INT PRIMARY KEY, col INT, name VARCHAR(40), code INT, KEY sk(name), UNIQUE KEY uk(code)) ENGINE=XENGINE;
|
|
INSERT INTO t VALUES (1, 1, '张三', 128), (2, 4, '李四', 256);
|
|
ALTER TABLE t DROP COLUMN col, ALGORITHM=INPLACE;
|
|
SHOW CREATE TABLE t;
|
|
Table Create Table
|
|
t CREATE TABLE `t` (
|
|
`id` int(11) NOT NULL,
|
|
`name` varchar(40) COLLATE utf8mb4_general_ci DEFAULT NULL,
|
|
`code` int(11) DEFAULT NULL,
|
|
PRIMARY KEY (`id`),
|
|
UNIQUE KEY `uk` (`code`),
|
|
KEY `sk` (`name`)
|
|
) ENGINE=XENGINE DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
|
|
SELECT name FROM t;
|
|
name
|
|
张三
|
|
李四
|
|
SELECT code FROM t;
|
|
code
|
|
128
|
|
256
|
|
include/assert.inc [subtable of pk, uk and sk should not be changed after changing comment of index]
|
|
DROP TABLE t;
|