bk commit into 5.0 tree (hezx:1.2583) BUG#33029

[hezx]

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-11 13:09:13+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
  
  Prior to MySQL 5.1.12 (including 5.0.x), 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
  valule 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.
  
  After MySQL 5.1.12, the generated AUTO_INCREMENT value is written
  into the binary log, and the value will be used if availabe 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 one
  statement that generates AUTO_INCREMENT value by itself 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 itself.
  When a statement that did not generate AUTO_INCREMENT value by
  itself but by a function/trigger called by it, an erroenous Intvar
  event would be associated with the statement, this erroenous
  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 erronous value
  will be used.
  
  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-11 13:09:09+08:00,
hezx@stripped +10 -4
    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 and 4, table_id, file_id values

  mysql-test/r/rpl_auto_increment_bug33029.result@stripped, 2008-03-11 13:09:09+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-11 13:09:09+08:00,
hezx@stripped +0 -0

  mysql-test/t/rpl_auto_increment_bug33029.test@stripped, 2008-03-11 13:09:10+08:00,
hezx@stripped +100 -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-11 13:09:10+08:00,
hezx@stripped +0 -0

  sql/sql_class.cc@stripped, 2008-03-11 13:09:09+08:00, hezx@stripped +1 -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-11 13:09:09 +08:00
@@ -1,4 +1,10 @@
---let $binlog_start=98
---replace_column 5 #
---replace_regex /\/\* xid=.* \*\//\/* XID *\//
---eval show binlog events from $binlog_start
+# $_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-11 13:09:09 +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-11 13:09:10 +08:00
@@ -0,0 +1,100 @@
+# BUG#33029 5.0 to 5.1 replication fails on dup key when inserting
+# using a trig in SP
+
+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-11 13:09:09 +08:00
@@ -622,6 +622,7 @@ void THD::cleanup_after_query()
   {
     clear_next_insert_id= 0;
     next_insert_id= 0;
+    insert_id_used= 0;
   }
   /*
     Reset rand_used so that detection of calls to rand() will save random