#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
