List:Commits« Previous MessageNext Message »
From:Davi Arnaut Date:July 1 2009 9:58pm
Subject:bzr commit into mysql-5.1-bugteam branch (davi:2991) Bug#21704
View as plain text  
# At a local mysql-5.1-bugteam repository of davi

 2991 Davi Arnaut	2009-07-01
      Bug#21704: Renaming column does not update FK definition
      
      The problem is that renaming columns that take part in a
      foreign key constraint does not cause the definition to be
      updated to refer to the new column name.
      
      The solution is to not allow a column to be renamed if the
      column is part of a foreign key constraint (either in the
      referencing or referenced table).
      
      This is implemented by iterating over the table's foreign
      key constraints and checking whether a column that is part
      of the constraint is being renamed. If a rename is detected,
      the engine indicates that a full table copy should take place.
      During the table copy, foreign key checks will prevent the
      table from being altered.
     @ mysql-test/include/mix1.inc
        Add test case for Bug#21704
     @ mysql-test/include/mtr_warnings.sql
        Ignore table t3 errors.
     @ mysql-test/r/innodb_mysql.result
        Add test case result for Bug#21704
     @ storage/innobase/handler/ha_innodb.cc
        Iterate over the foreign key lists checking whether a
        column is being renamed.

    modified:
      mysql-test/include/mix1.inc
      mysql-test/include/mtr_warnings.sql
      mysql-test/r/innodb_mysql.result
      storage/innobase/handler/ha_innodb.cc
=== modified file 'mysql-test/include/mix1.inc'
--- a/mysql-test/include/mix1.inc	2009-06-15 15:57:06 +0000
+++ b/mysql-test/include/mix1.inc	2009-07-01 21:58:20 +0000
@@ -1504,37 +1504,6 @@ SELECT * FROM t1 WHERE tid = 1 AND vid =
 
 DROP TABLE t1;
 
-#
-# Bug#21704: Renaming column does not update FK definition.
-#
-
-#
-# --disable_warnings
-# DROP TABLE IF EXISTS t1;
-# DROP TABLE IF EXISTS t2;
-# --enable_warnings
-#
-# CREATE TABLE t1(id INT PRIMARY KEY)
-#   ENGINE=innodb;
-#
-# CREATE TABLE t2(
-#   t1_id INT PRIMARY KEY,
-#   CONSTRAINT fk1 FOREIGN KEY (t1_id) REFERENCES t1(id))
-#   ENGINE=innodb;
-#
-# --echo
-#
-# --disable_result_log
-# --error ER_ERROR_ON_RENAME
-# ALTER TABLE t1 CHANGE id id2 INT;
-# --enable_result_log
-#
-# --echo
-#
-# DROP TABLE t2;
-# DROP TABLE t1;
-#
-
 --echo #
 --echo # Bug #44290: explain crashes for subquery with distinct in
 --echo #             SQL_SELECT::test_quick_select
@@ -1581,4 +1550,98 @@ SELECT 1 FROM (SELECT COUNT(DISTINCT c1)
 
 DROP TABLE t1;
 
+--echo #
+--echo # Bug#21704: Renaming column does not update FK definition.
+--echo #
+
+--echo
+--echo # Test that it's not possible to rename columns participating in a
+--echo # foreign key (either in the referencing or referenced table).
+--echo
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+--enable_warnings
+
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT ENGINE=INNODB;
+
+CREATE TABLE t2 (a INT PRIMARY KEY, b INT,
+                 CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t1(a))
+ROW_FORMAT=COMPACT ENGINE=INNODB;
+
+CREATE TABLE t3 (a INT PRIMARY KEY, b INT, KEY(b), C INT,
+  CONSTRAINT fk2 FOREIGN KEY (b) REFERENCES t3 (a))
+ROW_FORMAT=COMPACT ENGINE=INNODB;
+
+INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
+INSERT INTO t2 VALUES (1,1),(2,2),(3,3);
+INSERT INTO t3 VALUES (1,1,1),(2,2,2),(3,3,3);
+
+--echo
+--echo # Test renaming the column in the referenced table.
+--echo
+
+# mysqltest first does replace_regex, then replace_result
+--replace_regex /'[^']*test\/#sql-[0-9a-f_]*'/'#sql-temporary'/
+# Embedded server doesn't chdir to data directory
+--replace_result $MYSQLTEST_VARDIR . mysqld.1/data/ ''
+--error ER_ERROR_ON_RENAME
+ALTER TABLE t1 CHANGE a c INT;
+
+--echo # Ensure that online column rename works.
+
+--enable_info
+ALTER TABLE t1 CHANGE b c INT;
+--disable_info
+
+--echo
+--echo # Test renaming the column in the referencing table
+--echo
+
+# mysqltest first does replace_regex, then replace_result
+--replace_regex /'[^']*test\/#sql-[0-9a-f_]*'/'#sql-temporary'/
+# Embedded server doesn't chdir to data directory
+--replace_result $MYSQLTEST_VARDIR . mysqld.1/data/ ''
+--error ER_ERROR_ON_RENAME
+ALTER TABLE t2 CHANGE a c INT;
+
+--echo # Ensure that online column rename works.
+
+--enable_info
+ALTER TABLE t2 CHANGE b c INT;
+--disable_info
+
+--echo
+--echo # Test with self-referential constraints
+--echo
+
+# mysqltest first does replace_regex, then replace_result
+--replace_regex /'[^']*test\/#sql-[0-9a-f_]*'/'#sql-temporary'/
+# Embedded server doesn't chdir to data directory
+--replace_result $MYSQLTEST_VARDIR . mysqld.1/data/ ''
+--error ER_ERROR_ON_RENAME
+ALTER TABLE t3 CHANGE a d INT;
+
+# mysqltest first does replace_regex, then replace_result
+--replace_regex /'[^']*test\/#sql-[0-9a-f_]*'/'#sql-temporary'/
+# Embedded server doesn't chdir to data directory
+--replace_result $MYSQLTEST_VARDIR . mysqld.1/data/ ''
+--error ER_ERROR_ON_RENAME
+ALTER TABLE t3 CHANGE b d INT;
+
+--echo # Ensure that online column rename works.
+
+--enable_info
+ALTER TABLE t3 CHANGE c d INT;
+--disable_info
+
+--echo
+--echo # Cleanup.
+--echo
+
+DROP TABLE t3;
+DROP TABLE t2;
+DROP TABLE t1;
+
 --echo End of 5.1 tests

=== modified file 'mysql-test/include/mtr_warnings.sql'
--- a/mysql-test/include/mtr_warnings.sql	2009-01-27 13:53:58 +0000
+++ b/mysql-test/include/mtr_warnings.sql	2009-07-01 21:58:20 +0000
@@ -139,9 +139,9 @@ INSERT INTO global_suppressions VALUES
  ("Cannot find or open table test\/bug29807 from"),
 
  /* innodb foreign key tests that fail in ALTER or RENAME produce this */
- ("InnoDB: Error: in ALTER TABLE `test`.`t[12]`"),
+ ("InnoDB: Error: in ALTER TABLE `test`.`t[123]`"),
  ("InnoDB: Error: in RENAME TABLE table `test`.`t1`"),
- ("InnoDB: Error: table `test`.`t[12]` does not exist in the InnoDB internal"),
+ ("InnoDB: Error: table `test`.`t[123]` does not exist in the InnoDB internal"),
 
  /* Test case for Bug#14233 produces the following warnings: */
  ("Stored routine 'test'.'bug14233_1': invalid value in column mysql.proc"),

=== modified file 'mysql-test/r/innodb_mysql.result'
--- a/mysql-test/r/innodb_mysql.result	2009-06-15 15:57:06 +0000
+++ b/mysql-test/r/innodb_mysql.result	2009-07-01 21:58:20 +0000
@@ -1759,6 +1759,60 @@ id	select_type	table	type	possible_keys	
 1	PRIMARY	<derived2>	system	NULL	NULL	NULL	NULL	1	
 2	DERIVED	t1	index	c3,c2	c2	14	NULL	5	
 DROP TABLE t1;
+#
+# Bug#21704: Renaming column does not update FK definition.
+#
+
+# Test that it's not possible to rename columns participating in a
+# foreign key (either in the referencing or referenced table).
+
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ROW_FORMAT=COMPACT ENGINE=INNODB;
+CREATE TABLE t2 (a INT PRIMARY KEY, b INT,
+CONSTRAINT fk1 FOREIGN KEY (a) REFERENCES t1(a))
+ROW_FORMAT=COMPACT ENGINE=INNODB;
+CREATE TABLE t3 (a INT PRIMARY KEY, b INT, KEY(b), C INT,
+CONSTRAINT fk2 FOREIGN KEY (b) REFERENCES t3 (a))
+ROW_FORMAT=COMPACT ENGINE=INNODB;
+INSERT INTO t1 VALUES (1,1),(2,2),(3,3);
+INSERT INTO t2 VALUES (1,1),(2,2),(3,3);
+INSERT INTO t3 VALUES (1,1,1),(2,2,2),(3,3,3);
+
+# Test renaming the column in the referenced table.
+
+ALTER TABLE t1 CHANGE a c INT;
+ERROR HY000: Error on rename of '#sql-temporary' to './test/t1' (errno: 150)
+# Ensure that online column rename works.
+ALTER TABLE t1 CHANGE b c INT;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+
+# Test renaming the column in the referencing table
+
+ALTER TABLE t2 CHANGE a c INT;
+ERROR HY000: Error on rename of '#sql-temporary' to './test/t2' (errno: 150)
+# Ensure that online column rename works.
+ALTER TABLE t2 CHANGE b c INT;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+
+# Test with self-referential constraints
+
+ALTER TABLE t3 CHANGE a d INT;
+ERROR HY000: Error on rename of '#sql-temporary' to './test/t3' (errno: 150)
+ALTER TABLE t3 CHANGE b d INT;
+ERROR HY000: Error on rename of '#sql-temporary' to './test/t3' (errno: 150)
+# Ensure that online column rename works.
+ALTER TABLE t3 CHANGE c d INT;
+affected rows: 0
+info: Records: 0  Duplicates: 0  Warnings: 0
+
+# Cleanup.
+
+DROP TABLE t3;
+DROP TABLE t2;
+DROP TABLE t1;
 End of 5.1 tests
 drop table if exists t1, t2, t3;
 create table t1(a int);

=== modified file 'storage/innobase/handler/ha_innodb.cc'
--- a/storage/innobase/handler/ha_innodb.cc	2009-06-25 09:52:46 +0000
+++ b/storage/innobase/handler/ha_innodb.cc	2009-07-01 21:58:20 +0000
@@ -8161,6 +8161,70 @@ innobase_set_cursor_view(
 }
 
 
+/***********************************************************************
+Check if column that is participates in a foreign key is being renamed. */
+static
+bool
+column_is_being_renamed(
+/*=====================*/
+	TABLE*		table,		/* in: MySQL table */
+	uint		n_cols,		/* in: number of columns */
+	const char**	col_names)	/* in: names of the columns */
+{
+	Field *field;
+	const char *col_name;
+
+	for (uint j = 0; j < n_cols; j++) {
+		col_name = col_names[j];
+		for (uint k = 0; k < table->s->fields; k++) {
+			field = table->field[k];
+			if (!(field->flags & FIELD_IS_RENAMED))
+				continue;
+			if (0 == innobase_strcasecmp(field->field_name, col_name))
+				goto match;
+		}
+	}
+
+	return FALSE;
+match:
+	return TRUE;
+}
+
+/***********************************************************************
+Check whether a column that participates in a foreign key definition is
+being renamed. */
+static
+bool
+renames_column_in_foreign_key(
+	row_prebuilt_t*	prebuilt,	/* in: InnoDB prebuilt struct */
+	TABLE*		table)		/* in: MySQL table */
+{
+	bool ret = FALSE;
+	dict_foreign_t* foreign;
+
+	row_mysql_lock_data_dictionary(prebuilt->trx);
+
+	/* Renaming a column in the referenced table. */
+	foreign = UT_LIST_GET_FIRST(prebuilt->table->referenced_list);
+	while (foreign != NULL && ret == FALSE) {
+		ret = column_is_being_renamed(table, foreign->n_fields,
+						foreign->referenced_col_names);
+		foreign = UT_LIST_GET_NEXT(referenced_list, foreign);
+	}
+
+	/* Renaming a column in the referencing table. */
+	foreign = UT_LIST_GET_FIRST(prebuilt->table->foreign_list);
+	while (foreign != NULL && ret == FALSE) {
+		ret = column_is_being_renamed(table, foreign->n_fields,
+						foreign->foreign_col_names);
+		foreign = UT_LIST_GET_NEXT(foreign_list, foreign);
+	}
+
+	row_mysql_unlock_data_dictionary(prebuilt->trx);
+
+	return ret;
+}
+
 bool ha_innobase::check_if_incompatible_data(
 	HA_CREATE_INFO*	info,
 	uint		table_changes)
@@ -8177,6 +8241,16 @@ bool ha_innobase::check_if_incompatible_
 		return COMPATIBLE_DATA_NO;
 	}
 
+	/* Check if a column participating in a foreign key was renamed.
+	There is no mechanism for updating InnoDB foreign key definitions. */
+	if ((UT_LIST_GET_LEN(prebuilt->table->foreign_list) > 0) ||
+		(UT_LIST_GET_LEN(prebuilt->table->referenced_list) > 0)) {
+		if (renames_column_in_foreign_key(prebuilt, table) == TRUE) {
+
+			return COMPATIBLE_DATA_NO;
+		}
+	}
+
 	/* Check that row format didn't change */
 	if ((info->used_fields & HA_CREATE_USED_ROW_FORMAT) &&
 		get_row_type() != info->row_type) {


Attachment: [text/bzr-bundle] bzr/davi.arnaut@sun.com-20090701215820-roilpc8xpi9nb39b.bundle
Thread
bzr commit into mysql-5.1-bugteam branch (davi:2991) Bug#21704Davi Arnaut1 Jul