Hi Serge
Thank you! See below, patch approved after fix this.
On 2008-04-18 Fri 23:50 +0400，Serge Kozlov wrote：
> Below is the list of changes that have just been committed into a local
> 5.1 repository of skozlov. When skozlov 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-04-18 23:50:17+04:00, skozlov@stripped +4 -0
> WL#3734, Slave group execution
>
> mysql-test/suite/rpl/r/rpl_slave_grp_exec.result@stripped, 2008-04-18 23:50:14+04:00,
> skozlov@stripped +125 -0
> Result file
>
> mysql-test/suite/rpl/r/rpl_slave_grp_exec.result@stripped, 2008-04-18 23:50:14+04:00,
> skozlov@stripped +0 -0
>
> mysql-test/suite/rpl/t/rpl_slave_grp_exec-master.opt@stripped, 2008-04-18
> 23:50:14+04:00, skozlov@stripped +1 -0
> Option file for master
>
> mysql-test/suite/rpl/t/rpl_slave_grp_exec-master.opt@stripped, 2008-04-18
> 23:50:14+04:00, skozlov@stripped +0 -0
>
> mysql-test/suite/rpl/t/rpl_slave_grp_exec-slave.opt@stripped, 2008-04-18 23:50:14+04:00,
> skozlov@stripped +1 -0
> Option file for slave
>
> mysql-test/suite/rpl/t/rpl_slave_grp_exec-slave.opt@stripped, 2008-04-18 23:50:14+04:00,
> skozlov@stripped +0 -0
>
> mysql-test/suite/rpl/t/rpl_slave_grp_exec.test@stripped, 2008-04-18 23:50:14+04:00,
> skozlov@stripped +184 -0
> Test case
>
> mysql-test/suite/rpl/t/rpl_slave_grp_exec.test@stripped, 2008-04-18 23:50:14+04:00,
> skozlov@stripped +0 -0
>
> diff -Nrup a/mysql-test/suite/rpl/r/rpl_slave_grp_exec.result
> b/mysql-test/suite/rpl/r/rpl_slave_grp_exec.result
> --- /dev/null Wed Dec 31 16:00:00 196900
> +++ b/mysql-test/suite/rpl/r/rpl_slave_grp_exec.result 2008-04-18 23:50:14 +04:00
> @@ -0,0 +1,125 @@
> +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;
> +
> +*** Preparing data ***
> +CREATE TABLE t1 (a INT NOT NULL, b VARCHAR(10)) ENGINE=MyISAM;
> +CREATE TABLE t2 LIKE t1;
> +CREATE TABLE t3 LIKE t1;
> +CREATE TRIGGER tr1 BEFORE UPDATE ON t1
> +FOR EACH ROW BEGIN
> +UPDATE t2 SET b='YY' WHERE a=NEW.a;
> +END|
> +CREATE TRIGGER tr2 AFTER UPDATE ON t1
> +FOR EACH ROW BEGIN
> +UPDATE t3 SET b='ZZ' WHERE a=NEW.a;
> +END|
> +
> +*** Test non-transactional group w/o PK ***
> +INSERT INTO t3 VALUES(1, 'AA');
> +INSERT INTO t2 VALUES(1, 'AA');
> +INSERT INTO t1 VALUES(1, 'AA');
> +RENAME TABLE t3 TO t3_bak;
> +UPDATE t1 SET b = 'XX' WHERE a = 1;
> +SELECT * FROM t1 ORDER BY a;
> +a b
> +1 XX
> +SELECT * FROM t2 ORDER BY a;
> +a b
> +1 YY
> +SELECT * FROM t3 ORDER BY a;
> +a b
> +1 ZZ
> +SHOW TABLES LIKE 't%';
> +Tables_in_test (t%)
> +t1
> +t2
> +t3_bak
> +SELECT * FROM t1 ORDER BY a;
> +a b
> +1 AA_for_row_or_XX_for_stmt_mixed
> +SELECT * FROM t2 ORDER BY a;
> +a b
> +1 AA_for_row_or_YY_for_stmt_mixed
> +STOP SLAVE;
> +RENAME TABLE t3_bak TO t3;
> +START SLAVE;
> +TRUNCATE t1;
> +TRUNCATE t2;
> +TRUNCATE t3;
> +
> +*** Test non-transactional group w/ PK ***
> +ALTER TABLE t1 ADD PRIMARY KEY (a);
> +ALTER TABLE t2 ADD PRIMARY KEY (a);
> +ALTER TABLE t3 ADD PRIMARY KEY (a);
> +RENAME TABLE t3 TO t3_bak;
> +INSERT INTO t3 VALUES(2, 'B');
> +INSERT INTO t2 VALUES(2, 'B');
> +INSERT INTO t1 VALUES(2, 'B');
> +UPDATE t1 SET b = 'X' WHERE a = 2;
> +SELECT * FROM t1 ORDER BY a;
> +a b
> +2 X
> +SELECT * FROM t2 ORDER BY a;
> +a b
> +2 YY
> +SELECT * FROM t3 ORDER BY a;
> +a b
> +2 ZZ
> +SHOW TABLES LIKE 't%';
> +Tables_in_test (t%)
> +t1
> +t2
> +t3_bak
> +SELECT * FROM t1 ORDER BY a;
> +a b
> +SELECT * FROM t2 ORDER BY a;
> +a b
> +STOP SLAVE;
> +RENAME TABLE t3_bak TO t3;
> +START SLAVE;
> +TRUNCATE t1;
> +TRUNCATE t2;
> +TRUNCATE t3;
> +
> +*** Test transactional group w/ PK ***
> +ALTER TABLE t1 ENGINE=InnoDB;
> +ALTER TABLE t2 ENGINE=InnoDB;
> +ALTER TABLE t3 ENGINE=InnoDB;
> +RENAME TABLE t3 TO t3_bak;
> +BEGIN;
> +INSERT INTO t3 VALUES (3, 'C'), (4, 'D');
> +INSERT INTO t2 VALUES (3, 'C'), (4, 'D');
> +INSERT INTO t1 VALUES (3, 'C'), (4, 'D');
> +UPDATE t1 SET b = 'X' WHERE a = 3;
> +UPDATE t1 SET b = 'X' WHERE a = 4;
> +COMMIT;
> +SELECT * FROM t1 ORDER BY a;
> +a b
> +3 X
> +4 X
> +SELECT * FROM t2 ORDER BY a;
> +a b
> +3 YY
> +4 YY
> +SELECT * FROM t3 ORDER BY a;
> +a b
> +3 ZZ
> +4 ZZ
> +SHOW TABLES LIKE 't%';
> +Tables_in_test (t%)
> +t1
> +t2
> +t3_bak
> +SELECT * FROM t1 ORDER BY a;
> +a b
> +SELECT * FROM t2 ORDER BY a;
> +a b
> +STOP SLAVE;
> +RENAME TABLE t3_bak TO t3;
> +START SLAVE;
> +*** Clean up ***
> +DROP TABLE t1,t2,t3;
> diff -Nrup a/mysql-test/suite/rpl/t/rpl_slave_grp_exec-master.opt
> b/mysql-test/suite/rpl/t/rpl_slave_grp_exec-master.opt
> --- /dev/null Wed Dec 31 16:00:00 196900
> +++ b/mysql-test/suite/rpl/t/rpl_slave_grp_exec-master.opt 2008-04-18 23:50:14
> +04:00
> @@ -0,0 +1 @@
> +--innodb
> diff -Nrup a/mysql-test/suite/rpl/t/rpl_slave_grp_exec-slave.opt
> b/mysql-test/suite/rpl/t/rpl_slave_grp_exec-slave.opt
> --- /dev/null Wed Dec 31 16:00:00 196900
> +++ b/mysql-test/suite/rpl/t/rpl_slave_grp_exec-slave.opt 2008-04-18 23:50:14 +04:00
> @@ -0,0 +1 @@
> +--innodb
> diff -Nrup a/mysql-test/suite/rpl/t/rpl_slave_grp_exec.test
> b/mysql-test/suite/rpl/t/rpl_slave_grp_exec.test
> --- /dev/null Wed Dec 31 16:00:00 196900
> +++ b/mysql-test/suite/rpl/t/rpl_slave_grp_exec.test 2008-04-18 23:50:14 +04:00
> @@ -0,0 +1,184 @@
> +#############################################################
> +# Author: Serge Kozlov <skozlov@stripped>
> +# Date: 03/21/2008
> +# Purpose:
> +# WL#3734 Test: slave group execution
> +# Testing slave group execution: stop in middle of the group
> +# (of events) should be immpossible on slave.
> +# Group of events means set of statements between BEGIN/COMMIT
> +# for transactional engines or a statement for
> +# non-transactional engines that fires triggers and
> +# affects to another tables.
> +# Implementation of slave stop in middle of the group:
> +# Delete the table on slave before start of the group
> +# on master where this table is participant of the group.
> +# So sql thread will failed inside of the group.
> +# Expected results:
> +# 1. For non-transactional engines w/o PK slave should trying
> +# to complete executing of the group.
> +# 2. For non-transactional engines slave should stop on
> +# start position of the group.
> +#############################################################
> +--source include/have_innodb.inc
> +--source include/master-slave.inc
> +--echo
> +
> +# Create tables and data
> +--echo *** Preparing data ***
> +--connection master
> +CREATE TABLE t1 (a INT NOT NULL, b VARCHAR(10)) ENGINE=MyISAM;
> +CREATE TABLE t2 LIKE t1;
> +CREATE TABLE t3 LIKE t1;
> +
> +DELIMITER |;
> +CREATE TRIGGER tr1 BEFORE UPDATE ON t1
> + FOR EACH ROW BEGIN
> + UPDATE t2 SET b='YY' WHERE a=NEW.a;
> + END|
> +CREATE TRIGGER tr2 AFTER UPDATE ON t1
> + FOR EACH ROW BEGIN
> + UPDATE t3 SET b='ZZ' WHERE a=NEW.a;
> + END|
> +DELIMITER ;|
> +--echo
> +
> +# Test non-transactional group with MyISAM tables w/o PK.
> +# Data for t1,t2 should be replicated for SBR even t3
> +# doesn't exist on slave
> +--echo *** Test non-transactional group w/o PK ***
> +
> +--connection master
> +INSERT INTO t3 VALUES(1, 'AA');
> +INSERT INTO t2 VALUES(1, 'AA');
> +INSERT INTO t1 VALUES(1, 'AA');
> +--sync_slave_with_master
> +
> +RENAME TABLE t3 TO t3_bak;
> +
> +--connection master
> +UPDATE t1 SET b = 'XX' WHERE a = 1;
> +SELECT * FROM t1 ORDER BY a;
> +SELECT * FROM t2 ORDER BY a;
> +SELECT * FROM t3 ORDER BY a;
> +
> +--connection slave
> +--source include/wait_for_slave_sql_to_stop.inc
> +SHOW TABLES LIKE 't%';
> +if (`SELECT @@BINLOG_FORMAT = 'ROW'`) {
> +--replace_regex /AA/AA_for_row_or_XX_for_stmt_mixed/
> + SELECT * FROM t1 ORDER BY a;
> +--replace_regex /AA/AA_for_row_or_YY_for_stmt_mixed/
> + SELECT * FROM t2 ORDER BY a;
> +}
> +if (!`SELECT @@BINLOG_FORMAT = 'ROW'`) {
> +--replace_regex /XX/AA_for_row_or_XX_for_stmt_mixed/
> + SELECT * FROM t1 ORDER BY a;
> +--replace_regex /YY/AA_for_row_or_YY_for_stmt_mixed/
> + SELECT * FROM t2 ORDER BY a;
> +}
> +
> +STOP SLAVE;
> +--source include/wait_for_slave_to_stop.inc
> +RENAME TABLE t3_bak TO t3;
> +START SLAVE;
> +--source include/wait_for_slave_to_start.inc
> +
> +--connection master
> +TRUNCATE t1;
> +TRUNCATE t2;
> +TRUNCATE t3;
> +--sync_slave_with_master
> +--echo
> +
> +
> +# Test non-transactional group with MyISAM tables and PK.
> +# No data replicated because update based on PK
> +--echo *** Test non-transactional group w/ PK ***
> +
> +--connection master
> +ALTER TABLE t1 ADD PRIMARY KEY (a);
> +ALTER TABLE t2 ADD PRIMARY KEY (a);
> +ALTER TABLE t3 ADD PRIMARY KEY (a);
> +--sync_slave_with_master
> +RENAME TABLE t3 TO t3_bak;
> +
> +--connection master
> +INSERT INTO t3 VALUES(2, 'B');
> +INSERT INTO t2 VALUES(2, 'B');
> +INSERT INTO t1 VALUES(2, 'B');
> +UPDATE t1 SET b = 'X' WHERE a = 2;
> +
> +--connection slave
> +--source include/wait_for_slave_sql_to_stop.inc
> +
> +--connection master
> +SELECT * FROM t1 ORDER BY a;
> +SELECT * FROM t2 ORDER BY a;
> +SELECT * FROM t3 ORDER BY a;
> +
> +--connection slave
> +SHOW TABLES LIKE 't%';
> +SELECT * FROM t1 ORDER BY a;
> +SELECT * FROM t2 ORDER BY a;
> +
> +STOP SLAVE;
> +--source include/wait_for_slave_to_stop.inc
> +RENAME TABLE t3_bak TO t3;
> +START SLAVE;
> +--source include/wait_for_slave_to_start.inc
> +
> +--connection master
> +TRUNCATE t1;
> +TRUNCATE t2;
> +TRUNCATE t3;
> +--sync_slave_with_master
> +--echo
> +
> +
> +# Test transactional group with InnoDB tables with PK
> +# No data replicated if errors happens inside transaction
> +--echo *** Test transactional group w/ PK ***
> +
> +--connection master
> +ALTER TABLE t1 ENGINE=InnoDB;
> +ALTER TABLE t2 ENGINE=InnoDB;
> +ALTER TABLE t3 ENGINE=InnoDB;
> +
> +--connection slave
> +RENAME TABLE t3 TO t3_bak;
> +
> +--connection master
> +BEGIN;
> +INSERT INTO t3 VALUES (3, 'C'), (4, 'D');
> +INSERT INTO t2 VALUES (3, 'C'), (4, 'D');
> +INSERT INTO t1 VALUES (3, 'C'), (4, 'D');
> +UPDATE t1 SET b = 'X' WHERE a = 3;
> +UPDATE t1 SET b = 'X' WHERE a = 4;
> +COMMIT;
I think it's better to not fail the transaction on the first statement,
and since for transactional engine, we do not need triggers to create
transaction group, so I think we can remove the UPDATE statements for
simplicity. So I would suggest the following:
--connection master
BEGIN;
INSERT INTO t1 VALUES (3, 'C'), (4, 'D');
INSERT INTO t2 VALUES (3, 'C'), (4, 'D');
INSERT INTO t3 VALUES (3, 'C'), (4, 'D');
COMMIT;
> +
> +--connection slave
> +--source include/wait_for_slave_sql_to_stop.inc
> +
> +--connection master
> +SELECT * FROM t1 ORDER BY a;
> +SELECT * FROM t2 ORDER BY a;
> +SELECT * FROM t3 ORDER BY a;
> +
> +--connection slave
> +SHOW TABLES LIKE 't%';
> +SELECT * FROM t1 ORDER BY a;
> +SELECT * FROM t2 ORDER BY a;
> +
> +STOP SLAVE;
> +--source include/wait_for_slave_to_stop.inc
> +RENAME TABLE t3_bak TO t3;
> +START SLAVE;
> +--source include/wait_for_slave_to_start.inc
> +
> +# Clean up
> +--echo *** Clean up ***
> +--connection master
> +DROP TABLE t1,t2,t3;
> +--sync_slave_with_master
> +
> +# End of 5.1 test
