# At a local mysql-5.1-bugteam repository of davi
2988 Davi Arnaut 2009-06-29
Bug#21704: Renaming column does not update FK definition
The problem is that renaming columns that are referenced by
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 being referenced by a foreign key constraint.
This is implemented by iterating over the table's referenced
foreign key constraints and checking whether a referenced
column name 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/r/innodb_mysql.result
Add test case result for Bug#21704
@ storage/innobase/handler/ha_innodb.cc
Iterate over the referenced list checking whether a column
is being renamed.
modified:
mysql-test/include/mix1.inc
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-06-30 02:25:53 +0000
@@ -1508,32 +1508,32 @@ 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;
-#
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+--enable_warnings
+
+CREATE TABLE t1(id INT PRIMARY KEY, co INT)
+ 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;
+ALTER TABLE t1 CHANGE co co2 INT;
+--enable_result_log
+
+--echo
+
+DROP TABLE t2;
+DROP TABLE t1;
+
--echo #
--echo # Bug #44290: explain crashes for subquery with distinct in
=== 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-06-30 02:25:53 +0000
@@ -1711,6 +1711,20 @@ vid tid idx name type
3 1 2 c1 NULL
3 1 1 pk NULL
DROP TABLE t1;
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+CREATE TABLE t1(id INT PRIMARY KEY, co INT)
+ENGINE=innodb;
+CREATE TABLE t2(
+t1_id INT PRIMARY KEY,
+CONSTRAINT fk1 FOREIGN KEY (t1_id) REFERENCES t1(id))
+ENGINE=innodb;
+
+ALTER TABLE t1 CHANGE id id2 INT;
+ALTER TABLE t1 CHANGE co co2 INT;
+
+DROP TABLE t2;
+DROP TABLE t1;
#
# Bug #44290: explain crashes for subquery with distinct in
# SQL_SELECT::test_quick_select
=== 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-06-30 02:25:53 +0000
@@ -8161,6 +8161,34 @@ innobase_set_cursor_view(
}
+/***********************************************************************
+Check if column that is referenced by a foreign key is being renamed. */
+static
+bool
+foreign_referenced_is_renamed(
+/*=====================*/
+ TABLE* table, /* in: MySQL table */
+ dict_foreign_t* foreign)/* in: referenced key */
+{
+ Field *field;
+ const char *col_name;
+
+ for (uint j = 0; j < foreign->n_fields; j++) {
+ col_name = foreign->referenced_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;
+}
+
bool ha_innobase::check_if_incompatible_data(
HA_CREATE_INFO* info,
uint table_changes)
@@ -8177,6 +8205,24 @@ bool ha_innobase::check_if_incompatible_
return COMPATIBLE_DATA_NO;
}
+ /* Check if a column referenced by a foreign key was renamed.
+ There is no mechanism for updating InnoDB foreign key definitions. */
+ if (referenced_by_foreign_key()) {
+ dict_foreign_t* foreign;
+ row_mysql_lock_data_dictionary(prebuilt->trx);
+ foreign = UT_LIST_GET_FIRST(prebuilt->table->referenced_list);
+ while (foreign != NULL) {
+ if (foreign_referenced_is_renamed(table, foreign))
+ break;
+ foreign = UT_LIST_GET_NEXT(referenced_list, foreign);
+ }
+ row_mysql_unlock_data_dictionary(prebuilt->trx);
+ if (foreign != NULL) {
+
+ 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-20090630022553-kyxvyfoo3d1n4hfa.bundle
