MySQL Bugs: #3749: Cannot create AUTO

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:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	4.1.2	OS:
Assigned to:	Heikki Tuuri	CPU Architecture:	Any

Description:
When I create table that have foreign key and auto increment column,
Table that I created do not have auto increment property.

In example sql result,

mysql> DESC stockholder;
+----------------+-------------+------+-----+---------+-------+
| Field          | Type        | Null | Key | Default | Extra |
+----------------+-------------+------+-----+---------+-------+
| stockholder_id | int(9)      |      | PRI | 0       |       |
| company_code   | int(4)      |      | MUL | 0       |       |
| name           | varchar(20) | YES  |     | NULL    |       |
| relationship   | varchar(20) | YES  |     | NULL    |       |
| rrn            | varchar(13) | YES  |     | NULL    |       |
+----------------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

You can see that stockholder_id is not auto increment column.

How to repeat:
CREATE TABLE company (
company_code		int(4) NOT NULL,
company_name		varchar(40) BINARY NOT NULL,
company_type		int(1) NOT NULL,
corp_no			char(13),
president_name		varchar(20) BINARY,
president_rrn		char(13),
company_phone		varchar(20) BINARY,
incorporation_date	int(8),	
charger1_id		int(9),
charger2_id		int(9),

PRIMARY KEY (company_code)
) TYPE=INNODB;

CREATE TABLE stockholder (
stockholder_id		int(9) AUTO_INCREMENT,
company_code		int(4) NOT NULL,
name			varchar(20) BINARY,
relationship		varchar(20) BINARY,
rrn			char(13),

PRIMARY KEY (stockholder_id),
INDEX (company_code),
FOREIGN KEY (company_code) REFERENCES company(company_code) ON DELETE CASCADE ON UPDATE CASCADE,
UNIQUE ( company_code, rrn )
) TYPE=INNODB;

DESC stockholder;

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;

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

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;
        }
      }
    }

Hi!

The fix is now pushed to the 4.1.2 tree.

Heikki

That's great! :)