MySQL Bugs: #47484: Online Backup: Restore does not set the auto

Bug #47484	Online Backup: Restore does not set the auto_increment value correctly
Submitted:	21 Sep 17:01	Modified:	27 Oct 9:07
Reporter:	Ingo Strüwing
Status:	Patch queued
Category:	Server: Backup	Severity:	S3 (Non-critical)
Version:	5.4.4	OS:	Any
Assigned to:	Ingo Strüwing	Target Version:
Triage:	Triaged: D2 (Serious)

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.

The test case

Attachment: wl4844-3.test (application/octet-stream, text), 4.13 KiB.

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.

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.

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.

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.

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.

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.

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.

Good to push.

Approved

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.

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

Queued to mysql-6.0-backup.