# 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 23:31:22 +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,99 @@ 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;
+DROP TABLE IF EXISTS t3;
+--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 23:31:22 +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 23:31:22 +0000
@@ -1759,6 +1759,61 @@ 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;
+DROP TABLE IF EXISTS t3;
+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 23:31:22 +0000
@@ -8161,6 +8161,71 @@ 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 */
+{
+ uint j, k;
+ Field *field;
+ const char *col_name;
+
+ for (j = 0; j < n_cols; j++) {
+ col_name = col_names[j];
+ for (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
+is_foreign_key_column_renamed(
+ 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 +8242,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 (is_foreign_key_column_renamed(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-20090701233122-qw621x8eqo4olycc.bundle
