MDEV-24763 ALTER TABLE fails to rename a column in SYS_FIELDSbb-10.2-MDEV-24763

innobase_rename_column_try(): When renaming SYS_FIELDS records
for secondary indexes, try to use both formats of SYS_FIELDS.POS
as keys, in case the PRIMARY KEY includes column prefix indexes.

Without this fix, an ALTER TABLE that renames a column followed
by a server restart would make the table inaccessible.

3 files changed, 52 insertions, 0 deletions

diff --git a/mysql-test/suite/innodb/r/alter_table.result b/mysql-test/suite/innodb/r/alter_table.result
index fc08f29e515..56f27ff7fd4 100644
--- a/mysql-test/suite/innodb/r/alter_table.result
+++ b/mysql-test/suite/innodb/r/alter_table.result
@@ -70,3 +70,12 @@ ERROR HY000: Tablespace has been discarded for table `t`
 ALTER TABLE t FORCE;
 ERROR HY000: Tablespace has been discarded for table `t`
 DROP TABLE t;
+#
+# MDEV-24763 ALTER TABLE fails to rename a column in SYS_FIELDS
+#
+CREATE TABLE t1 (a INT, b TEXT, c INT, PRIMARY KEY(b(9)), INDEX(c,a))
+ENGINE=InnoDB;
+ALTER TABLE t1 CHANGE COLUMN a u INT;
+SELECT * FROM information_schema.innodb_sys_fields where name='a';
+INDEX_ID	NAME	POS
+DROP TABLE t1;
diff --git a/mysql-test/suite/innodb/t/alter_table.test b/mysql-test/suite/innodb/t/alter_table.test
index 5050abdc087..35ae4c8f24b 100644
--- a/mysql-test/suite/innodb/t/alter_table.test
+++ b/mysql-test/suite/innodb/t/alter_table.test
@@ -79,3 +79,12 @@ ALTER TABLE t ENGINE INNODB;
 --error ER_TABLESPACE_DISCARDED
 ALTER TABLE t FORCE;
 DROP TABLE t;
+
+--echo #
+--echo # MDEV-24763 ALTER TABLE fails to rename a column in SYS_FIELDS
+--echo #
+CREATE TABLE t1 (a INT, b TEXT, c INT, PRIMARY KEY(b(9)), INDEX(c,a))
+ENGINE=InnoDB;
+ALTER TABLE t1 CHANGE COLUMN a u INT;
+SELECT * FROM information_schema.innodb_sys_fields where name='a';
+DROP TABLE t1;
diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc
index 4b077c44f17..e03621795ba 100644
--- a/storage/innobase/handler/handler0alter.cc
+++ b/storage/innobase/handler/handler0alter.cc
@@ -6734,6 +6734,7 @@ innobase_rename_column_try(
 {
 	pars_info_t*	info;
 	dberr_t		error;
+	bool clust_has_prefixes = false;
 
 	DBUG_ENTER("innobase_rename_column_try");
 
@@ -6822,6 +6823,39 @@ err_exit:
 			if (error != DB_SUCCESS) {
 				goto err_exit;
 			}
+
+			if (!has_prefixes || !clust_has_prefixes
+			    || field->prefix_len) {
+				continue;
+			}
+
+			/* For secondary indexes, the
+			has_prefixes check can be 'polluted'
+			by PRIMARY KEY column prefix. Try also
+			the simpler encoding of SYS_FIELDS.POS. */
+			info = pars_info_create();
+
+			pars_info_add_ull_literal(info, "indexid", index->id);
+			pars_info_add_int4_literal(info, "nth", i);
+			pars_info_add_str_literal(info, "new", to);
+
+			error = que_eval_sql(
+				info,
+				"PROCEDURE RENAME_SYS_FIELDS_PROC () IS\n"
+				"BEGIN\n"
+				"UPDATE SYS_FIELDS SET COL_NAME=:new\n"
+				"WHERE INDEX_ID=:indexid\n"
+				"AND POS=:nth;\n"
+				"END;\n",
+				FALSE, trx);
+
+			if (error != DB_SUCCESS) {
+				goto err_exit;
+			}
+		}
+
+		if (index == dict_table_get_first_index(user_table)) {
+			clust_has_prefixes = has_prefixes;
 		}
 	}