From 93beb269c29b69f21b60822bfc00dd6a8b81edd9 Mon Sep 17 00:00:00 2001 From: "xingying.1024" Date: Fri, 1 May 2026 23:10:18 +0800 Subject: [PATCH] Fixed a bug in the DDL for invisible indexes. --- mysql-test/r/bug118233.result | 187 ++++++++++++++++++++++++++++++++++ mysql-test/t/bug118233.test | 139 +++++++++++++++++++++++++ sql/sql_table.cc | 37 ++++++- 3 files changed, 362 insertions(+), 1 deletion(-) create mode 100644 mysql-test/r/bug118233.result create mode 100644 mysql-test/t/bug118233.test diff --git a/mysql-test/r/bug118233.result b/mysql-test/r/bug118233.result new file mode 100644 index 00000000000..fd9ce349b71 --- /dev/null +++ b/mysql-test/r/bug118233.result @@ -0,0 +1,187 @@ +# +# 1. Default algorithm: DROP + ADD INDEX with visibility flip +# must take effect (originally reported case). +# +CREATE TABLE t1 ( +user_id INT PRIMARY KEY AUTO_INCREMENT, +user_name VARCHAR(50), +age INT, +city VARCHAR(50), +KEY idx_age (age) +) ENGINE=InnoDB; +ALTER TABLE t1 DROP INDEX idx_age, ADD INDEX idx_age (age) INVISIBLE; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `user_id` int NOT NULL AUTO_INCREMENT, + `user_name` varchar(50) DEFAULT NULL, + `age` int DEFAULT NULL, + `city` varchar(50) DEFAULT NULL, + PRIMARY KEY (`user_id`), + KEY `idx_age` (`age`) /*!80000 INVISIBLE */ +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci +SELECT INDEX_NAME, IS_VISIBLE +FROM information_schema.statistics +WHERE table_schema = DATABASE() AND table_name = 't1' AND index_name = 'idx_age'; +INDEX_NAME IS_VISIBLE +idx_age NO +ALTER TABLE t1 DROP INDEX idx_age, ADD INDEX idx_age (age) VISIBLE; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `user_id` int NOT NULL AUTO_INCREMENT, + `user_name` varchar(50) DEFAULT NULL, + `age` int DEFAULT NULL, + `city` varchar(50) DEFAULT NULL, + PRIMARY KEY (`user_id`), + KEY `idx_age` (`age`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci +SELECT INDEX_NAME, IS_VISIBLE +FROM information_schema.statistics +WHERE table_schema = DATABASE() AND table_name = 't1' AND index_name = 'idx_age'; +INDEX_NAME IS_VISIBLE +idx_age YES +DROP TABLE t1; +# +# 2. ALGORITHM=INPLACE must keep working: DROP+ADD with visibility +# flip succeeds in-place (no ER_ALTER_OPERATION_NOT_SUPPORTED). +# +CREATE TABLE t1 ( +a INT, +b INT, +KEY k (a) +) ENGINE=InnoDB; +ALTER TABLE t1 DROP INDEX k, ADD INDEX k (a) INVISIBLE, ALGORITHM=INPLACE; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int DEFAULT NULL, + `b` int DEFAULT NULL, + KEY `k` (`a`) /*!80000 INVISIBLE */ +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci +ALTER TABLE t1 DROP INDEX k, ADD INDEX k (a) VISIBLE, ALGORITHM=INPLACE; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int DEFAULT NULL, + `b` int DEFAULT NULL, + KEY `k` (`a`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci +DROP TABLE t1; +# +# 3. ALGORITHM=COPY variant works and yields the right visibility. +# +CREATE TABLE t1 ( +a INT, +b INT, +KEY k (a) +) ENGINE=InnoDB; +ALTER TABLE t1 DROP INDEX k, ADD INDEX k (a) INVISIBLE, ALGORITHM=COPY; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int DEFAULT NULL, + `b` int DEFAULT NULL, + KEY `k` (`a`) /*!80000 INVISIBLE */ +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci +DROP TABLE t1; +# +# 4. Regression guard for the previously rejected patch scenario: +# ALTER INDEX ... VISIBLE, ALGORITHM=INPLACE must stay INPLACE. +# +CREATE TABLE t1 (a INT, b INT, KEY (a) INVISIBLE, KEY (b)) ENGINE=InnoDB; +ALTER TABLE t1 ALTER INDEX a VISIBLE, ALGORITHM=INPLACE; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int DEFAULT NULL, + `b` int DEFAULT NULL, + KEY `a` (`a`), + KEY `b` (`b`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci +ALTER TABLE t1 ALTER INDEX a INVISIBLE, ALGORITHM=INPLACE; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int DEFAULT NULL, + `b` int DEFAULT NULL, + KEY `a` (`a`) /*!80000 INVISIBLE */, + KEY `b` (`b`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci +DROP TABLE t1; +# +# 5. Multi-column / prefix / composite key variant. +# +CREATE TABLE t1 ( +a VARCHAR(64), +b INT, +c INT, +KEY idx_ab (a(10), b) +) ENGINE=InnoDB; +ALTER TABLE t1 DROP INDEX idx_ab, ADD INDEX idx_ab (a(10), b) INVISIBLE; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` varchar(64) DEFAULT NULL, + `b` int DEFAULT NULL, + `c` int DEFAULT NULL, + KEY `idx_ab` (`a`(10),`b`) /*!80000 INVISIBLE */ +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci +SELECT INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, SUB_PART, IS_VISIBLE +FROM information_schema.statistics +WHERE table_schema = DATABASE() AND table_name = 't1' + ORDER BY SEQ_IN_INDEX; +INDEX_NAME SEQ_IN_INDEX COLUMN_NAME SUB_PART IS_VISIBLE +idx_ab 1 a 10 NO +idx_ab 2 b NULL NO +DROP TABLE t1; +# +# 6. Combined with an unrelated DROP/ADD in the same statement: +# visibility flip on k1 coexists with a structural change on k2. +# +CREATE TABLE t1 ( +a INT, +b INT, +c INT, +KEY k1 (a), +KEY k2 (b) +) ENGINE=InnoDB; +ALTER TABLE t1 +DROP INDEX k1, ADD INDEX k1 (a) INVISIBLE, +DROP INDEX k2, ADD INDEX k2 (b, c); +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int DEFAULT NULL, + `b` int DEFAULT NULL, + `c` int DEFAULT NULL, + KEY `k1` (`a`) /*!80000 INVISIBLE */, + KEY `k2` (`b`,`c`) +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci +SELECT INDEX_NAME, COLUMN_NAME, IS_VISIBLE +FROM information_schema.statistics +WHERE table_schema = DATABASE() AND table_name = 't1' + ORDER BY INDEX_NAME, SEQ_IN_INDEX; +INDEX_NAME COLUMN_NAME IS_VISIBLE +k1 a NO +k2 b YES +k2 c YES +DROP TABLE t1; +# +# 7. Unique key variant. +# +CREATE TABLE t1 (a INT, b INT, UNIQUE KEY uk (a)) ENGINE=InnoDB; +ALTER TABLE t1 DROP INDEX uk, ADD UNIQUE KEY uk (a) INVISIBLE; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `a` int DEFAULT NULL, + `b` int DEFAULT NULL, + UNIQUE KEY `uk` (`a`) /*!80000 INVISIBLE */ +) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci +SELECT INDEX_NAME, NON_UNIQUE, IS_VISIBLE +FROM information_schema.statistics +WHERE table_schema = DATABASE() AND table_name = 't1'; +INDEX_NAME NON_UNIQUE IS_VISIBLE +uk 0 NO +DROP TABLE t1; diff --git a/mysql-test/t/bug118233.test b/mysql-test/t/bug118233.test new file mode 100644 index 00000000000..23535250ed3 --- /dev/null +++ b/mysql-test/t/bug118233.test @@ -0,0 +1,139 @@ +# +# Bug#118233: DROP INDEX + ADD INDEX in a single ALTER TABLE silently +# loses the INVISIBLE / VISIBLE change. +# +# When an identical-looking key (same name, same columns, same prefix, +# same flags) is dropped and re-added in a single ALTER TABLE with a +# different visibility, fill_alter_inplace_info() used to treat the +# new and old keys as structurally identical and emitted no handler +# flags, so the visibility change was silently lost on disk. +# +# The fix detects a mismatch between table_key->is_visible and +# new_key->is_visible after has_index_def_changed() returns false, and +# funnels it through the same fast in-place path that +# ALTER INDEX ... [IN]VISIBLE already uses. +# + +--echo # +--echo # 1. Default algorithm: DROP + ADD INDEX with visibility flip +--echo # must take effect (originally reported case). +--echo # +CREATE TABLE t1 ( + user_id INT PRIMARY KEY AUTO_INCREMENT, + user_name VARCHAR(50), + age INT, + city VARCHAR(50), + KEY idx_age (age) +) ENGINE=InnoDB; + +# Visible -> Invisible +ALTER TABLE t1 DROP INDEX idx_age, ADD INDEX idx_age (age) INVISIBLE; +SHOW CREATE TABLE t1; +SELECT INDEX_NAME, IS_VISIBLE + FROM information_schema.statistics + WHERE table_schema = DATABASE() AND table_name = 't1' AND index_name = 'idx_age'; + +# Invisible -> Visible +ALTER TABLE t1 DROP INDEX idx_age, ADD INDEX idx_age (age) VISIBLE; +SHOW CREATE TABLE t1; +SELECT INDEX_NAME, IS_VISIBLE + FROM information_schema.statistics + WHERE table_schema = DATABASE() AND table_name = 't1' AND index_name = 'idx_age'; + +DROP TABLE t1; + +--echo # +--echo # 2. ALGORITHM=INPLACE must keep working: DROP+ADD with visibility +--echo # flip succeeds in-place (no ER_ALTER_OPERATION_NOT_SUPPORTED). +--echo # +CREATE TABLE t1 ( + a INT, + b INT, + KEY k (a) +) ENGINE=InnoDB; + +ALTER TABLE t1 DROP INDEX k, ADD INDEX k (a) INVISIBLE, ALGORITHM=INPLACE; +SHOW CREATE TABLE t1; + +ALTER TABLE t1 DROP INDEX k, ADD INDEX k (a) VISIBLE, ALGORITHM=INPLACE; +SHOW CREATE TABLE t1; + +DROP TABLE t1; + +--echo # +--echo # 3. ALGORITHM=COPY variant works and yields the right visibility. +--echo # +CREATE TABLE t1 ( + a INT, + b INT, + KEY k (a) +) ENGINE=InnoDB; + +ALTER TABLE t1 DROP INDEX k, ADD INDEX k (a) INVISIBLE, ALGORITHM=COPY; +SHOW CREATE TABLE t1; + +DROP TABLE t1; + +--echo # +--echo # 4. Regression guard for the previously rejected patch scenario: +--echo # ALTER INDEX ... VISIBLE, ALGORITHM=INPLACE must stay INPLACE. +--echo # +CREATE TABLE t1 (a INT, b INT, KEY (a) INVISIBLE, KEY (b)) ENGINE=InnoDB; +ALTER TABLE t1 ALTER INDEX a VISIBLE, ALGORITHM=INPLACE; +SHOW CREATE TABLE t1; +ALTER TABLE t1 ALTER INDEX a INVISIBLE, ALGORITHM=INPLACE; +SHOW CREATE TABLE t1; +DROP TABLE t1; + +--echo # +--echo # 5. Multi-column / prefix / composite key variant. +--echo # +CREATE TABLE t1 ( + a VARCHAR(64), + b INT, + c INT, + KEY idx_ab (a(10), b) +) ENGINE=InnoDB; + +ALTER TABLE t1 DROP INDEX idx_ab, ADD INDEX idx_ab (a(10), b) INVISIBLE; +SHOW CREATE TABLE t1; +SELECT INDEX_NAME, SEQ_IN_INDEX, COLUMN_NAME, SUB_PART, IS_VISIBLE + FROM information_schema.statistics + WHERE table_schema = DATABASE() AND table_name = 't1' + ORDER BY SEQ_IN_INDEX; + +DROP TABLE t1; + +--echo # +--echo # 6. Combined with an unrelated DROP/ADD in the same statement: +--echo # visibility flip on k1 coexists with a structural change on k2. +--echo # +CREATE TABLE t1 ( + a INT, + b INT, + c INT, + KEY k1 (a), + KEY k2 (b) +) ENGINE=InnoDB; + +ALTER TABLE t1 + DROP INDEX k1, ADD INDEX k1 (a) INVISIBLE, + DROP INDEX k2, ADD INDEX k2 (b, c); +SHOW CREATE TABLE t1; +SELECT INDEX_NAME, COLUMN_NAME, IS_VISIBLE + FROM information_schema.statistics + WHERE table_schema = DATABASE() AND table_name = 't1' + ORDER BY INDEX_NAME, SEQ_IN_INDEX; + +DROP TABLE t1; + +--echo # +--echo # 7. Unique key variant. +--echo # +CREATE TABLE t1 (a INT, b INT, UNIQUE KEY uk (a)) ENGINE=InnoDB; +ALTER TABLE t1 DROP INDEX uk, ADD UNIQUE KEY uk (a) INVISIBLE; +SHOW CREATE TABLE t1; +SELECT INDEX_NAME, NON_UNIQUE, IS_VISIBLE + FROM information_schema.statistics + WHERE table_schema = DATABASE() AND table_name = 't1'; +DROP TABLE t1; diff --git a/sql/sql_table.cc b/sql/sql_table.cc index 5269d211178..c89f12f1399 100644 --- a/sql/sql_table.cc +++ b/sql/sql_table.cc @@ -11949,7 +11949,17 @@ static bool fill_alter_inplace_info(THD *thd, TABLE *table, !(ha_alter_info->index_rename_buffer = (KEY_PAIR *)thd->alloc( sizeof(KEY_PAIR) * alter_info->alter_rename_key_list.size())) || !(ha_alter_info->index_altered_visibility_buffer = (KEY_PAIR *)thd->alloc( - sizeof(KEY_PAIR) * alter_info->alter_index_visibility_list.size()))) + /* + Bug#118233: in addition to explicit + ALTER INDEX ... VISIBLE/INVISIBLE entries, DROP INDEX + + ADD INDEX on the same name can also produce an implicit + visibility change (detected later in the per-key loop). + Upper-bound the buffer by the number of existing keys so + add_altered_index_visibility() never overflows. + */ + sizeof(KEY_PAIR) * + (alter_info->alter_index_visibility_list.size() + + table->s->keys)))) return true; /* First we setup ha_alter_flags based on what was detected by parser. */ @@ -12385,6 +12395,31 @@ static bool fill_alter_inplace_info(THD *thd, TABLE *table, */ if (table_key->is_algorithm_explicit != new_key->is_algorithm_explicit) ha_alter_info->handler_flags |= Alter_inplace_info::CHANGE_INDEX_OPTION; + + /* + Bug#118233: DROP INDEX + ADD INDEX ... [IN]VISIBLE on the same key + name with identical structure reaches here because has_index_def_ + changed() does not consider KEY::is_visible. The explicit + "ALTER INDEX ... VISIBLE/INVISIBLE" path is already handled in the + alter_index_visibility_list loop above; detect here the implicit + visibility change coming from a DROP+ADD pair, and route it through + the same fast in-place path to avoid silently losing the change. + */ + if (table_key->is_visible != new_key->is_visible) { + bool already_handled = false; + for (const Alter_index_visibility *alter_index_visibility : + alter_info->alter_index_visibility_list) { + if (my_strcasecmp(system_charset_info, new_key->name, + alter_index_visibility->name()) == 0) { + already_handled = true; + break; + } + } + if (!already_handled) { + ha_alter_info->handler_flags |= Alter_inplace_info::RENAME_INDEX; + ha_alter_info->add_altered_index_visibility(table_key, new_key); + } + } } } -- 2.39.3 (Apple Git-145)