Bug #3749 | Cannot create AUTO_INCREMENT column. | ||
---|---|---|---|
Submitted: | 13 May 2004 19:20 | Modified: | 14 May 2004 8:07 |
Reporter: | Hyun-Woong Kim | Email Updates: | |
Status: | Closed | Impact on me: | |
Category: | MySQL Server | Severity: | S1 (Critical) |
Version: | 4.1.2 | OS: | |
Assigned to: | Heikki Tuuri | CPU Architecture: | Any |
[13 May 2004 19:20]
Hyun-Woong Kim
[13 May 2004 20:17]
Dean Ellis
Verified, thank you for the report! The additional index on the foreign key column seems to trigger it. CREATE TABLE t1 ( a int primary key ) ENGINE=InnoDB; CREATE TABLE t2 ( a int auto_increment primary key, b int, index(b), foreign key (b) references t1(a), unique(b) ) ENGINE=InnoDB; DESC t2; DROP TABLE t2; CREATE TABLE t2 ( a int auto_increment primary key, b int, index(b), foreign key (b) references t1(a) ) ENGINE=InnoDB; DESC t2; DROP TABLE t2, t1;
[14 May 2004 6:26]
Heikki Tuuri
Hi! Thank you for the bug report! The bug does not happen with the most recent 4.0.20, or a 4.1.1 tree from last summer. The bug does occur in a 4.1.2 tree from May 12th, 2004. It might be the following patch by Marko/Monty that causes the bug. Regards, Heikki ChangeSet@1.1822.5.2, 2004-05-12 00:29:52+03:00, monty@mysql.com Don't automaticly generate a new key for a foreign key constraint if there is already a usable key. Prefer not automatic keys before automatic keys. If there is two conf
[14 May 2004 7:57]
Heikki Tuuri
Hi! There seems to be a bug in the following algorithm in sql_table.cc. If the 'generated' index for the FOREIGN KEY is detected redundant twice (i.e., there are two user-defined indexes where the FOREIGN KEY appears as a prefix), then the code excerpt sets it ->name = ignore_key twice, and the *key_count will get a wrong value. That would explain Dean's observation that the bug only manifests if there are two indexes on b. Regards, Heikki key_iterator2.rewind (); if (key->type != Key::FOREIGN_KEY) { while ((key2 = key_iterator2++) != key) { if ((key2->type != Key::FOREIGN_KEY && !foreign_key_prefix(key, key2))) { /* TO DO: issue warning message */ /* mark that the generated key should be ignored */ if (!key2->generated || (key->generated && key->columns.elements < key2->columns.elements)) key->name= ignore_key; else { /* Remove the previous, generated key */ key2->name= ignore_key; key_parts-= key2->columns.elements; (*key_count)--; } break; } } }
[14 May 2004 8:07]
Heikki Tuuri
Hi! The fix is now pushed to the 4.1.2 tree. Heikki
[14 May 2004 8:50]
Hyun-Woong Kim
That's great! :)