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.
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.