Bug #21404 AUTO_INCREMENT value reset when Adding FKEY (or ALTER?)

• Submitted: 1 Aug 2006 19:34
• Modified: 10 Jan 2007 13:11
• Reporter: Eric Carlstrom
• Status: Duplicate
• Category: MySQL Server: InnoDB storage engine
• Severity: S2 (Serious)
• Version: MySQL - 5.0.18-standard/5.0/5.1bk
• OS: Linux (Fedora)
• Assigned to: Assigned Account
• CPU Architecture: Any
• Tags: alter, auto_increment

[1 Aug 2006 19:34] Eric Carlstrom

Description:
If I create a table with an explicit AUTO_INCREMENT value, and then add a foreign key via an ALTER stmt, the AUTO_INCREMENT is reset to 1!  Other ALTER stmts untested.

How to repeat:
CREATE TABLE `foo` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `title` varchar(128) NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=100;

CREATE TABLE `bar` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `foo_id` int(10) unsigned default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=200;

ALTER TABLE `bar` ADD CONSTRAINT `foo_bar_1` FOREIGN KEY (`foo_id`) REFERENCES `foo`(`id`);

[1 Aug 2006 19:36] Eric Carlstrom

Updating Version/OS:

MySQL - 5.0.18-standard
Fedora

[1 Aug 2006 20:33] Heikki Tuuri

A new foreign key is created by recreating and rebuilding the whole table. The rebuilt table should inherit the auto-inc counter from the old table.

Hmm... does CREATE INDEX also reset the auto-inc counter?

[1 Aug 2006 20:40] MySQL Verification Team

Thank you for the bug report.

miguel@hegel:~/dbs/5.0> bin/mysqladmin -uroot create dbo
miguel@hegel:~/dbs/5.0> bin/mysql -uroot dbo
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.25-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE `foo` (
    ->   `id` int(10) unsigned NOT NULL auto_increment,
    ->   `title` varchar(128) NOT NULL,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=100;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> CREATE TABLE `bar` (
    ->   `id` int(10) unsigned NOT NULL auto_increment,
    ->   `foo_id` int(10) unsigned default NULL,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=200;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='bar'\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: dbo
     TABLE_NAME: bar
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: 200
    CREATE_TIME: 2006-08-01 17:13:23
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: InnoDB free: 4096 kB
1 row in set (0.01 sec)

mysql> ALTER TABLE `bar` ADD CONSTRAINT `foo_bar_1` FOREIGN KEY (`foo_id`) REFERENCES
    -> `foo`(`id`);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='bar'\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: dbo
     TABLE_NAME: bar
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 16384
      DATA_FREE: 0
 AUTO_INCREMENT: 1
    CREATE_TIME: 2006-08-01 17:15:08
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: InnoDB free: 4096 kB; (`foo_id`) REFER `dbo/foo`(`id`)
1 row in set (0.01 sec)

mysql> 
-------------------------------------------------------------------------
mysql> SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='bar'\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: dbo
     TABLE_NAME: bar
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 0
      DATA_FREE: 0
 AUTO_INCREMENT: 200
    CREATE_TIME: 2006-08-01 17:19:59
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: InnoDB free: 4096 kB
1 row in set (0.02 sec)

mysql> ALTER TABLE `bar` ADD CONSTRAINT `foo_bar_1` FOREIGN KEY (`foo_id`) REFERENCES
    -> `foo`(`id`);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='bar'\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: dbo
     TABLE_NAME: bar
     TABLE_TYPE: BASE TABLE
         ENGINE: InnoDB
        VERSION: 10
     ROW_FORMAT: Compact
     TABLE_ROWS: 0
 AVG_ROW_LENGTH: 0
    DATA_LENGTH: 16384
MAX_DATA_LENGTH: 0
   INDEX_LENGTH: 16384
      DATA_FREE: 0
 AUTO_INCREMENT: 1
    CREATE_TIME: 2006-08-01 17:20:28
    UPDATE_TIME: NULL
     CHECK_TIME: NULL
TABLE_COLLATION: utf8_general_ci
       CHECKSUM: NULL
 CREATE_OPTIONS: 
  TABLE_COMMENT: InnoDB free: 3072 kB; (`foo_id`) REFER `dbo`.`foo`(`id`)
1 row in set (0.01 sec)

mysql> SELECT VERSION();
+-------------------+
| VERSION()         |
+-------------------+
| 5.1.12-beta-debug | 
+-------------------+
1 row in set (0.00 sec)

mysql>

[19 Sep 2006 20:03] [ name withheld ]

I get the same error when I create an index on a table. Example:

SET GLOBAL sql_mode='ansi,strict_trans_tables';
SET SESSION sql_mode='ansi,strict_trans_tables,no_auto_value_on_zero';

CREATE TABLE foo (
  id INTEGER NOT NULL AUTO_INCREMENT,
  key_name VARCHAR(64) NOT NULL,
  CONSTRAINT pk_event_type__id
  PRIMARY KEY (id)
)
  ENGINE InnoDB
  AUTO_INCREMENT 1024;

CREATE INDEX idx_foo_key_name on foo(key_name);

INSERT INTO foo (key_name) VALUES ('foo');
INSERT INTO foo (key_name) VALUES ('bar');
SELECT * FROM foo;

When I run this through mysql < test.sql, it outputs:

id      key_name
2       bar
1       foo

What it should output is

id      key_name
1024    foo
1025    bar

MySQL 5.0.24a on Mac OS X.

[8 Nov 2006 14:56] Heikki Tuuri

Marko,

please make the altered table to inherit the auto-inc counter value of the old table.

Regards,

Heikki

[10 Jan 2007 13:11] Marko Mäkelä

Fixing Bug #23313 fixes also this one.

[26 Apr 2007 11:36] Bugs System

Pushed into 5.1.18-beta

[26 Apr 2007 11:37] Bugs System

Pushed into 5.0.42

[5 May 2010 15:03] Bugs System

Pushed into 5.1.47 (revid:[e-mail]-20100505145753-ivlt4hclbrjy8eye) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 5:46] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20100524190136-egaq7e8zgkwb9aqi) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (pib:16)

[28 May 2010 6:16] Bugs System

Pushed into 6.0.14-alpha (revid:[e-mail]-20100524190941-nuudpx60if25wsvx) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[28 May 2010 6:43] Bugs System

Pushed into 5.5.5-m3 (revid:[e-mail]-20100524185725-c8k5q7v60i5nix3t) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 11:46] Bugs System

Pushed into 5.1.47-ndb-7.0.16 (revid:[e-mail]-20100617114014-bva0dy24yyd67697) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 12:23] Bugs System

Pushed into 5.1.47-ndb-6.2.19 (revid:[e-mail]-20100617115448-idrbic6gbki37h1c) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

[17 Jun 2010 13:11] Bugs System

Pushed into 5.1.47-ndb-6.3.35 (revid:[e-mail]-20100617114611-61aqbb52j752y116) (version source revid:[e-mail]-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)