MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Davi Arnaut Date:September 13 2007 10:22pm
Subject:bk commit into 5.1 tree (davi:1.2611) BUG#30747
View as plain text  
Below is the list of changes that have just been committed into a local
5.1 repository of davi. When davi does a push these changes will
be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2007-09-13 19:22:08-03:00, davi@stripped +3 -0
  Bug#30747 Create table with identical constraint names behaves incorrectly
  
  MySQL provides what appears to be a non standard extension to the
  FOREIGN KEY syntax which let users name (label/tag) a foreign key
  to more easily identify a specific foreign key if any problems show
  up later during the query parsing or execution. But the foreign key
  name was not being properly set to the right key, possible leaving
  the foreign key with no name.

  mysql-test/include/mix1.inc@stripped, 2007-09-13 19:22:04-03:00, davi@stripped +34 -0
    Add test case for Bug#30747

  mysql-test/r/innodb_mysql.result@stripped, 2007-09-13 19:22:05-03:00, davi@stripped +36 -0
    Add test case result for Bug#30747

  sql/sql_yacc.yy@stripped, 2007-09-13 19:22:05-03:00, davi@stripped +3 -2
    Set the foreign key name to the constraint name if a specific name was
    not provided. As for the constraint name, only use the foreign name if
    a specific name was not provided.

diff -Nrup a/mysql-test/include/mix1.inc b/mysql-test/include/mix1.inc
--- a/mysql-test/include/mix1.inc	2007-08-21 06:27:03 -03:00
+++ b/mysql-test/include/mix1.inc	2007-09-13 19:22:04 -03:00
@@ -1096,4 +1096,38 @@ select @b:=f2 from t1;
 select if(@a=@b,"ok","wrong");
 drop table t1;
 
+#
+# Bug#30747 Create table with identical constraint names behaves incorrectly
+#
+
+if ($test_foreign_keys)
+{
+  CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b)) engine=innodb;
+  --error ER_WRONG_FK_DEF
+  CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
+                  CONSTRAINT c2 FOREIGN KEY f2 (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb;
+  --error ER_WRONG_FK_DEF
+  CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
+                  CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb;
+  CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
+                  CONSTRAINT c1 FOREIGN KEY c2 (c) REFERENCES t1 (a) ON DELETE NO ACTION,
+                  CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb;
+  ALTER TABLE t2 DROP FOREIGN KEY c2;
+  DROP TABLE t2;
+  --error ER_WRONG_FK_DEF
+  CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
+                  FOREIGN KEY (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb;
+  --error ER_WRONG_FK_DEF
+  CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
+                  FOREIGN KEY f1 (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb;
+  CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
+                  CONSTRAINT c1 FOREIGN KEY f1 (c) REFERENCES t1 (a) ON DELETE NO ACTION,
+                  CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION,
+                  FOREIGN KEY f3 (c) REFERENCES t1 (a) ON UPDATE NO ACTION,
+                  FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb;
+  SHOW CREATE TABLE t2;
+  DROP TABLE t2;
+  DROP TABLE t1;
+}
+
 --echo End of 5.1 tests
diff -Nrup a/mysql-test/r/innodb_mysql.result b/mysql-test/r/innodb_mysql.result
--- a/mysql-test/r/innodb_mysql.result	2007-08-21 06:27:03 -03:00
+++ b/mysql-test/r/innodb_mysql.result	2007-09-13 19:22:05 -03:00
@@ -1273,4 +1273,40 @@ select if(@a=@b,"ok","wrong");
 if(@a=@b,"ok","wrong")
 ok
 drop table t1;
+CREATE TABLE t1 (a INT NOT NULL, b INT NOT NULL, PRIMARY KEY (a,b)) engine=innodb;
+CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
+CONSTRAINT c2 FOREIGN KEY f2 (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb;
+ERROR 42000: Incorrect foreign key definition for 'f2': Key reference and table reference don't match
+CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
+CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a,b) ON UPDATE NO ACTION) engine=innodb;
+ERROR 42000: Incorrect foreign key definition for 'c2': Key reference and table reference don't match
+CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
+CONSTRAINT c1 FOREIGN KEY c2 (c) REFERENCES t1 (a) ON DELETE NO ACTION,
+CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb;
+ALTER TABLE t2 DROP FOREIGN KEY c2;
+DROP TABLE t2;
+CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
+FOREIGN KEY (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb;
+ERROR 42000: Incorrect foreign key definition for 'foreign key without name': Key reference and table reference don't match
+CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
+FOREIGN KEY f1 (c) REFERENCES t1 (a,k) ON UPDATE NO ACTION) engine=innodb;
+ERROR 42000: Incorrect foreign key definition for 'f1': Key reference and table reference don't match
+CREATE TABLE t2 (c INT NOT NULL, d INT NOT NULL, PRIMARY KEY (c,d),
+CONSTRAINT c1 FOREIGN KEY f1 (c) REFERENCES t1 (a) ON DELETE NO ACTION,
+CONSTRAINT c2 FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION,
+FOREIGN KEY f3 (c) REFERENCES t1 (a) ON UPDATE NO ACTION,
+FOREIGN KEY (c) REFERENCES t1 (a) ON UPDATE NO ACTION) engine=innodb;
+SHOW CREATE TABLE t2;
+Table	Create Table
+t2	CREATE TABLE `t2` (
+  `c` int(11) NOT NULL,
+  `d` int(11) NOT NULL,
+  PRIMARY KEY (`c`,`d`),
+  CONSTRAINT `c1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON DELETE NO ACTION,
+  CONSTRAINT `c2` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION,
+  CONSTRAINT `t2_ibfk_1` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION,
+  CONSTRAINT `t2_ibfk_2` FOREIGN KEY (`c`) REFERENCES `t1` (`a`) ON UPDATE NO ACTION
+) ENGINE=InnoDB DEFAULT CHARSET=latin1
+DROP TABLE t2;
+DROP TABLE t1;
 End of 5.1 tests
diff -Nrup a/sql/sql_yacc.yy b/sql/sql_yacc.yy
--- a/sql/sql_yacc.yy	2007-09-13 11:39:17 -03:00
+++ b/sql/sql_yacc.yy	2007-09-13 19:22:05 -03:00
@@ -4656,8 +4656,9 @@ key_def:
         | opt_constraint FOREIGN KEY_SYM opt_ident '(' key_list ')' references
           {
             LEX *lex=Lex;
-            const char *key_name= $4 ? $4 : $1;
-            Key *key= new Foreign_key(key_name, lex->col_list,
+            const char *key_name= $1 ? $1 : $4;
+            const char *fkey_name = $4 ? $4 : key_name;
+            Key *key= new Foreign_key(fkey_name, lex->col_list,
                                       $8,
                                       lex->ref_list,
                                       lex->fk_delete_opt,
Thread
bk commit into 5.1 tree (davi:1.2611) BUG#30747Davi Arnaut14 Sep
  • Re: bk commit into 5.1 tree (davi:1.2611) BUG#30747Konstantin Osipov27 Sep