Hi Andrei,
Thanks for a thorough review! I agree to most of your suggestions,
except I think the test has to be split into two files, and they should
be in the rpl_ndb suite and the rpl suite, respectively. Also, we can't
assume that ndb modifies its system tables at any particular point -
that happens nondeterministically. See details below.
Andrei Elkin wrote:
> 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.
The problem is that the event shows up at a nondeterministic point in
the binlog. Sometimes there is an event, sometimes not. So it is
difficult to come up with a condition to wait for. I also think it makes
sense to split the test into two parts, since they are quite different.
>> 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.
I have discussed this with Magnus and subsequently with Mats. The
problem is that different suites have different server options. E.g.,
tests in the rpl suite start up a slave server, whereas tests in binlog
do not, but they run with --have-binlog, etc. Therefore, it does not
make sense to have a specific bugs suite, at least with the way it works
now. Mats agreed on IRC to let the test stay in rpl. If we later change
so that the tests don't rely on per-suite server options, we can move
the test to the bug suite at that time.
> 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
mtr only sets up ndb and replication for tests in the rpl_ndb suite. So
the test has to stay in rpl_ndb.
>
> 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.
OK, good, I didn't know about this feature. I'll fix this.
>> +#
>> +# 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.
Good point, done.
>> 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?
Right, good point, will add that.
>
>> +#
>> +# ==== 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).
OK, done.
>
> regards,
>
> Andrei
--
Sven Sandberg, Software Engineer
MySQL AB, www.mysql.com
