#At file:///home/anders/work/bzrwork/bug45999/mysql-5.1-bugteam/ based on revid:joro@stripped
3078 Li-Bing.Song@stripped 2009-08-27
BUG#45999 Row based replication fails when auto_increment field = 0
In RBP, Slave trait 0 as NULL of an auto_increment field whenever NO_AUTO_VALUE_ON_ZERO has setted or not.
So slave always generates a new index for an auto_increment field if the value of an auto_increment field is 0.
This results in an inconsistancy of the data between master and slave.
Many times, it results in an error of "duplicate key".
In fact, RBP slave does not care about what value is an auto_increment field.
It must write the value without any change.
This patch wrote code to trait 0 as a value of an auto_increment field on slave
whenever NO_AUTO_VALUE_ON_ZERO has setted or not.
@ sql/handler.cc
DBUG_ASSERT(next_insert_id == 0) results in a crash when DBUG opened.
So it is erased.
modified:
mysql-test/extra/rpl_tests/rpl_auto_increment.test
mysql-test/suite/rpl/r/rpl_auto_increment.result
sql/handler.cc
=== modified file 'mysql-test/extra/rpl_tests/rpl_auto_increment.test'
--- a/mysql-test/extra/rpl_tests/rpl_auto_increment.test 2009-01-14 08:27:32 +0000
+++ b/mysql-test/extra/rpl_tests/rpl_auto_increment.test 2009-08-27 08:19:38 +0000
@@ -163,5 +163,34 @@ show create table t1;
connection master;
drop table t1;
+#
+# BUG#45999 Row based replication fails when auto_increment field = 0.
+# Slave generates a new index for the auto_increment field which's value is 0.
+# In this case, the new index is 1. This will results in an error of
+# "duplicate key" when writing the second row.
+#
+connection master;
+SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
+disable_warnings;
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+enable_warnings;
+eval CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=$engine_type;
+eval CREATE TABLE t2 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=$engine_type2;
+INSERT INTO t1 VALUES(0);
+INSERT INTO t2 VALUES(0);
+sync_slave_with_master;
+echo ----on master----;
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+connection slave;
+echo ----on slave----;
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+connection master;
+DROP TABLE t1;
+DROP TABLE t2;
# End cleanup
sync_slave_with_master;
=== modified file 'mysql-test/suite/rpl/r/rpl_auto_increment.result'
--- a/mysql-test/suite/rpl/r/rpl_auto_increment.result 2009-01-14 08:27:32 +0000
+++ b/mysql-test/suite/rpl/r/rpl_auto_increment.result 2009-08-27 08:19:38 +0000
@@ -244,3 +244,26 @@ t1 CREATE TABLE `t1` (
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
drop table t1;
+SET SQL_MODE=NO_AUTO_VALUE_ON_ZERO;
+DROP TABLE IF EXISTS t1;
+DROP TABLE IF EXISTS t2;
+CREATE TABLE t1 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=innodb;
+CREATE TABLE t2 (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY) ENGINE=myisam;
+INSERT INTO t1 VALUES(0);
+INSERT INTO t2 VALUES(0);
+----on master----
+SELECT * FROM t1;
+id
+0
+SELECT * FROM t2;
+id
+0
+----on slave----
+SELECT * FROM t1;
+id
+0
+SELECT * FROM t2;
+id
+0
+DROP TABLE t1;
+DROP TABLE t2;
=== modified file 'sql/handler.cc'
--- a/sql/handler.cc 2009-07-29 08:54:20 +0000
+++ b/sql/handler.cc 2009-08-27 08:19:38 +0000
@@ -2296,7 +2296,8 @@ int handler::update_auto_increment()
if ((nr= table->next_number_field->val_int()) != 0 ||
(table->auto_increment_field_not_null &&
- thd->variables.sql_mode & MODE_NO_AUTO_VALUE_ON_ZERO))
+ thd->variables.sql_mode & MODE_NO_AUTO_VALUE_ON_ZERO) ||
+ (thd->slave_thread && thd->current_stmt_binlog_row_based))
{
/*
Update next_insert_id if we had already generated a value in this
@@ -4572,12 +4573,6 @@ static int binlog_log_row(TABLE* table,
int handler::ha_external_lock(THD *thd, int lock_type)
{
DBUG_ENTER("handler::ha_external_lock");
- /*
- Whether this is lock or unlock, this should be true, and is to verify that
- if get_auto_increment() was called (thus may have reserved intervals or
- taken a table lock), ha_release_auto_increment() was too.
- */
- DBUG_ASSERT(next_insert_id == 0);
/*
We cache the table flags if the locking succeeded. Otherwise, we
Attachment: [text/bzr-bundle] bzr/li-bing.song@sun.com-20090827081938-rn9rx4uebf9ihkvi.bundle
