| Bug #30747 | Create table with identical constraint names behaves incorrectly | ||
|---|---|---|---|
| Submitted: | 31 Aug 2007 10:23 | Modified: | 24 Oct 2007 20:09 |
| Reporter: | Jan Lindström | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0 & 5.1 | OS: | Any |
| Assigned to: | Davi Arnaut | CPU Architecture: | Any |
[6 Sep 2007 17:00]
Konstantin Osipov
Davi, please check and apply the suggested patch. Do not implement the uniqueness check, since this is a distinct bug/feature request (we plan to do it separately in 6.1 or later). Fix in 5.1 please.
[13 Sep 2007 22:22]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/34224 ChangeSet@1.2611, 2007-09-13 19:22:08-03:00, davi@moksha.local +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.
[27 Sep 2007 15:06]
Davi Arnaut
Pushed into mysql-5.1-runtime
[19 Oct 2007 18:53]
Bugs System
Pushed into 5.1.23-beta
[24 Oct 2007 19:47]
Paul DuBois
Noted in 5.1.23 changelog.
Description: Constraint names should be unique thus table with two identical constraint names should not be allowed. mysql> drop table if exists t2, t1; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> mysql> create table t1(a int not null, b int not null, primary key (a, b)); Query OK, 0 rows affected (0.01 sec) mysql> mysql> create table t2(a int not null, b int not null, c int not null, -> d int not null, f int not null, -> primary key (a), -> constraint cfk_1 foreign key fk_1 (b,c) references t1(a,b), -> constraint cfk_1 foreign key fk_2 (d,f) references t1(a,b)); Query OK, 0 rows affected (0.00 sec) Note that InnoDB works correctly: mysql> mysql> drop table if exists t2, t1; Query OK, 0 rows affected (0.00 sec) mysql> mysql> create table t1(a int not null, b int not null, primary key (a, b)) engine=soliddb; Query OK, 0 rows affected, 2 warnings (0.00 sec) mysql> mysql> create table t2(a int not null, b int not null, c int not null, -> d int not null, f int not null, primary key (a), -> constraint cfk_1 foreign key fk_1 (b,c) references t1(a,b), -> constraint cfk_1 foreign key fk_2 (d,f) references t1(a,b)) engine=innodb; ERROR 1005 (HY000): Can't create table 'test.t2' (errno: 150) How to repeat: drop table if exists t2, t1; create table t1(a int not null, b int not null, primary key (a, b)); create table t2(a int not null, b int not null, c int not null, d int not null, f int not null, primary key (a), constraint cfk_1 foreign key fk_1 (b,c) references t1(a,b), constraint cfk_1 foreign key fk_2 (d,f) references t1(a,b)); show create table t2; Suggested fix: At least set correct constraint name to foreign key class i.e.: ==== //depot/MySQL/l2x/mysql-5.1-orig/sql/sql_yacc.yy#1 - /home/jan/solid/MySQL/l2x/mysql-5.1-orig/sql/sql_yacc.yy ==== @@ -4654,7 +4654,8 @@ { LEX *lex=Lex; const char *key_name= $4 ? $4 : $1; - Key *key= new Foreign_key(key_name, lex->col_list, + const char *fkey_name = $1 ? $1 : ($4 ? $4: $1); + Key *key= new Foreign_key(fkey_name, lex->col_list, $8, lex->ref_list, lex->fk_delete_opt, And add a uniqueness check.