Re: bzr commit into mysql-6.0 branch (sven:2662) Bug#37373

[Andrei Elkin]

Sven, hello.

Here are some comments before I have read the newest patch that I
could not find.
We can improve the test in some ways, I think, but perhaps a bit
differently than your patch offers.

> #At file:///home/sven/bzr/b37373-rpl_ndb_transaction/6.0-rpl/
>
>  2662 Sven Sandberg	2008-06-17
>       BUG#37373: rpl_ndb_transaction fails sporadically in pb on sol10-amd64-a and
> sol10-sparc-a
>       Problem: rpl_ndb_transaction fails because it assumes nothing
>       is written to the binlog at a certain point. However, ndb may
>       write updates to ndb system tables to the binlog at a

did not you mean
write updates *from* ndb system tables to the binlog ...?

>       nondeterministic time point after an ndb table update has
>       been committed.

A straightforward fix, as it stands to me, could be to wait on master
till the event on ndb table will show up in the binlog.


>       Fix: break the test into two. rpl_ndb_transaction still does
>       the ndb updates needed by the first half of the test. The new
>       test case rpl_bug26395 includes the part that assumes nothing
>       more will be written to the binlog.
>       Also improved test cases a bit.
> added:
>   mysql-test/suite/rpl/r/rpl_bug26395.result
>   mysql-test/suite/rpl/t/rpl_bug26395-master.opt
>   mysql-test/suite/rpl/t/rpl_bug26395-slave.opt
>   mysql-test/suite/rpl/t/rpl_bug26395.test

I'd rather to agree with Mats who thinks we are better to collect
bugXYZ tests in the bugs suite.
However, in the case of the 2nd part of the original
rpl_ndb_transaction.test a basic property is verified.
That makes me think the 2nd part of the test is better to remain in a
basic suite test.
I guess you will second that the name rpl_ndb_transaction.test
neither the suite rpl_ndb does not fit to problems of the test.
It is supposed to verify XA-aware and XA-ignorant egnines replication,
ndb is just one of them.

So I'd rather to rename it like

    rpl_xa_various_engine.test

and move into the general replication suite. Splitting the test on two
parts does not look necessary.


> modified:
>   mysql-test/suite/rpl_ndb/r/rpl_ndb_transaction.result
>   mysql-test/suite/rpl_ndb/t/rpl_ndb_transaction-master.opt
>   mysql-test/suite/rpl_ndb/t/rpl_ndb_transaction.test
>
> per-file messages:
>   mysql-test/suite/rpl/r/rpl_bug26395.result
>     New result file.
>   mysql-test/suite/rpl/t/rpl_bug26395-master.opt
>     New opt file.
>   mysql-test/suite/rpl/t/rpl_bug26395-slave.opt
>     New opt file.
>   mysql-test/suite/rpl/t/rpl_bug26395.test
>     Moved second half of rpl_ndb_transaction here.
>   mysql-test/suite/rpl_ndb/r/rpl_ndb_transaction.result
>     Updated result file.
>   mysql-test/suite/rpl_ndb/t/rpl_ndb_transaction-master.opt
>     No need for the special debug flag any more, it was used by
>     the second part of the test which is now in rpl_bug26395.
>   mysql-test/suite/rpl_ndb/t/rpl_ndb_transaction.test
>     1. Moved second half of the test to another test (rpl_bug26395)
>     2. Improved comments.
>     3. Extended the mixed transaction test to include also innodb.
>     4. Used 'source include/diff_tables.inc' instead of listing
>     several identical tables in the result file.
> === added file 'mysql-test/suite/rpl/r/rpl_bug26395.result'
> --- a/mysql-test/suite/rpl/r/rpl_bug26395.result	1970-01-01 00:00:00 +0000
> +++ b/mysql-test/suite/rpl/r/rpl_bug26395.result	2008-06-17 13:59:41 +0000
> @@ -0,0 +1,42 @@
> +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;
> +==== Initialize ====
> +[on master]
> +CREATE TABLE tinnodb (a INT) ENGINE = INNODB;
> +SHOW CREATE TABLE tinnodb;
> +Table	Create Table
> +tinnodb	CREATE TABLE `tinnodb` (
> +  `a` int(11) DEFAULT NULL
> +) ENGINE=InnoDB DEFAULT CHARSET=latin1
> +==== Test ====
> +INSERT INTO tinnodb VALUES (1);
> +SELECT * FROM tinnodb ORDER BY a;
> +a
> +1
> +[on slave]
> +==== Verify results on slave ====
> +show binlog events;
> +Log_name	Pos	Event_type	Server_id	End_log_pos	Info
> +slave-bin.000001	4	Format_desc	2	107	Server ver: 6.0.6-alpha-debug-log, Binlog ver:
> 4
> +slave-bin.000001	107	Query	2	217	use `test`; drop table if exists
> t1,t2,t3,t4,t5,t6,t7,t8,t9
> +slave-bin.000001	217	Query	1	324	use `test`; CREATE TABLE tinnodb (a INT) ENGINE =
> INNODB
> +STOP SLAVE;
> +SELECT "" AS Slave_IO_State;
> +Slave_IO_State
> +
> +SELECT "" AS Last_SQL_Error;
> +Last_SQL_Error
> +
> +SELECT "" AS Last_IO_Error;
> +Last_IO_Error
> +
> +SELECT * FROM tinnodb ORDER BY a;
> +a
> +==== Clean up ====
> +[on master]
> +DROP TABLE tinnodb;
> +DROP TABLE tinnodb;
>
> === added file 'mysql-test/suite/rpl/t/rpl_bug26395-master.opt'
> --- a/mysql-test/suite/rpl/t/rpl_bug26395-master.opt	1970-01-01 00:00:00 +0000
> +++ b/mysql-test/suite/rpl/t/rpl_bug26395-master.opt	2008-06-17 13:59:41 +0000
> @@ -0,0 +1 @@
> +--innodb --debug=d,do_not_write_xid
>
> === added file 'mysql-test/suite/rpl/t/rpl_bug26395-slave.opt'
> --- a/mysql-test/suite/rpl/t/rpl_bug26395-slave.opt	1970-01-01 00:00:00 +0000
> +++ b/mysql-test/suite/rpl/t/rpl_bug26395-slave.opt	2008-06-17 13:59:41 +0000
> @@ -0,0 +1 @@
> +--innodb
>
> === added file 'mysql-test/suite/rpl/t/rpl_bug26395.test'
> --- a/mysql-test/suite/rpl/t/rpl_bug26395.test	1970-01-01 00:00:00 +0000
> +++ b/mysql-test/suite/rpl/t/rpl_bug26395.test	2008-06-17 13:59:41 +0000
> @@ -0,0 +1,85 @@
> +# ==== Purpose ====
> +#
> +# Tests that an autocommitted XA transaction where the master crashes
> +# just before writing the XID log event is executed correctly.  The
> +# master rolls back, so the slave should not execute statement.
> +#
> +#
> +# ==== Method ====
> +#
> +# We want master to be alive sot that it can replicate the statement
> +# to the slave. So in the test case, we must therefore not crash the
> +# master. Instead, we fake the crash by just not writing the XID event
> +# to the binlog. This is done by the --debug=d,do_not_write_xid flag
> +# in the .opt file. This, in turn, requires us to do 'source
> +# include_have_debug.inc'

the opt file is an obsolate idea.
set @@debug='d,...' in the test itself is more flexible and should not
force the servers to restart.

Feel free to change, if you think this is a good idea.

> +#
> +# So, unlike if the master had crashed, the master *will* execute the
> +# statement. But the slave should not execute it. Hence, after the
> +# first test is executed, the expected result on master is a table
> +# with one row, and on slave a table with no rows.
> +#
> +# To simulate the slave correctly, we wait until everything up to the
> +# XID is replicated. We cannot sync_slave_with_master, because that
> +# would wait for the transaction to end. Instead, we wait for
> +# "sufficiently long time". 

Actually we can eliminate waiting with --sleep in favor to
wait for Read_Master_Log_Pos value corresponding to the INSERT
of the removed xa-commit.

I suggest doing this.

>  Then we stop the slave.  While this does
> +# introduce nondeterminism in the test, the nondeterminism in this
> +# case is harmless, since the slave should not execute the event in
> +# either case.
> +#
> +#
> +# ==== Related bugs ====
> +#
> +# BUG#26395: if crash during autocommit update to transactional table on master,
> slave fails
> +
> +source include/have_innodb.inc;
> +source include/have_debug.inc;
> +source include/master-slave.inc;
> +
> +
> +--echo ==== Initialize ====
> +
> +--echo [on master]
> +--connection master
> +
> +CREATE TABLE tinnodb (a INT) ENGINE = INNODB;
> +SHOW CREATE TABLE tinnodb;
> +
> +
> +--echo ==== Test ====
> +
> +INSERT INTO tinnodb VALUES (1);
> +SELECT * FROM tinnodb ORDER BY a;
> +
> +--echo [on slave]
> +--connection slave
> +

> +sleep 5;

Wait for Read_Master_Log_Pos instead of the sleep.
Sure, this waiting should be prepared with waiting for injector has
completed with ndb events extraction on the master.

> +
> +
> +--echo ==== Verify results on slave ====
> +
> +show binlog events;
> +
> +STOP SLAVE;
> +source include/wait_for_slave_to_stop.inc;
> +let $tmp= query_get_value("SHOW SLAVE STATUS", Slave_IO_State, 1);
> +eval SELECT "$tmp" AS Slave_IO_State;
> +let $tmp= query_get_value("SHOW SLAVE STATUS", Last_SQL_Error, 1);
> +eval SELECT "$tmp" AS Last_SQL_Error;
> +let $tmp= query_get_value("SHOW SLAVE STATUS", Last_IO_Error, 1);
> +eval SELECT "$tmp" AS Last_IO_Error;
> +SELECT * FROM tinnodb ORDER BY a;
> +
> +
> +--echo ==== Clean up ====
> +
> +# Easiest to clean up master and slave separately, without
> +# replication, since master and slave are out of sync.
> +
> +--echo [on master]
> +connection master;
> +DROP TABLE tinnodb;
> +
> +connection slave;
> +DROP TABLE tinnodb;
>
> === modified file 'mysql-test/suite/rpl_ndb/r/rpl_ndb_transaction.result'
> --- a/mysql-test/suite/rpl_ndb/r/rpl_ndb_transaction.result	2008-01-13 21:13:17
> +0000
> +++ b/mysql-test/suite/rpl_ndb/r/rpl_ndb_transaction.result	2008-06-17 13:59:41
> +0000
> @@ -22,89 +22,244 @@ Table	Create Table
>  tndb	CREATE TABLE `tndb` (
>    `a` int(11) DEFAULT NULL
>  ) ENGINE=ndbcluster DEFAULT CHARSET=latin1
> -==== Test 1: Non-XA Engines ====
> ---- on master ---
> +[on master]
>  SET AUTOCOMMIT = 1;
> -INSERT INTO tndb VALUES (1);
> +==== Single-engine transactions ====
> +---- autocommitted ----
>  INSERT INTO tmyisam VALUES (1);
> -BEGIN;
> -INSERT INTO tndb VALUES (2);
> +INSERT INTO tinnodb VALUES (2);
>  INSERT INTO tndb VALUES (3);
> +---- committed ----
> +BEGIN;
> +INSERT INTO tmyisam VALUES (4);
> +INSERT INTO tmyisam VALUES (5);
>  COMMIT;
>  BEGIN;
> -INSERT INTO tmyisam VALUES (2);
> -INSERT INTO tmyisam VALUES (3);
> +INSERT INTO tinnodb VALUES (6);
> +INSERT INTO tinnodb VALUES (7);
>  COMMIT;
>  BEGIN;
> -INSERT INTO tndb VALUES (4);
> -INSERT INTO tmyisam VALUES (4);
> +INSERT INTO tndb VALUES (8);
> +INSERT INTO tndb VALUES (9);
>  COMMIT;
> +---- rolled back ----
>  BEGIN;
> -INSERT INTO tndb VALUES (5);
> -INSERT INTO tndb VALUES (6);
> +INSERT INTO tmyisam VALUES (10);
> +INSERT INTO tmyisam VALUES (11);
>  ROLLBACK;
> +Warnings:
> +Warning	1196	Some non-transactional changed tables couldn't be rolled back
>  BEGIN;
> -INSERT INTO tmyisam VALUES (5);
> -INSERT INTO tmyisam VALUES (6);
> +INSERT INTO tinnodb VALUES (12);
> +INSERT INTO tinnodb VALUES (13);
> +ROLLBACK;
> +BEGIN;
> +INSERT INTO tndb VALUES (14);
> +INSERT INTO tndb VALUES (15);
> +ROLLBACK;
> +==== MyISAM + InnoDB ====
> +---- committed ----
> +BEGIN;
> +INSERT INTO tmyisam VALUES (16);
> +INSERT INTO tinnodb VALUES (17);
> +COMMIT;
> +BEGIN;
> +INSERT INTO tinnodb VALUES (18);
> +INSERT INTO tmyisam VALUES (19);
> +COMMIT;
> +---- rolled back ----
> +BEGIN;
> +INSERT INTO tmyisam VALUES (20);
> +INSERT INTO tinnodb VALUES (21);
>  ROLLBACK;
>  Warnings:
>  Warning	1196	Some non-transactional changed tables couldn't be rolled back
>  BEGIN;
> -INSERT INTO tndb VALUES (7);
> -INSERT INTO tmyisam VALUES (7);
> +INSERT INTO tinnodb VALUES (22);
> +INSERT INTO tmyisam VALUES (23);
>  ROLLBACK;
>  Warnings:
>  Warning	1196	Some non-transactional changed tables couldn't be rolled back
> -SELECT * FROM tndb ORDER BY a;
> -a
> -1
> -2
> -3
> -4
> +==== MyISAM + NDB ====
> +---- committed ----
> +BEGIN;
> +INSERT INTO tmyisam VALUES (24);
> +INSERT INTO tndb VALUES (25);
> +COMMIT;
> +BEGIN;
> +INSERT INTO tndb VALUES (26);
> +INSERT INTO tmyisam VALUES (27);
> +COMMIT;
> +---- rolled back ----
> +BEGIN;
> +INSERT INTO tmyisam VALUES (28);
> +INSERT INTO tndb VALUES (29);
> +ROLLBACK;
> +Warnings:
> +Warning	1196	Some non-transactional changed tables couldn't be rolled back
> +BEGIN;
> +INSERT INTO tndb VALUES (30);
> +INSERT INTO tmyisam VALUES (31);
> +ROLLBACK;
> +Warnings:
> +Warning	1196	Some non-transactional changed tables couldn't be rolled back
> +==== InnoDB + NDB ====
> +---- committed ----
> +BEGIN;
> +INSERT INTO tinnodb VALUES (32);
> +INSERT INTO tndb VALUES (33);
> +COMMIT;
> +BEGIN;
> +INSERT INTO tndb VALUES (34);
> +INSERT INTO tinnodb VALUES (35);
> +COMMIT;
> +---- rolled back ----
> +BEGIN;
> +INSERT INTO tinnodb VALUES (36);
> +INSERT INTO tndb VALUES (37);
> +ROLLBACK;
> +BEGIN;
> +INSERT INTO tndb VALUES (38);
> +INSERT INTO tinnodb VALUES (39);
> +ROLLBACK;
> +==== MyISAM + InnoDB + NDB ====
> +---- committed ----
> +BEGIN;
> +INSERT INTO tmyisam VALUES (40);
> +INSERT INTO tinnodb VALUES (41);
> +INSERT INTO tndb VALUES (42);
> +COMMIT;
> +BEGIN;
> +INSERT INTO tmyisam VALUES (43);
> +INSERT INTO tndb VALUES (44);
> +INSERT INTO tinnodb VALUES (45);
> +COMMIT;
> +BEGIN;
> +INSERT INTO tinnodb VALUES (46);
> +INSERT INTO tmyisam VALUES (47);
> +INSERT INTO tndb VALUES (48);
> +COMMIT;
> +BEGIN;
> +INSERT INTO tinnodb VALUES (49);
> +INSERT INTO tndb VALUES (50);
> +INSERT INTO tmyisam VALUES (51);
> +COMMIT;
> +BEGIN;
> +INSERT INTO tndb VALUES (52);
> +INSERT INTO tmyisam VALUES (53);
> +INSERT INTO tinnodb VALUES (54);
> +COMMIT;
> +BEGIN;
> +INSERT INTO tndb VALUES (55);
> +INSERT INTO tinnodb VALUES (56);
> +INSERT INTO tmyisam VALUES (57);
> +COMMIT;
> +---- rolled back ----
> +BEGIN;
> +INSERT INTO tmyisam VALUES (58);
> +INSERT INTO tinnodb VALUES (59);
> +INSERT INTO tndb VALUES (60);
> +ROLLBACK;
> +Warnings:
> +Warning	1196	Some non-transactional changed tables couldn't be rolled back
> +BEGIN;
> +INSERT INTO tmyisam VALUES (61);
> +INSERT INTO tndb VALUES (62);
> +INSERT INTO tinnodb VALUES (63);
> +ROLLBACK;
> +Warnings:
> +Warning	1196	Some non-transactional changed tables couldn't be rolled back
> +BEGIN;
> +INSERT INTO tinnodb VALUES (64);
> +INSERT INTO tmyisam VALUES (65);
> +INSERT INTO tndb VALUES (66);
> +ROLLBACK;
> +Warnings:
> +Warning	1196	Some non-transactional changed tables couldn't be rolled back
> +BEGIN;
> +INSERT INTO tinnodb VALUES (67);
> +INSERT INTO tndb VALUES (68);
> +INSERT INTO tmyisam VALUES (69);
> +ROLLBACK;
> +Warnings:
> +Warning	1196	Some non-transactional changed tables couldn't be rolled back
> +BEGIN;
> +INSERT INTO tndb VALUES (70);
> +INSERT INTO tmyisam VALUES (71);
> +INSERT INTO tinnodb VALUES (72);
> +ROLLBACK;
> +Warnings:
> +Warning	1196	Some non-transactional changed tables couldn't be rolled back
> +BEGIN;
> +INSERT INTO tndb VALUES (73);
> +INSERT INTO tinnodb VALUES (74);
> +INSERT INTO tmyisam VALUES (75);
> +ROLLBACK;
> +Warnings:
> +Warning	1196	Some non-transactional changed tables couldn't be rolled back
> +==== Verify the result ====
>  SELECT * FROM tmyisam ORDER BY a;
>  a
>  1
> -2
> -3
>  4
>  5
> -6
> -7
> ---- on slave ---
> -SELECT * FROM tndb ORDER BY a;
> -a
> -1
> -2
> -3
> -4
> -SELECT * FROM tmyisam ORDER BY a;
> +10
> +11
> +16
> +19
> +20
> +23
> +24
> +27
> +28
> +31
> +40
> +43
> +47
> +51
> +53
> +57
> +58
> +61
> +65
> +69
> +71
> +75
> +SELECT * FROM tinnodb ORDER BY a;
>  a
> -1
>  2
> -3
> -4
> -5
>  6
>  7
> -==== Test 2: Master crash before writing XID event on XA engine ====
> ---- on master ---
> -INSERT INTO tinnodb VALUES (1);
> -SELECT * FROM tinnodb ORDER BY a;
> -a
> -1
> ---- on slave ---
> -STOP SLAVE;
> -SELECT "" AS Slave_IO_State;
> -Slave_IO_State
> -
> -SELECT "" AS Last_SQL_Error;
> -Last_SQL_Error
> -
> -SELECT "" AS Last_IO_Error;
> -Last_IO_Error
> -
> -SELECT * FROM tinnodb ORDER BY a;
> +17
> +18
> +32
> +35
> +41
> +45
> +46
> +49
> +54
> +56
> +SELECT * FROM tndb ORDER BY a;
>  a
> ---- on master ---
> -DROP TABLE tmyisam, tinnodb, tndb;
> +3
> +8
> +9
> +25
> +26
> +33
> +34
> +42
> +44
> +48
> +50
> +52
> +55
> +[on slave]
> +Comparing tables master:test.tmyisam and master:test.tmyisam
> +Comparing tables master:test.tinnodb and master:test.tinnodb
> +Comparing tables master:test.tndb and master:test.tndb
> +==== Clean up ====
> +[on master]
>  DROP TABLE tmyisam, tinnodb, tndb;
> +[on slave]
>
> === modified file 'mysql-test/suite/rpl_ndb/t/rpl_ndb_transaction-master.opt'
> --- a/mysql-test/suite/rpl_ndb/t/rpl_ndb_transaction-master.opt	2007-12-14 13:40:45
> +0000
> +++ b/mysql-test/suite/rpl_ndb/t/rpl_ndb_transaction-master.opt	2008-06-17 13:59:41
> +0000
> @@ -1 +1 @@
> ---innodb --debug=d,do_not_write_xid
> +--innodb
>
> === modified file 'mysql-test/suite/rpl_ndb/t/rpl_ndb_transaction.test'
> --- a/mysql-test/suite/rpl_ndb/t/rpl_ndb_transaction.test	2008-06-10 22:27:52 +0000
> +++ b/mysql-test/suite/rpl_ndb/t/rpl_ndb_transaction.test	2008-06-17 13:59:41 +0000
> @@ -1,14 +1,23 @@
> -# Tests that transactions are replicated correctly, with various
> -# combinations of non-transactional and transactional non-XA tables.
> -# Also tests that an XA transaction where the master crashes just
> -# before writing the XID log event is executed correctly.  See below
> -# for implementation details.
> +# ==== Purpose ====
> +#
> +# Tests that transactions containing multiple table types are
> +# replicated correctly to the slave.
> +#
> +#
> +# ==== Method ====
> +#
> +# Try all combinations of committed/rollback transactions involving
> +# myisam, innodb, ndb, or combinations of them.
> +#

At it's claimed `all combinations' should not we start at least one
transaction with SET AUTOCOMMIT=0?


> +#
> +# ==== Related bugs ====
> +#
> +# BUG#26395: if crash during autocommit update to transactional table on master,
> slave fails
> +
>  
>  source include/ndb_master-slave.inc;
>  source include/have_innodb.inc;
>  source include/have_ndb.inc;
> -source include/have_innodb.inc;
> -source include/have_debug.inc;
>  
>  CREATE TABLE tmyisam (a int) ENGINE = MYISAM;
>  CREATE TABLE tinnodb (a int) ENGINE = INNODB;
> @@ -19,115 +28,243 @@ SHOW CREATE TABLE tinnodb;
>  SHOW CREATE TABLE tndb;
>  
>  
> ---echo ==== Test 1: Non-XA Engines ====
> -# Test that everything works fine with non-XA engines.  We just try
> -# all ways to do transactions involving ndb and/or myisam, with
> -# rollback or commit.
> -
> ---echo --- on master ---
> +--echo [on master]
>  
>  SET AUTOCOMMIT = 1;
>  
> -INSERT INTO tndb VALUES (1);
> +
> +--echo ==== Single-engine transactions ====
> +
> +--echo ---- autocommitted ----
> +
>  INSERT INTO tmyisam VALUES (1);
> +INSERT INTO tinnodb VALUES (2);
> +INSERT INTO tndb VALUES (3);
> +
> +--echo ---- committed ----
>  
>  BEGIN;
> -INSERT INTO tndb VALUES (2);
> -INSERT INTO tndb VALUES (3);
> +INSERT INTO tmyisam VALUES (4);
> +INSERT INTO tmyisam VALUES (5);
>  COMMIT;
>  
>  BEGIN;
> -INSERT INTO tmyisam VALUES (2);
> -INSERT INTO tmyisam VALUES (3);
> +INSERT INTO tinnodb VALUES (6);
> +INSERT INTO tinnodb VALUES (7);
>  COMMIT;
>  
>  BEGIN;
> -INSERT INTO tndb VALUES (4);
> -INSERT INTO tmyisam VALUES (4);
> +INSERT INTO tndb VALUES (8);
> +INSERT INTO tndb VALUES (9);
>  COMMIT;
>  
> +--echo ---- rolled back ----
> +
>  BEGIN;
> -INSERT INTO tndb VALUES (5);
> -INSERT INTO tndb VALUES (6);
> +INSERT INTO tmyisam VALUES (10);
> +INSERT INTO tmyisam VALUES (11);
>  ROLLBACK;
>  
>  BEGIN;
> -INSERT INTO tmyisam VALUES (5);
> -INSERT INTO tmyisam VALUES (6);
> -#--warning 1196
> +INSERT INTO tinnodb VALUES (12);
> +INSERT INTO tinnodb VALUES (13);
>  ROLLBACK;
>  
>  BEGIN;
> -INSERT INTO tndb VALUES (7);
> -INSERT INTO tmyisam VALUES (7);
> -#--warning 1196
> +INSERT INTO tndb VALUES (14);
> +INSERT INTO tndb VALUES (15);
>  ROLLBACK;
>  
> -SELECT * FROM tndb ORDER BY a;
> -SELECT * FROM tmyisam ORDER BY a;
>  
> ---echo --- on slave ---
> ---sync_slave_with_master
> -SELECT * FROM tndb ORDER BY a;
> -SELECT * FROM tmyisam ORDER BY a;
> +--echo ==== MyISAM + InnoDB ====
>  
> +--echo ---- committed ----
> +
> +BEGIN;
> +INSERT INTO tmyisam VALUES (16);
> +INSERT INTO tinnodb VALUES (17);
> +COMMIT;
>  
> ---echo ==== Test 2: Master crash before writing XID event on XA engine ====
> -# We now want to test the following scenario, to verify that BUG#26395
> -# has been fixed:
> -
> -#   "master and slave have a transactional table that uses XA. Master
> -#    has AUTOCOMMIT on and executes a statement (in this case an
> -#    INSERT). Master crashes just before writing the XID event."
> -
> -# In this scenario, master will roll back, so slave should not execute
> -# the statement, and slave should roll back later when master is
> -# restarted.
> -
> -# However, we want the master to be alive so that we are sure it
> -# replicates the statement to the slave. So in the test case, we must
> -# therefore not crash the master. Instead, we fake the crash by just
> -# not writing the XID event to the binlog. This is done by the
> -# --debug=d,do_not_write_xid flag in the .opt file.
> -
> -# So, unlike if the master had crashed, the master *will* execute the
> -# statement. But the slave should not execute it. Hence, after the
> -# first test is executed, the expected result on master is a table
> -# with one row, and on slave a table with no rows.
> -
> -# To simulate the slave correctly, we wait until everything up to the
> -# XID is replicated. We cannot sync_slave_with_master, because that
> -# would wait for the transaction to end. Instead, we wait for
> -# "sufficiently long time". Then we stop the slave.
> +BEGIN;
> +INSERT INTO tinnodb VALUES (18);
> +INSERT INTO tmyisam VALUES (19);
> +COMMIT;
>  
> -# Note: since this puts the master binlog in an inconsistent state,
> -# this should be the last test of the file.
> +--echo ---- rolled back ----
>  
> ---echo --- on master ---
> ---connection master
> +BEGIN;
> +INSERT INTO tmyisam VALUES (20);
> +INSERT INTO tinnodb VALUES (21);
> +ROLLBACK;
>  
> -INSERT INTO tinnodb VALUES (1);
> -SELECT * FROM tinnodb ORDER BY a;
> +BEGIN;
> +INSERT INTO tinnodb VALUES (22);
> +INSERT INTO tmyisam VALUES (23);
> +ROLLBACK;
> +
> +
> +--echo ==== MyISAM + NDB ====
> +
> +--echo ---- committed ----
> +
> +BEGIN;
> +INSERT INTO tmyisam VALUES (24);
> +INSERT INTO tndb VALUES (25);
> +COMMIT;
> +
> +BEGIN;
> +INSERT INTO tndb VALUES (26);
> +INSERT INTO tmyisam VALUES (27);
> +COMMIT;
> +
> +--echo ---- rolled back ----
> +
> +BEGIN;
> +INSERT INTO tmyisam VALUES (28);
> +INSERT INTO tndb VALUES (29);
> +ROLLBACK;
> +
> +BEGIN;
> +INSERT INTO tndb VALUES (30);
> +INSERT INTO tmyisam VALUES (31);
> +ROLLBACK;
> +
> +
> +--echo ==== InnoDB + NDB ====
> +
> +--echo ---- committed ----
> +
> +BEGIN;
> +INSERT INTO tinnodb VALUES (32);
> +INSERT INTO tndb VALUES (33);
> +COMMIT;
> +
> +BEGIN;
> +INSERT INTO tndb VALUES (34);
> +INSERT INTO tinnodb VALUES (35);
> +COMMIT;
> +
> +--echo ---- rolled back ----
> +
> +BEGIN;
> +INSERT INTO tinnodb VALUES (36);
> +INSERT INTO tndb VALUES (37);
> +ROLLBACK;
> +
> +BEGIN;
> +INSERT INTO tndb VALUES (38);
> +INSERT INTO tinnodb VALUES (39);
> +ROLLBACK;
> +
> +
> +--echo ==== MyISAM + InnoDB + NDB ====
> +
> +--echo ---- committed ----
> +
> +BEGIN;
> +INSERT INTO tmyisam VALUES (40);
> +INSERT INTO tinnodb VALUES (41);
> +INSERT INTO tndb VALUES (42);
> +COMMIT;
> +
> +BEGIN;
> +INSERT INTO tmyisam VALUES (43);
> +INSERT INTO tndb VALUES (44);
> +INSERT INTO tinnodb VALUES (45);
> +COMMIT;
> +
> +BEGIN;
> +INSERT INTO tinnodb VALUES (46);
> +INSERT INTO tmyisam VALUES (47);
> +INSERT INTO tndb VALUES (48);
> +COMMIT;
> +
> +BEGIN;
> +INSERT INTO tinnodb VALUES (49);
> +INSERT INTO tndb VALUES (50);
> +INSERT INTO tmyisam VALUES (51);
> +COMMIT;
> +
> +BEGIN;
> +INSERT INTO tndb VALUES (52);
> +INSERT INTO tmyisam VALUES (53);
> +INSERT INTO tinnodb VALUES (54);
> +COMMIT;
> +
> +BEGIN;
> +INSERT INTO tndb VALUES (55);
> +INSERT INTO tinnodb VALUES (56);
> +INSERT INTO tmyisam VALUES (57);
> +COMMIT;
> +
> +--echo ---- rolled back ----
> +
> +BEGIN;
> +INSERT INTO tmyisam VALUES (58);
> +INSERT INTO tinnodb VALUES (59);
> +INSERT INTO tndb VALUES (60);
> +ROLLBACK;
>  
> ---echo --- on slave ---
> ---connection slave
> ---sleep 3
> -STOP SLAVE;
> -source include/wait_for_slave_to_stop.inc;
> -let $tmp= query_get_value("SHOW SLAVE STATUS", Slave_IO_State, 1);
> -eval SELECT "$tmp" AS Slave_IO_State;
> -let $tmp= query_get_value("SHOW SLAVE STATUS", Last_SQL_Error, 1);
> -eval SELECT "$tmp" AS Last_SQL_Error;
> -let $tmp= query_get_value("SHOW SLAVE STATUS", Last_IO_Error, 1);
> -eval SELECT "$tmp" AS Last_IO_Error;
> +BEGIN;
> +INSERT INTO tmyisam VALUES (61);
> +INSERT INTO tndb VALUES (62);
> +INSERT INTO tinnodb VALUES (63);
> +ROLLBACK;
> +
> +BEGIN;
> +INSERT INTO tinnodb VALUES (64);
> +INSERT INTO tmyisam VALUES (65);
> +INSERT INTO tndb VALUES (66);
> +ROLLBACK;
> +
> +BEGIN;
> +INSERT INTO tinnodb VALUES (67);
> +INSERT INTO tndb VALUES (68);
> +INSERT INTO tmyisam VALUES (69);
> +ROLLBACK;
> +
> +BEGIN;
> +INSERT INTO tndb VALUES (70);
> +INSERT INTO tmyisam VALUES (71);
> +INSERT INTO tinnodb VALUES (72);
> +ROLLBACK;
> +
> +BEGIN;
> +INSERT INTO tndb VALUES (73);
> +INSERT INTO tinnodb VALUES (74);
> +INSERT INTO tmyisam VALUES (75);
> +ROLLBACK;
> +
> +
> +--echo ==== Verify the result ====
> +
> +SELECT * FROM tmyisam ORDER BY a;
>  SELECT * FROM tinnodb ORDER BY a;
> +SELECT * FROM tndb ORDER BY a;
> +
> +--echo [on slave]
> +--sync_slave_with_master
>  
> -# Clean up. We cannot do it on master and replicate over, because
> -# master binlog is in a bad state after last test. So we do it both on
> -# master and on slave.
> ---echo --- on master ---
> +let $diff_table_1=master:test.tmyisam;
> +let $diff_table_2=master:test.tmyisam;
> +source include/diff_tables.inc;
> +
> +let $diff_table_1=master:test.tinnodb;
> +let $diff_table_2=master:test.tinnodb;
> +source include/diff_tables.inc;
> +
> +let $diff_table_1=master:test.tndb;
> +let $diff_table_2=master:test.tndb;
> +source include/diff_tables.inc;
> +
> +
> +--echo ==== Clean up ====
> +
> +--echo [on master]
>  connection master;
>  DROP TABLE tmyisam, tinnodb, tndb;
>  
> -connection slave;
> -DROP TABLE tmyisam, tinnodb, tndb;
> +--echo [on slave]
> +sync_slave_with_master;
> +
> +source include/master-slave-end.inc;

The last thing to note, it should be worned that no more test should be
added after the emulation of the crash part. Because binlogging and
reading it gets crewed up to some degree (or one needs to prove that
it is safe indeed).

regards,

Andrei