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:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: likely), regression (vs. 5.1.18
Triage: D2 (Serious)

[28 Feb 2008 11:09] David Carr
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)
[28 Feb 2008 13:35] Valeriy Kravchuk
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';
[28 Feb 2008 17:35] Heikki Tuuri
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
[1 Mar 2008 23:57] Sunny Bains
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
[1 Mar 2008 23:59] Sunny Bains
The previous comment should read fixable in all version >= 5.0.
[12 Mar 2008 7:39] Bugs System
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.
[27 Mar 2008 1:41] Bugs System
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.
[27 Mar 2008 22:04] Bugs System
Pushed into 5.1.24-rc
[28 Mar 2008 11:10] Bugs System
Pushed into 6.0.5-alpha
[11 Apr 2008 16:00] Jon Stephens
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.
[5 May 2010 15:03] Bugs System
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)
[6 May 2010 14:07] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[28 May 2010 6:06] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:34] Bugs System
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)
[28 May 2010 7:02] Bugs System
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)
[29 May 2010 22:34] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 12:10] Bugs System
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)
[17 Jun 2010 12:58] Bugs System
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)
[17 Jun 2010 13:38] Bugs System
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)