Bug #44425 mysqldump --no-data still sets AUTO_INCREMENT base values to CREATE TABLE statem
Submitted: 23 Apr 2009 10:34 Modified: 23 Apr 2009 11:49
Reporter: Thierry de Villeneuve Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.1.22-rc-log OS:Any
Assigned to: CPU Architecture:Any
Tags: mysqldump --no-data generates out of scope AUTO_INCREMENT table options

[23 Apr 2009 10:34] Thierry de Villeneuve
Description:
Hello,

This is not really a 'bug' but an 'unexpected feature'.

I actually want to put together a clean DB schema creation script out of an existing DB. I use the well known 'mysqldump --no-data' command. I just need to be able to recreate the structure of an existing DB into another instance. No data need to be exported here.

With the --no-data flag, I hope to get an output I can directly use to create an empty schema in another MySQL instance.

--> The problem I'm reporting to you is that even if no "INSERT" statements are included in the mysqldump output, still, CREATE TABLE statements that have columns with a AUTO_INCREMENT gets initialized with an AUTO_INCREMENT=[value >1] sequence index that correspond to the DB being dumped. As no data are expected in this dump but only the DB structure, the presence of a auto_increment sequence initialization other than the default value '1' causes problem if the output of that mysqldump command is uses as-is to create a DB in another instance.

# Here is the command I've used
 mysqldump -u root --opt --no-data --dump-date -pniceone mynicedb > `date "+mynicedb_$(hostname)_newstruct_%y%m%d.sql"`

# Here is an excerpt of the log with the offending AUTO_INCREMENT=4473 table option.
CREATE TABLE `ECAR_HARDWARE` (
  `ID_HARDWARE` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    -- some more columns here
) ENGINE=InnoDB AUTO_INCREMENT=4473 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

How to repeat:
Everytime one runs 'mysqldump --no-data' against a DB that already contain rows in Tables with an auto_increment

Suggested fix:

I would recommend that:

a/  Either to stop adding the AUTO_INCREMENT=[some value >1] table option when the --no-data switch is used;

b/  Or to add a new switch to the mysqldump utility to not get it to add AUTO_INCREMENT table option added in the output.

I would prefer option a/ as it makes much more sense.
[23 Apr 2009 11:28] Valeriy Kravchuk
Thank you for a reasonable feature request. mysqldump just uses the results of SHOW CREATE TABLE it seems.
[23 Apr 2009 11:49] Thierry de Villeneuve
Indeed !   I didn't pay attention to that SHOW CREATE TABLE command.

I hope you'll be able to adjust mysqldump code, then, to filter out the AUTO_INCREMENT table option presets in the case where only a clean schema is looked for.

Thanks a lot !