bzr commit into mysql-5.0-bugteam branch (anurag.shekhar:2820) Bug#46599

[Anurag Shekhar]

#At file:///home/anurag/mysqlsrc/mysql-5.0-bugteam-46599/ based on revid:joro@stripped

 2820 Anurag Shekhar	2009-10-15
      Bug #46599  ALTER TABLE causes inconsistent values for foreign keys
      
      While altering table foreign key constraint checks are suppressed which
      causes this behaviour. 
      To fix this problem modification in a field which is part of foreign 
      key is blocked. Work around to alter such field is to drop the key
      and recreate it after altering the column.
      A new error message is introduced which suggests uses to refer manual 
      for the work around.
     @ mysql-test/r/innodb_mysql.result
        updated result file.
     @ mysql-test/t/innodb_mysql.test
        test case for bug#46599.
     @ sql/share/errmsg.txt
        Added a new error message informing caller that column participating 
        in foreign key can't be modified.
     @ sql/sql_table.cc
        Added a new check in mysql_alter_table to check if the field being
        modified is part of a foreign key. An error ER_CANNOT_MODIFY_FOREIGN_KEY_FIELD
        is returned if it is.

    modified:
      mysql-test/r/innodb_mysql.result
      mysql-test/t/innodb_mysql.test
      sql/share/errmsg.txt
      sql/sql_table.cc
=== modified file 'mysql-test/r/innodb_mysql.result'
--- a/mysql-test/r/innodb_mysql.result	2009-06-15 15:29:26 +0000
+++ b/mysql-test/r/innodb_mysql.result	2009-10-15 09:28:51 +0000
@@ -1315,4 +1315,14 @@ id	select_type	table	type	possible_keys	
 2	DEPENDENT SUBQUERY	t1	system	NULL	NULL	NULL	NULL	0	const row not found
 2	DEPENDENT SUBQUERY	t2	ALL	NULL	NULL	NULL	NULL	1	
 DROP TABLE t1,t2;
+#
+#Bug #46599 ALTER TABLE causes inconsistent values for foreign keys
+#
+CREATE TABLE t1 (
+a VARCHAR(2), KEY(a)) ENGINE=InnoDB;
+CREATE TABLE t2 (a VARCHAR(2),
+FOREIGN KEY (a) REFERENCES t1 (a)) ENGINE=InnoDB;
+ALTER TABLE t2 MODIFY COLUMN a VARCHAR(2) NOT NULL DEFAULT '';
+ERROR HY000: Can't modify a :part of foreign key t2_ibfk_1,  please consult the manual for workaround
+DROP TABLE t2, t1;
 End of 5.0 tests

=== modified file 'mysql-test/t/innodb_mysql.test'
--- a/mysql-test/t/innodb_mysql.test	2009-06-15 15:29:26 +0000
+++ b/mysql-test/t/innodb_mysql.test	2009-10-15 09:28:51 +0000
@@ -1073,4 +1073,18 @@ explain
 select b from t1 where a not in (select b from t1,t2 group by a) group by a;
 DROP TABLE t1,t2;
 
+--echo #
+--echo #Bug #46599 ALTER TABLE causes inconsistent values for foreign keys
+--echo #
+CREATE TABLE t1 (
+	a VARCHAR(2), KEY(a)) ENGINE=InnoDB;
+
+CREATE TABLE t2 (a VARCHAR(2),
+       FOREIGN KEY (a) REFERENCES t1 (a)) ENGINE=InnoDB;
+
+--error ER_CANNOT_MODIFY_FOREIGN_KEY_FIELD
+ALTER TABLE t2 MODIFY COLUMN a VARCHAR(2) NOT NULL DEFAULT '';
+
+DROP TABLE t2, t1;
+
 --echo End of 5.0 tests

=== modified file 'sql/share/errmsg.txt'
--- a/sql/share/errmsg.txt	2009-07-17 08:43:53 +0000
+++ b/sql/share/errmsg.txt	2009-10-15 09:28:51 +0000
@@ -5651,3 +5651,5 @@ ER_XA_RBDEADLOCK XA102
 	eng "XA_RBDEADLOCK: Transaction branch was rolled back: deadlock was detected"
 ER_TOO_MANY_CONCURRENT_TRXS
         eng  "Too many active concurrent transactions"
+ER_CANNOT_MODIFY_FOREIGN_KEY_FIELD
+        eng  "Can't modify %s :part of foreign key %s,  please consult the manual for workaround"

=== modified file 'sql/sql_table.cc'
--- a/sql/sql_table.cc	2009-09-07 16:35:37 +0000
+++ b/sql/sql_table.cc	2009-10-15 09:28:51 +0000
@@ -3416,8 +3416,41 @@ view_err:
   List_iterator<Alter_column> alter_it(alter_info->alter_list);
   Alter_info new_info;                   // Add new columns and indexes here
   create_field *def;
+  List_iterator<create_field> create_it(alter_info->create_list);
 
   /*
+    Check if any field in change list is part of foreign key.
+  */
+  List <FOREIGN_KEY_INFO> f_key_list;
+  /*
+   Retrieve list of foreign keys
+  */
+  table->file->get_foreign_key_list(thd, &f_key_list);
+  List_iterator_fast<FOREIGN_KEY_INFO> f_key_it(f_key_list);
+  FOREIGN_KEY_INFO *f_key;
+  while ((f_key=f_key_it++)) 
+  {
+    List_iterator_fast<LEX_STRING> field_names(f_key->referenced_fields);
+    LEX_STRING *lex_field;
+    while ((lex_field=field_names++)) 
+    {
+      def_it.rewind();
+      while ((def=def_it++)) 
+      {
+        if (def->change && !my_strcasecmp(system_charset_info,
+           lex_field->str, def->change))
+        {
+          /*
+            Cann't modify field if its part of foreign key
+          */
+          my_error(ER_CANNOT_MODIFY_FOREIGN_KEY_FIELD, MYF(0), def->change,
+                                f_key->forein_id->str);
+          DBUG_RETURN(TRUE);
+        }
+      }
+    }
+  }
+  /*
     First collect all fields from table which isn't in drop_list
   */
 


Attachment: [text/bzr-bundle] bzr/[e-mail]