Bug #47484 Online Backup: Restore does not set the auto_increment value correctly
Submitted: 21 Sep 2009 15:01 Modified: 7 Mar 2010 19:40
Reporter: Ingo Strüwing Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Backup Severity:S3 (Non-critical)
Version:5.4.4 OS:Any
Assigned to: Ingo Strüwing CPU Architecture:Any

[21 Sep 2009 15:01] Ingo Strüwing 
Description:
After the push of Bug#42895/WL#4844 (Implement a locking scheme for RESTORE), RESTORE does not set the auto_increment value correctly.

It uses an implcit TRUNCATE on the freshly created tables. This resets the auto_increment value (to 1).

For MyISAM tables this is adjusted properly. This could come from the native driver. MyISAM may store the auto_increment value in the index file, which is restored by the native driver.

The MEMORY engine adjusts the value to the highest key value plus one.

InnoDB leaves the value at 1 and receives duplicate key errors when trying to INSERT using the auto_increment value.

Normally it is not possible to set the auto_increment value less or equal to the highest key value in the table. But the InnoDB restore, truncating the table, resetting the value and not using full INSERT statements leads to that situation.

How to repeat:
I will add a test case.

Suggested fix:
Save the tables' auto_increment values in the backup image and restore them explicitly after data restore.
[21 Sep 2009 15:02] Ingo Strüwing 
The test case

Attachment: wl4844-3.test (application/octet-stream, text), 4.13 KiB.
[21 Sep 2009 15:08] Ingo Strüwing 
Medium defect, requires a new backup image format.
Unacceptable workaround, one needs to adjust auto_increment values manually.
Widespread impact, everyone with InnoDB tables is affected.
[22 Sep 2009 5:47] Rafal Somla 
An alternative approach would be to require backup/restore drivers to preserve the value of AUTO_INCREMENT. This is already the case for MyISAM and Memory drivers. One would have to fix the logical CS driver (perhaps also the blocking one) to set the AUTO_INCREMENT after restoring table data.

With this solution there is no need to change backup image format as the value of AUTO_INCREMENT would be saved by backup driver in table data snapshot.
[22 Sep 2009 7:30] Ingo Strüwing 
IMHO it is just by chance, not by design, that the MyISAM driver preserves that auto_increment value.

The MEMORY driver does _not_ preserve the value. I'm sorry if I was unclear about it. A restored MEMORY table shows tha auto_increment value as the highest existing value plus one. This is not necessarily the same value as of the time of backup. The value can be higher than max+1, not only by explicit ALTER, but also by deleting the row with the max value.
[20 Oct 2009 11:09] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/87456

2872 Ingo Struewing	2009-10-20
      Bug#47484 - Online Backup: Restore does not
                  set the auto_increment value correctly
      
      When implementing the new locking scheme for RESTORE,
      the table's auto_increment values won't be restored.
      The new scheme contains TRUNCATE, which resets the
      auto_increment values to zero. This will lead to a
      "duplicate key" error on the next attempt to insert
      an auto_increment value, at least for the InnoDB
      storage engine.
      
      This patch arranges for a reset of the values to
      what they were at backup time. The auto_increment
      value of each table is stored at the end of the
      table data stream in its own chunk.
      
      Note that this is done for the default- and
      consistent snapshot drivers only. The MyISAM native
      driver restores the value on its own already.
      
      Note that the auto_increment value for a MEMORY table
      is reset correctly, but an incremented value is used
      on the next insert. I can't say if this is a problem
      of MEMORY or of the default driver. There is no other
      storage engine, which has a (working) auto_increment
      feature and uses the default driver.
      
      Also there is a chance that the restored auto_increment
      value can be higher than at the validation point of
      BACKUP. One case is mentioned above. Another case could
      be concurrent DML on a table backed up with the
      consistent snapshot driver. Since we do not guarantee
      gap free auto_increment values, I consider this as
      acceptable. At least this patch assures that the values
      cannot be lower.
     @ mysql-test/suite/backup/r/backup_auto_incr.result
        Bug#47484 - Online Backup: Restore does not
                    set the auto_increment value correctly
        New test result.
     @ mysql-test/suite/backup/t/backup_auto_incr.test
        Bug#47484 - Online Backup: Restore does not
                    set the auto_increment value correctly
        New test case.
     @ sql/backup/be_default.cc
        Bug#47484 - Online Backup: Restore does not
                    set the auto_increment value correctly
        Added write and read operations for auto_increment values
        to the table data stream writer/reader objects.
     @ sql/backup/be_default.h
        Bug#47484 - Online Backup: Restore does not
                    set the auto_increment value correctly
        Added AUTO_INCR buffer type.
[20 Oct 2009 15:43] Ingo Strüwing 
Please understand the above patch as a prototype.
The attached test case does not have the final quality.
A couple of other test cases need updated result files.
[21 Oct 2009 12:10] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/87626

2879 Ingo Struewing	2009-10-21
      Bug#47484 - Online Backup: Restore does not
                set the auto_increment value correctly
      
      When implementing the new locking scheme for RESTORE,
      the table's auto_increment values won't be restored.
      The new scheme contains TRUNCATE, which resets the
      auto_increment values to zero. This will lead to a
      "duplicate key" error on the next attempt to insert
      an auto_increment value, at least for the InnoDB
      storage engine.
      
      This patch arranges for a reset of the values to
      what they were at backup time. The auto_increment
      value of each table is stored at the end of the
      table data stream in its own chunk.
      
      Note that this is done for the default- and
      consistent snapshot drivers only. The MyISAM native
      driver restores the value on its own already.
      
      Note that there is a chance that the restored
      auto_increment value can be higher than at the
      validation point of BACKUP. Concurrent DML on a table
      backed up with the consistent snapshot driver can
      increase the auto_increment value before the driver
      reads it. Since we do not guarantee gap free
      auto_increment values, I consider this as acceptable.
      At least this patch assures that the values cannot be
      lower.
      
      Note that the patch does also contain a fix to the
      MEMORY storage engine. ha_heap::reset_auto_increment()
      assigned the value incorrectly. This has not been
      noticed yet because the only use case of the method
      was to reset the value to zero.
     @ mysql-test/suite/backup/r/backup_auto_incr.result
        Bug#47484 - Online Backup: Restore does not
                    set the auto_increment value correctly
        New test result.
     @ mysql-test/suite/backup/t/backup_auto_incr.test
        Bug#47484 - Online Backup: Restore does not
                    set the auto_increment value correctly
        New test case.
     @ sql/backup/be_default.cc
        Bug#47484 - Online Backup: Restore does not
                    set the auto_increment value correctly
        Added write and read operations for auto_increment values
        to the table data stream writer/reader objects.
     @ sql/backup/be_default.h
        Bug#47484 - Online Backup: Restore does not
                    set the auto_increment value correctly
        Added AUTO_INCR buffer type.
     @ storage/heap/ha_heap.cc
        Bug#47484 - Online Backup: Restore does not
                    set the auto_increment value correctly
        Fixed the value assignment to HP_SHARE::auto_increment
        in ha_heap::reset_auto_increment().
     @ storage/heap/hp_open.c
        Bug#47484 - Online Backup: Restore does not
                    set the auto_increment value correctly
        Added DBUG.
[23 Oct 2009 9:06] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/87903

2879 Ingo Struewing	2009-10-23
      Bug#47484 - Online Backup: Restore does not
                set the auto_increment value correctly
      
      When implementing the new locking scheme for RESTORE,
      the table's auto_increment values won't be restored.
      The new scheme contains TRUNCATE, which resets the
      auto_increment values to zero. This will lead to a
      "duplicate key" error on the next attempt to insert
      an auto_increment value, at least for the InnoDB
      storage engine.
      
      This patch arranges for a reset of the values to
      what they were at backup time. The auto_increment
      value of each table is stored at the end of the
      table data stream in its own chunk.
      
      Note that this is done for the default- and
      consistent snapshot drivers only. The MyISAM native
      driver restores the value on its own already.
      
      Note that there is a chance that the restored
      auto_increment value can be higher than at the
      validation point of BACKUP. Concurrent DML on a table
      backed up with the consistent snapshot driver can
      increase the auto_increment value before the driver
      reads it. Since we do not guarantee gap free
      auto_increment values, I consider this as acceptable.
      At least this patch assures that the values cannot be
      lower.
      
      Note that the patch does also contain a fix to the
      MEMORY storage engine. ha_heap::reset_auto_increment()
      assigned the value incorrectly. This has not been
      noticed yet because the only use case of the method
      was to reset the value to zero.
     @ mysql-test/suite/backup/r/backup_auto_incr.result
        Bug#47484 - Online Backup: Restore does not
                    set the auto_increment value correctly
        New test result.
     @ mysql-test/suite/backup/t/backup_auto_incr.test
        Bug#47484 - Online Backup: Restore does not
                    set the auto_increment value correctly
        New test case.
     @ sql/backup/be_default.cc
        Bug#47484 - Online Backup: Restore does not
                    set the auto_increment value correctly
        Added write and read operations for auto_increment values
        to the table data stream writer/reader objects.
     @ sql/backup/be_default.h
        Bug#47484 - Online Backup: Restore does not
                    set the auto_increment value correctly
        Added AUTO_INCR buffer type.
     @ storage/heap/ha_heap.cc
        Bug#47484 - Online Backup: Restore does not
                    set the auto_increment value correctly
        Fixed the value assignment to HP_SHARE::auto_increment
        in ha_heap::reset_auto_increment().
     @ storage/heap/hp_open.c
        Bug#47484 - Online Backup: Restore does not
                    set the auto_increment value correctly
        Added DBUG.
[23 Oct 2009 13:37] Rafal Somla 
Good to push.
[23 Oct 2009 14:46] Chuck Bell 
Approved
[23 Oct 2009 14:54] Ingo Strüwing 
Hi docs team,

this patch arranges the default and consistent snapshot backup/restore drivers so that they restore the auto_increment value of each table to a value equal or higher to the value it had at backup validity time.

The MyISAM native driver does already restore the auto_increment values equal to what they were at backup validity time.

Implicitly, we do now have a new requirement for backup/restore drivers. They need to restore the auto_increment value of each table as equal or higher to what it was at backup validity time. I don't know, where such requirement can be documented though.
[23 Oct 2009 15:05] Ingo Strüwing 
Oops, while editing my above comment, Chuck approved. When I saved my comment, the status has implicitly been reset to what it was when I started typing. :-(
[27 Oct 2009 8:07] Ingo Strüwing 
Queued to mysql-6.0-backup.
[11 Jan 2010 15:32] Bugs System 
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/96509

3050 Ingo Struewing	2010-01-11
      WL#5101 - MySQL Backup back port
      Merged revid:ingo.struewing@sun.com-20091023090611-spkufmaiz1sttp58
        Bug#47484 - Online Backup: Restore does not
                  set the auto_increment value correctly
        
        When implementing the new locking scheme for RESTORE,
        the table's auto_increment values won't be restored.
        The new scheme contains TRUNCATE, which resets the
        auto_increment values to zero. This will lead to a
        "duplicate key" error on the next attempt to insert
        an auto_increment value, at least for the InnoDB
        storage engine.
        
        This patch arranges for a reset of the values to
        what they were at backup time. The auto_increment
        value of each table is stored at the end of the
        table data stream in its own chunk.
        
        Note that this is done for the default- and
        consistent snapshot drivers only. The MyISAM native
        driver restores the value on its own already.
        
        Note that there is a chance that the restored
        auto_increment value can be higher than at the
        validation point of BACKUP. Concurrent DML on a table
        backed up with the consistent snapshot driver can
        increase the auto_increment value before the driver
        reads it. Since we do not guarantee gap free
        auto_increment values, I consider this as acceptable.
        At least this patch assures that the values cannot be
        lower.
        
        Note that the patch does also contain a fix to the
        MEMORY storage engine. ha_heap::reset_auto_increment()
        assigned the value incorrectly. This has not been
        noticed yet because the only use case of the method
        was to reset the value to zero.
     @ storage/heap/ha_heap.cc
        WL#5101 - MySQL Backup back port
            Bug#47484 - Online Backup: Restore does not
                        set the auto_increment value correctly
            Fixed the value assignment to HP_SHARE::auto_increment
            in ha_heap::reset_auto_increment().
     @ storage/heap/hp_open.c
        WL#5101 - MySQL Backup back port
            Bug#47484 - Online Backup: Restore does not
                        set the auto_increment value correctly
            Added DBUG.
[20 Feb 2010 9:17] Bugs System 
Pushed into 6.0.14-alpha (revid:ingo.struewing@sun.com-20100218152520-s4v1ld76bif06eqn) (version source revid:ingo.struewing@sun.com-20100119103538-wtp5alpz4p2jayl5) (merge vers: 6.0.14-alpha) (pib:16)
[7 Mar 2010 19:40] Paul DuBois 
Noted in 6.0.14 changelog.

RESTORE did not restore table AUTO_INCREMENT values properly.