He Zhenxing wrote:
> Hi Mats
>
> I think the patch is good, approved!
>
> But I have some suggestions not strictly related to this bug, they are
> related to the code handling binlog and transactions in general, I think
> this part of the code is not working as expected, BUG#40221, and this
> bug are all related to this. And there might be other hidden bugs too. I
> think we should make this part of code more consistent and simpler. And
> I think your patch for this bug is one step of this goal.
>
> According to the comment for THD::binlog_start_trans_and_stmt, this
> function should be called for all statments in a
> transaction(AUTOCOMMIT=0 or BEGIN is seen), but actually this is not the
> case. Mostly, this function is only called for the first statement of
> the transaction. BUG#40221 reveals that this is not called for other
> statements in RBR, and the truth is that this is not called for other
> statements in SBR either, which also means that
> THD::binlog_commit/THD::binlog_rollback is not called for each statement
> in the transaction as expected. The reason is because in SBR, we only
> call THD::binlog_start_trans_and_stmt when the transaction log is empty,
> so the thd->transaction.stmt.ha_list will not be set. Although I don't
> see any problems caused by this, but I think it is a problem if the code
> is not working as we supposed.
>
> So here are some ideas:
> 1. always log all events (transactional or non-transactional updates) to
> the cache first to simplify the logic.
I think this is already the case. If it isn't, we should fix that.
> 2. seperate THD::binlog_start_trans_and_stmt to two functions,
> THD::binlog_start_transaction for transaction, and
> THD::binlog_start_statement for statement. Allways call
> THD::binlog_start_statement for each statement.
I concur. It would be good if we could have a generic solution that does not
request an explicit call for the binary log (thinking about separating
replication code from main server here).
However, I think this should be part of that effort (WL#3665) instead.
> 3. Add THD::binlog_end_transaction and THD::binlog_end_statement, these
> are the counter parts of THD::binlog_start_transaction and
> THD::binlog_start_statement. These functions will call
> binlog_commit/rollback accordingly.
Why? I see no problem in using the existing interface, which is the handlerton.
We just need to extend it to support the "start" version as well.
Just my few cents,
Mats Kindahl
> 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)
>>
>
>
--
Mats Kindahl
Lead Software Developer
Replication Team
MySQL AB, www.mysql.com
