Hi Jason,
I found a few things that need to be fixed (most are trivial), however there
is a bigger problem. The test does not pass on my Windows machine using the
latest mysql-5.0-rpl clone. Please check your patch and resubmit it so I can
ensure it's ok before I approve it.
Chuck
rpl_auto_increment_bug33029 [ fail ]
---
d:/source/c++/mysql-5.0_BUG_33029/mysql-test/r/rpl_auto_increment_bug33029.r
esult 2008-03-13 18:14:25.725880300 +0300
+++
d:\source\c++\mysql-5.0_BUG_33029\mysql-test\r\rpl_auto_increment_bug33029.r
eject 2008-03-13 18:19:11.895880300 +0300
@@ -44,6 +44,40 @@
CALL p1();
show binlog events from <binlog_start>;
Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # use `test`; DROP
TABLE I
F EXISTS t1, t2
+master-bin.000001 # Query # # use `test`; DROP
TRIGGER
IF EXISTS tr1
+master-bin.000001 # Query # # use `test`; CREATE
TABLE
t1 (id INT AUTO_INCREMENT PRIMARY KEY)
+master-bin.000001 # Query # # use `test`; CREATE
TABLE
t2 (id INT AUTO_INCREMENT PRIMARY KEY)
+master-bin.000001 # Query # # use `test`; CREATE
DEFIN
ER=`root`@`localhost` 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
+master-bin.000001 # Query # # use `test`; CREATE
DEFIN
ER=`root`@`localhost` 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
+master-bin.000001 # Query # # use `test`; CREATE
DEFIN
ER=`root`@`localhost` TRIGGER tr1 BEFORE DELETE
+ON t1 FOR EACH ROW
+BEGIN
+INSERT INTO t2 VALUES (NULL);
+END
+master-bin.000001 # Query # # use `test`; CREATE
DEFIN
ER=`root`@`localhost` FUNCTION f1 (i int) RETURNS int
+BEGIN
+INSERT INTO t2 VALUES (NULL);
+RETURN i;
+END
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
@@ -98,6 +132,65 @@
CALL p2();
show binlog events from <binlog_start>;
Log_name Pos Event_type Server_id End_log_pos Info
+master-bin.000001 # Query # # use `test`; DROP
TABLE I
F EXISTS t1, t2
+master-bin.000001 # Query # # use `test`; DROP
TRIGGER
IF EXISTS tr1
+master-bin.000001 # Query # # use `test`; CREATE
TABLE
t1 (id INT AUTO_INCREMENT PRIMARY KEY)
+master-bin.000001 # Query # # use `test`; CREATE
TABLE
t2 (id INT AUTO_INCREMENT PRIMARY KEY)
+master-bin.000001 # Query # # use `test`; CREATE
DEFIN
ER=`root`@`localhost` 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
+master-bin.000001 # Query # # use `test`; CREATE
DEFIN
ER=`root`@`localhost` 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
+master-bin.000001 # Query # # use `test`; CREATE
DEFIN
ER=`root`@`localhost` TRIGGER tr1 BEFORE DELETE
+ON t1 FOR EACH ROW
+BEGIN
+INSERT INTO t2 VALUES (NULL);
+END
+master-bin.000001 # Query # # use `test`; CREATE
DEFIN
ER=`root`@`localhost` FUNCTION f1 (i int) RETURNS int
+BEGIN
+INSERT INTO t2 VALUES (NULL);
+RETURN i;
+END
+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
+master-bin.000001 # Query # # use `test`; DROP
TRIGGER
tr1
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)
mysqltest: Result content mismatch
> -----Original Message-----
> From: hezx@stripped [mailto:hezx@stripped]
> Sent: Wednesday, March 12, 2008 4:47 AM
> To: commits@stripped
> Subject: bk commit into 5.0 tree (hezx:1.2583) BUG#33029
>
> 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-12 16:46:34+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 valule itself, there will be no Intvar event (SET
Spelling error. 'value'
> 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
> availabe when executing the stored routine or trigger.
Spelling error. 'available'
>
> 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 erroenous Intvar event would be
> associated with the statement, this erroenous INSERT_ID value
Spelling error in above 2 lines. 'erroneous'
> 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, 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.
>
A minor request for future patches (don't worry about this one). Please try
to make your comments (more) concise. :)
> mysql-test/include/show_binlog_events.inc@stripped, 2008-03-12
> 16:46:32+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 and 4, table_id, file_id values
>
Is column 2 and 4 tableid and fileid respectfully?
> mysql-test/r/rpl_auto_increment_bug33029.result@stripped,
> 2008-03-12 16:46:32+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-12 16:46:32+08:00, hezx@stripped +0 -0
>
> mysql-test/t/rpl_auto_increment_bug33029.test@stripped,
> 2008-03-12 16:46:32+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-12 16:46:32+08:00, hezx@stripped +0 -0
>
> sql/sql_class.cc@stripped, 2008-03-12 16:46:32+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-12 16:46:32 +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-12 16:46:32 +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-12 16:46:32 +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//
> +
Can you format these better to make them easier to read? Something like:
CREATE FUNCTION..
BEGIN
INSERT...
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-12 16:46:32 +08:00
> @@ -622,6 +622,7 @@ void THD::cleanup_after_query()
> {
> clear_next_insert_id= 0;
> next_insert_id= 0;
> + insert_id_used= 0;
Please add a brief comment to this line so we know why it was added.
> }
> /*
> Reset rand_used so that detection of calls to rand()
> will save random
>
> --
> MySQL Code Commits Mailing List
> For list archives: http://lists.mysql.com/commits
> To unsubscribe:
> http://lists.mysql.com/commits?unsub=1
>
