Hi Mats,
I have forced innodb in slave. Test works great. I guess I just
assumed that include/have_innodb.inc would report something if at least
one server (master or slave) was not configured for loading innodb SE.
I can see no other problems with the patch now. Approved.
Good that you liked the suggested test. Sorry about the false alarm,
though :) .
Mats Kindahl wrote:
> Hi Luìs!
>
> I have no problems getting the tests below to pass. However, you should note
> that if the server does not find the InnoDB engine available, it will substitute
> with MyISAM, which causes the test to fail (since the tests cannot rollback).
>
> By default, there is no InnoDB engine available on the slave; I suspect this is
> what caused the problem. There are two ways to load InnoDB into the slave:
> either supply a foo-slave.opt file containing --innodb for the foo.test file
> (which is what I do for rpl_trigger.test, it is named rpl_trigger-slave.opt), or
> provide the --mysqld=--innodb option to MTR at the command line.
>
> However, your suggested test is better than the one I supplied, so I will
> rewrite the existing test in rpl_trigger.test with something more like what you
> have (as an incremental patch, if you don't mind. Bazaar still does not save my
> commit messages when I uncommit a patch.)
>
> Just my few cents,
> Mats Kindahl
>
>
> Luís Soares wrote:
>> 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)
>>>
>>>
>
>
> ------------------------------------------------------------------------
>
>
