Hi Alfranio,
Nice work, but a problem need to be clarified.
The option --ignore-causality introduces an another problem in SBM.
The following statements will cause a inconsistency between master and
slave.
CREATE TABLE t1(c1 INTEGER) ENGINE=Innodb;
CREATE TABLE t2(c1 INTEGER) ENGINE=MyISAM;
BEGIN ;
INSERT INTO t1 VALUES (1),(2);
INSERT INTO t2 SELECT * FROM t1;
COMMIT;
so, I think that locking the N_table before starting a transaction is
better.
Maybe It is difficult to implement, however give users a advice is easy.
I propose --ignore-causality is a session variable which can be changed
at runtime,
if it has to be used.
this will reduce the option's influence.
Alfranio Correia wrote:
> #At
> file:///home/acorreia/workspace.sun/repository.mysql/bzrwork/bug-46364/mysql-5.1-bugteam/
> based on revid:azundris@stripped
>
> 3160 Alfranio Correia 2009-11-03
> BUG#46364 MyISAM transbuffer problems (NTM problem)
>
> It is well-known that due to concurrency issues, a slave can become
> inconsistent when a transaction contains updates to both transaction and
> non-transactional tables in statement and mixed modes.
>
> In a nutshell, the current code-base tries to preserve causality among the
> statements by not writing non-transactional statements directly to the
> binary log. Unfortunately, modifications done to non-transactional tables
> on behalf of a transaction become immediately visible to other connections
> but may not immediately get into the binary log and therefore consistency
> may be broken.
>
> In general, it is impossible to automatically detect causality/dependency
> among statements by just analyzing the statements sent to the server. This
> happen because dependency may be hidden in the application code and it is
> necessary to know a Pryor all the statements processed in the context of
> a transaction such as in a procedure. Moreover, even for the few cases that
> we could automatically address in the server, the computation effort
> required could make the approach infeasible.
>
> So, in this patch we introduce the option "ignore-causality" that can be
> used to bypass the current behavior in order to write directly to binary
> log statements that change non-transactional tables.
>
> added:
> mysql-test/suite/rpl/r/rpl_stm_causality_mixing_engines.result
> mysql-test/suite/rpl/t/rpl_stm_causality_mixing_engines-master.opt
> mysql-test/suite/rpl/t/rpl_stm_causality_mixing_engines.test
> modified:
> sql/log.cc
> sql/mysql_priv.h
> sql/mysqld.cc
> sql/set_var.cc
> === added file 'mysql-test/suite/rpl/r/rpl_stm_causality_mixing_engines.result'
> --- a/mysql-test/suite/rpl/r/rpl_stm_causality_mixing_engines.result 1970-01-01
> 00:00:00 +0000
> +++ b/mysql-test/suite/rpl/r/rpl_stm_causality_mixing_engines.result 2009-11-03
> 22:24:42 +0000
> @@ -0,0 +1,870 @@
> +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;
> +###################################################################################
> +# CONFIGURATION
> +###################################################################################
> +SET SQL_LOG_BIN=0;
> +CREATE TABLE nt_1 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM;
> +CREATE TABLE nt_2 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM;
> +CREATE TABLE nt_3 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM;
> +CREATE TABLE nt_4 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM;
> +CREATE TABLE tt_1 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb;
> +CREATE TABLE tt_2 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb;
> +CREATE TABLE tt_3 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb;
> +CREATE TABLE tt_4 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb;
> +SET SQL_LOG_BIN=1;
> +SET SQL_LOG_BIN=0;
> +CREATE TABLE nt_1 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM;
> +CREATE TABLE nt_2 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM;
> +CREATE TABLE nt_3 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM;
> +CREATE TABLE nt_4 (a text, b int PRIMARY KEY, c text) ENGINE = MyISAM;
> +CREATE TABLE tt_1 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb;
> +CREATE TABLE tt_2 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb;
> +CREATE TABLE tt_3 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb;
> +CREATE TABLE tt_4 (a text, b int PRIMARY KEY, c text) ENGINE = Innodb;
> +SET SQL_LOG_BIN=1;
> +CREATE FUNCTION f1 () RETURNS VARCHAR(64)
> +BEGIN
> +RETURN "Testing...";
> +END|
> +CREATE FUNCTION f2 () RETURNS VARCHAR(64)
> +BEGIN
> +RETURN f1();
> +END|
> +CREATE PROCEDURE pc_i_tt_3 (IN x INT, IN y VARCHAR(64))
> +BEGIN
> +INSERT INTO tt_3 VALUES (y,x,x);
> +END|
> +CREATE TRIGGER tr_i_tt_3_to_nt_3 BEFORE INSERT ON tt_3 FOR EACH ROW
> +BEGIN
> +INSERT INTO nt_3 VALUES (NEW.a, NEW.b, NEW.c);
> +END|
> +CREATE TRIGGER tr_i_nt_4_to_tt_4 BEFORE INSERT ON nt_4 FOR EACH ROW
> +BEGIN
> +INSERT INTO tt_4 VALUES (NEW.a, NEW.b, NEW.c);
> +END|
> +###################################################################################
> +# MIXING TRANSACTIONAL and NON-TRANSACTIONAL TABLES
> +###################################################################################
> +#
> +#1) "B T T C" generates in binlog the "B T T C" entries.
> +#
> +BEGIN;
> +INSERT INTO tt_1 VALUES ("new text 4", 4, "new text 4");
> +INSERT INTO tt_2 VALUES ("new text 4", 4, "new text 4");
> +COMMIT;
> +show binlog events from <binlog_start>;
> +Log_name Pos Event_type Server_id End_log_pos Info
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 4", 4,
> "new text 4")
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_2 VALUES ("new text 4", 4,
> "new text 4")
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +
> +
> +
> +
> +#
> +#1.e) "B T T C" with error in T generates in binlog the "B T T C" entries.
> +#
> +INSERT INTO tt_1 VALUES ("new text -2", -2, "new text -2");
> +BEGIN;
> +INSERT INTO tt_1 VALUES ("new text -1", -1, "new text -1"), ("new text -2", -2, "new
> text -2");
> +ERROR 23000: Duplicate entry '-2' for key 'PRIMARY'
> +INSERT INTO tt_2 VALUES ("new text -3", -3, "new text -3");
> +COMMIT;
> +BEGIN;
> +INSERT INTO tt_2 VALUES ("new text -5", -5, "new text -5");
> +INSERT INTO tt_2 VALUES ("new text -4", -4, "new text -4"), ("new text -5", -5, "new
> text -5");
> +ERROR 23000: Duplicate entry '-5' for key 'PRIMARY'
> +COMMIT;
> +show binlog events from <binlog_start>;
> +Log_name Pos Event_type Server_id End_log_pos Info
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text -2",
> -2, "new text -2")
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_2 VALUES ("new text -3",
> -3, "new text -3")
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_2 VALUES ("new text -5",
> -5, "new text -5")
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +
> +
> +
> +
> +#
> +#2) "B T T R" generates in binlog an "empty" entry.
> +#
> +BEGIN;
> +INSERT INTO tt_1 VALUES ("new text 5", 5, "new text 5");
> +INSERT INTO tt_2 VALUES ("new text 5", 5, "new text 5");
> +ROLLBACK;
> +show binlog events from <binlog_start>;
> +Log_name Pos Event_type Server_id End_log_pos Info
> +
> +
> +
> +
> +#
> +#2.e) "B T T R" with error in T generates in binlog an "empty" entry.
> +#
> +INSERT INTO tt_1 VALUES ("new text -7", -7, "new text -7");
> +BEGIN;
> +INSERT INTO tt_1 VALUES ("new text -6", -6, "new text -6"), ("new text -7", -7, "new
> text -7");
> +ERROR 23000: Duplicate entry '-7' for key 'PRIMARY'
> +INSERT INTO tt_2 VALUES ("new text -8", -8, "new text -8");
> +ROLLBACK;
> +BEGIN;
> +INSERT INTO tt_2 VALUES ("new text -10", -10, "new text -10");
> +INSERT INTO tt_2 VALUES ("new text -9", -9, "new text -9"), ("new text -10", -10,
> "new text -10");
> +ERROR 23000: Duplicate entry '-10' for key 'PRIMARY'
> +ROLLBACK;
> +show binlog events from <binlog_start>;
> +Log_name Pos Event_type Server_id End_log_pos Info
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text -7",
> -7, "new text -7")
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +
> +
> +
> +
> +#
> +#3) "B T N C" generates in binlog the "B T N C" entries.
> +#
> +BEGIN;
> +INSERT INTO tt_1 VALUES ("new text 6", 6, "new text 6");
> +INSERT INTO nt_1 VALUES ("new text 6", 6, "new text 6");
> +COMMIT;
> +show binlog events from <binlog_start>;
> +Log_name Pos Event_type Server_id End_log_pos Info
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_1 VALUES ("new text 6", 6,
> "new text 6")
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 6", 6,
> "new text 6")
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +
> +
> +
> +
> +#
> +#3.e) "B T N C" with error in either T or N generates in binlog the "B T N C"
> entries.
> +#
> +INSERT INTO tt_1 VALUES ("new text -12", -12, "new text -12");
> +BEGIN;
> +INSERT INTO tt_1 VALUES ("new text -11", -11, "new text -11"), ("new text -12", -12,
> "new text -12");
> +ERROR 23000: Duplicate entry '-12' for key 'PRIMARY'
> +INSERT INTO nt_1 VALUES ("new text -13", -13, "new text -13");
> +COMMIT;
> +BEGIN;
> +INSERT INTO tt_1 VALUES ("new text -14", -14, "new text -14");
> +INSERT INTO nt_1 VALUES ("new text -16", -16, "new text -16");
> +INSERT INTO nt_1 VALUES ("new text -15", -15, "new text -15"), ("new text -16", -16,
> "new text -16");
> +ERROR 23000: Duplicate entry '-16' for key 'PRIMARY'
> +COMMIT;
> +show binlog events from <binlog_start>;
> +Log_name Pos Event_type Server_id End_log_pos Info
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text -12",
> -12, "new text -12")
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_1 VALUES ("new text -13",
> -13, "new text -13")
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_1 VALUES ("new text -16",
> -16, "new text -16")
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_1 VALUES ("new text -15",
> -15, "new text -15"), ("new text -16", -16, "new text -16")
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text -14",
> -14, "new text -14")
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +
> +
> +
> +
> +#
> +#4) "B T N R" generates in binlog the "B T N R" entries.
> +#
> +BEGIN;
> +INSERT INTO tt_1 VALUES ("new text 7", 7, "new text 7");
> +INSERT INTO nt_1 VALUES ("new text 7", 7, "new text 7");
> +ROLLBACK;
> +Warnings:
> +Warning 1196 Some non-transactional changed tables couldn't be rolled back
> +show binlog events from <binlog_start>;
> +Log_name Pos Event_type Server_id End_log_pos Info
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_1 VALUES ("new text 7", 7,
> "new text 7")
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 7", 7,
> "new text 7")
> +master-bin.000001 # Query # # ROLLBACK
> +
> +
> +
> +
> +#
> +#4.e) "B T N R" with error in either T or N generates in binlog the "B T N R"
> entries.
> +#
> +INSERT INTO tt_1 VALUES ("new text -17", -17, "new text -17");
> +BEGIN;
> +INSERT INTO tt_1 VALUES ("new text -16", -16, "new text -16"), ("new text -17", -17,
> "new text -17");
> +ERROR 23000: Duplicate entry '-17' for key 'PRIMARY'
> +INSERT INTO nt_1 VALUES ("new text -18", -18, "new text -18");
> +ROLLBACK;
> +Warnings:
> +Warning 1196 Some non-transactional changed tables couldn't be rolled back
> +BEGIN;
> +INSERT INTO tt_1 VALUES ("new text -19", -19, "new text -19");
> +INSERT INTO nt_1 VALUES ("new text -21", -21, "new text -21");
> +INSERT INTO nt_1 VALUES ("new text -20", -20, "new text -20"), ("new text -21", -21,
> "new text -21");
> +ERROR 23000: Duplicate entry '-21' for key 'PRIMARY'
> +ROLLBACK;
> +Warnings:
> +Warning 1196 Some non-transactional changed tables couldn't be rolled back
> +show binlog events from <binlog_start>;
> +Log_name Pos Event_type Server_id End_log_pos Info
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text -17",
> -17, "new text -17")
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_1 VALUES ("new text -18",
> -18, "new text -18")
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_1 VALUES ("new text -21",
> -21, "new text -21")
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_1 VALUES ("new text -20",
> -20, "new text -20"), ("new text -21", -21, "new text -21")
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text -19",
> -19, "new text -19")
> +master-bin.000001 # Query # # ROLLBACK
> +
> +
> +
> +
> +#
> +#5) "T" generates in binlog the "B T C" entry.
> +#
> +INSERT INTO tt_1 VALUES ("new text 8", 8, "new text 8");
> +show binlog events from <binlog_start>;
> +Log_name Pos Event_type Server_id End_log_pos Info
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 8", 8,
> "new text 8")
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +
> +
> +
> +
> +#
> +#5.e) "T" with error in T generates in binlog an "empty" entry.
> +#
> +INSERT INTO tt_1 VALUES ("new text -1", -1, "new text -1");
> +INSERT INTO tt_1 VALUES ("new text -1", -1, "new text -1"), ("new text -22", -22,
> "new text -22");
> +ERROR 23000: Duplicate entry '-1' for key 'PRIMARY'
> +INSERT INTO tt_1 VALUES ("new text -23", -23, "new text -23"), ("new text -1", -1,
> "new text -1");
> +ERROR 23000: Duplicate entry '-1' for key 'PRIMARY'
> +show binlog events from <binlog_start>;
> +Log_name Pos Event_type Server_id End_log_pos Info
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text -1",
> -1, "new text -1")
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +
> +
> +
> +
> +#
> +#6) "N" generates in binlog the "N" entry.
> +#
> +INSERT INTO nt_1 VALUES ("new text 9", 9, "new text 9");
> +show binlog events from <binlog_start>;
> +Log_name Pos Event_type Server_id End_log_pos Info
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_1 VALUES ("new text 9", 9,
> "new text 9")
> +
> +
> +
> +
> +#
> +#6.e) "N" with error in N generates in binlog an empty entry if the error
> +# happens in the first tuple. Otherwise, generates the "N" entry and
> +# the error is appended.
> +#
> +INSERT INTO nt_1 VALUES ("new text -1", -1, "new text -1");
> +INSERT INTO nt_1 VALUES ("new text -1", -1, "new text -1");
> +ERROR 23000: Duplicate entry '-1' for key 'PRIMARY'
> +INSERT INTO nt_1 VALUES ("new text -24", -24, "new text -24"), ("new text -1", -1,
> "new text -1");
> +ERROR 23000: Duplicate entry '-1' for key 'PRIMARY'
> +show binlog events from <binlog_start>;
> +Log_name Pos Event_type Server_id End_log_pos Info
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_1 VALUES ("new text -1",
> -1, "new text -1")
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_1 VALUES ("new text -24",
> -24, "new text -24"), ("new text -1", -1, "new text -1")
> +
> +
> +
> +
> +#
> +#7) "M" generates in binglog the "B M C" entries.
> +#
> +DELETE FROM nt_1;
> +INSERT INTO nt_1 SELECT * FROM tt_1;
> +DELETE FROM tt_1;
> +INSERT INTO tt_1 SELECT * FROM nt_1;
> +INSERT INTO tt_3 VALUES ("new text 000", 000, '');
> +INSERT INTO tt_3 VALUES("new text 100", 100, f1());
> +INSERT INTO nt_4 VALUES("new text 100", 100, f1());
> +INSERT INTO tt_3 VALUES("new text 200", 200, f2());
> +INSERT INTO nt_4 VALUES ("new text 300", 300, '');
> +INSERT INTO nt_4 VALUES ("new text 400", 400, f1());
> +INSERT INTO nt_4 VALUES ("new text 500", 500, f2());
> +CALL pc_i_tt_3(600, "Testing...");
> +UPDATE nt_3, nt_4, tt_3, tt_4 SET nt_3.a= "new text 1", nt_4.a= "new text 1",
> tt_3.a= "new text 1", tt_4.a= "new text 1" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and
> tt_3.b = tt_4.b and tt_4.b = 100;
> +UPDATE tt_3, tt_4, nt_3, nt_4 SET tt_3.a= "new text 2", tt_4.a= "new text 2",
> nt_3.a= "new text 2", nt_4.a = "new text 2" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and
> tt_3.b = tt_4.b and tt_4.b = 100;
> +UPDATE tt_3, nt_3, nt_4, tt_4 SET tt_3.a= "new text 3", nt_3.a= "new text 3",
> nt_4.a= "new text 3", tt_4.a = "new text 3" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and
> tt_3.b = tt_4.b and tt_4.b = 100;
> +UPDATE tt_3, nt_3, nt_4, tt_4 SET tt_3.a= "new text 4", nt_3.a= "new text 4",
> nt_4.a= "new text 4", tt_4.a = "new text 4" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and
> tt_3.b = tt_4.b and tt_4.b = 100;
> +show binlog events from <binlog_start>;
> +Log_name Pos Event_type Server_id End_log_pos Info
> +master-bin.000001 # Query # # use `test`; DELETE FROM nt_1
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_1 SELECT * FROM tt_1
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; DELETE FROM tt_1
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 SELECT * FROM nt_1
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_3 VALUES ("new text 000",
> 000, '')
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_3 VALUES("new text 100",
> 100, f1())
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_4 VALUES("new text 100",
> 100, f1())
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_3 VALUES("new text 200",
> 200, f2())
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_4 VALUES ("new text 300",
> 300, '')
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_4 VALUES ("new text 400",
> 400, f1())
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_4 VALUES ("new text 500",
> 500, f2())
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_3 VALUES (
> NAME_CONST('y',_latin1'Testing...' COLLATE 'latin1_swedish_ci'), NAME_CONST('x',600),
> NAME_CONST('x',600))
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; UPDATE nt_3, nt_4, tt_3, tt_4 SET nt_3.a=
> "new text 1", nt_4.a= "new text 1", tt_3.a= "new text 1", tt_4.a= "new text 1" where
> nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; UPDATE tt_3, tt_4, nt_3, nt_4 SET tt_3.a=
> "new text 2", tt_4.a= "new text 2", nt_3.a= "new text 2", nt_4.a = "new text 2" where
> nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; UPDATE tt_3, nt_3, nt_4, tt_4 SET tt_3.a=
> "new text 3", nt_3.a= "new text 3", nt_4.a= "new text 3", tt_4.a = "new text 3" where
> nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; UPDATE tt_3, nt_3, nt_4, tt_4 SET tt_3.a=
> "new text 4", nt_3.a= "new text 4", nt_4.a= "new text 4", tt_4.a = "new text 4" where
> nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +
> +
> +
> +
> +#
> +#7.e) "M" with error in M generates in binglog the "B M R" entries.
> +#
> +INSERT INTO nt_3 VALUES ("new text -26", -26, '');
> +SELECT * FROM tt_3;
> +a b c
> +new text 000 0
> +new text 4 100 Testing...
> +new text 200 200 Testing...
> +Testing... 600 600
> +INSERT INTO tt_3 VALUES ("new text -25", -25, ''), ("new text -26", -26, '');
> +ERROR 23000: Duplicate entry '-26' for key 'PRIMARY'
> +SELECT * FROM tt_3;
> +a b c
> +new text 000 0
> +new text 4 100 Testing...
> +new text 200 200 Testing...
> +Testing... 600 600
> +INSERT INTO tt_4 VALUES ("new text -26", -26, '');
> +SELECT * FROM nt_4;
> +a b c
> +new text 4 100 Testing...
> +new text 300 300
> +new text 400 400 Testing...
> +new text 500 500 Testing...
> +INSERT INTO nt_4 VALUES ("new text -25", -25, ''), ("new text -26", -26, '');
> +ERROR 23000: Duplicate entry '-26' for key 'PRIMARY'
> +SELECT * FROM nt_4;
> +a b c
> +new text 4 100 Testing...
> +new text 300 300
> +new text 400 400 Testing...
> +new text 500 500 Testing...
> +new text -25 -25
> +show binlog events from <binlog_start>;
> +Log_name Pos Event_type Server_id End_log_pos Info
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_3 VALUES ("new text -26",
> -26, '')
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_3 VALUES ("new text -25",
> -25, ''), ("new text -26", -26, '')
> +master-bin.000001 # Query # # ROLLBACK
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_4 VALUES ("new text -26",
> -26, '')
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_4 VALUES ("new text -25",
> -25, ''), ("new text -26", -26, '')
> +master-bin.000001 # Query # # ROLLBACK
> +
> +
> +
> +
> +#
> +#8) "B N N T C" generates in binglog the "N N B T C" entries.
> +#
> +BEGIN;
> +INSERT INTO nt_1 VALUES ("new text 10", 10, "new text 10");
> +INSERT INTO nt_2 VALUES ("new text 10", 10, "new text 10");
> +INSERT INTO tt_1 VALUES ("new text 10", 10, "new text 10");
> +COMMIT;
> +show binlog events from <binlog_start>;
> +Log_name Pos Event_type Server_id End_log_pos Info
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_1 VALUES ("new text 10",
> 10, "new text 10")
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_2 VALUES ("new text 10",
> 10, "new text 10")
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 10",
> 10, "new text 10")
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +
> +
> +
> +
> +#
> +#8.e) "B N N T R" See 6.e and 9.e.
> +#
> +
> +
> +
> +
> +#
> +#9) "B N N T R" generates in binlog the "N N B T R" entries.
> +#
> +BEGIN;
> +INSERT INTO nt_1 VALUES ("new text 11", 11, "new text 11");
> +INSERT INTO nt_2 VALUES ("new text 11", 11, "new text 11");
> +INSERT INTO tt_1 VALUES ("new text 11", 11, "new text 11");
> +ROLLBACK;
> +Warnings:
> +Warning 1196 Some non-transactional changed tables couldn't be rolled back
> +show binlog events from <binlog_start>;
> +Log_name Pos Event_type Server_id End_log_pos Info
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_1 VALUES ("new text 11",
> 11, "new text 11")
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_2 VALUES ("new text 11",
> 11, "new text 11")
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 11",
> 11, "new text 11")
> +master-bin.000001 # Query # # ROLLBACK
> +
> +
> +
> +
> +#
> +#9.e) "B N N T R" with error in N generates in binlog the "N N B T R" entries.
> +#
> +BEGIN;
> +INSERT INTO nt_1 VALUES ("new text -25", -25, "new text -25");
> +INSERT INTO nt_2 VALUES ("new text -25", -25, "new text -25");
> +INSERT INTO nt_2 VALUES ("new text -26", -26, "new text -26"), ("new text -25", -25,
> "new text -25");
> +ERROR 23000: Duplicate entry '-25' for key 'PRIMARY'
> +INSERT INTO tt_1 VALUES ("new text -27", -27, "new text -27");
> +ROLLBACK;
> +Warnings:
> +Warning 1196 Some non-transactional changed tables couldn't be rolled back
> +show binlog events from <binlog_start>;
> +Log_name Pos Event_type Server_id End_log_pos Info
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_1 VALUES ("new text -25",
> -25, "new text -25")
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_2 VALUES ("new text -25",
> -25, "new text -25")
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_2 VALUES ("new text -26",
> -26, "new text -26"), ("new text -25", -25, "new text -25")
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text -27",
> -27, "new text -27")
> +master-bin.000001 # Query # # ROLLBACK
> +
> +
> +
> +
> +#
> +#10) "B N N C" generates in binglog the "N N" entries.
> +#
> +BEGIN;
> +INSERT INTO nt_1 VALUES ("new text 12", 12, "new text 12");
> +INSERT INTO nt_2 VALUES ("new text 12", 12, "new text 12");
> +COMMIT;
> +show binlog events from <binlog_start>;
> +Log_name Pos Event_type Server_id End_log_pos Info
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_1 VALUES ("new text 12",
> 12, "new text 12")
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_2 VALUES ("new text 12",
> 12, "new text 12")
> +
> +
> +
> +
> +#
> +#10.e) "B N N C" See 6.e and 9.e.
> +#
> +
> +
> +
> +
> +#
> +#11) "B N N R" generates in binlog the "N N" entries.
> +#
> +BEGIN;
> +INSERT INTO nt_1 VALUES ("new text 13", 13, "new text 13");
> +INSERT INTO nt_2 VALUES ("new text 13", 13, "new text 13");
> +ROLLBACK;
> +Warnings:
> +Warning 1196 Some non-transactional changed tables couldn't be rolled back
> +show binlog events from <binlog_start>;
> +Log_name Pos Event_type Server_id End_log_pos Info
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_1 VALUES ("new text 13",
> 13, "new text 13")
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_2 VALUES ("new text 13",
> 13, "new text 13")
> +
> +
> +
> +
> +#
> +#11.e) "B N N R" See 6.e and 9.e.
> +#
> +
> +
> +
> +
> +#
> +#12) "B M T C" generates in the binlog the "B M T C" entries.
> +#
> +DELETE FROM nt_1;
> +BEGIN;
> +INSERT INTO nt_1 SELECT * FROM tt_1;
> +INSERT INTO tt_2 VALUES ("new text 14", 14, "new text 14");
> +COMMIT;
> +DELETE FROM tt_1;
> +BEGIN;
> +INSERT INTO tt_1 SELECT * FROM nt_1;
> +INSERT INTO tt_2 VALUES ("new text 15", 15, "new text 15");
> +COMMIT;
> +BEGIN;
> +INSERT INTO tt_3 VALUES ("new text 700", 700, '');
> +INSERT INTO tt_1 VALUES ("new text 800", 800, '');
> +COMMIT;
> +BEGIN;
> +INSERT INTO tt_3 VALUES("new text 900", 900, f1());
> +INSERT INTO tt_1 VALUES ("new text 1000", 1000, '');
> +COMMIT;
> +BEGIN;
> +INSERT INTO tt_3 VALUES(1100, 1100, f2());
> +INSERT INTO tt_1 VALUES ("new text 1200", 1200, '');
> +COMMIT;
> +BEGIN;
> +INSERT INTO nt_4 VALUES ("new text 1300", 1300, '');
> +INSERT INTO tt_1 VALUES ("new text 1400", 1400, '');
> +COMMIT;
> +BEGIN;
> +INSERT INTO nt_4 VALUES("new text 1500", 1500, f1());
> +INSERT INTO tt_1 VALUES ("new text 1600", 1600, '');
> +COMMIT;
> +BEGIN;
> +INSERT INTO nt_4 VALUES("new text 1700", 1700, f2());
> +INSERT INTO tt_1 VALUES ("new text 1800", 1800, '');
> +COMMIT;
> +BEGIN;
> +CALL pc_i_tt_3(1900, "Testing...");
> +INSERT INTO tt_1 VALUES ("new text 2000", 2000, '');
> +COMMIT;
> +BEGIN;
> +UPDATE nt_3, nt_4, tt_3, tt_4 SET nt_3.a= "new text 5", nt_4.a= "new text 5",
> tt_3.a= "new text 5", tt_4.a= "new text 5" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and
> tt_3.b = tt_4.b and tt_4.b = 100;
> +INSERT INTO tt_1 VALUES ("new text 2100", 2100, '');
> +COMMIT;
> +BEGIN;
> +UPDATE tt_3, tt_4, nt_3, nt_4 SET tt_3.a= "new text 6", tt_4.a= "new text 6",
> nt_3.a= "new text 6", nt_4.a = "new text 6" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and
> tt_3.b = tt_4.b and tt_4.b = 100;
> +INSERT INTO tt_1 VALUES ("new text 2200", 2200, '');
> +COMMIT;
> +BEGIN;
> +UPDATE tt_3, nt_3, nt_4, tt_4 SET tt_3.a= "new text 7", nt_3.a= "new text 7",
> nt_4.a= "new text 7", tt_4.a = "new text 7" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and
> tt_3.b = tt_4.b and tt_4.b = 100;
> +INSERT INTO tt_1 VALUES ("new text 2300", 2300, '');
> +COMMIT;
> +BEGIN;
> +UPDATE tt_3, nt_3, nt_4, tt_4 SET tt_3.a= "new text 8", nt_3.a= "new text 8",
> nt_4.a= "new text 8", tt_4.a = "new text 8" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and
> tt_3.b = tt_4.b and tt_4.b = 100;
> +INSERT INTO tt_1 VALUES ("new text 2400", 2400, '');
> +COMMIT;
> +show binlog events from <binlog_start>;
> +Log_name Pos Event_type Server_id End_log_pos Info
> +master-bin.000001 # Query # # use `test`; DELETE FROM nt_1
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_1 SELECT * FROM tt_1
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_2 VALUES ("new text 14",
> 14, "new text 14")
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; DELETE FROM tt_1
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 SELECT * FROM nt_1
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_2 VALUES ("new text 15",
> 15, "new text 15")
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_3 VALUES ("new text 700",
> 700, '')
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 800",
> 800, '')
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_3 VALUES("new text 900",
> 900, f1())
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 1000",
> 1000, '')
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_3 VALUES(1100, 1100, f2())
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 1200",
> 1200, '')
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_4 VALUES ("new text 1300",
> 1300, '')
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 1400",
> 1400, '')
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_4 VALUES("new text 1500",
> 1500, f1())
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 1600",
> 1600, '')
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_4 VALUES("new text 1700",
> 1700, f2())
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 1800",
> 1800, '')
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_3 VALUES (
> NAME_CONST('y',_latin1'Testing...' COLLATE 'latin1_swedish_ci'), NAME_CONST('x',1900),
> NAME_CONST('x',1900))
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 2000",
> 2000, '')
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; UPDATE nt_3, nt_4, tt_3, tt_4 SET nt_3.a=
> "new text 5", nt_4.a= "new text 5", tt_3.a= "new text 5", tt_4.a= "new text 5" where
> nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 2100",
> 2100, '')
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; UPDATE tt_3, tt_4, nt_3, nt_4 SET tt_3.a=
> "new text 6", tt_4.a= "new text 6", nt_3.a= "new text 6", nt_4.a = "new text 6" where
> nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 2200",
> 2200, '')
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; UPDATE tt_3, nt_3, nt_4, tt_4 SET tt_3.a=
> "new text 7", nt_3.a= "new text 7", nt_4.a= "new text 7", tt_4.a = "new text 7" where
> nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 2300",
> 2300, '')
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; UPDATE tt_3, nt_3, nt_4, tt_4 SET tt_3.a=
> "new text 8", nt_3.a= "new text 8", nt_4.a= "new text 8", tt_4.a = "new text 8" where
> nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 2400",
> 2400, '')
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +
> +
> +
> +
> +#
> +#12.e) "B M T C" with error in M generates in the binlog the "B M T C" entries.
> +#
> +# There is a bug in the slave that needs to be fixed before enabling
> +# this part of the test. A bug report will be filed referencing this
> +# test case.
> +BEGIN;
> +INSERT INTO nt_3 VALUES ("new text -28", -28, '');
> +INSERT INTO tt_3 VALUES ("new text -27", -27, ''), ("new text -28", -28, '');
> +ERROR 23000: Duplicate entry '-28' for key 'PRIMARY'
> +INSERT INTO tt_1 VALUES ("new text -27", -27, '');
> +COMMIT;
> +BEGIN;
> +INSERT INTO tt_4 VALUES ("new text -28", -28, '');
> +INSERT INTO nt_4 VALUES ("new text -27", -27, ''), ("new text -28", -28, '');
> +ERROR 23000: Duplicate entry '-28' for key 'PRIMARY'
> +INSERT INTO tt_1 VALUES ("new text -28", -28, '');
> +COMMIT;
> +show binlog events from <binlog_start>;
> +Log_name Pos Event_type Server_id End_log_pos Info
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_3 VALUES ("new text -28",
> -28, '')
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_3 VALUES ("new text -27",
> -27, ''), ("new text -28", -28, '')
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text -27",
> -27, '')
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_4 VALUES ("new text -28",
> -28, '')
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_4 VALUES ("new text -27",
> -27, ''), ("new text -28", -28, '')
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text -28",
> -28, '')
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +
> +
> +
> +
> +#
> +#13) "B M T R" generates in the binlog the "B M T R" entries
> +#
> +DELETE FROM nt_1;
> +BEGIN;
> +INSERT INTO nt_1 SELECT * FROM tt_1;
> +INSERT INTO tt_2 VALUES ("new text 17", 17, "new text 17");
> +ROLLBACK;
> +Warnings:
> +Warning 1196 Some non-transactional changed tables couldn't be rolled back
> +DELETE FROM tt_1;
> +BEGIN;
> +INSERT INTO tt_1 SELECT * FROM nt_1;
> +INSERT INTO tt_2 VALUES ("new text 18", 18, "new text 18");
> +ROLLBACK;
> +INSERT INTO tt_1 SELECT * FROM nt_1;
> +BEGIN;
> +INSERT INTO tt_3 VALUES ("new text 2500", 2500, '');
> +INSERT INTO tt_1 VALUES ("new text 2600", 2600, '');
> +ROLLBACK;
> +Warnings:
> +Warning 1196 Some non-transactional changed tables couldn't be rolled back
> +BEGIN;
> +INSERT INTO tt_3 VALUES("new text 2700", 2700, f1());
> +INSERT INTO tt_1 VALUES ("new text 2800", 2800, '');
> +ROLLBACK;
> +Warnings:
> +Warning 1196 Some non-transactional changed tables couldn't be rolled back
> +BEGIN;
> +INSERT INTO tt_3 VALUES(2900, 2900, f2());
> +INSERT INTO tt_1 VALUES ("new text 3000", 3000, '');
> +ROLLBACK;
> +Warnings:
> +Warning 1196 Some non-transactional changed tables couldn't be rolled back
> +BEGIN;
> +INSERT INTO nt_4 VALUES ("new text 3100", 3100, '');
> +INSERT INTO tt_1 VALUES ("new text 3200", 3200, '');
> +ROLLBACK;
> +Warnings:
> +Warning 1196 Some non-transactional changed tables couldn't be rolled back
> +BEGIN;
> +INSERT INTO nt_4 VALUES("new text 3300", 3300, f1());
> +INSERT INTO tt_1 VALUES ("new text 3400", 3400, '');
> +ROLLBACK;
> +Warnings:
> +Warning 1196 Some non-transactional changed tables couldn't be rolled back
> +BEGIN;
> +INSERT INTO nt_4 VALUES("new text 3500", 3500, f2());
> +INSERT INTO tt_1 VALUES ("new text 3600", 3600, '');
> +ROLLBACK;
> +Warnings:
> +Warning 1196 Some non-transactional changed tables couldn't be rolled back
> +BEGIN;
> +CALL pc_i_tt_3(3700, "Testing...");
> +INSERT INTO tt_1 VALUES ("new text 3700", 3700, '');
> +ROLLBACK;
> +Warnings:
> +Warning 1196 Some non-transactional changed tables couldn't be rolled back
> +BEGIN;
> +UPDATE nt_3, nt_4, tt_3, tt_4 SET nt_3.a= "new text 9", nt_4.a= "new text 9",
> tt_3.a= "new text 9", tt_4.a= "new text 9" where nt_3.b = nt_4.b and nt_4.b = tt_3.b and
> tt_3.b = tt_4.b and tt_4.b = 100;
> +INSERT INTO tt_1 VALUES ("new text 3800", 3800, '');
> +ROLLBACK;
> +Warnings:
> +Warning 1196 Some non-transactional changed tables couldn't be rolled back
> +BEGIN;
> +UPDATE tt_3, tt_4, nt_3, nt_4 SET tt_3.a= "new text 10", tt_4.a= "new text 10",
> nt_3.a= "new text 10", nt_4.a = "new text 10" where nt_3.b = nt_4.b and nt_4.b = tt_3.b
> and tt_3.b = tt_4.b and tt_4.b = 100;
> +INSERT INTO tt_1 VALUES ("new text 3900", 3900, '');
> +ROLLBACK;
> +Warnings:
> +Warning 1196 Some non-transactional changed tables couldn't be rolled back
> +BEGIN;
> +UPDATE tt_3, nt_3, nt_4, tt_4 SET tt_3.a= "new text 11", nt_3.a= "new text 11",
> nt_4.a= "new text 11", tt_4.a = "new text 11" where nt_3.b = nt_4.b and nt_4.b = tt_3.b
> and tt_3.b = tt_4.b and tt_4.b = 100;
> +INSERT INTO tt_1 VALUES ("new text 4000", 4000, '');
> +ROLLBACK;
> +Warnings:
> +Warning 1196 Some non-transactional changed tables couldn't be rolled back
> +BEGIN;
> +UPDATE tt_3, nt_3, nt_4, tt_4 SET tt_3.a= "new text 12", nt_3.a= "new text 12",
> nt_4.a= "new text 12", tt_4.a = "new text 12" where nt_3.b = nt_4.b and nt_4.b = tt_3.b
> and tt_3.b = tt_4.b and tt_4.b = 100;
> +INSERT INTO tt_1 VALUES ("new text 4100", 4100, '');
> +ROLLBACK;
> +Warnings:
> +Warning 1196 Some non-transactional changed tables couldn't be rolled back
> +show binlog events from <binlog_start>;
> +Log_name Pos Event_type Server_id End_log_pos Info
> +master-bin.000001 # Query # # use `test`; DELETE FROM nt_1
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_1 SELECT * FROM tt_1
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_2 VALUES ("new text 17",
> 17, "new text 17")
> +master-bin.000001 # Query # # ROLLBACK
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; DELETE FROM tt_1
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 SELECT * FROM nt_1
> +master-bin.000001 # Xid # # COMMIT /* XID */
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_3 VALUES ("new text 2500",
> 2500, '')
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 2600",
> 2600, '')
> +master-bin.000001 # Query # # ROLLBACK
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_3 VALUES("new text 2700",
> 2700, f1())
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 2800",
> 2800, '')
> +master-bin.000001 # Query # # ROLLBACK
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_3 VALUES(2900, 2900, f2())
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 3000",
> 3000, '')
> +master-bin.000001 # Query # # ROLLBACK
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_4 VALUES ("new text 3100",
> 3100, '')
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 3200",
> 3200, '')
> +master-bin.000001 # Query # # ROLLBACK
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_4 VALUES("new text 3300",
> 3300, f1())
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 3400",
> 3400, '')
> +master-bin.000001 # Query # # ROLLBACK
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_4 VALUES("new text 3500",
> 3500, f2())
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 3600",
> 3600, '')
> +master-bin.000001 # Query # # ROLLBACK
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_3 VALUES (
> NAME_CONST('y',_latin1'Testing...' COLLATE 'latin1_swedish_ci'), NAME_CONST('x',3700),
> NAME_CONST('x',3700))
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 3700",
> 3700, '')
> +master-bin.000001 # Query # # ROLLBACK
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; UPDATE nt_3, nt_4, tt_3, tt_4 SET nt_3.a=
> "new text 9", nt_4.a= "new text 9", tt_3.a= "new text 9", tt_4.a= "new text 9" where
> nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 3800",
> 3800, '')
> +master-bin.000001 # Query # # ROLLBACK
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; UPDATE tt_3, tt_4, nt_3, nt_4 SET tt_3.a=
> "new text 10", tt_4.a= "new text 10", nt_3.a= "new text 10", nt_4.a = "new text 10" where
> nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 3900",
> 3900, '')
> +master-bin.000001 # Query # # ROLLBACK
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; UPDATE tt_3, nt_3, nt_4, tt_4 SET tt_3.a=
> "new text 11", nt_3.a= "new text 11", nt_4.a= "new text 11", tt_4.a = "new text 11" where
> nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 4000",
> 4000, '')
> +master-bin.000001 # Query # # ROLLBACK
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; UPDATE tt_3, nt_3, nt_4, tt_4 SET tt_3.a=
> "new text 12", nt_3.a= "new text 12", nt_4.a= "new text 12", tt_4.a = "new text 12" where
> nt_3.b = nt_4.b and nt_4.b = tt_3.b and tt_3.b = tt_4.b and tt_4.b = 100
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text 4100",
> 4100, '')
> +master-bin.000001 # Query # # ROLLBACK
> +
> +
> +
> +
> +#
> +#13.e) "B M T R" with error in M generates in the binlog the "B M T R" entries.
> +#
> +BEGIN;
> +INSERT INTO nt_3 VALUES ("new text -30", -30, '');
> +INSERT INTO tt_3 VALUES ("new text -29", -29, ''), ("new text -30", -30, '');
> +ERROR 23000: Duplicate entry '-30' for key 'PRIMARY'
> +INSERT INTO tt_1 VALUES ("new text -30", -30, '');
> +ROLLBACK;
> +Warnings:
> +Warning 1196 Some non-transactional changed tables couldn't be rolled back
> +BEGIN;
> +INSERT INTO tt_4 VALUES ("new text -30", -30, '');
> +INSERT INTO nt_4 VALUES ("new text -29", -29, ''), ("new text -30", -30, '');
> +ERROR 23000: Duplicate entry '-30' for key 'PRIMARY'
> +INSERT INTO tt_1 VALUES ("new text -31", -31, '');
> +ROLLBACK;
> +Warnings:
> +Warning 1196 Some non-transactional changed tables couldn't be rolled back
> +show binlog events from <binlog_start>;
> +Log_name Pos Event_type Server_id End_log_pos Info
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_3 VALUES ("new text -30",
> -30, '')
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_3 VALUES ("new text -29",
> -29, ''), ("new text -30", -30, '')
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text -30",
> -30, '')
> +master-bin.000001 # Query # # ROLLBACK
> +master-bin.000001 # Query # # BEGIN
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_4 VALUES ("new text -30",
> -30, '')
> +master-bin.000001 # Query # # use `test`; INSERT INTO nt_4 VALUES ("new text -29",
> -29, ''), ("new text -30", -30, '')
> +master-bin.000001 # Query # # use `test`; INSERT INTO tt_1 VALUES ("new text -31",
> -31, '')
> +master-bin.000001 # Query # # ROLLBACK
> +###################################################################################
> +# CLEAN
> +###################################################################################
> +DROP TABLE tt_1;
> +DROP TABLE tt_2;
> +DROP TABLE tt_3;
> +DROP TABLE tt_4;
> +DROP TABLE nt_1;
> +DROP TABLE nt_2;
> +DROP TABLE nt_3;
> +DROP TABLE nt_4;
> +DROP PROCEDURE pc_i_tt_3;
> +DROP FUNCTION f1;
> +DROP FUNCTION f2;
>
> === added file 'mysql-test/suite/rpl/t/rpl_stm_causality_mixing_engines-master.opt'
> --- a/mysql-test/suite/rpl/t/rpl_stm_causality_mixing_engines-master.opt 1970-01-01
> 00:00:00 +0000
> +++ b/mysql-test/suite/rpl/t/rpl_stm_causality_mixing_engines-master.opt 2009-11-03
> 22:24:42 +0000
> @@ -0,0 +1 @@
> +--ignore-causality
>
> === added file 'mysql-test/suite/rpl/t/rpl_stm_causality_mixing_engines.test'
> --- a/mysql-test/suite/rpl/t/rpl_stm_causality_mixing_engines.test 1970-01-01
> 00:00:00 +0000
> +++ b/mysql-test/suite/rpl/t/rpl_stm_causality_mixing_engines.test 2009-11-03
> 22:24:42 +0000
> @@ -0,0 +1,5 @@
> +--source include/have_binlog_format_statement.inc
> +--source include/master-slave.inc
> +--source include/have_innodb.inc
> +
> +--source extra/rpl_tests/rpl_mixing_engines.test
>
> === modified file 'sql/log.cc'
> --- a/sql/log.cc 2009-10-23 00:03:41 +0000
> +++ b/sql/log.cc 2009-11-03 22:24:42 +0000
> @@ -4076,6 +4076,30 @@ bool MYSQL_BIN_LOG::write(Log_event *eve
> to write to the binlog cache in that case to handle updates to mixed
> trans/non-trans table types the best possible in binlogging)
> - or if the event asks for it (cache_stmt == TRUE).
> +
> + However, we do not use the cache if the option ignore-causality is set,
> + the current statement is logged as statement and there are no updates to
> + transactional tables. This is a feature added to the code to avoid the
> + well-known problems when a transaction contains updates to both transaction
> + and non-transactional tables in statement and mixed modes.
> +
> + In a nutshell, the default behavior tries to preserve causality among the
> + statements by not writing non-transactional statements directly to the
> + binary log. Unfortunately, modifications done to non-transactional tables
> + on behalf of a transaction become immediately visible to other connections
> + but may not immediately get into the binary log and therefore consistency
> + may be broken.
> +
> + In general, it is impossible to automatically detect causality/dependency
> + among statements by just analyzing the statements sent to the server. This
> + happen because dependency may be hidden in the application code and it is
> + necessary to know a priori all the statements processed in the context of
> + a transaction such as in a procedure. Moreover, even for the few cases that
> + we could automatically address in the server, the computation effort
> + required could make the approach infeasible.
> +
> + So, we always tries to preserve causality/dependency unless the option
> + ignore-causality is specified.
> */
> if (opt_using_transactions && thd)
> {
> @@ -4086,8 +4110,10 @@ bool MYSQL_BIN_LOG::write(Log_event *eve
> (binlog_trx_data*) thd_get_ha_data(thd, binlog_hton);
> IO_CACHE *trans_log= &trx_data->trans_log;
> my_off_t trans_log_pos= my_b_tell(trans_log);
> - if (event_info->get_cache_stmt() || trans_log_pos != 0 ||
> - stmt_has_updated_trans_table(thd))
> + if ((event_info->get_cache_stmt() || trans_log_pos != 0 ||
> + stmt_has_updated_trans_table(thd)) &&
> + (thd->current_stmt_binlog_row_based ||
> stmt_has_updated_trans_table(thd)
> + || !ignore_causality))
> {
> DBUG_PRINT("info", ("Using trans_log: cache: %d, trans_log_pos: %lu",
> event_info->get_cache_stmt(),
>
> === modified file 'sql/mysql_priv.h'
> --- a/sql/mysql_priv.h 2009-10-27 13:20:34 +0000
> +++ b/sql/mysql_priv.h 2009-11-03 22:24:42 +0000
> @@ -2017,6 +2017,7 @@ extern uint opt_large_page_size;
> #ifdef MYSQL_SERVER
> extern char *opt_logname, *opt_slow_logname;
> extern const char *log_output_str;
> +extern bool ignore_causality;
>
> extern MYSQL_PLUGIN_IMPORT MYSQL_BIN_LOG mysql_bin_log;
> extern LOGGER logger;
>
> === modified file 'sql/mysqld.cc'
> --- a/sql/mysqld.cc 2009-10-27 13:20:34 +0000
> +++ b/sql/mysqld.cc 2009-11-03 22:24:42 +0000
> @@ -560,6 +560,7 @@ ulong slow_launch_threads = 0, sync_binl
> ulong expire_logs_days = 0;
> ulong rpl_recovery_rank=0;
> const char *log_output_str= "FILE";
> +bool ignore_causality;
>
> time_t server_start_time, flush_status_time;
>
> @@ -3870,6 +3871,12 @@ with --log-bin instead.");
> {
> DBUG_ASSERT(global_system_variables.binlog_format != BINLOG_FORMAT_UNSPEC);
> }
> + if (ignore_causality && !opt_bin_log)
> + {
> + sql_print_error("You need to use --log-bin to make "
> + "--ignore-causality work.");
> + unireg_abort(1);
> + }
>
> /* Check that we have not let the format to unspecified at this point */
> DBUG_ASSERT((uint)global_system_variables.binlog_format <=
> @@ -5692,7 +5699,8 @@ enum options_mysqld
> OPT_SLAVE_EXEC_MODE,
> OPT_GENERAL_LOG_FILE,
> OPT_SLOW_QUERY_LOG_FILE,
> - OPT_IGNORE_BUILTIN_INNODB
> + OPT_IGNORE_BUILTIN_INNODB,
> + OPT_IGNORE_CAUSALITY
> };
>
>
> @@ -7031,6 +7039,10 @@ The minimum value for this variable is 4
> (uchar**) &max_system_variables.net_wait_timeout, 0, GET_ULONG,
> REQUIRED_ARG, NET_WAIT_TIMEOUT, 1, IF_WIN(INT_MAX32/1000, LONG_TIMEOUT),
> 0, 1, 0},
> + {"ignore-causality", OPT_IGNORE_CAUSALITY,
> + "Specify whether to immediatily write to non-transactional statements to the
> binary log thus ignoring any causality among the statements.",
> + (uchar**) &ignore_causality, (uchar**) &ignore_causality, 0, GET_BOOL,
> NO_ARG, 0,
> + 0, 0, 0, 0, 0},
> {0, 0, 0, 0, 0, 0, GET_NO_ARG, NO_ARG, 0, 0, 0, 0, 0, 0}
> };
>
>
> === modified file 'sql/set_var.cc'
> --- a/sql/set_var.cc 2009-09-29 15:38:40 +0000
> +++ b/sql/set_var.cc 2009-11-03 22:24:42 +0000
> @@ -171,7 +171,9 @@ sys_auto_increment_offset(&vars, "auto_i
>
> static sys_var_bool_ptr sys_automatic_sp_privileges(&vars,
> "automatic_sp_privileges",
> &sp_automatic_privileges);
> -
> +static sys_var_const sys_ignore_causality(&vars, "ignore-causality",
> + OPT_GLOBAL, SHOW_BOOL,
> + (uchar*) &ignore_causality);
> static sys_var_const sys_back_log(&vars, "back_log",
> OPT_GLOBAL, SHOW_LONG,
> (uchar*) &back_log);
>
>
> ------------------------------------------------------------------------
>
>
--
Your Sincerely,
Libing Song
==================================
MySQL Replication Team
Software Engineer
Certified (ISC)2 CISSP
Email : Li-Bing.Song@stripped
Skype : libing.song
MSN : slb_database@stripped
Phone : +86 010-6505-4020 ext. 319
Mobile: +86 138-1144-2038
==================================
