List:Commits« Previous MessageNext Message »
From:hezx Date:March 14 2008 2:03am
Subject:bk commit into 5.0 tree (hezx:1.2583) BUG#33029
View as plain text  
Below is the list of changes that have just been committed into a local
5.0 repository of hezx.  When hezx does a push these changes
will be propagated to the main repository and, within 24 hours after the
push, to the public repository.
For information on how to access the public repository
see http://dev.mysql.com/doc/mysql/en/installing-source-tree.html

ChangeSet@stripped, 2008-03-14 10:03:01+08:00, hezx@stripped +4 -0
  BUG#33029 5.0 to 5.1 replication fails on dup key when inserting
  using a trig in SP
  
  For all 5.0 and up to 5.1.12 exclusive, when a stored routine or
  trigger caused an INSERT into an AUTO_INCREMENT column, the
  generated AUTO_INCREMENT value should not be written into the
  binary log, which means if a statement does not generate
  AUTO_INCREMENT value itself, there will be no Intvar event (SET
  INSERT_ID) associated with it even if one of the stored routine
  or trigger caused generation of such a value. And meanwhile, when
  executing a stored routine or trigger, it would ignore the
  INSERT_ID value even if there is a INSERT_ID value available set
  by a SET INSERT_ID statement.
  
  Starting from MySQL 5.1.12, the generated AUTO_INCREMENT value is
  written into the binary log, and the value will be used if
  available when executing the stored routine or trigger.
  
  Prior fix of this bug in MySQL 5.0 and prior MySQL 5.1.12
  (referenced as the buggy versions in the text below), when a
  statement that generates AUTO_INCREMENT value by the top
  statement was executed in the body of a SP, all statements in the
  SP after this statement would be treated as if they had generated
  AUTO_INCREMENT by the top statement.  When a statement that did
  not generate AUTO_INCREMENT value by the top statement but by a
  function/trigger called by it, an erroneous Intvar event would be
  associated with the statement, this erroneous INSERT_ID value
  wouldn't cause problem when replicating between masters and
  slaves of 5.0.x or prior 5.1.12, because the erroneous INSERT_ID
  value was not used when executing functions/triggers. But when
  replicating from buggy versions to 5.1.12 or newer, which will
  use the INSERT_ID value in functions/triggers, the erroneous
  value will be used, which would cause duplicate entry error and
  cause the slave to stop.
  
  The patch for 5.0 fixed it not to generate the erroneous Intvar
  event, another patch for 5.1 fixed it to ignore the SET INSERT_ID
  value when executing functions/triggers if it is replicating from
  a master of buggy versions.

  mysql-test/include/show_binlog_events.inc@stripped, 2008-03-14 10:02:59+08:00, hezx@stripped +9 -3
    add $binlog_start parameter to set the start position when show binlog events, if not set a default value will be used.
    
    mask out column 2(Pos), 4(Server_id), table_id, and file_id

  mysql-test/r/rpl_auto_increment_bug33029.result@stripped, 2008-03-14 10:02:59+08:00, hezx@stripped +167 -0
    Add test for bug33029, test if the master generate the erroneous event or not

  mysql-test/r/rpl_auto_increment_bug33029.result@stripped, 2008-03-14 10:02:59+08:00, hezx@stripped +0 -0

  mysql-test/t/rpl_auto_increment_bug33029.test@stripped, 2008-03-14 10:02:59+08:00, hezx@stripped +107 -0
    Add test for bug33029, test if the master generate the erroneous event or not

  mysql-test/t/rpl_auto_increment_bug33029.test@stripped, 2008-03-14 10:02:59+08:00, hezx@stripped +0 -0

  sql/sql_class.cc@stripped, 2008-03-14 10:02:59+08:00, hezx@stripped +7 -0
    Reset insert_id_used after each query in SP

diff -Nrup a/mysql-test/include/show_binlog_events.inc b/mysql-test/include/show_binlog_events.inc
--- a/mysql-test/include/show_binlog_events.inc	2007-06-08 17:30:02 +08:00
+++ b/mysql-test/include/show_binlog_events.inc	2008-03-14 10:02:59 +08:00
@@ -1,4 +1,10 @@
---let $binlog_start=98
---replace_column 5 #
---replace_regex /\/\* xid=.* \*\//\/* XID *\//
+# $binlog_start can be set by caller or take a default value
+
+if (!$binlog_start)
+{
+  let $binlog_start=98;
+}
+--replace_result $binlog_start <binlog_start>
+--replace_column 2 # 4 # 5 #
+--replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/ /file_id=[0-9]+/file_id=#/
 --eval show binlog events from $binlog_start
diff -Nrup a/mysql-test/r/rpl_auto_increment_bug33029.result b/mysql-test/r/rpl_auto_increment_bug33029.result
--- /dev/null	Wed Dec 31 16:00:00 196900
+++ b/mysql-test/r/rpl_auto_increment_bug33029.result	2008-03-14 10:02:59 +08:00
@@ -0,0 +1,167 @@
+stop slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+reset master;
+reset slave;
+drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
+start slave;
+DROP TABLE IF EXISTS t1, t2;
+DROP PROCEDURE IF EXISTS p1;
+DROP PROCEDURE IF EXISTS p2;
+DROP TRIGGER IF EXISTS tr1;
+DROP FUNCTION IF EXISTS f1;
+CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY);
+CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY);
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE ins_count INT DEFAULT 10; 
+WHILE ins_count > 0 DO
+INSERT INTO t1 VALUES (NULL);
+SET ins_count = ins_count - 1;
+END WHILE;
+DELETE FROM t1 WHERE id = 1;
+DELETE FROM t1 WHERE id = 2;
+DELETE FROM t2 WHERE id = 1;
+DELETE FROM t2 WHERE id = 2;
+END//
+CREATE PROCEDURE p2()
+BEGIN
+INSERT INTO t1 VALUES (NULL);
+DELETE FROM t1 WHERE id = f1(3);
+DELETE FROM t1 WHERE id = f1(4);
+DELETE FROM t2 WHERE id = 3;
+DELETE FROM t2 WHERE id = 4;
+END//
+CREATE TRIGGER tr1 BEFORE DELETE
+ON t1 FOR EACH ROW 
+BEGIN
+INSERT INTO t2  VALUES (NULL);
+END//
+CREATE FUNCTION f1 (i int) RETURNS int
+BEGIN
+INSERT INTO t2 VALUES (NULL);
+RETURN i;
+END//
+CALL p1();
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Intvar	#	#	INSERT_ID=1
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (NULL)
+master-bin.000001	#	Intvar	#	#	INSERT_ID=2
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (NULL)
+master-bin.000001	#	Intvar	#	#	INSERT_ID=3
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (NULL)
+master-bin.000001	#	Intvar	#	#	INSERT_ID=4
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (NULL)
+master-bin.000001	#	Intvar	#	#	INSERT_ID=5
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (NULL)
+master-bin.000001	#	Intvar	#	#	INSERT_ID=6
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (NULL)
+master-bin.000001	#	Intvar	#	#	INSERT_ID=7
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (NULL)
+master-bin.000001	#	Intvar	#	#	INSERT_ID=8
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (NULL)
+master-bin.000001	#	Intvar	#	#	INSERT_ID=9
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (NULL)
+master-bin.000001	#	Intvar	#	#	INSERT_ID=10
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (NULL)
+master-bin.000001	#	Query	#	#	use `test`; DELETE FROM t1 WHERE id = 1
+master-bin.000001	#	Query	#	#	use `test`; DELETE FROM t1 WHERE id = 2
+master-bin.000001	#	Query	#	#	use `test`; DELETE FROM t2 WHERE id = 1
+master-bin.000001	#	Query	#	#	use `test`; DELETE FROM t2 WHERE id = 2
+# Result on master
+SELECT * FROM t1;
+id
+3
+4
+5
+6
+7
+8
+9
+10
+SELECT * FROM t2;
+id
+# Result on slave
+SELECT * FROM t1;
+id
+3
+4
+5
+6
+7
+8
+9
+10
+SELECT * FROM t2;
+id
+DROP TRIGGER tr1;
+CALL p2();
+show binlog events from <binlog_start>;
+Log_name	Pos	Event_type	Server_id	End_log_pos	Info
+master-bin.000001	#	Intvar	#	#	INSERT_ID=11
+master-bin.000001	#	Query	#	#	use `test`; INSERT INTO t1 VALUES (NULL)
+master-bin.000001	#	Query	#	#	use `test`; DELETE FROM t1 WHERE id = f1(3)
+master-bin.000001	#	Query	#	#	use `test`; DELETE FROM t1 WHERE id = f1(4)
+master-bin.000001	#	Query	#	#	use `test`; DELETE FROM t2 WHERE id = 3
+master-bin.000001	#	Query	#	#	use `test`; DELETE FROM t2 WHERE id = 4
+# Result on master
+SELECT * FROM t1;
+id
+5
+6
+7
+8
+9
+10
+11
+SELECT * FROM t2;
+id
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
+# Result on slave
+SELECT * FROM t1;
+id
+5
+6
+7
+8
+9
+10
+11
+SELECT * FROM t2;
+id
+5
+6
+7
+8
+9
+10
+11
+12
+13
+14
+15
+16
+17
+18
+19
+DROP TABLE IF EXISTS t1, t2;
+DROP PROCEDURE IF EXISTS p1;
+DROP PROCEDURE IF EXISTS p2;
+DROP FUNCTION IF EXISTS f1;
+DROP TRIGGER IF EXISTS tr1;
+Warnings:
+Note	1360	Trigger does not exist
diff -Nrup a/mysql-test/t/rpl_auto_increment_bug33029.test b/mysql-test/t/rpl_auto_increment_bug33029.test
--- /dev/null	Wed Dec 31 16:00:00 196900
+++ b/mysql-test/t/rpl_auto_increment_bug33029.test	2008-03-14 10:02:59 +08:00
@@ -0,0 +1,107 @@
+# BUG#33029 5.0 to 5.1 replication fails on dup key when inserting
+# using a trig in SP
+
+# For all 5.0 up to 5.0.58 exclusive, and 5.1 up to 5.1.12 exclusive,
+# if one statement in a SP generated AUTO_INCREMENT value by the top
+# statement, all statements after it would be considered generated
+# AUTO_INCREMENT value by the top statement, and a erroneous INSERT_ID
+# value might be associated with these statement, which could cause
+# duplicate entry error and stop the slave.
+
+source include/master-slave.inc;
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2;
+DROP PROCEDURE IF EXISTS p1;
+DROP PROCEDURE IF EXISTS p2;
+DROP TRIGGER IF EXISTS tr1;
+DROP FUNCTION IF EXISTS f1;
+--enable_warnings
+
+CREATE TABLE t1 (id INT AUTO_INCREMENT PRIMARY KEY);
+CREATE TABLE t2 (id INT AUTO_INCREMENT PRIMARY KEY);
+
+delimiter //;
+
+CREATE PROCEDURE p1()
+BEGIN
+   DECLARE ins_count INT DEFAULT 10; 
+
+   WHILE ins_count > 0 DO
+       INSERT INTO t1 VALUES (NULL);
+       SET ins_count = ins_count - 1;
+   END WHILE;
+
+   DELETE FROM t1 WHERE id = 1;
+   DELETE FROM t1 WHERE id = 2;
+   DELETE FROM t2 WHERE id = 1;
+   DELETE FROM t2 WHERE id = 2;
+END//
+
+CREATE PROCEDURE p2()
+BEGIN
+   INSERT INTO t1 VALUES (NULL);
+   DELETE FROM t1 WHERE id = f1(3);
+   DELETE FROM t1 WHERE id = f1(4);
+   DELETE FROM t2 WHERE id = 3;
+   DELETE FROM t2 WHERE id = 4;
+END//
+
+CREATE TRIGGER tr1 BEFORE DELETE
+    ON t1 FOR EACH ROW 
+    BEGIN
+        INSERT INTO t2  VALUES (NULL);
+    END//
+
+CREATE FUNCTION f1 (i int) RETURNS int
+    BEGIN
+        INSERT INTO t2 VALUES (NULL);
+        RETURN i;
+    END//
+
+delimiter ;//
+
+# the $binlog_start will be used by the show_binlog_events.inc, so
+# that we can skip binlog events we don't care
+let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
+CALL p1();
+source include/show_binlog_events.inc;
+
+echo # Result on master;
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+sync_slave_with_master;
+
+echo # Result on slave;
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+connection master;
+
+DROP TRIGGER tr1;
+
+# the $binlog_start will be used by the show_binlog_events.inc, so
+# that we can skip binlog events we don't care
+let $binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
+CALL p2();
+source include/show_binlog_events.inc;
+
+echo # Result on master;
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+sync_slave_with_master;
+
+echo # Result on slave;
+SELECT * FROM t1;
+SELECT * FROM t2;
+
+# clean up
+connection master;
+DROP TABLE IF EXISTS t1, t2;
+DROP PROCEDURE IF EXISTS p1;
+DROP PROCEDURE IF EXISTS p2;
+DROP FUNCTION IF EXISTS f1;
+DROP TRIGGER IF EXISTS tr1;
+sync_slave_with_master;
diff -Nrup a/sql/sql_class.cc b/sql/sql_class.cc
--- a/sql/sql_class.cc	2007-12-15 19:04:58 +08:00
+++ b/sql/sql_class.cc	2008-03-14 10:02:59 +08:00
@@ -622,6 +622,13 @@ void THD::cleanup_after_query()
   {
     clear_next_insert_id= 0;
     next_insert_id= 0;
+
+    /*
+      BUG#33029, if one statement in a SP set this member to 1, all
+      statment after this statement in the SP would be considered used
+      INSERT_ID value, reset this member after each query to fix this.
+    */
+    insert_id_used= 0;
   }
   /*
     Reset rand_used so that detection of calls to rand() will save random 
Thread
bk commit into 5.0 tree (hezx:1.2583) BUG#33029hezx14 Mar