Hi Mats,
My preliminary tests have found a problem. After applying the patch
against 5.1-bugteam tree (most up to date), the problem was still there.
The changes seem to reach the slave effectively after the commit.
However, rolled back changes also seem to get there!
I have taken the part of the test case that you added in the patch,
removed the selects and added a diff tables for table t1 in master and
slave.
Additionally, I have added a sanity check for regular insert on table
*without* trigger and rollback (in innodb table). This is the first part
on the test below.
The second part, just inserts into table *with* trigger, rolls back, and
syncs the slave with the master. In the end they differ. Checkout the
test case below.
Attached you can find a result file from another test case with a
detailed execution flow.
Please run the test below and let me know more about this (I might be
missing something).
==================================== TEST
source include/master-slave.inc;
source include/have_innodb.inc;
connection master;
create table t1 ( f int ) engine = innodb;
create table t2 ( f int ) engine = innodb;
create table log ( r int ) engine = myisam;
create trigger tr after insert on t1 for each row insert into log values
( new.f );
set autocommit = 0;
insert into t2 values ( 2 );
rollback;
sync_slave_with_master;
let $diff_table_1=master:test.t1;
let $diff_table_2=slave:test.t1;
--source include/diff_tables.inc
connection master;
insert into t1 values ( 1 );
rollback;
sync_slave_with_master;
let $diff_table_1=master:test.t1;
let $diff_table_2=slave:test.t1;
--source include/diff_tables.inc
connection master;
drop table t1, t2, log;
sync_slave_with_master;
Mats Kindahl wrote:
> #At file:///home/bzr/bugs/b40116-5.1-5.1.29-rc/
>
> 2776 Mats Kindahl 2008-12-03
> Bug #40116: Uncommited changes are replicated and stay on slave
> after rollback on master
>
> When starting a transaction with a statement containing changes
> to both transactional tables and non-transactional tables, the
> statement is considered as non-transactional and is therefore
> written directly to the binary log. This behaviour was present
> in 5.0, and has propagated to 5.1.
>
> If a trigger containing a change of a non-transactional table is
> added to a transactional table, any changes to the transactional
> table is "tainted" as non-transactional.
>
> This patch solves the problem by removing the existing "hack" that
> allows non-transactional statements appearing first in a transaction
> to be written directly to the binary log. Instead, anything inside
> a transaction is treaded as part of the transaction and not written
> to the binary log until the transaction is committed.
> modified:
> mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result
> mysql-test/suite/rpl/r/rpl_row_create_table.result
> mysql-test/suite/rpl/r/rpl_slave_skip.result
> mysql-test/suite/rpl/r/rpl_trigger.result
> mysql-test/suite/rpl/t/rpl_row_create_table.test
> mysql-test/suite/rpl/t/rpl_slave_skip.test
> mysql-test/suite/rpl/t/rpl_trigger.test
> sql/log.cc
>
> per-file messages:
> mysql-test/suite/rpl/t/rpl_row_create_table.test
> Removing positions from SHOW BINLOG EVENTS and using
> reset_master_and_slave to start on a fresh binary log each time.
> mysql-test/suite/rpl/t/rpl_slave_skip.test
> Adding explicit commit in AUTOCOMMIT=0 to make test work correctly.
> mysql-test/suite/rpl/t/rpl_trigger.test
> Adding test case for BUG#40116.
> sql/log.cc
> Changing commit logic in binlog_commit() to only commit when
> committing a real transaction or committing a punch transaction.
> === modified file 'mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result'
> --- a/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result 2008-07-29
> 15:36:13 +0000
> +++ b/mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result 2008-12-03
> 19:55:49 +0000
> @@ -133,10 +133,6 @@ master-bin.000001 # Query # # use `test`
> master-bin.000001 # Table_map # # table_id: # (test.t1)
> master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
> master-bin.000001 # Xid # # COMMIT /* XID */
> -master-bin.000001 # Query # # use `test`; BEGIN
> -master-bin.000001 # Table_map # # table_id: # (test.t2)
> -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
> -master-bin.000001 # Query # # use `test`; COMMIT
> insert into t1 values(11);
> commit;
> show binlog events from <binlog_start>;
> @@ -148,8 +144,6 @@ master-bin.000001 # Xid # # COMMIT /* XI
> master-bin.000001 # Query # # use `test`; BEGIN
> master-bin.000001 # Table_map # # table_id: # (test.t2)
> master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
> -master-bin.000001 # Query # # use `test`; COMMIT
> -master-bin.000001 # Query # # use `test`; BEGIN
> master-bin.000001 # Table_map # # table_id: # (test.t1)
> master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
> master-bin.000001 # Xid # # COMMIT /* XID */
> @@ -278,10 +272,6 @@ master-bin.000001 # Query # # use `test`
> master-bin.000001 # Table_map # # table_id: # (test.t1)
> master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
> master-bin.000001 # Xid # # COMMIT /* XID */
> -master-bin.000001 # Query # # use `test`; BEGIN
> -master-bin.000001 # Table_map # # table_id: # (test.t2)
> -master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
> -master-bin.000001 # Query # # use `test`; COMMIT
> master-bin.000001 # Query # # use `test`; drop table t1,t2
> master-bin.000001 # Query # # use `test`; create table t0 (n int)
> master-bin.000001 # Query # # use `test`; BEGIN
> @@ -382,7 +372,7 @@ master-bin.000001 # Query # # use `test`
> master-bin.000001 # Query # # use `test`; BEGIN
> master-bin.000001 # Table_map # # table_id: # (test.t1)
> master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
> -master-bin.000001 # Query # # use `test`; COMMIT
> +master-bin.000001 # Query # # use `test`; ROLLBACK
> master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t2
> master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (a int, b int, primary key
> (a)) engine=innodb
> master-bin.000001 # Query # # use `test`; BEGIN
> @@ -395,16 +385,16 @@ master-bin.000001 # Xid # # COMMIT /* XI
> master-bin.000001 # Query # # use `test`; BEGIN
> master-bin.000001 # Table_map # # table_id: # (test.t1)
> master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
> +master-bin.000001 # Table_map # # table_id: # (test.t2)
> +master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
> master-bin.000001 # Query # # use `test`; COMMIT
> master-bin.000001 # Query # # use `test`; DROP TABLE t2
> master-bin.000001 # Query # # use `test`; BEGIN
> master-bin.000001 # Table_map # # table_id: # (test.t1)
> master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
> -master-bin.000001 # Query # # use `test`; COMMIT
> -master-bin.000001 # Query # # use `test`; BEGIN
> master-bin.000001 # Table_map # # table_id: # (test.t1)
> master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
> -master-bin.000001 # Query # # use `test`; COMMIT
> +master-bin.000001 # Query # # use `test`; ROLLBACK
> master-bin.000001 # Query # # use `test`; BEGIN
> master-bin.000001 # Table_map # # table_id: # (test.t1)
> master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
> @@ -412,7 +402,7 @@ master-bin.000001 # Query # # use `test`
> master-bin.000001 # Query # # use `test`; BEGIN
> master-bin.000001 # Table_map # # table_id: # (test.t1)
> master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
> -master-bin.000001 # Query # # use `test`; COMMIT
> +master-bin.000001 # Query # # use `test`; ROLLBACK
> master-bin.000001 # Query # # use `test`; BEGIN
> master-bin.000001 # Query # # use `test`; TRUNCATE table t2
> master-bin.000001 # Query # # use `test`; COMMIT
>
> === modified file 'mysql-test/suite/rpl/r/rpl_row_create_table.result'
> --- a/mysql-test/suite/rpl/r/rpl_row_create_table.result 2008-10-08 09:15:00 +0000
> +++ b/mysql-test/suite/rpl/r/rpl_row_create_table.result 2008-12-03 19:55:49 +0000
> @@ -4,34 +4,39 @@ reset master;
> reset slave;
> drop table if exists t1,t2,t3,t4,t5,t6,t7,t8,t9;
> start slave;
> +**** Resetting master and slave ****
> +STOP SLAVE;
> +RESET SLAVE;
> +RESET MASTER;
> +START SLAVE;
> CREATE TABLE t1 (a INT, b INT);
> CREATE TABLE t2 (a INT, b INT) ENGINE=Merge;
> CREATE TABLE t3 (a INT, b INT) CHARSET=utf8;
> CREATE TABLE t4 (a INT, b INT) ENGINE=Merge CHARSET=utf8;
> -SHOW BINLOG EVENTS FROM 216;
> +SHOW BINLOG EVENTS FROM 106;
> Log_name #
> -Pos 216
> +Pos 106
> Event_type Query
> Server_id #
> -End_log_pos 309
> +End_log_pos 199
> Info use `test`; CREATE TABLE t1 (a INT, b INT)
> Log_name #
> -Pos 309
> +Pos 199
> Event_type Query
> Server_id #
> -End_log_pos 415
> +End_log_pos 305
> Info use `test`; CREATE TABLE t2 (a INT, b INT) ENGINE=Merge
> Log_name #
> -Pos 415
> +Pos 305
> Event_type Query
> Server_id #
> -End_log_pos 521
> +End_log_pos 411
> Info use `test`; CREATE TABLE t3 (a INT, b INT) CHARSET=utf8
> Log_name #
> -Pos 521
> +Pos 411
> Event_type Query
> Server_id #
> -End_log_pos 640
> +End_log_pos 530
> Info use `test`; CREATE TABLE t4 (a INT, b INT) ENGINE=Merge CHARSET=utf8
> **** On Master ****
> SHOW CREATE TABLE t1;
> @@ -125,9 +130,14 @@ NULL 3 6
> NULL 4 2
> NULL 5 10
> NULL 6 12
> +**** Resetting master and slave ****
> +STOP SLAVE;
> +RESET SLAVE;
> +RESET MASTER;
> +START SLAVE;
> CREATE TABLE t7 (UNIQUE(b)) SELECT a,b FROM tt3;
> ERROR 23000: Duplicate entry '2' for key 'b'
> -SHOW BINLOG EVENTS FROM 1374;
> +SHOW BINLOG EVENTS FROM 106;
> Log_name Pos Event_type Server_id End_log_pos Info
> CREATE TABLE t7 (a INT, b INT UNIQUE);
> INSERT INTO t7 SELECT a,b FROM tt3;
> @@ -137,18 +147,23 @@ a b
> 1 2
> 2 4
> 3 6
> -SHOW BINLOG EVENTS FROM 1374;
> +SHOW BINLOG EVENTS FROM 106;
> Log_name Pos Event_type Server_id End_log_pos Info
> -# 1374 Query # 1474 use `test`; CREATE TABLE t7 (a INT, b INT UNIQUE)
> -# 1474 Query # 1542 use `test`; BEGIN
> -# 1542 Table_map # 1584 table_id: # (test.t7)
> -# 1584 Write_rows # 1640 table_id: # flags: STMT_END_F
> -# 1640 Query # 1711 use `test`; ROLLBACK
> +# 106 Query # 206 use `test`; CREATE TABLE t7 (a INT, b INT UNIQUE)
> +# 206 Query # 274 use `test`; BEGIN
> +# 274 Table_map # 316 table_id: # (test.t7)
> +# 316 Write_rows # 372 table_id: # flags: STMT_END_F
> +# 372 Query # 443 use `test`; ROLLBACK
> SELECT * FROM t7 ORDER BY a,b;
> a b
> 1 2
> 2 4
> 3 6
> +**** Resetting master and slave ****
> +STOP SLAVE;
> +RESET SLAVE;
> +RESET MASTER;
> +START SLAVE;
> CREATE TEMPORARY TABLE tt4 (a INT, b INT);
> INSERT INTO tt4 VALUES (4,8), (5,10), (6,12);
> BEGIN;
> @@ -156,12 +171,12 @@ INSERT INTO t7 SELECT a,b FROM tt4;
> ROLLBACK;
> Warnings:
> Warning 1196 Some non-transactional changed tables couldn't be rolled back
> -SHOW BINLOG EVENTS FROM 1711;
> +SHOW BINLOG EVENTS FROM 106;
> Log_name Pos Event_type Server_id End_log_pos Info
> -# 1711 Query # 1779 use `test`; BEGIN
> -# 1779 Table_map # 1821 table_id: # (test.t7)
> -# 1821 Write_rows # 1877 table_id: # flags: STMT_END_F
> -# 1877 Query # 1946 use `test`; COMMIT
> +# 106 Query # 174 use `test`; BEGIN
> +# 174 Table_map # 216 table_id: # (test.t7)
> +# 216 Write_rows # 272 table_id: # flags: STMT_END_F
> +# 272 Query # 343 use `test`; ROLLBACK
> SELECT * FROM t7 ORDER BY a,b;
> a b
> 1 2
> @@ -178,6 +193,11 @@ a b
> 4 8
> 5 10
> 6 12
> +**** Resetting master and slave ****
> +STOP SLAVE;
> +RESET SLAVE;
> +RESET MASTER;
> +START SLAVE;
> CREATE TABLE t8 LIKE t4;
> CREATE TABLE t9 LIKE tt4;
> CREATE TEMPORARY TABLE tt5 LIKE t4;
> @@ -196,10 +216,10 @@ Create Table CREATE TABLE `t9` (
> `a` int(11) DEFAULT NULL,
> `b` int(11) DEFAULT NULL
> ) ENGINE=MyISAM DEFAULT CHARSET=latin1
> -SHOW BINLOG EVENTS FROM 1946;
> +SHOW BINLOG EVENTS FROM 106;
> Log_name Pos Event_type Server_id End_log_pos Info
> -# 1946 Query # 2032 use `test`; CREATE TABLE t8 LIKE t4
> -# 2032 Query # 2171 use `test`; CREATE TABLE `t9` (
> +# 106 Query # 192 use `test`; CREATE TABLE t8 LIKE t4
> +# 192 Query # 331 use `test`; CREATE TABLE `t9` (
> `a` int(11) DEFAULT NULL,
> `b` int(11) DEFAULT NULL
> )
> @@ -276,9 +296,8 @@ a
> 1
> 2
> 3
> -SHOW BINLOG EVENTS;
> +SHOW BINLOG EVENTS FROM 106;
> Log_name Pos Event_type Server_id End_log_pos Info
> -# 4 Format_desc # 106 Server ver: #, Binlog ver: #
> # 106 Query # 192 use `test`; CREATE TABLE t1 (a INT)
> # 192 Query # 260 use `test`; BEGIN
> # 260 Table_map # 301 table_id: # (test.t1)
> @@ -308,7 +327,7 @@ Log_name Pos Event_type Server_id End_lo
> # 1329 Query # 1397 use `test`; BEGIN
> # 1397 Table_map # 1438 table_id: # (test.t1)
> # 1438 Write_rows # 1482 table_id: # flags: STMT_END_F
> -# 1482 Query # 1551 use `test`; COMMIT
> +# 1482 Query # 1553 use `test`; ROLLBACK
> SHOW TABLES;
> Tables_in_test
> t1
> @@ -371,9 +390,8 @@ a
> 4
> 6
> 9
> -SHOW BINLOG EVENTS;
> +SHOW BINLOG EVENTS FROM 106;
> Log_name Pos Event_type Server_id End_log_pos Info
> -# 4 Format_desc # 106 Server ver: #, Binlog ver: #
> # 106 Query # 192 use `test`; CREATE TABLE t1 (a INT)
> # 192 Query # 260 use `test`; BEGIN
> # 260 Table_map # 301 table_id: # (test.t1)
> @@ -394,6 +412,11 @@ a
> 6
> 9
> TRUNCATE TABLE t2;
> +**** Resetting master and slave ****
> +STOP SLAVE;
> +RESET SLAVE;
> +RESET MASTER;
> +START SLAVE;
> BEGIN;
> INSERT INTO t2 SELECT a*a FROM t1;
> CREATE TEMPORARY TABLE tt2
> @@ -406,14 +429,14 @@ Warnings:
> Warning 1196 Some non-transactional changed tables couldn't be rolled back
> SELECT * FROM t2 ORDER BY a;
> a
> -SHOW BINLOG EVENTS FROM 949;
> +SHOW BINLOG EVENTS FROM 106;
> Log_name Pos Event_type Server_id End_log_pos Info
> -# 949 Query # 1017 use `test`; BEGIN
> -# 1017 Table_map # 1058 table_id: # (test.t2)
> -# 1058 Write_rows # 1102 table_id: # flags: STMT_END_F
> -# 1102 Table_map # 1143 table_id: # (test.t2)
> -# 1143 Write_rows # 1182 table_id: # flags: STMT_END_F
> -# 1182 Query # 1253 use `test`; ROLLBACK
> +# 106 Query # 174 use `test`; BEGIN
> +# 174 Table_map # 215 table_id: # (test.t2)
> +# 215 Write_rows # 259 table_id: # flags: STMT_END_F
> +# 259 Table_map # 300 table_id: # (test.t2)
> +# 300 Write_rows # 339 table_id: # flags: STMT_END_F
> +# 339 Query # 410 use `test`; ROLLBACK
> SELECT * FROM t2 ORDER BY a;
> a
> DROP TABLE t1,t2;
>
> === modified file 'mysql-test/suite/rpl/r/rpl_slave_skip.result'
> --- a/mysql-test/suite/rpl/r/rpl_slave_skip.result 2008-03-28 12:16:41 +0000
> +++ b/mysql-test/suite/rpl/r/rpl_slave_skip.result 2008-12-03 19:55:49 +0000
> @@ -174,6 +174,7 @@ DROP TRIGGER tr2;
> INSERT INTO t1 VALUES (3,'master/slave');
> INSERT INTO t2 VALUES (3,'master/slave');
> INSERT INTO t3 VALUES (3,'master/slave');
> +COMMIT;
> SELECT * FROM t1 ORDER BY a;
> a b
> 2 master only
>
> === modified file 'mysql-test/suite/rpl/r/rpl_trigger.result'
> --- a/mysql-test/suite/rpl/r/rpl_trigger.result 2007-12-18 09:07:08 +0000
> +++ b/mysql-test/suite/rpl/r/rpl_trigger.result 2008-12-03 19:55:49 +0000
> @@ -975,3 +975,29 @@ a b
> 2 b
> 3 c
> drop table t1;
> +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;
> +create table t1 ( f int ) engine = innodb;
> +create table log ( r int ) engine = myisam;
> +create trigger tr
> +after insert on t1
> +for each row insert into log values ( new.f );
> +set autocommit = 0;
> +insert into t1 values ( 1 );
> +select * from t1;
> +f
> +1
> +select * from t1;
> +f
> +rollback;
> +Warnings:
> +Warning 1196 Some non-transactional changed tables couldn't be rolled back
> +select * from t1;
> +f
> +select * from t1;
> +f
> +drop table t1, log;
>
> === modified file 'mysql-test/suite/rpl/t/rpl_row_create_table.test'
> --- a/mysql-test/suite/rpl/t/rpl_row_create_table.test 2008-10-08 09:15:00 +0000
> +++ b/mysql-test/suite/rpl/t/rpl_row_create_table.test 2008-12-03 19:55:49 +0000
> @@ -29,6 +29,8 @@ SET GLOBAL storage_engine=memory;
> START SLAVE;
> --enable_query_log
>
> +--source include/reset_master_and_slave.inc
> +
> connection master;
> CREATE TABLE t1 (a INT, b INT);
> CREATE TABLE t2 (a INT, b INT) ENGINE=Merge;
> @@ -36,7 +38,7 @@ CREATE TABLE t3 (a INT, b INT) CHARSET=u
> CREATE TABLE t4 (a INT, b INT) ENGINE=Merge CHARSET=utf8;
> --replace_column 1 # 4 #
> --replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/
> ---query_vertical SHOW BINLOG EVENTS FROM 216
> +--query_vertical SHOW BINLOG EVENTS FROM 106
> --echo **** On Master ****
> --query_vertical SHOW CREATE TABLE t1
> --query_vertical SHOW CREATE TABLE t2
> @@ -65,6 +67,8 @@ SELECT * FROM t5 ORDER BY a,b,c;
> --query_vertical SHOW CREATE TABLE t6
> SELECT * FROM t6 ORDER BY a,b,c;
>
> +--source include/reset_master_and_slave.inc
> +
> connection master;
> # Test for erroneous constructions
> --error ER_DUP_ENTRY
> @@ -72,7 +76,7 @@ CREATE TABLE t7 (UNIQUE(b)) SELECT a,b F
> # Shouldn't be written to the binary log
> --replace_column 1 # 4 #
> --replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/
> -SHOW BINLOG EVENTS FROM 1374;
> +SHOW BINLOG EVENTS FROM 106;
>
> # Test that INSERT-SELECT works the same way as for SBR.
> CREATE TABLE t7 (a INT, b INT UNIQUE);
> @@ -82,10 +86,12 @@ SELECT * FROM t7 ORDER BY a,b;
> # Should be written to the binary log
> --replace_column 1 # 4 #
> --replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/
> -SHOW BINLOG EVENTS FROM 1374;
> +SHOW BINLOG EVENTS FROM 106;
> sync_slave_with_master;
> SELECT * FROM t7 ORDER BY a,b;
>
> +--source include/reset_master_and_slave.inc
> +
> connection master;
> CREATE TEMPORARY TABLE tt4 (a INT, b INT);
> INSERT INTO tt4 VALUES (4,8), (5,10), (6,12);
> @@ -94,11 +100,13 @@ INSERT INTO t7 SELECT a,b FROM tt4;
> ROLLBACK;
> --replace_column 1 # 4 #
> --replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/
> -SHOW BINLOG EVENTS FROM 1711;
> +SHOW BINLOG EVENTS FROM 106;
> SELECT * FROM t7 ORDER BY a,b;
> sync_slave_with_master;
> SELECT * FROM t7 ORDER BY a,b;
>
> +--source include/reset_master_and_slave.inc
> +
> connection master;
> CREATE TABLE t8 LIKE t4;
> CREATE TABLE t9 LIKE tt4;
> @@ -110,7 +118,7 @@ CREATE TEMPORARY TABLE tt7 SELECT 1;
> --query_vertical SHOW CREATE TABLE t9
> --replace_column 1 # 4 #
> --replace_regex /\/\* xid=.* \*\//\/* XID *\// /table_id: [0-9]+/table_id: #/
> -SHOW BINLOG EVENTS FROM 1946;
> +SHOW BINLOG EVENTS FROM 106;
> sync_slave_with_master;
> --echo **** On Slave ****
> --query_vertical SHOW CREATE TABLE t8
> @@ -162,7 +170,7 @@ SELECT * FROM t3 ORDER BY a;
> SELECT * FROM t4 ORDER BY a;
> --replace_column 1 # 4 #
> --replace_regex /\/\* xid=.* \*\//\/* XID *\// /Server ver: .*, Binlog ver:
> .*/Server ver: #, Binlog ver: #/ /table_id: [0-9]+/table_id: #/
> -SHOW BINLOG EVENTS;
> +SHOW BINLOG EVENTS FROM 106;
> sync_slave_with_master;
> SHOW TABLES;
> SELECT TABLE_NAME,ENGINE
> @@ -208,13 +216,17 @@ COMMIT;
> SELECT * FROM t2 ORDER BY a;
> --replace_column 1 # 4 #
> --replace_regex /\/\* xid=.* \*\//\/* XID *\// /Server ver: .*, Binlog ver:
> .*/Server ver: #, Binlog ver: #/ /table_id: [0-9]+/table_id: #/
> -SHOW BINLOG EVENTS;
> +SHOW BINLOG EVENTS FROM 106;
> sync_slave_with_master;
> SELECT * FROM t2 ORDER BY a;
>
> connection master;
> TRUNCATE TABLE t2;
> +sync_slave_with_master;
> +
> +--source include/reset_master_and_slave.inc
>
> +connection master;
> BEGIN;
> INSERT INTO t2 SELECT a*a FROM t1;
> CREATE TEMPORARY TABLE tt2
> @@ -227,7 +239,7 @@ ROLLBACK;
> SELECT * FROM t2 ORDER BY a;
> --replace_column 1 # 4 #
> --replace_regex /\/\* xid=.* \*\//\/* XID *\// /Server ver: .*, Binlog ver:
> .*/Server ver: #, Binlog ver: #/ /table_id: [0-9]+/table_id: #/
> -SHOW BINLOG EVENTS FROM 949;
> +SHOW BINLOG EVENTS FROM 106;
> sync_slave_with_master;
> SELECT * FROM t2 ORDER BY a;
>
>
> === modified file 'mysql-test/suite/rpl/t/rpl_slave_skip.test'
> --- a/mysql-test/suite/rpl/t/rpl_slave_skip.test 2008-03-28 12:16:41 +0000
> +++ b/mysql-test/suite/rpl/t/rpl_slave_skip.test 2008-12-03 19:55:49 +0000
> @@ -121,6 +121,7 @@ DROP TRIGGER tr2;
> INSERT INTO t1 VALUES (3,'master/slave');
> INSERT INTO t2 VALUES (3,'master/slave');
> INSERT INTO t3 VALUES (3,'master/slave');
> +COMMIT;
>
> SELECT * FROM t1 ORDER BY a;
> SELECT * FROM t2 ORDER BY a;
>
> === modified file 'mysql-test/suite/rpl/t/rpl_trigger.test'
> --- a/mysql-test/suite/rpl/t/rpl_trigger.test 2007-12-18 09:07:08 +0000
> +++ b/mysql-test/suite/rpl/t/rpl_trigger.test 2008-12-03 19:55:49 +0000
> @@ -467,20 +467,46 @@ drop trigger if exists t1_bi;
> insert into t1 values (3, "c");
>
> select * from t1;
> +sync_slave_with_master;
> +select * from t1;
> +
> +connection master;
> +
> +drop table t1;
> +sync_slave_with_master;
> +
> +#
> +# Bug#40116: Uncommited changes are replicated and stay on slave after
> +# rollback on master
> +#
>
> -save_master_pos;
> -connection slave;
> -sync_with_master;
> +source include/master-slave-reset.inc;
> +source include/have_innodb.inc;
>
> +connection master;
> +create table t1 ( f int ) engine = innodb;
> +create table log ( r int ) engine = myisam;
> +create trigger tr
> + after insert on t1
> + for each row insert into log values ( new.f );
> +
> +set autocommit = 0;
> +insert into t1 values ( 1 );
> +
> +select * from t1;
> +sync_slave_with_master;
> select * from t1;
>
> connection master;
> +rollback;
> +select * from t1;
> +sync_slave_with_master;
> +select * from t1;
>
> -drop table t1;
> +connection master;
> +drop table t1, log;
> +sync_slave_with_master;
>
> #
> # End of tests
> #
> -save_master_pos;
> -connection slave;
> -sync_with_master;
>
> === modified file 'sql/log.cc'
> --- a/sql/log.cc 2008-10-21 12:18:38 +0000
> +++ b/sql/log.cc 2008-12-03 19:55:49 +0000
> @@ -1478,60 +1478,11 @@ static int binlog_commit(handlerton *hto
> }
>
> /*
> - Decision table for committing a transaction. The top part, the
> - *conditions* represent different cases that can occur, and hte
> - bottom part, the *actions*, represent what should be done in that
> - particular case.
> + We commit the transaction if:
>
> - Real transaction 'all' was true
> + - We are not in a transaction and committing a statement, or
>
> - Statement in cache There were at least one statement in the
> - transaction cache
> -
> - In transaction We are inside a transaction
> -
> - Stmt modified non-trans The statement being committed modified a
> - non-transactional table
> -
> - All modified non-trans Some statement before this one in the
> - transaction modified a non-transactional
> - table
> -
> -
> - ============================= = = = = = = = = = = = = = = = =
> - Real transaction N N N N N N N N N N N N N N N N
> - Statement in cache N N N N N N N N Y Y Y Y Y Y Y Y
> - In transaction N N N N Y Y Y Y N N N N Y Y Y Y
> - Stmt modified non-trans N N Y Y N N Y Y N N Y Y N N Y Y
> - All modified non-trans N Y N Y N Y N Y N Y N Y N Y N Y
> -
> - Action: (C)ommit/(A)ccumulate C C - C A C - C - - - - A A - A
> - ============================= = = = = = = = = = = = = = = = =
> -
> -
> - ============================= = = = = = = = = = = = = = = = =
> - Real transaction Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y Y
> - Statement in cache N N N N N N N N Y Y Y Y Y Y Y Y
> - In transaction N N N N Y Y Y Y N N N N Y Y Y Y
> - Stmt modified non-trans N N Y Y N N Y Y N N Y Y N N Y Y
> - All modified non-trans N Y N Y N Y N Y N Y N Y N Y N Y
> -
> - (C)ommit/(A)ccumulate/(-) - - - - C C - C - - - - C C - C
> - ============================= = = = = = = = = = = = = = = = =
> -
> - In other words, we commit the transaction if and only if both of
> - the following are true:
> - - We are not in a transaction and committing a statement
> -
> - - We are in a transaction and one (or more) of the following are
> - true:
> -
> - - A full transaction is committed
> -
> - OR
> -
> - - A non-transactional statement is committed and there is
> - no statement cached
> + - We are in a transaction and a full transaction is committed
>
> Otherwise, we accumulate the statement
> */
> @@ -1544,11 +1495,7 @@ static int binlog_commit(handlerton *hto
> YESNO(in_transaction),
> YESNO(thd->transaction.all.modified_non_trans_table),
> YESNO(thd->transaction.stmt.modified_non_trans_table)));
> - if (in_transaction &&
> - (all ||
> - (!trx_data->at_least_one_stmt &&
> - thd->transaction.stmt.modified_non_trans_table)) ||
> - !in_transaction && !all)
> + if (!in_transaction || all)
> {
> Query_log_event qev(thd, STRING_WITH_LEN("COMMIT"), TRUE, FALSE);
> qev.error_code= 0; // see comment in MYSQL_LOG::write(THD, IO_CACHE)
>
>
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;
create table t1 ( f int ) engine = innodb;
create table log ( r int ) engine = myisam;
create table t2 ( v int ) engine = innodb;
create trigger tr after insert on t1 for each row insert into log values ( new.f );
set autocommit = 0;
###### SIMPLE COMMIT (no trigger) ######
### MASTER
insert into t2 values ( 10 );
select * from t2;
v
10
commit;
select * from t2;
v
10
### SLAVE
sync_slave_with_master
select * from t2;
v
10
###### SIMPLE ROLLBACK (no trigger) ######
### MASTER
insert into t2 values ( 11 );
select * from t2;
v
10
11
rollback;
select * from t2;
v
10
### SLAVE
sync_slave_with_master
select * from t2;
v
10
###### COMMIT A TRANSACTION #####
insert into t1 values ( 1 );
insert into t2 values ( 1 );
### MASTER
select * from t1;
f
1
select * from log;
r
1
select * from t2;
v
10
1
### SLAVE
sync_slave_with_master
select * from t1;
f
select * from log;
r
select * from t2;
v
10
### MASTER
commit;
select * from t1;
f
1
select * from log;
r
1
select * from t2;
v
10
1
### SLAVE
sync_slave_with_master
select * from t1;
f
1
select * from log;
r
1
select * from t2;
v
10
1
###### ROLLBACK TRANSACTION #######
insert into t1 values ( 2 );
insert into t2 values ( 2 );
### MASTER
select * from t1;
f
1
2
select * from log;
r
1
2
select * from t2;
v
10
1
2
### SLAVE
sync_slave_with_master;
select * from t1;
f
1
select * from log;
r
1
select * from t2;
v
10
1
### MASTER
rollback;
Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
select * from t1;
f
1
select * from log;
r
1
2
select * from t2;
v
10
1
### SLAVE
sync_slave_with_master
select * from t1;
f
1
2
select * from log;
r
1
2
select * from t2;
v
10
1
2
