MySQL Bugs: #21404: AUTO_INCREMENT value reset when Adding FKEY (or ALTER?)

Bug #21404	AUTO_INCREMENT value reset when Adding FKEY (or ALTER?)
Submitted:	1 Aug 2006 21:34	Modified:	10 Jan 2007 14:11
Reporter:	Eric Carlstrom
Status:	Duplicate
Category:	Server: InnoDB	Severity:	S2 (Serious)
Version:	MySQL - 5.0.18-standard/5.0/5.1bk	OS:	Linux (Fedora)
Assigned to:	Marko Mäkelä	Target Version:
Tags:	auto_increment, alter

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`);

Updating Version/OS:

MySQL - 5.0.18-standard
Fedora

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?

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>

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.

Marko,

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

Regards,

Heikki

Fixing Bug #23313 fixes also this one.

Pushed into 5.1.18-beta

Pushed into 5.0.42