1--source include/have_rocksdb.inc 2 3## 4## test dropping index inplace 5## 6 7CREATE TABLE t1 (a INT, b INT AUTO_INCREMENT, KEY ka(a), KEY kb(a,b), PRIMARY KEY(b)) ENGINE=ROCKSDB; 8SHOW CREATE TABLE t1; 9INSERT INTO t1 (a) VALUES (1); 10INSERT INTO t1 (a) VALUES (3); 11INSERT INTO t1 (a) VALUES (5); 12 13ALTER TABLE t1 DROP INDEX ka, ALGORITHM=INPLACE; 14SHOW CREATE TABLE t1; 15 16# Key ka does not exist in table t1 17--error ER_KEY_DOES_NOT_EXITS 18SELECT * FROM t1 FORCE INDEX(ka) where a > 1; 19 20--sorted_result 21SELECT * FROM t1 FORCE INDEX(kb) where a > 1; 22--sorted_result 23SELECT * FROM t1 where b > 1; 24 25DROP TABLE t1; 26 27## 28## test dropping multiple indexes at once and multi-part indexes 29## 30 31CREATE TABLE t1 (a INT AUTO_INCREMENT, b INT, c INT, KEY kb(b), KEY kbc(b,c), KEY kc(c), PRIMARY KEY(a)) ENGINE=ROCKSDB; 32SHOW CREATE TABLE t1; 33INSERT INTO t1 (b,c) VALUES (1,2); 34INSERT INTO t1 (b,c) VALUES (3,4); 35INSERT INTO t1 (b,c) VALUES (5,6); 36ALTER TABLE t1 DROP INDEX kb, DROP INDEX kbc, ALGORITHM=INPLACE; 37SHOW CREATE TABLE t1; 38 39 40# test restarting to make sure everything is still ok and persisted properly 41--source include/restart_mysqld.inc 42 43SHOW CREATE TABLE t1; 44 45INSERT INTO t1 (b,c) VALUES (1,2); 46INSERT INTO t1 (b,c) VALUES (3,4); 47INSERT INTO t1 (b,c) VALUES (5,6); 48 49--sorted_result 50SELECT * FROM t1 FORCE INDEX(kc) where c > 3; 51--sorted_result 52SELECT * FROM t1 where b > 3; 53 54DROP TABLE t1; 55 56# test dropping pk to see if thats still ok 57CREATE TABLE t1 (a INT, b INT, c INT, KEY kb(b), KEY kbc(b,c), KEY kc(c), PRIMARY KEY(a)) ENGINE=ROCKSDB; 58SHOW INDEX IN t1; 59ALTER TABLE t1 DROP INDEX kb, DROP INDEX kbc, ALGORITHM=INPLACE; 60SHOW INDEX IN t1; 61 62ALTER TABLE t1 DROP PRIMARY KEY; 63SHOW INDEX IN t1; 64# test dropping index on tables with no pk 65ALTER TABLE t1 DROP INDEX kc, ALGORITHM=INPLACE; 66SHOW INDEX IN t1; 67 68DROP TABLE t1; 69 70# test dropping unique keys 71CREATE TABLE t1 (a INT AUTO_INCREMENT, b INT, c INT, PRIMARY KEY(a)) ENGINE=ROCKSDB; 72ALTER TABLE t1 ADD UNIQUE INDEX kb(b); 73ALTER TABLE t1 ADD UNIQUE INDEX kbc(b,c); 74ALTER TABLE t1 ADD UNIQUE INDEX kc(c); 75SHOW INDEX IN t1; 76 77ALTER TABLE t1 DROP INDEX kb, DROP INDEX kbc; 78SHOW INDEX IN t1; 79 80# test restarting to make sure everything is still ok and persisted properly 81--source include/restart_mysqld.inc 82 83--sorted_result 84INSERT INTO t1 (b,c) VALUES (1,2); 85INSERT INTO t1 (b,c) VALUES (3,4); 86INSERT INTO t1 (b,c) VALUES (5,6); 87SELECT * FROM t1 FORCE INDEX(kc) where c > 3; 88 89# test dropping index on tables with no pk 90ALTER TABLE t1 DROP INDEX kc, ALGORITHM=INPLACE; 91SHOW CREATE TABLE t1; 92 93DROP TABLE t1; 94 95# case where dropping column, where column is the key, we dont want to use 96# inplace in this scenario 97CREATE TABLE t1 (col1 INT, col2 INT, col3 INT) ENGINE=ROCKSDB; 98INSERT INTO t1 (col1,col2,col3) VALUES (1,2,3); 99ALTER TABLE t1 ADD KEY idx ( col1, col2 ); 100ANALYZE TABLE t1; 101ALTER TABLE t1 DROP COLUMN col2; 102ALTER TABLE t1 DROP COLUMN col3; 103DROP TABLE t1; 104 105# case drop and add at same time, should not use inplace algorithm yet 106CREATE TABLE t1 (col1 INT, col2 INT, col3 INT) ENGINE=ROCKSDB; 107INSERT INTO t1 (col1,col2,col3) VALUES (1,2,3); 108ALTER TABLE t1 ADD KEY idx ( col1, col2 ); 109ANALYZE TABLE t1; 110ALTER TABLE t1 DROP COLUMN col2; 111ALTER TABLE t1 DROP COLUMN col3; 112DROP TABLE t1; 113