MySQL Bugs: #34920: auto_increment resets to 1 on foreign key creation

Bug #34920	auto_increment resets to 1 on foreign key creation
Submitted:	28 Feb 2008 11:09	Modified:	19 Jun 2010 17:46
Reporter:	David Carr	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S2 (Serious)
Version:	5.1.23-rc, 5.0.56	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	likely), regression (vs. 5.1.18

Description:
If I create a table with an explicit AUTO_INCREMENT value, and then add a foreign key, AUTO_INCREMENT is reset to 1.  There may be other situations where it also resets as it is happening to me quite regularly.

Perhaps this is a recurrence of problem #21404?

How to repeat:
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.03 sec)

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.03 sec)

mysql> select auto_increment from information_schema.tables where table_name='ba
r';
+----------------+
| auto_increment |
+----------------+
|            200 |
+----------------+
1 row in set (0.00 sec)

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

mysql> select auto_increment from information_schema.tables where table_name='ba
r';
+----------------+
| auto_increment |
+----------------+
|              1 |
+----------------+
1 row in set (0.00 sec)

Thank you for a bug report. Verified just as described. Test case is:

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;

select auto_increment 
from information_schema.tables 
where table_name='bar';

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

select auto_increment 
from information_schema.tables 
where table_name='bar';

Sunny,

please look at this.

Can we remember the AUTO-INC counter of the original table, and let the rebuilt table inherit it?

Regards,

Heikki

The change looks trivial.

The problem:
When we create a table within InnoDB currently we only check the flag for whether an auto_increment value was supplied during a CREATE TABLE statement.

The solution:
We need to check for ALTER TABLE too because MySQL does pass the current auto_increment value to be used in create_info to ha_innobase::create().

This should be fixable in all versions > 5.0

The previous comment should read fixable in all version >= 5.0.

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/43805

ChangeSet@1.2555, 2008-03-12 01:39:13-06:00, tsmith@ramayana.hindu.god +12 -0
  Apply InnoDB snapshot innodb-5.1-ss2360.
  
  Fixes:
  - Bug #34920: auto_increment resets to 1 on foreign key creation
  
    We need to use/inherit the passed in autoinc counter for ALTER TABLE
    statements too.

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/44490

ChangeSet@1.2569, 2008-03-27 02:40:45+01:00, tsmith@rhel5-ia64-a.mysql.com +10 -0
  Apply innodb-5.1-ss2360 snapshot
  
  Fixes:
  - Bug #34920: auto_increment resets to 1 on foreign key creation
    We need to use/inherit the passed in autoinc counter for ALTER TABLE
    statements too.

Pushed into 5.1.24-rc

Pushed into 6.0.5-alpha

Documented in the 5.1.24 and 6.0.5 changelogs as follows:

        Creating a foreign key on an InnoDB table that was created with an
        explicit AUTO_INCREMENT value caused that value to be reset to 1.

Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)

Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.

Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)

Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)