Bug #19025 mysqldump doesn't correctly dump "auto_increment = [int]"
Submitted: 11 Apr 2006 22:10 Modified: 17 May 2006 18:27
Reporter: Torrey Hoffman Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.0.15, 4.1.18 OS:pc-linux-gnu (i686)
Assigned to: Tatiana Azundris Nuernberg CPU Architecture:Any

[11 Apr 2006 22:10] Torrey Hoffman
Description:
We are building a database cluster / replication setup.

Some of our tables have AUTO_INCREMENT values greater than 1.

For instance (simplified a bit from reality) we create a table like this:

CREATE TABLE `port_group` (
    port_group_id int(10) unsigned NOT NULL auto_increment,
    port_group_name varchar(255) default NULL,
    PRIMARY KEY (port_group_id),
    KEY port_group_managed(port_group_managed)
) TYPE=MyISAM AUTO_INCREMENT = 1000;

When setting up the cluster, we use mysqldump and copy the dump file from the master to the slave, as described in the mysql manual.  But the mysql dump file for this table will look like this:

CREATE TABLE `sal_port_group` (
  `port_group_id` int(10) unsigned NOT NULL auto_increment,
  `port_group_name` varchar(255) collate latin1_general_ci default NULL,
  PRIMARY KEY  (`port_group_id`),
  KEY `port_group_managed` (`port_group_managed`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci;

As you can see, the AUTO_INCREMENT = 1000 is missing.

So the cluster setup fails.

How to repeat:
Create a table with AUTO_INCREMENT = 1000.
Use mysqldump and look at the output.  
The AUTO_INCREMENT is not dumped.

Suggested fix:
Include AUTO_INCREMENT in mysqldump output.
[12 Apr 2006 8:39] Hartmut Holzgraefe
The same is true for SHOW CREATE TABLE, the initial auto_increment value is not shown

test case:

drop table if exists t1;
create table t1 ( i int auto_increment primary key) auto_increment=1000;
show create table t1;
[20 Apr 2006 23:08] 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/5233
[22 Apr 2006 1:10] 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/5329
[22 Apr 2006 14:03] 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/5338
[24 Apr 2006 20:12] 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/5429
[28 Apr 2006 6:09] 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/5668
[15 May 2006 14:06] Tatiana Azundris Nuernberg
fixed in 4.1.20, 5.0.22
[17 May 2006 18:27] Paul DuBois
Noted in 4.1.20, 5.0.22 changelogs.

For a table with an <literal>AUTO_INCREMENT</literal> column,
<literal>SHOW CREATE TABLE</literal> now shows the next
<literal>AUTO_INCREMENT</literal> value to be generated.
[1 Nov 2007 16:28] Richard Fearn
To prevent any confusion - this bug was fixed in 5.0.23, not 5.0.22. It's mentioned in the 5.0.23 changelog:

  http://dev.mysql.com/doc/refman/5.0/en/news-5-0-23.html