MySQL Bugs: #5118: auto_increment not set in backup-file

Bug #5118	auto_increment not set in backup-file
Submitted:	20 Aug 2004 3:30	Modified:	28 May 2009 22:00
Reporter:	Jochen Ulbricht	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	MySQL Administrator	Severity:	S4 (Feature request)
Version:		OS:	Any
Assigned to:		CPU Architecture:	Any
Triage:	D5 (Feature request)

Description:
actual auto_increment for primary key col isn't set in backupfile. so if next insert 
is done maybe with an id which is set in an foreign key of another table which was deleted in main table before i get in trouble..
i also couldn't find any options to set this "on" like it is default in phpMyAdmin for example..

How to repeat:
just make 10 inserts into a table with auto_increment col, delete the last one and make a backup and a restore. 
your next auto_increment id will be 10 (not 11 like in your orig.-table )

Dear User,

Thank you for your bug report!

I think this is a feature request as mysqldump does not mark the current auto_increment value 
either and how it's handled is really up to the table handler.  The innodb table handler, for 
example, may reuse the deleted id value.

Best Regards

Many thanks for writing a feature request.

The behaviour for auto_increment of all our backup tools is an expected behaviour.

When you want to have same values as before please use replication for backup and not a single backup tool.

After 5 years I do not even user this "single tools". I wrote my own while I was waiting ;)

best regards