Bug #30957 MySQLDump: parameter to not place auto-increment values into the dump

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.

Fixing the version.

Many thanks for pointing this out.

This is a bug in mysqldump. The auto_increment value should not be in schema dump.

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.

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?

Workaround: use sed/awk/perl/whatever to process .sql file and replace auto_increment=<number> with auto_increment=1.

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.

Actually this is a duplicate of bug #20786.