MySQL Lists are EOL. Please join:

List:Commits« Previous MessageNext Message »
From:Ingo Struewing Date:October 23 2009 9:06am
Subject:bzr commit into mysql-6.0-backup branch (ingo.struewing:2879) Bug#47484
View as plain text  
#At file:///home2/mydev/bzrroot/mysql-6.0-bug47484-2/ based on revid:charles.bell@stripped

 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.

    added:
      mysql-test/suite/backup/r/backup_auto_incr.result
      mysql-test/suite/backup/t/backup_auto_incr.test
    modified:
      sql/backup/be_default.cc
      sql/backup/be_default.h
      storage/heap/ha_heap.cc
      storage/heap/hp_open.c
=== added file 'mysql-test/suite/backup/r/backup_auto_incr.result'
--- a/mysql-test/suite/backup/r/backup_auto_incr.result	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/r/backup_auto_incr.result	2009-10-23 09:06:11 +0000
@@ -0,0 +1,500 @@
+SET DEBUG_SYNC= 'RESET';
+DROP TABLE IF EXISTS t11, t12, t13;
+DROP TABLE IF EXISTS t21, t22, t23;
+DROP TABLE IF EXISTS t31, t32, t33;
+#
+# Bug#47484 - Online Backup: Restore does not
+#             set the auto_increment value correctly
+#
+# Show how BACKUP/RESTORE treat AUTO_INCREMENT.
+#
+CREATE DATABASE bug47484_db1;
+USE bug47484_db1;
+#
+CREATE TABLE t11 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=MyISAM;
+# Insert values 1, 2, 3.
+INSERT INTO t11 VALUES (NULL), (NULL), (NULL);
+# Delete 3, auto_increment stays at 4.
+DELETE FROM t11 WHERE c1=3;
+# Set an explicit auto_increment value.
+ALTER TABLE t11 AUTO_INCREMENT=8;
+SHOW CREATE TABLE t11;
+Table	Create Table
+t11	CREATE TABLE `t11` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
+SELECT * FROM t11;
+c1
+1
+2
+#
+CREATE TABLE t12 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=MyISAM;
+# Insert values 1, 2, 3.
+INSERT INTO t12 VALUES (NULL), (NULL), (NULL);
+# Delete 2.
+DELETE FROM t12 WHERE c1=2;
+# Set an explicit auto_increment value.
+ALTER TABLE t12 AUTO_INCREMENT=2;
+SHOW CREATE TABLE t12;
+Table	Create Table
+t12	CREATE TABLE `t12` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
+SELECT * FROM t12;
+c1
+1
+3
+#
+CREATE TABLE t13 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=MyISAM;
+# Insert values 1, 2, 3.
+INSERT INTO t13 VALUES (NULL), (NULL), (NULL);
+# Delete 3.
+DELETE FROM t13 WHERE c1=3;
+# Set an explicit auto_increment value.
+ALTER TABLE t13 AUTO_INCREMENT=2;
+SHOW CREATE TABLE t13;
+Table	Create Table
+t13	CREATE TABLE `t13` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
+SELECT * FROM t13;
+c1
+1
+2
+#
+CREATE TABLE t21 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=MEMORY;
+# Insert values 1, 2, 3.
+INSERT INTO t21 VALUES (NULL), (NULL), (NULL);
+# Delete 3, auto_increment stays at 4.
+DELETE FROM t21 WHERE c1=3;
+# Set an explicit auto_increment value.
+ALTER TABLE t21 AUTO_INCREMENT=8;
+SHOW CREATE TABLE t21;
+Table	Create Table
+t21	CREATE TABLE `t21` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
+SELECT * FROM t21;
+c1
+1
+2
+#
+CREATE TABLE t22 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=MEMORY;
+# Insert values 1, 2, 3.
+INSERT INTO t22 VALUES (NULL), (NULL), (NULL);
+# Delete 2.
+DELETE FROM t22 WHERE c1=2;
+# Set an explicit auto_increment value.
+ALTER TABLE t22 AUTO_INCREMENT=2;
+SHOW CREATE TABLE t22;
+Table	Create Table
+t22	CREATE TABLE `t22` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
+SELECT * FROM t22;
+c1
+1
+3
+#
+CREATE TABLE t23 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=MEMORY;
+# Insert values 1, 2, 3.
+INSERT INTO t23 VALUES (NULL), (NULL), (NULL);
+# Delete 3.
+DELETE FROM t23 WHERE c1=3;
+# Set an explicit auto_increment value.
+ALTER TABLE t23 AUTO_INCREMENT=2;
+SHOW CREATE TABLE t23;
+Table	Create Table
+t23	CREATE TABLE `t23` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
+SELECT * FROM t23;
+c1
+1
+2
+#
+CREATE TABLE t31 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
+# Insert values 1, 2, 3.
+INSERT INTO t31 VALUES (NULL), (NULL), (NULL);
+# Delete 3, auto_increment stays at 4.
+DELETE FROM t31 WHERE c1=3;
+# Set an explicit auto_increment value.
+ALTER TABLE t31 AUTO_INCREMENT=8;
+SHOW CREATE TABLE t31;
+Table	Create Table
+t31	CREATE TABLE `t31` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
+SELECT * FROM t31;
+c1
+1
+2
+#
+CREATE TABLE t32 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
+# Insert values 1, 2, 3.
+INSERT INTO t32 VALUES (NULL), (NULL), (NULL);
+# Delete 2.
+DELETE FROM t32 WHERE c1=2;
+# Set an explicit auto_increment value.
+ALTER TABLE t32 AUTO_INCREMENT=2;
+SHOW CREATE TABLE t32;
+Table	Create Table
+t32	CREATE TABLE `t32` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
+SELECT * FROM t32;
+c1
+1
+3
+#
+CREATE TABLE t33 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
+# Insert values 1, 2, 3.
+INSERT INTO t33 VALUES (NULL), (NULL), (NULL);
+# Delete 3.
+DELETE FROM t33 WHERE c1=3;
+# Set an explicit auto_increment value.
+ALTER TABLE t33 AUTO_INCREMENT=2;
+SHOW CREATE TABLE t33;
+Table	Create Table
+t33	CREATE TABLE `t33` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
+SELECT * FROM t33;
+c1
+1
+2
+#
+BACKUP DATABASE bug47484_db1 TO 'bug47484_db1.bak';
+backup_id
+###
+
+# exec $MYSQL_BACKUP
+
+Image path: #
+Image size: #
+Image compression:   none
+Image version:       1
+Creation time: #
+Server version: #
+Server byte order: #
+Server charset:      'utf8'
+Backup image chrset: 'utf8'
+Snapshot count:      3
+
+Snapshots:
+
+  Snapshot 0 type    native from 'MyISAM'  version 1.0
+  Snapshot 0 version 1
+  Snapshot 0 tables  3
+  Snapshot 0 table   'bug47484_db1'.'t11'
+  Snapshot 0 table   'bug47484_db1'.'t12'
+  Snapshot 0 table   'bug47484_db1'.'t13'
+  Snapshot 1 type    logical from locked tables
+  Snapshot 1 version 1
+  Snapshot 1 tables  3
+  Snapshot 1 table   'bug47484_db1'.'t21'
+  Snapshot 1 table   'bug47484_db1'.'t22'
+  Snapshot 1 table   'bug47484_db1'.'t23'
+  Snapshot 2 type    logical from consistent snapshot
+  Snapshot 2 version 1
+  Snapshot 2 tables  3
+  Snapshot 2 table   'bug47484_db1'.'t31'
+  Snapshot 2 table   'bug47484_db1'.'t32'
+  Snapshot 2 table   'bug47484_db1'.'t33'
+
+Catalog details:
+
+  Database  'bug47484_db1'
+    Table     'bug47484_db1'.'t11'
+    Table     'bug47484_db1'.'t12'
+    Table     'bug47484_db1'.'t13'
+    Table     'bug47484_db1'.'t21'
+    Table     'bug47484_db1'.'t22'
+    Table     'bug47484_db1'.'t23'
+    Table     'bug47484_db1'.'t31'
+    Table     'bug47484_db1'.'t32'
+    Table     'bug47484_db1'.'t33'
+
+Data chunks:
+  Chunk 1 has 15 bytes for table 'bug47484_db1'.'t11' from snapshot 0
+  Chunk 2 has 2049 bytes for table 'bug47484_db1'.'t11' from snapshot 0
+  Chunk 3 has 15 bytes for table 'bug47484_db1'.'t12' from snapshot 0
+  Chunk 4 has 2049 bytes for table 'bug47484_db1'.'t12' from snapshot 0
+  Chunk 5 has 15 bytes for table 'bug47484_db1'.'t13' from snapshot 0
+  Chunk 6 has 2049 bytes for table 'bug47484_db1'.'t13' from snapshot 0
+  Chunk 7 has 6 bytes for table 'bug47484_db1'.'t21' from snapshot 1
+  Chunk 8 has 6 bytes for table 'bug47484_db1'.'t21' from snapshot 1
+  Chunk 9 has 9 bytes for table 'bug47484_db1'.'t21' from snapshot 1
+  Chunk 10 has 6 bytes for table 'bug47484_db1'.'t22' from snapshot 1
+  Chunk 11 has 6 bytes for table 'bug47484_db1'.'t22' from snapshot 1
+  Chunk 12 has 9 bytes for table 'bug47484_db1'.'t22' from snapshot 1
+  Chunk 13 has 6 bytes for table 'bug47484_db1'.'t23' from snapshot 1
+  Chunk 14 has 6 bytes for table 'bug47484_db1'.'t23' from snapshot 1
+  Chunk 15 has 9 bytes for table 'bug47484_db1'.'t23' from snapshot 1
+  Chunk 16 has 6 bytes for table 'bug47484_db1'.'t31' from snapshot 2
+  Chunk 17 has 6 bytes for table 'bug47484_db1'.'t31' from snapshot 2
+  Chunk 18 has 9 bytes for table 'bug47484_db1'.'t31' from snapshot 2
+  Chunk 19 has 6 bytes for table 'bug47484_db1'.'t32' from snapshot 2
+  Chunk 20 has 6 bytes for table 'bug47484_db1'.'t32' from snapshot 2
+  Chunk 21 has 9 bytes for table 'bug47484_db1'.'t32' from snapshot 2
+  Chunk 22 has 6 bytes for table 'bug47484_db1'.'t33' from snapshot 2
+  Chunk 23 has 6 bytes for table 'bug47484_db1'.'t33' from snapshot 2
+  Chunk 24 has 9 bytes for table 'bug47484_db1'.'t33' from snapshot 2
+#
+DROP DATABASE bug47484_db1;
+USE test;
+#
+RESTORE FROM 'bug47484_db1.bak';
+backup_id
+###
+USE bug47484_db1;
+#
+SHOW CREATE TABLE t11;
+Table	Create Table
+t11	CREATE TABLE `t11` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
+SHOW CREATE TABLE t12;
+Table	Create Table
+t12	CREATE TABLE `t12` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
+SHOW CREATE TABLE t13;
+Table	Create Table
+t13	CREATE TABLE `t13` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
+SHOW CREATE TABLE t21;
+Table	Create Table
+t21	CREATE TABLE `t21` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
+SHOW CREATE TABLE t22;
+Table	Create Table
+t22	CREATE TABLE `t22` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
+SHOW CREATE TABLE t23;
+Table	Create Table
+t23	CREATE TABLE `t23` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=MEMORY AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
+SHOW CREATE TABLE t31;
+Table	Create Table
+t31	CREATE TABLE `t31` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
+SHOW CREATE TABLE t32;
+Table	Create Table
+t32	CREATE TABLE `t32` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
+SHOW CREATE TABLE t33;
+Table	Create Table
+t33	CREATE TABLE `t33` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
+#
+SELECT * FROM t11;
+c1
+1
+2
+SELECT * FROM t12;
+c1
+1
+3
+SELECT * FROM t13;
+c1
+1
+2
+SELECT * FROM t21;
+c1
+1
+2
+SELECT * FROM t22;
+c1
+1
+3
+SELECT * FROM t23;
+c1
+1
+2
+SELECT * FROM t31;
+c1
+1
+2
+SELECT * FROM t32;
+c1
+1
+3
+SELECT * FROM t33;
+c1
+1
+2
+#
+INSERT INTO t11 VALUES (NULL);
+INSERT INTO t12 VALUES (NULL);
+INSERT INTO t13 VALUES (NULL);
+INSERT INTO t21 VALUES (NULL);
+INSERT INTO t22 VALUES (NULL);
+INSERT INTO t23 VALUES (NULL);
+INSERT INTO t31 VALUES (NULL);
+INSERT INTO t32 VALUES (NULL);
+INSERT INTO t33 VALUES (NULL);
+#
+SELECT * FROM t11;
+c1
+1
+2
+8
+SELECT * FROM t12;
+c1
+1
+3
+4
+SELECT * FROM t13;
+c1
+1
+2
+3
+SELECT * FROM t21;
+c1
+1
+2
+8
+SELECT * FROM t22;
+c1
+1
+3
+4
+SELECT * FROM t23;
+c1
+1
+2
+3
+SELECT * FROM t31;
+c1
+1
+2
+8
+SELECT * FROM t32;
+c1
+1
+3
+4
+SELECT * FROM t33;
+c1
+1
+2
+3
+#
+DROP DATABASE bug47484_db1;
+USE test;
+#
+# Show how DML in parallel to BACKUP affects auto_increment
+# Relevant for CS driver only, at the moment.
+#
+CREATE DATABASE bug47484_db1;
+USE bug47484_db1;
+CREATE TABLE t31 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
+INSERT INTO t31 VALUES (NULL), (NULL), (NULL);
+# Show table auto_increment value and contents.
+SHOW CREATE TABLE t31;
+Table	Create Table
+t31	CREATE TABLE `t31` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
+SELECT * FROM t31;
+c1
+1
+2
+3
+# Start a backup, which stalls during table backup so that
+# another transaction can change the auto_increment value.
+SET DEBUG_SYNC= 'before_backup_auto_incr SIGNAL sync WAIT_FOR go';
+BACKUP DATABASE bug47484_db1 TO 'bug47484_db1.bak';
+#
+# connection con1, wait for BACKUP to reach the sync point.
+SET DEBUG_SYNC= 'now WAIT_FOR sync';
+# Execute some INSERTs to increase the auto_increment value.
+USE bug47484_db1;
+INSERT INTO t31 VALUES (NULL);
+INSERT INTO t31 VALUES (NULL);
+INSERT INTO t31 VALUES (NULL);
+# Let BACKUP proceed.
+SET DEBUG_SYNC= 'now SIGNAL go';
+#
+# connection default, fetch BACKUP result.
+backup_id
+###
+SET DEBUG_SYNC= 'RESET';
+# Show table auto_increment value and contents.
+SHOW CREATE TABLE t31;
+Table	Create Table
+t31	CREATE TABLE `t31` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
+SELECT * FROM t31;
+c1
+1
+2
+3
+4
+5
+6
+#
+DROP DATABASE bug47484_db1;
+USE test;
+#
+RESTORE FROM 'bug47484_db1.bak';
+backup_id
+###
+USE bug47484_db1;
+# Show table auto_increment value and contents.
+SHOW CREATE TABLE t31;
+Table	Create Table
+t31	CREATE TABLE `t31` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=latin1
+SELECT * FROM t31;
+c1
+1
+2
+3
+INSERT INTO t31 VALUES (NULL);
+SHOW CREATE TABLE t31;
+Table	Create Table
+t31	CREATE TABLE `t31` (
+  `c1` int(11) NOT NULL AUTO_INCREMENT,
+  PRIMARY KEY (`c1`)
+) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=latin1
+SELECT * FROM t31;
+c1
+1
+2
+3
+7
+#
+DROP DATABASE bug47484_db1;
+USE test;

=== added file 'mysql-test/suite/backup/t/backup_auto_incr.test'
--- a/mysql-test/suite/backup/t/backup_auto_incr.test	1970-01-01 00:00:00 +0000
+++ b/mysql-test/suite/backup/t/backup_auto_incr.test	2009-10-23 09:06:11 +0000
@@ -0,0 +1,261 @@
+#
+# Show how BACKUP/RESTORE treat AUTO_INCREMENT.
+#
+# Bug#47484 - Online Backup: Restore does not
+#             set the auto_increment value correctly
+#
+# Ingo Struewing, 2009-10-20
+#
+
+--source include/not_embedded.inc
+--source include/have_innodb.inc
+--source include/have_debug_sync.inc
+
+--let $MYSQLD_BACKUPDIR= `SELECT @@backupdir`
+
+SET DEBUG_SYNC= 'RESET';
+--disable_warnings
+DROP TABLE IF EXISTS t11, t12, t13;
+DROP TABLE IF EXISTS t21, t22, t23;
+DROP TABLE IF EXISTS t31, t32, t33;
+--enable_warnings
+
+--echo #
+--echo # Bug#47484 - Online Backup: Restore does not
+--echo #             set the auto_increment value correctly
+--echo #
+--echo # Show how BACKUP/RESTORE treat AUTO_INCREMENT.
+--echo #
+#
+CREATE DATABASE bug47484_db1;
+USE bug47484_db1;
+#
+--echo #
+CREATE TABLE t11 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=MyISAM;
+--echo # Insert values 1, 2, 3.
+INSERT INTO t11 VALUES (NULL), (NULL), (NULL);
+--echo # Delete 3, auto_increment stays at 4.
+DELETE FROM t11 WHERE c1=3;
+--echo # Set an explicit auto_increment value.
+ALTER TABLE t11 AUTO_INCREMENT=8;
+SHOW CREATE TABLE t11;
+SELECT * FROM t11;
+#
+--echo #
+CREATE TABLE t12 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=MyISAM;
+--echo # Insert values 1, 2, 3.
+INSERT INTO t12 VALUES (NULL), (NULL), (NULL);
+--echo # Delete 2.
+DELETE FROM t12 WHERE c1=2;
+--echo # Set an explicit auto_increment value.
+ALTER TABLE t12 AUTO_INCREMENT=2;
+SHOW CREATE TABLE t12;
+SELECT * FROM t12;
+#
+--echo #
+CREATE TABLE t13 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=MyISAM;
+--echo # Insert values 1, 2, 3.
+INSERT INTO t13 VALUES (NULL), (NULL), (NULL);
+--echo # Delete 3.
+DELETE FROM t13 WHERE c1=3;
+--echo # Set an explicit auto_increment value.
+ALTER TABLE t13 AUTO_INCREMENT=2;
+SHOW CREATE TABLE t13;
+SELECT * FROM t13;
+#
+--echo #
+CREATE TABLE t21 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=MEMORY;
+--echo # Insert values 1, 2, 3.
+INSERT INTO t21 VALUES (NULL), (NULL), (NULL);
+--echo # Delete 3, auto_increment stays at 4.
+DELETE FROM t21 WHERE c1=3;
+--echo # Set an explicit auto_increment value.
+ALTER TABLE t21 AUTO_INCREMENT=8;
+SHOW CREATE TABLE t21;
+SELECT * FROM t21;
+#
+--echo #
+CREATE TABLE t22 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=MEMORY;
+--echo # Insert values 1, 2, 3.
+INSERT INTO t22 VALUES (NULL), (NULL), (NULL);
+--echo # Delete 2.
+DELETE FROM t22 WHERE c1=2;
+--echo # Set an explicit auto_increment value.
+ALTER TABLE t22 AUTO_INCREMENT=2;
+SHOW CREATE TABLE t22;
+SELECT * FROM t22;
+#
+--echo #
+CREATE TABLE t23 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=MEMORY;
+--echo # Insert values 1, 2, 3.
+INSERT INTO t23 VALUES (NULL), (NULL), (NULL);
+--echo # Delete 3.
+DELETE FROM t23 WHERE c1=3;
+--echo # Set an explicit auto_increment value.
+ALTER TABLE t23 AUTO_INCREMENT=2;
+SHOW CREATE TABLE t23;
+SELECT * FROM t23;
+#
+--echo #
+CREATE TABLE t31 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
+--echo # Insert values 1, 2, 3.
+INSERT INTO t31 VALUES (NULL), (NULL), (NULL);
+--echo # Delete 3, auto_increment stays at 4.
+DELETE FROM t31 WHERE c1=3;
+--echo # Set an explicit auto_increment value.
+ALTER TABLE t31 AUTO_INCREMENT=8;
+SHOW CREATE TABLE t31;
+SELECT * FROM t31;
+#
+--echo #
+CREATE TABLE t32 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
+--echo # Insert values 1, 2, 3.
+INSERT INTO t32 VALUES (NULL), (NULL), (NULL);
+--echo # Delete 2.
+DELETE FROM t32 WHERE c1=2;
+--echo # Set an explicit auto_increment value.
+ALTER TABLE t32 AUTO_INCREMENT=2;
+SHOW CREATE TABLE t32;
+SELECT * FROM t32;
+#
+--echo #
+CREATE TABLE t33 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
+--echo # Insert values 1, 2, 3.
+INSERT INTO t33 VALUES (NULL), (NULL), (NULL);
+--echo # Delete 3.
+DELETE FROM t33 WHERE c1=3;
+--echo # Set an explicit auto_increment value.
+ALTER TABLE t33 AUTO_INCREMENT=2;
+SHOW CREATE TABLE t33;
+SELECT * FROM t33;
+#
+--echo #
+--replace_column 1 ###
+BACKUP DATABASE bug47484_db1 TO 'bug47484_db1.bak';
+#
+--echo
+--echo # exec \$MYSQL_BACKUP
+--source suite/backup/include/backup_client_regex_output.inc
+exec $MYSQL_BACKUP -v --catalog-details --snapshots --data-chunks $MYSQLD_BACKUPDIR/bug47484_db1.bak 2>&1;
+#
+--echo #
+DROP DATABASE bug47484_db1;
+USE test;
+#
+--echo #
+--replace_column 1 ###
+RESTORE FROM 'bug47484_db1.bak';
+USE bug47484_db1;
+#
+--echo #
+SHOW CREATE TABLE t11;
+SHOW CREATE TABLE t12;
+SHOW CREATE TABLE t13;
+SHOW CREATE TABLE t21;
+SHOW CREATE TABLE t22;
+SHOW CREATE TABLE t23;
+SHOW CREATE TABLE t31;
+SHOW CREATE TABLE t32;
+SHOW CREATE TABLE t33;
+--echo #
+SELECT * FROM t11;
+SELECT * FROM t12;
+SELECT * FROM t13;
+SELECT * FROM t21;
+SELECT * FROM t22;
+SELECT * FROM t23;
+SELECT * FROM t31;
+SELECT * FROM t32;
+SELECT * FROM t33;
+--echo #
+INSERT INTO t11 VALUES (NULL);
+INSERT INTO t12 VALUES (NULL);
+INSERT INTO t13 VALUES (NULL);
+INSERT INTO t21 VALUES (NULL);
+INSERT INTO t22 VALUES (NULL);
+INSERT INTO t23 VALUES (NULL);
+INSERT INTO t31 VALUES (NULL);
+INSERT INTO t32 VALUES (NULL);
+INSERT INTO t33 VALUES (NULL);
+--echo #
+SELECT * FROM t11;
+SELECT * FROM t12;
+SELECT * FROM t13;
+SELECT * FROM t21;
+SELECT * FROM t22;
+SELECT * FROM t23;
+SELECT * FROM t31;
+SELECT * FROM t32;
+SELECT * FROM t33;
+#
+--echo #
+DROP DATABASE bug47484_db1;
+USE test;
+--remove_file $MYSQLD_BACKUPDIR/bug47484_db1.bak
+
+--echo #
+--echo # Show how DML in parallel to BACKUP affects auto_increment
+--echo # Relevant for CS driver only, at the moment.
+--echo #
+#
+CREATE DATABASE bug47484_db1;
+USE bug47484_db1;
+#
+CREATE TABLE t31 (c1 INT PRIMARY KEY AUTO_INCREMENT) ENGINE=InnoDB;
+INSERT INTO t31 VALUES (NULL), (NULL), (NULL);
+#
+--echo # Show table auto_increment value and contents.
+SHOW CREATE TABLE t31;
+SELECT * FROM t31;
+#
+--echo # Start a backup, which stalls during table backup so that
+--echo # another transaction can change the auto_increment value.
+SET DEBUG_SYNC= 'before_backup_auto_incr SIGNAL sync WAIT_FOR go';
+send BACKUP DATABASE bug47484_db1 TO 'bug47484_db1.bak';
+#
+    --echo #
+    --echo # connection con1, wait for BACKUP to reach the sync point.
+    --connect (con1,localhost,root,,)
+    SET DEBUG_SYNC= 'now WAIT_FOR sync';
+    --echo # Execute some INSERTs to increase the auto_increment value.
+    USE bug47484_db1;
+    INSERT INTO t31 VALUES (NULL);
+    INSERT INTO t31 VALUES (NULL);
+    INSERT INTO t31 VALUES (NULL);
+    --echo # Let BACKUP proceed.
+    SET DEBUG_SYNC= 'now SIGNAL go';
+    --disconnect con1
+#
+--echo #
+--echo # connection default, fetch BACKUP result.
+--connection default
+--replace_column 1 ###
+reap;
+SET DEBUG_SYNC= 'RESET';
+#
+--echo # Show table auto_increment value and contents.
+SHOW CREATE TABLE t31;
+SELECT * FROM t31;
+#
+--echo #
+DROP DATABASE bug47484_db1;
+USE test;
+#
+--echo #
+--replace_column 1 ###
+RESTORE FROM 'bug47484_db1.bak';
+USE bug47484_db1;
+#
+--echo # Show table auto_increment value and contents.
+SHOW CREATE TABLE t31;
+SELECT * FROM t31;
+#
+INSERT INTO t31 VALUES (NULL);
+SHOW CREATE TABLE t31;
+SELECT * FROM t31;
+#
+--echo #
+DROP DATABASE bug47484_db1;
+USE test;
+--remove_file $MYSQLD_BACKUPDIR/bug47484_db1.bak
+

=== modified file 'sql/backup/be_default.cc'
--- a/sql/backup/be_default.cc	2009-09-10 13:46:13 +0000
+++ b/sql/backup/be_default.cc	2009-10-23 09:06:11 +0000
@@ -421,8 +421,25 @@ result_t Backup::get_data(Buffer &buf)
     */
     if (last_read_res == HA_ERR_END_OF_FILE)
     {
+      /*
+        TABLE::found_next_number_field references the auto_increment
+        field, if there is one. Otherwise it is NULL.
+      */
+      if (cur_table->found_next_number_field)
+      {
+        DEBUG_SYNC(locking_thd->m_thd, "before_backup_auto_incr");
+        *buf.data= AUTO_INCR;
+        buf.size= META_SIZE + 8;
+        hdl->info(HA_STATUS_AUTO);
+        int8store(buf.data + META_SIZE, hdl->stats.auto_increment_value);
+        DBUG_PRINT("be_default",
+                   ("table: '%s'.'%s'  auto_inc: %lu",
+                    cur_table->s->db.str, cur_table->s->table_name.str,
+                    (ulong) hdl->stats.auto_increment_value));
+      }
+      else
+        buf.size= 0;
       end_tbl_read();
-      buf.size= 0;
       buf.last= TRUE;
       mode= GET_NEXT_TABLE;
 
@@ -717,6 +734,7 @@ result_t Restore::send_data(Buffer &buf)
   /* 
     Determine mode of operation and execute mode.
   */
+  DBUG_PRINT("default_restore", ("mode %u", mode));
   switch (mode) {
 
   /*
@@ -765,8 +783,22 @@ result_t Restore::send_data(Buffer &buf)
       /*
          Now we're reconstructing the rec from multiple parts.
       */
+      DBUG_PRINT("default_restore", ("block_type %u", block_type));
       switch (block_type) {
 
+      case AUTO_INCR:
+        {
+          ulonglong auto_incr;
+          DBUG_ASSERT(size == 8);
+          auto_incr= uint8korr((byte *)buf.data + META_SIZE);
+          DBUG_PRINT("default_restore",
+                     ("table: '%s'.'%s'  auto_inc: %lu",
+                      cur_table->s->db.str, cur_table->s->table_name.str,
+                      (ulong) auto_incr));
+          hdl->ha_reset_auto_increment(auto_incr);
+          break;
+        }
+
       /*
         Buffer iterator not needed, just write the data.
       */
@@ -775,11 +807,8 @@ result_t Restore::send_data(Buffer &buf)
         uint error= unpack((byte *)buf.data + META_SIZE);
         if (error)
           DBUG_RETURN(ERROR);
-        else 
-        {
-          mode= CHECK_BLOBS;
-          DBUG_RETURN(PROCESSING);
-        }
+        mode= CHECK_BLOBS;
+        DBUG_RETURN(PROCESSING);
       }
 
       /*
@@ -803,6 +832,7 @@ result_t Restore::send_data(Buffer &buf)
         break;
 
       }
+
       /*
         If this is the last part, assemble and write.
       */
@@ -813,7 +843,9 @@ result_t Restore::send_data(Buffer &buf)
         unpack(ptr);
         rec_buffer.reset();
         mode= CHECK_BLOBS;
+        /*FALLTHROUGH to default*/
       }
+
       default:
         DBUG_RETURN(ERROR);
       }
@@ -949,8 +981,10 @@ result_t Restore::send_data(Buffer &buf)
   }
 
   default:
+    DBUG_PRINT("default_restore", ("default: return ERROR"));
     DBUG_RETURN(ERROR);
   }
+  DBUG_PRINT("default_restore", ("return OK"));
   DBUG_RETURN(OK); 
 }
 

=== modified file 'sql/backup/be_default.h'
--- a/sql/backup/be_default.h	2009-03-16 14:38:05 +0000
+++ b/sql/backup/be_default.h	2009-10-23 09:06:11 +0000
@@ -31,6 +31,7 @@ const byte BLOB_ONCE=   3U;     // Singl
 const byte BLOB_FIRST= (3U<<1); // First data block in buffer for blob buffer
 const byte BLOB_DATA=  (3U<<2); // Intermediate data block for blob buffer
 const byte BLOB_LAST=  (3U<<3); // Last data block in buffer for blob buffer
+const byte AUTO_INCR=   5U;     // Auto_increment value
 
 
 /**

=== modified file 'storage/heap/ha_heap.cc'
--- a/storage/heap/ha_heap.cc	2009-09-16 05:54:22 +0000
+++ b/storage/heap/ha_heap.cc	2009-10-23 09:06:11 +0000
@@ -402,7 +402,10 @@ int ha_heap::info(uint flag)
   stats.max_data_file_length= hp_info.max_records * hp_info.reclength;
   stats.delete_length=        hp_info.deleted * hp_info.reclength;
   if (flag & HA_STATUS_AUTO)
+  {
     stats.auto_increment_value= hp_info.auto_increment;
+    DBUG_PRINT("heap", ("autoinc: %lu", (ulong) stats.auto_increment_value));
+  }
   /*
     If info() is called for the first time after open(), we will still
     have to update the key statistics. Hoping that a table lock is now
@@ -443,7 +446,12 @@ int ha_heap::delete_all_rows()
 
 int ha_heap::reset_auto_increment(ulonglong value)
 {
-  file->s->auto_increment= value;
+  /*
+    The heap share holds the last used auto_increment value.
+    See heap_info() and ha_heap::create().
+  */
+  file->s->auto_increment= value ? value - 1 : 0;
+  DBUG_PRINT("heap", ("autoinc: %lu", (ulong) file->s->auto_increment));
   return 0;
 }
 

=== modified file 'storage/heap/hp_open.c'
--- a/storage/heap/hp_open.c	2008-03-27 18:40:00 +0000
+++ b/storage/heap/hp_open.c	2009-10-23 09:06:11 +0000
@@ -53,9 +53,11 @@ HP_INFO *heap_open_from_share(HP_SHARE *
 #ifndef DBUG_OFF
   info->opt_flag= READ_CHECK_USED;		/* Check when changing */
 #endif
-  DBUG_PRINT("exit",("heap: %p  reclength: %d  records_in_block: %d",
+  DBUG_PRINT("exit",("heap: %p  reclength: %d  records_in_block: %d  "
+                     "autoinc: %lu",
 		     info, share->reclength,
-                     share->block.records_in_block));
+                     share->block.records_in_block,
+                     (ulong) share->auto_increment));
   DBUG_RETURN(info);
 }
 


Attachment: [text/bzr-bundle] bzr/ingo.struewing@sun.com-20091023090611-spkufmaiz1sttp58.bundle
Thread
bzr commit into mysql-6.0-backup branch (ingo.struewing:2879) Bug#47484Ingo Struewing23 Oct
  • Re: bzr commit into mysql-6.0-backup branch (ingo.struewing:2879)Bug#47484Rafal Somla23 Oct
    • Re: bzr commit into mysql-6.0-backup branch (ingo.struewing:2879)Bug#47484Ingo Strüwing23 Oct
    • Re: bzr commit into mysql-6.0-backup branch (ingo.struewing:2879)Bug#47484Paul DuBois23 Oct
  • Re: bzr commit into mysql-6.0-backup branch (ingo.struewing:2879)Bug#47484Charles Bell23 Oct
    • Re: bzr commit into mysql-6.0-backup branch (ingo.struewing:2879)Bug#47484Ingo Strüwing23 Oct