List:Commits« Previous MessageNext Message »
From:Davi Arnaut Date:June 30 2009 2:25am
Subject:bzr commit into mysql-5.1-bugteam branch (davi:2988) Bug#21704
View as plain text  
# 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
Thread
bzr commit into mysql-5.1-bugteam branch (davi:2988) Bug#21704Davi Arnaut30 Jun
  • Re: bzr commit into mysql-5.1-bugteam branch (davi:2988) Bug#21704Konstantin Osipov1 Jul
    • Re: bzr commit into mysql-5.1-bugteam branch (davi:2988) Bug#21704Davi Arnaut1 Jul
      • Re: bzr commit into mysql-5.1-bugteam branch (davi:2988) Bug#21704Konstantin Osipov1 Jul
  • Re: bzr commit into mysql-5.1-bugteam branch (davi:2988) Bug#21704Konstantin Osipov1 Jul