please mark my action items in the minutes-of-meeting from the
2009-09-01 backup meeting as closed.
1. I verified that it is not possible with plain INSERT and ALTER
statements to generate duplicate key errors on AUTO_INCREMENT columns.
The key part of this behavior is that ALTER does never set an
AUTO_INCREMENT value less or equal to the highest key value.
2. I tested that mysqldump creates SQL files that restore the
AUTO_INCREMENT value properly when fed to the mysql utility. The key
part of this behavior is that it uses INSERT statements to load the
data. These update the AUTO_INCREMENT value properly.
3. I reported Bug#47484 (Online Backup: Restore does not set the
auto_increment value correctly). The main problem is that BACKUP/RESTORE
does not use full INSERT statements to restore the table data. The
AUTO_INCREMENT value is left at 1. Later INSERTs that use the wrong
AUTO_INCREMENT value receive duplicate key errors. One successful INSERT
with explicit key value fixes the AUTO_INCREMENT value for that table.
Item 3 may require a new team decision. We may not want to live with
difficult to use InnoDB tables after RESTORE.
We can perhaps fix this by using the "metadata-extra" information per
table to transport the AUTO_INCREMENT values. This might work without an
image file format change.
I will not push Bug#42895/WL#4844 (Implement a locking scheme for
RESTORE) without a new decision.
Ingo Strüwing, Database Group
Sun Microsystems GmbH, Sonnenallee 1, D-85551 Kirchheim-Heimstetten
Geschäftsführer: Thomas Schröder, Wolfgang Engels, Wolf Frenkel
Vorsitzender des Aufsichtsrates: Martin Häring HRB München 161028