MySQL Bugs: #21803: AUTO_INCREMENT information ignored by mysqldump

Bug #21803	AUTO_INCREMENT information ignored by mysqldump
Submitted:	24 Aug 2006 8:06	Modified:	24 Aug 2006 9:59
Reporter:	Giannis Tsakiris	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: mysqldump Command-line Client	Severity:	S3 (Non-critical)
Version:	5.0.24-community-nt	OS:	Microsoft Windows (Windows)
Assigned to:		CPU Architecture:	Any
Tags:	auto_increment, mysqldump

Description:
When setting a starting value for an auto_increment column, when dumping the table (using mysqldump) the directive for setting the starting value is omitted.

How to repeat:
Create a sample table.

mysql> CREATE TABLE test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY KEY(ID)) AUTO_INCREMENT = 10000;
Query OK, 0 rows affected (0.16 sec)

Now try taking a dump of the database:

C:\Documents and Settings\Administrator>mysqldump -u root -p mydb
Enter password: **
-- MySQL dump 10.10
...
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=greek;
...

As you can see, the AUTO_INCREMENT = 10000 has gone away.
I use "mysqldump  Ver 10.10 Distrib 5.0.24, for Win32 (ia32)".

Suggested fix:
I think that the only thing you can do is editing the dump files and add the AUTO_INCREMENT directive yourself...

Thank you for a problem report. Sorry, but I was not able to repeat the behaviour described with 5.0.25-BK on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.25-debug

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

mysql> create database mydb;
Query OK, 1 row affected (0.02 sec)

mysql> use mydb;
Database changed
mysql> CREATE TABLE test ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, PRIMARY
    -> KEY(ID)) AUTO_INCREMENT = 10000;
Query OK, 0 rows affected (0.01 sec)

mysql> exit
Bye
openxs@suse:~/dbs/5.0> bin/mysqldump -uroot mydb
-- MySQL dump 10.10
--
-- Host: localhost    Database: mydb
-- ------------------------------------------------------
-- Server version       5.0.25-debug

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0
*/;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Table structure for table `test`
--

DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL auto_increment,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10000 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `test`
--

LOCK TABLES `test` WRITE;
/*!40000 ALTER TABLE `test` DISABLE KEYS */;
/*!40000 ALTER TABLE `test` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2006-08-24  7:58:14

I haven't try it on Linux, maybe the problem occurs only in Windows.

I just noticed something interesting.
If I change the table type to MyISAM, mysqldump correctly includes the AUTO_INCREMENT = 10000 directive in the table definition.
When I use the InnoDB engine, the AUTO_INCREMENT directive vanishes.
Maybe the "bug" is specific to the InnoDB engine.

This happenned to me this weekend when moving my website to another server:

Tables are MyIsam

mysqldump  Ver 10.10 Distrib 5.0.27, for redhat-linux-gnu (i686)

heres the command I used to dump:

mysqldump --default-character-set=latin1 --skip-opt --skip-lock-tables --add-drop-table --quick --default-character-set=latin1 --user=nottellingyou --password=itsasecret mydbname > mydump.txt