MDEV-31086 MODIFY COLUMN can break FK constraints, and lead to unrestorable dumpsbb-10.6-MDEV-31086

ha_innobase::check_if_supported_inplace_alter(): Doesn't support
the column modification when the column is involved in
foreign key relationship. This behaviour applies when
foreign_key_checks is enabled.

3 files changed, 144 insertions, 0 deletions

diff --git a/mysql-test/suite/innodb/r/foreign_key.result b/mysql-test/suite/innodb/r/foreign_key.result
index acf021db22c..f2896627a2f 100644
--- a/mysql-test/suite/innodb/r/foreign_key.result
+++ b/mysql-test/suite/innodb/r/foreign_key.result
@@ -1029,4 +1029,52 @@ connection con1;
 disconnect con1;
 connection default;
 DROP TABLE IF EXISTS t2, t1;
+#
+#  MDEV-31086 MODIFY COLUMN can break FK constraints, and
+#	lead to unrestorable dumps
+#
+CREATE TABLE t1(
+id SERIAL,
+msg VARCHAR(100) CHARACTER SET utf8mb3,
+KEY(msg))ENGINE=InnoDB;
+CREATE TABLE t2(
+id SERIAL,
+msg varchar(100) CHARACTER SET utf8mb4,
+CONSTRAINT fk_t1 FOREIGN KEY (msg) REFERENCES t1 (msg))ENGINE=InnoDB;
+ERROR HY000: Can't create table `test`.`t2` (errno: 150 "Foreign key constraint is incorrectly formed")
+CREATE TABLE t2(
+id SERIAL,
+msg varchar(100) CHARACTER SET utf8mb3,
+CONSTRAINT fk_t1 FOREIGN KEY (msg) REFERENCES t1 (msg)
+ON DELETE CASCADE)ENGINE=InnoDB;
+SET FOREIGN_KEY_CHECKS=1;
+ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(100) CHARACTER SET utf8mb4, algorithm=INPLACE;
+ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Column change is not allowed when foreign key is involved. Disable foreign_key_checks and try again. Try ALGORITHM=COPY
+ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(200) CHARACTER SET utf8mb3, algorithm=INPLACE;
+ERROR 0A000: ALGORITHM=INPLACE is not supported. Reason: Column change is not allowed when foreign key is involved. Disable foreign_key_checks and try again. Try ALGORITHM=COPY
+SET FOREIGN_KEY_CHECKS=0;
+ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(200) CHARACTER SET utf8mb3, algorithm=INPLACE;
+ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(200) CHARACTER SET utf8mb4, algorithm=INPLACE;
+SHOW CREATE TABLE t1;
+Table	Create Table
+t1	CREATE TABLE `t1` (
+  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+  `msg` varchar(100) CHARACTER SET utf8mb3 COLLATE utf8mb3_general_ci DEFAULT NULL,
+  UNIQUE KEY `id` (`id`),
+  KEY `msg` (`msg`)
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
+  `msg` varchar(200) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,
+  UNIQUE KEY `id` (`id`),
+  KEY `fk_t1` (`msg`),
+  CONSTRAINT `fk_t1` FOREIGN KEY (`msg`) REFERENCES `t1` (`msg`) ON DELETE CASCADE
+) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_swedish_ci
+SET FOREIGN_KEY_CHECKS=1;
+INSERT INTO t1 VALUES(1, REPEAT('a', 100));
+INSERT INTO t2 VALUES(1, REPEAT('a', 200));
+ERROR 23000: Cannot add or update a child row: a foreign key constraint fails (`test`.`t2`, CONSTRAINT `fk_t1` FOREIGN KEY (`msg`) REFERENCES `t1` (`msg`) ON DELETE CASCADE)
+DROP TABLE t2, t1;
 # End of 10.6 tests
diff --git a/mysql-test/suite/innodb/t/foreign_key.test b/mysql-test/suite/innodb/t/foreign_key.test
index 4b047ea4d4a..29862b2c0de 100644
--- a/mysql-test/suite/innodb/t/foreign_key.test
+++ b/mysql-test/suite/innodb/t/foreign_key.test
@@ -1072,6 +1072,49 @@ INSERT INTO t2 VALUES (1);
 DROP TABLE IF EXISTS t2, t1;
 --enable_warnings
 
+--echo #
+--echo #  MDEV-31086 MODIFY COLUMN can break FK constraints, and
+--echo #	lead to unrestorable dumps
+--echo #
+CREATE TABLE t1(
+  id SERIAL,
+  msg VARCHAR(100) CHARACTER SET utf8mb3,
+  KEY(msg))ENGINE=InnoDB;
+
+--error ER_CANT_CREATE_TABLE
+CREATE TABLE t2(
+id SERIAL,
+msg varchar(100) CHARACTER SET utf8mb4,
+CONSTRAINT fk_t1 FOREIGN KEY (msg) REFERENCES t1 (msg))ENGINE=InnoDB;
+
+CREATE TABLE t2(
+id SERIAL,
+msg varchar(100) CHARACTER SET utf8mb3,
+CONSTRAINT fk_t1 FOREIGN KEY (msg) REFERENCES t1 (msg)
+ON DELETE CASCADE)ENGINE=InnoDB;
+
+SET FOREIGN_KEY_CHECKS=1;
+--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
+ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(100) CHARACTER SET utf8mb4, algorithm=INPLACE;
+
+--error ER_ALTER_OPERATION_NOT_SUPPORTED_REASON
+ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(200) CHARACTER SET utf8mb3, algorithm=INPLACE;
+
+# Allow column character length, character set change when
+# foreign_key_check is disabled
+
+SET FOREIGN_KEY_CHECKS=0;
+ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(200) CHARACTER SET utf8mb3, algorithm=INPLACE;
+ALTER TABLE t2 MODIFY COLUMN msg VARCHAR(200) CHARACTER SET utf8mb4, algorithm=INPLACE;
+
+SHOW CREATE TABLE t1;
+SHOW CREATE TABLE t2;
+
+SET FOREIGN_KEY_CHECKS=1;
+INSERT INTO t1 VALUES(1, REPEAT('a', 100));
+--error ER_NO_REFERENCED_ROW_2
+INSERT INTO t2 VALUES(1, REPEAT('a', 200));
+DROP TABLE t2, t1;
 --echo # End of 10.6 tests
 
 --source include/wait_until_count_sessions.inc
diff --git a/storage/innobase/handler/handler0alter.cc b/storage/innobase/handler/handler0alter.cc
index 06101e6cca5..0aa387db063 100644
--- a/storage/innobase/handler/handler0alter.cc
+++ b/storage/innobase/handler/handler0alter.cc
@@ -2183,6 +2183,31 @@ next_page:
   goto scan_leaf;
 }
 
+/** Check whether the column is present in table foreign key
+relations.
+@param	table	table which has foreign key relation
+@param	col	column to be checked */
+static bool check_col_is_in_fk_indexes(const dict_table_t *table,
+                                       const dict_col_t *col)
+{
+  for (dict_foreign_set::iterator it= table->referenced_set.begin();
+       it!= table->referenced_set.end(); ++it)
+  {
+    for (ulint i= 0; i < (*it)->n_fields; i++)
+      if ((*it)->referenced_index->fields[i].col == col)
+        return true;
+  }
+
+  for (dict_foreign_set::iterator it= table->foreign_set.begin();
+       it!= table->foreign_set.end(); ++it)
+  {
+    for (ulint i= 0; i < (*it)->n_fields; i++)
+      if ((*it)->foreign_index->fields[i].col == col)
+        return true;
+  }
+  return false;
+}
+
 /** Check if InnoDB supports a particular alter table in-place
 @param altered_table TABLE object for new version of table.
 @param ha_alter_info Structure describing changes to be done
@@ -2666,6 +2691,34 @@ next_column:
 		af++;
 	}
 
+	/** Alter shouldn't support if the foreign and referenced
+	index columns are modified */
+	if ((ha_alter_info->handler_flags
+	     & ALTER_COLUMN_TYPE_CHANGE_BY_ENGINE)
+	    && !thd_test_options(
+			m_user_thd, OPTION_NO_FOREIGN_KEY_CHECKS)) {
+		List_iterator<Create_field> it(
+			ha_alter_info->alter_info->create_list);
+		for (uint i = 0; i < table->s->fields; i++) {
+			Field* field = table->field[i];
+			Create_field *f= it++;
+			if (!f || field->is_equal(*f))
+				continue;
+
+			dict_col_t *col= dict_table_get_nth_col(
+				m_prebuilt->table, i);
+			/* Check foreign key index */
+			if (check_col_is_in_fk_indexes(
+				m_prebuilt->table, col)) {
+				ha_alter_info->unsupported_reason=
+				  "Column change is not allowed when"
+				  " foreign key is involved. Disable "
+				  "foreign_key_checks and try again";
+				DBUG_RETURN(HA_ALTER_INPLACE_NOT_SUPPORTED);
+			}
+		}
+	}
+
 	const bool supports_instant = instant_alter_column_possible(
 		*m_prebuilt->table, ha_alter_info, table, altered_table,
 		is_innodb_strict_mode());