MDEV-18041 Database corruption after renaming a prefix-indexed column

This is a regression after MDEV-13671.

The bug is related to key part prefix lengths wich are stored in SYS_FIELDS.
Storage format is not obvious and was handled incorrectly which led to data
dictionary corruption.

SYS_FIELDS.POS actually contains prefix length too in case if any key part
has prefix length.

innobase_rename_column_try(): fixed prefixes handling

Tests for prefixed indexes added too.

Closes #1063

4 files changed, 70 insertions, 28 deletions

diff --git a/mysql-test/suite/innodb/r/innodb-alter.result b/mysql-test/suite/innodb/r/innodb-alter.result
index 2f9b832328b..57638a84517 100644
--- a/mysql-test/suite/innodb/r/innodb-alter.result
+++ b/mysql-test/suite/innodb/r/innodb-alter.result
@@ -865,6 +865,27 @@ WHERE T.NAME='test/t1';
 NAME
 a
 DROP TABLE t1;
+# and an MDEV-18041 regression related to indexes prefixes
+create table `test` (
+`test_old` varchar(255) NOT NULL,
+`other` varchar(255) NOT NULL,
+PRIMARY KEY (`test_old`,`other`),
+UNIQUE KEY uk (`test_old`(100), `other`)
+) ENGINE=InnoDB;
+select name, pos from information_schema.innodb_SYS_FIELDS where name in ('test_old', 'other', 'test_new');
+name	pos
+test_old	0
+other	1
+test_old	0
+other	1
+alter table `test` CHANGE COLUMN `test_old` `test_new` varchar(255) NOT NULL;
+select name, pos from information_schema.innodb_SYS_FIELDS where name in ('test_old', 'other', 'test_new');
+name	pos
+test_new	0
+other	1
+test_new	0
+other	1
+drop table `test`;
 #
 # BUG 20029625 - HANDLE_FATAL_SIGNAL (SIG=11) IN
 #                DICT_MEM_TABLE_COL_RENAME_LOW
diff --git a/mysql-test/suite/innodb/t/innodb-alter.test b/mysql-test/suite/innodb/t/innodb-alter.test
index 30e3292ec10..a47573626aa 100644
--- a/mysql-test/suite/innodb/t/innodb-alter.test
+++ b/mysql-test/suite/innodb/t/innodb-alter.test
@@ -522,6 +522,19 @@ SELECT C.NAME FROM INFORMATION_SCHEMA.INNODB_SYS_COLUMNS C INNER JOIN
   WHERE T.NAME='test/t1';
 DROP TABLE t1;
 
+--echo # and an MDEV-18041 regression related to indexes prefixes
+create table `test` (
+  `test_old` varchar(255) NOT NULL,
+  `other` varchar(255) NOT NULL,
+  PRIMARY KEY (`test_old`,`other`),
+  UNIQUE KEY uk (`test_old`(100), `other`)
+) ENGINE=InnoDB;
+
+select name, pos from information_schema.innodb_SYS_FIELDS where name in ('test_old', 'other', 'test_new');
+alter table `test` CHANGE COLUMN `test_old` `test_new` varchar(255) NOT NULL;
+select name, pos from information_schema.innodb_SYS_FIELDS where name in ('test_old', 'other', 'test_new');
+drop table `test`;
+
 
 --echo #
 --echo # BUG 20029625 - HANDLE_FATAL_SIGNAL (SIG=11) IN
diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc
index 6d9255a628e..8fbdf6c6884 100644
--- a/storage/innobase/handler/handler0alter.cc
+++ b/storage/innobase/handler/handler0alter.cc
@@ -4533,36 +4533,40 @@ err_exit:
 	     index != NULL;
 	     index = dict_table_get_next_index(index)) {
 
+		bool has_prefixes = false;
+		for (size_t i = 0; i < dict_index_get_n_fields(index); i++) {
+			if (dict_index_get_nth_field(index, i)->prefix_len) {
+				has_prefixes = true;
+				break;
+			}
+		}
+
 		for (ulint i = 0; i < dict_index_get_n_fields(index); i++) {
-			if (my_strcasecmp(
-				system_charset_info,
-				dict_index_get_nth_field(index, i)->name,
-				from)) {
+			const dict_field_t* field
+			    = dict_index_get_nth_field(index, i);
+			if (my_strcasecmp(system_charset_info, field->name,
+					  from)) {
 				continue;
 			}
 
 			info = pars_info_create();
 
+			int pos = i;
+			if (has_prefixes) {
+				pos = (pos << 16) + field->prefix_len;
+			}
+
 			pars_info_add_ull_literal(info, "indexid", index->id);
-			pars_info_add_int4_literal(info, "nth", i);
+			pars_info_add_int4_literal(info, "nth", pos);
 			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"
-
-				/* Try again, in case there is a prefix_len
-				encoded in SYS_FIELDS.POS */
-
-				"UPDATE SYS_FIELDS SET COL_NAME=:new\n"
-				"WHERE INDEX_ID=:indexid\n"
-				"AND POS>=65536*:nth AND POS<65536*(:nth+1);\n"
-
 				"END;\n",
 				FALSE, trx);
 
diff --git a/storage/xtradb/handler/handler0alter.cc b/storage/xtradb/handler/handler0alter.cc
index 9508adbbd12..bdec9a076ac 100644
--- a/storage/xtradb/handler/handler0alter.cc
+++ b/storage/xtradb/handler/handler0alter.cc
@@ -4547,36 +4547,40 @@ err_exit:
 	     index != NULL;
 	     index = dict_table_get_next_index(index)) {
 
+		bool has_prefixes = false;
+		for (size_t i = 0; i < dict_index_get_n_fields(index); i++) {
+			if (dict_index_get_nth_field(index, i)->prefix_len) {
+				has_prefixes = true;
+				break;
+			}
+		}
+
 		for (ulint i = 0; i < dict_index_get_n_fields(index); i++) {
-			if (my_strcasecmp(
-				system_charset_info,
-				dict_index_get_nth_field(index, i)->name,
-				from)) {
+			const dict_field_t* field
+			    = dict_index_get_nth_field(index, i);
+			if (my_strcasecmp(system_charset_info, field->name,
+					  from)) {
 				continue;
 			}
 
 			info = pars_info_create();
 
+			int pos = i;
+			if (has_prefixes) {
+				pos = (pos << 16) + field->prefix_len;
+			}
+
 			pars_info_add_ull_literal(info, "indexid", index->id);
-			pars_info_add_int4_literal(info, "nth", i);
+			pars_info_add_int4_literal(info, "nth", pos);
 			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"
-
-				/* Try again, in case there is a prefix_len
-				encoded in SYS_FIELDS.POS */
-
-				"UPDATE SYS_FIELDS SET COL_NAME=:new\n"
-				"WHERE INDEX_ID=:indexid\n"
-				"AND POS>=65536*:nth AND POS<65536*(:nth+1);\n"
-
 				"END;\n",
 				FALSE, trx);