Skip to content

Commit dbcfef4

Browse files
author
Alfranio Correia
committed
BUG#28976 Mixing trans and non-trans tables in one transaction results in incorrect
binlog Mixing transactional (T) and non-transactional (N) tables on behalf of a transaction may lead to inconsistencies among masters and slaves in STATEMENT mode. The problem stems from the fact that although modifications done to non-transactional tables on behalf of a transaction become immediately visible to other connections they do not immediately get to the binary log and therefore consistency is broken. Although there may be issues in mixing T and M tables in STATEMENT mode, there are safe combinations that clients find useful. In this bug, we fix the following issue. Mixing N and T tables in multi-level (e.g. a statement that fires a trigger) or multi-table table statements (e.g. update t1, t2...) were not handled correctly. In such cases, it was not possible to distinguish when a T table was updated if the sequence of changes was N and T. In a nutshell, just the flag "modified_non_trans_table" was not enough to reflect that both a N and T tables were changed. To circumvent this issue, we check if an engine is registered in the handler's list and changed something which means that a T table was modified. Check WL 2687 for a full-fledged patch that will make the use of either the MIXED or ROW modes completely safe.
1 parent 31afccc commit dbcfef4

File tree

9 files changed

+1642
-7
lines changed

9 files changed

+1642
-7
lines changed

‎mysql-test/extra/rpl_tests/rpl_mixing_engines.test‎

Lines changed: 727 additions & 0 deletions
Large diffs are not rendered by default.

‎mysql-test/include/commit.inc‎

Lines changed: 2 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -725,9 +725,9 @@ call p_verify_status_increment(4, 4, 4, 4);
725725
alter table t3 add column (b int);
726726
call p_verify_status_increment(2, 0, 2, 0);
727727
alter table t3 rename t4;
728-
call p_verify_status_increment(1, 0, 1, 0);
728+
call p_verify_status_increment(2, 2, 2, 2);
729729
rename table t4 to t3;
730-
call p_verify_status_increment(1, 0, 1, 0);
730+
call p_verify_status_increment(2, 2, 2, 2);
731731
truncate table t3;
732732
call p_verify_status_increment(4, 4, 4, 4);
733733
create view v1 as select * from t2;

‎mysql-test/r/commit_1innodb.result‎

Lines changed: 4 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -841,17 +841,17 @@ call p_verify_status_increment(2, 0, 2, 0);
841841
SUCCESS
842842

843843
alter table t3 rename t4;
844-
call p_verify_status_increment(1, 0, 1, 0);
844+
call p_verify_status_increment(2, 2, 2, 2);
845845
SUCCESS
846846

847847
rename table t4 to t3;
848-
call p_verify_status_increment(1, 0, 1, 0);
848+
call p_verify_status_increment(2, 2, 2, 2);
849849
SUCCESS
850850

851851
truncate table t3;
852852
call p_verify_status_increment(4, 4, 4, 4);
853-
ERROR
854-
Expected commit increment: 4 actual: 2
853+
SUCCESS
854+
855855
create view v1 as select * from t2;
856856
call p_verify_status_increment(1, 0, 1, 0);
857857
SUCCESS

‎mysql-test/suite/binlog/r/binlog_row_mix_innodb_myisam.result‎

Lines changed: 4 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -379,7 +379,9 @@ master-bin.000001 # Query # # BEGIN
379379
master-bin.000001 # Table_map # # table_id: # (test.t1)
380380
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
381381
master-bin.000001 # Query # # COMMIT
382+
master-bin.000001 # Query # # BEGIN
382383
master-bin.000001 # Query # # use `test`; TRUNCATE table t2
384+
master-bin.000001 # Xid # # COMMIT /* XID */
383385
master-bin.000001 # Query # # BEGIN
384386
master-bin.000001 # Table_map # # table_id: # (test.t1)
385387
master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
@@ -838,8 +840,10 @@ UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */;
838840
ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
839841
show binlog events from <binlog_start>;
840842
Log_name Pos Event_type Server_id End_log_pos Info
843+
master-bin.000001 # Query # # BEGIN
841844
master-bin.000001 # Intvar # # INSERT_ID=6
842845
master-bin.000001 # Query # # use `test`; UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */
846+
master-bin.000001 # Query # # ROLLBACK
843847
select count(*) from t1 /* must be 4 */;
844848
count(*)
845849
4

‎mysql-test/suite/binlog/r/binlog_stm_mix_innodb_myisam.result‎

Lines changed: 6 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -346,7 +346,9 @@ master-bin.000001 # Query # # use `test`; INSERT INTO t1 values (3,3)
346346
master-bin.000001 # Query # # use `test`; DROP TABLE IF EXISTS t2
347347
master-bin.000001 # Query # # use `test`; CREATE TABLE t2 (a int, b int, primary key (a)) engine=innodb
348348
master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (4,4)
349+
master-bin.000001 # Query # # BEGIN
349350
master-bin.000001 # Query # # use `test`; TRUNCATE table t2
351+
master-bin.000001 # Xid # # COMMIT /* XID */
350352
master-bin.000001 # Query # # use `test`; INSERT INTO t1 VALUES (5,5)
351353
master-bin.000001 # Query # # use `test`; DROP TABLE t2
352354
master-bin.000001 # Query # # use `test`; INSERT INTO t1 values (6,6)
@@ -545,8 +547,10 @@ UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */;
545547
ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
546548
show binlog events from <binlog_start>;
547549
Log_name Pos Event_type Server_id End_log_pos Info
550+
master-bin.000001 # Query # # BEGIN
548551
master-bin.000001 # Intvar # # INSERT_ID=6
549552
master-bin.000001 # Query # # use `test`; UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */
553+
master-bin.000001 # Query # # ROLLBACK
550554
/* the output must denote there is the query */;
551555
select count(*) from t1 /* must be 4 */;
552556
count(*)
@@ -782,8 +786,10 @@ UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */;
782786
ERROR 23000: Duplicate entry '2' for key 'PRIMARY'
783787
show binlog events from <binlog_start>;
784788
Log_name Pos Event_type Server_id End_log_pos Info
789+
master-bin.000001 # Query # # BEGIN
785790
master-bin.000001 # Intvar # # INSERT_ID=6
786791
master-bin.000001 # Query # # use `test`; UPDATE t4,t3 SET t4.a=t3.a + bug27417(1) /* top level non-ta table */
792+
master-bin.000001 # Query # # ROLLBACK
787793
select count(*) from t1 /* must be 4 */;
788794
count(*)
789795
4

‎mysql-test/suite/rpl/r/rpl_innodb_mixed_dml.result‎

Lines changed: 2 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -963,7 +963,9 @@ master-bin.000001 # Xid 1 # #
963963
master-bin.000001 # Query 1 # BEGIN
964964
master-bin.000001 # Query 1 # use `test_rpl`; INSERT INTO t1 VALUES(1, 't1, text 1')
965965
master-bin.000001 # Xid 1 # #
966+
master-bin.000001 # Query 1 # BEGIN
966967
master-bin.000001 # Query 1 # use `test_rpl`; TRUNCATE t1
968+
master-bin.000001 # Xid 1 # #
967969
master-bin.000001 # Query 1 # BEGIN
968970
master-bin.000001 # Query 1 # use `test_rpl`; DELETE FROM t1
969971
master-bin.000001 # Xid 1 # #

0 commit comments

Comments
 (0)