Bug #30957 MySQLDump: parameter to not place auto-increment values into the dump
Submitted: 11 Sep 2007 16:22 Modified: 13 Nov 2009 11:15
Reporter: Alex W Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server: mysqldump Command-line Client Severity:S4 (Feature request)
Version:5.0, 5.1, 6.0 OS:Any
Assigned to: CPU Architecture:Any
Tags: mysqldump

[11 Sep 2007 16:22] Alex W 
Description:
We're using MySQLDump to produce dumps of the database into a source control system; we have one script that places the DDL into schema.sql, and another one that places system data into data.sql. 

It would be great to have a parameter for MySQLDump that the auto-increment value should not be output into the CREATE TABLE statement. We found a workaround - using compatibility mode - but that kills any MySQL-specific details such as the engine being used, so it's not exactly optimal.

) ENGINE=InnoDB ----> AUTO_INCREMENT=3 <----- DEFAULT CHARSET=latin1

How to repeat:
x

Suggested fix:
Please add a mysqldump --skip-auto-increment command line parameter.
[11 Sep 2007 16:36] Alex W 
Fixing the version.
[30 Sep 2008 14:03] Susanne Ebrecht 
Many thanks for pointing this out.

This is a bug in mysqldump. The auto_increment value should not be in schema dump.
[30 Sep 2008 15:38] Jim Winstead 
mysqldump just uses 'SHOW CREATE TABLE' to get the table definition, so the issue needs to be addressed server-side. As I recall, always including the value of AUTO_INCREMENT was itself a bug fix for an earlier issue. I think it was Bug #19025.
[12 Nov 2009 18:30] Alex W 
It seems that there are two distinct scenarios here - one where outputting auto-increment values is useful, and another - where it's actually detrimental. Would you consider adding a switch to mysqldump that allows the user to choose?
[12 Nov 2009 18:55] Valeriy Kravchuk 
Workaround: use sed/awk/perl/whatever to process .sql file and replace auto_increment=<number> with auto_increment=1.
[12 Nov 2009 20:07] Alex W 
I'm hopeful that mysqldump can be a production level tool that does not require post-processing by awk or sed. Customers could use SHOW CREATE TABLE directly, but MySQLDump is a useful abstraction on top of the raw SQL statements that saves time and ensures that the output is indeed well-formed.
[13 Nov 2009 11:15] Valeriy Kravchuk 
Actually this is a duplicate of bug #20786.