#At file:///home/sven/bzr/b37373-rpl_ndb_transaction/5.1-rpl/
2618 Sven Sandberg 2008-07-03
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
binlog updates in ndb system tables at a nondeterministic
time point after an ndb table update has been committed.
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.
added:
mysql-test/include/save_master_pos.inc
mysql-test/include/wait_for_slave_io_to_sync.inc
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
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/include/save_master_pos.inc
Like the test language's built-in save_master_pos, but saves it in a test
language variable so it's accessible to tests. This was needed to
implement wait_for_slave_io_to_sync.inc.
mysql-test/include/wait_for_slave_io_to_sync.inc
Like sync_slave_with_master, but only waits until the IO thread has
synced; the SQL thread may still be behind.
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.
- Improved the test case: instead of using a sleep,
it waits for the slave's io thread to sync up to a proper
position, and for the slave's sql thread to sync up to
another position.
- Added a warning that no more tests should be added at the
end of the file.
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
- Moved second half of the test to another test (rpl_bug26395)
- Improved comments.
- Extended the mixed transaction test to include also innodb.
- Used 'source include/diff_tables.inc' instead of listing
several identical tables in the result file.
=== added file 'mysql-test/include/save_master_pos.inc'
--- a/mysql-test/include/save_master_pos.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/save_master_pos.inc 2008-07-03 09:28:39 +0000
@@ -0,0 +1,16 @@
+# ==== Purpose ====
+#
+# Saves the master position in the variables
+# $saved_master_file and $saved_master_position.
+#
+# ==== Usage ====
+#
+# source include/save_master_pos.inc;
+#
+# Must be executed on a master.
+#
+# Usually followed by source include/wait_for_slave_io_to_sync.inc; or
+# some other script that uses the saved position.
+
+let $saved_master_file= query_get_value("SHOW MASTER STATUS", File, 1);
+let $saved_master_pos= query_get_value("SHOW MASTER STATUS", Position, 1);
=== added file 'mysql-test/include/wait_for_slave_io_to_sync.inc'
--- a/mysql-test/include/wait_for_slave_io_to_sync.inc 1970-01-01 00:00:00 +0000
+++ b/mysql-test/include/wait_for_slave_io_to_sync.inc 2008-07-03 09:28:39 +0000
@@ -0,0 +1,40 @@
+# ==== Purpose ====
+#
+# Waits until the slave IO thread has been synced, i.e., all events
+# have been copied over to slave. Does not care if the SQL thread is
+# in sync.
+#
+#
+# ==== Usage ====
+#
+# Syncs the slave to the position saved in the last call to
+# include/save_master_pos.inc.
+#
+# Must be called on the slave.
+
+let $_error= `SELECT '$saved_master_pos' = '' OR '$saved_master_file' = ''`;
+if ($_error)
+{
+ --echo ERROR IN TEST SCRIPT: you must source include/save_master_pos.inc before include/wait_for_slave_io_to_sync.inc
+ exit;
+}
+
+let $_counter= 300;
+let $_not_done= 0;
+
+while ($_not_done)
+{
+ dec $_counter;
+ if (!$_counter)
+ {
+ --echo ERROR: failed while waiting for slave io to sync. _file=$_file _pos=$_pos saved_master_file=$saved_master_file saved_master_pos=$saved_master_pos
+ query_vertical show slave status;
+ exit;
+ }
+
+ sleep 0.1;
+
+ let $_file= query_get_value("SHOW SLAVE STATUS", Master_Log_File, 1);
+ let $_pos= query_get_value("SHOW SLAVE STATUS", Read_Master_Log_Pos, 1);
+ let $_not_done= `SELECT '$_file' != '$saved_master_file' OR '$_pos' != '$saved_master_pos'`;
+}
=== 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-07-03 09:28:39 +0000
@@ -0,0 +1,40 @@
+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 ====
+set @old_debug= @@debug;
+set @@debug= 'd,do_not_write_xid';
+INSERT INTO tinnodb VALUES (1);
+SELECT * FROM tinnodb ORDER BY a;
+a
+1
+[on slave]
+==== Verify results 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;
+a
+==== Clean up ====
+[on master]
+DROP TABLE tinnodb;
+DROP TABLE tinnodb;
+set @@debug= @old_debug;
=== 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-07-03 09:28:39 +0000
@@ -0,0 +1 @@
+--innodb
=== 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-07-03 09:28:39 +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-07-03 09:28:39 +0000
@@ -0,0 +1,97 @@
+# ==== 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 so that it can replicate the statement to
+# the slave. So in the test case, we must 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. This, in turn, requires us to do 'source
+# include/have_debug.inc'
+#
+# So, unlike if the master had crashed, the master *will* execute the
+# statement. But the slave should not execute it. Hence, after the
+# 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 but
+# not including the XID is replicated. This has to be done with
+# include/wait_for_slave_io_to_sync.inc, not sync_slave_with_master,
+# since the latter waits until the slave *SQL* thread has caught up
+# with the master's position, which it will never do.
+#
+#
+# ==== Related bugs ====
+#
+# BUG#26395: if crash during autocommit update to transactional table on master, slave fails
+
+source include/have_innodb.inc;
+# have_debug is needed since we use the @@debug variable on master
+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 ====
+
+set @old_debug= @@debug;
+set @@debug= 'd,do_not_write_xid';
+
+# Save the position up to which the slave SQL therad should execute.
+save_master_pos;
+
+# Execute query and check that the row made it to the table.
+INSERT INTO tinnodb VALUES (1);
+SELECT * FROM tinnodb ORDER BY a;
+
+# Sync slave's IO thread.
+source include/save_master_pos.inc;
+--echo [on slave]
+--connection slave
+source include/wait_for_slave_io_to_sync.inc;
+
+# Sync slave's SQL thread.
+sync_with_master 0;
+
+
+--echo ==== Verify results on slave ====
+
+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;
+
+set @@debug= @old_debug;
+
+# Warning: do not add more tests here. The binlog is in a bad state.
=== 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-07-03 09:28:39 +0000
@@ -22,89 +22,436 @@ 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]
+==== Single-engine transactions ====
+---- autocommitted ----
SET AUTOCOMMIT = 1;
-INSERT INTO tndb VALUES (1);
-INSERT INTO tmyisam VALUES (1);
-BEGIN;
+INSERT INTO tmyisam VALUES (0);
+INSERT INTO tinnodb VALUES (1);
INSERT INTO tndb VALUES (2);
-INSERT INTO tndb VALUES (3);
-COMMIT;
+---- committed with BEGIN ----
BEGIN;
-INSERT INTO tmyisam VALUES (2);
INSERT INTO tmyisam VALUES (3);
+INSERT INTO tmyisam VALUES (4);
COMMIT;
BEGIN;
-INSERT INTO tndb VALUES (4);
-INSERT INTO tmyisam VALUES (4);
+INSERT INTO tinnodb VALUES (5);
+INSERT INTO tinnodb VALUES (6);
COMMIT;
BEGIN;
-INSERT INTO tndb VALUES (5);
-INSERT INTO tndb VALUES (6);
+INSERT INTO tndb VALUES (7);
+INSERT INTO tndb VALUES (8);
+COMMIT;
+---- rolled back with BEGIN ----
+BEGIN;
+INSERT INTO tmyisam VALUES (9);
+INSERT INTO tmyisam VALUES (10);
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+BEGIN;
+INSERT INTO tinnodb VALUES (11);
+INSERT INTO tinnodb VALUES (12);
ROLLBACK;
BEGIN;
-INSERT INTO tmyisam VALUES (5);
-INSERT INTO tmyisam VALUES (6);
+INSERT INTO tndb VALUES (13);
+INSERT INTO tndb VALUES (14);
+ROLLBACK;
+---- committed with AUTOCOMMIT = 0 ----
+SET AUTOCOMMIT = 0;
+INSERT INTO tmyisam VALUES (15);
+INSERT INTO tmyisam VALUES (16);
+COMMIT;
+INSERT INTO tinnodb VALUES (17);
+INSERT INTO tinnodb VALUES (18);
+COMMIT;
+INSERT INTO tndb VALUES (19);
+INSERT INTO tndb VALUES (20);
+COMMIT;
+---- rolled back with AUTOCOMMIT = 0 ----
+INSERT INTO tmyisam VALUES (21);
+INSERT INTO tmyisam VALUES (22);
ROLLBACK;
Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
+INSERT INTO tinnodb VALUES (23);
+INSERT INTO tinnodb VALUES (24);
+ROLLBACK;
+INSERT INTO tndb VALUES (25);
+INSERT INTO tndb VALUES (26);
+ROLLBACK;
+SET AUTOCOMMIT = 1;
+==== MyISAM + InnoDB ====
+---- committed with BEGIN ----
BEGIN;
-INSERT INTO tndb VALUES (7);
-INSERT INTO tmyisam VALUES (7);
+INSERT INTO tmyisam VALUES (27);
+INSERT INTO tinnodb VALUES (28);
+COMMIT;
+BEGIN;
+INSERT INTO tinnodb VALUES (29);
+INSERT INTO tmyisam VALUES (30);
+COMMIT;
+---- rolled back with BEGIN ----
+BEGIN;
+INSERT INTO tmyisam VALUES (31);
+INSERT INTO tinnodb VALUES (32);
ROLLBACK;
Warnings:
Warning 1196 Some non-transactional changed tables couldn't be rolled back
-SELECT * FROM tndb ORDER BY a;
+BEGIN;
+INSERT INTO tinnodb VALUES (33);
+INSERT INTO tmyisam VALUES (34);
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+---- committed with AUTOCOMMIT = 0 ----
+SET AUTOCOMMIT = 0;
+INSERT INTO tmyisam VALUES (35);
+INSERT INTO tinnodb VALUES (36);
+COMMIT;
+INSERT INTO tinnodb VALUES (37);
+INSERT INTO tmyisam VALUES (38);
+COMMIT;
+---- rolled back with AUTOCOMMIT = 0 ----
+INSERT INTO tmyisam VALUES (39);
+INSERT INTO tinnodb VALUES (40);
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+INSERT INTO tinnodb VALUES (41);
+INSERT INTO tmyisam VALUES (42);
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+SET AUTOCOMMIT = 1;
+==== MyISAM + NDB ====
+---- committed with BEGIN----
+BEGIN;
+INSERT INTO tmyisam VALUES (43);
+INSERT INTO tndb VALUES (44);
+COMMIT;
+BEGIN;
+INSERT INTO tndb VALUES (45);
+INSERT INTO tmyisam VALUES (46);
+COMMIT;
+---- rolled back with BEGIN ----
+BEGIN;
+INSERT INTO tmyisam VALUES (47);
+INSERT INTO tndb VALUES (48);
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+BEGIN;
+INSERT INTO tndb VALUES (49);
+INSERT INTO tmyisam VALUES (50);
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+---- committed with AUTOCOMMIT = 0 ----
+SET AUTOCOMMIT = 0;
+INSERT INTO tmyisam VALUES (51);
+INSERT INTO tndb VALUES (52);
+COMMIT;
+INSERT INTO tndb VALUES (53);
+INSERT INTO tmyisam VALUES (54);
+COMMIT;
+---- rolled back with AUTOCOMMIT = 0 ----
+INSERT INTO tmyisam VALUES (55);
+INSERT INTO tndb VALUES (56);
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+INSERT INTO tndb VALUES (57);
+INSERT INTO tmyisam VALUES (58);
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+SET AUTOCOMMIT = 1;
+==== InnoDB + NDB ====
+---- committed with BEGIN ----
+BEGIN;
+INSERT INTO tinnodb VALUES (59);
+INSERT INTO tndb VALUES (60);
+COMMIT;
+BEGIN;
+INSERT INTO tndb VALUES (61);
+INSERT INTO tinnodb VALUES (62);
+COMMIT;
+---- rolled back with BEGIN ----
+BEGIN;
+INSERT INTO tinnodb VALUES (63);
+INSERT INTO tndb VALUES (64);
+ROLLBACK;
+BEGIN;
+INSERT INTO tndb VALUES (65);
+INSERT INTO tinnodb VALUES (66);
+ROLLBACK;
+---- committed with AUTOCOMMIT = 0 ----
+SET AUTOCOMMIT = 0;
+INSERT INTO tinnodb VALUES (67);
+INSERT INTO tndb VALUES (68);
+COMMIT;
+INSERT INTO tndb VALUES (69);
+INSERT INTO tinnodb VALUES (70);
+COMMIT;
+---- rolled back with AUTOCOMMIT = 0 ----
+INSERT INTO tinnodb VALUES (71);
+INSERT INTO tndb VALUES (72);
+ROLLBACK;
+INSERT INTO tndb VALUES (73);
+INSERT INTO tinnodb VALUES (74);
+ROLLBACK;
+SET AUTOCOMMIT = 1;
+==== MyISAM + InnoDB + NDB ====
+---- committed with BEGIN ----
+BEGIN;
+INSERT INTO tmyisam VALUES (75);
+INSERT INTO tinnodb VALUES (76);
+INSERT INTO tndb VALUES (77);
+COMMIT;
+BEGIN;
+INSERT INTO tmyisam VALUES (78);
+INSERT INTO tndb VALUES (79);
+INSERT INTO tinnodb VALUES (80);
+COMMIT;
+BEGIN;
+INSERT INTO tinnodb VALUES (81);
+INSERT INTO tmyisam VALUES (82);
+INSERT INTO tndb VALUES (83);
+COMMIT;
+BEGIN;
+INSERT INTO tinnodb VALUES (84);
+INSERT INTO tndb VALUES (85);
+INSERT INTO tmyisam VALUES (86);
+COMMIT;
+BEGIN;
+INSERT INTO tndb VALUES (87);
+INSERT INTO tmyisam VALUES (88);
+INSERT INTO tinnodb VALUES (89);
+COMMIT;
+BEGIN;
+INSERT INTO tndb VALUES (90);
+INSERT INTO tinnodb VALUES (91);
+INSERT INTO tmyisam VALUES (92);
+COMMIT;
+---- rolled back with BEGIN ----
+BEGIN;
+INSERT INTO tmyisam VALUES (93);
+INSERT INTO tinnodb VALUES (94);
+INSERT INTO tndb VALUES (95);
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+BEGIN;
+INSERT INTO tmyisam VALUES (96);
+INSERT INTO tndb VALUES (97);
+INSERT INTO tinnodb VALUES (98);
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+BEGIN;
+INSERT INTO tinnodb VALUES (99);
+INSERT INTO tmyisam VALUES (100);
+INSERT INTO tndb VALUES (101);
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+BEGIN;
+INSERT INTO tinnodb VALUES (102);
+INSERT INTO tndb VALUES (103);
+INSERT INTO tmyisam VALUES (104);
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+BEGIN;
+INSERT INTO tndb VALUES (105);
+INSERT INTO tmyisam VALUES (106);
+INSERT INTO tinnodb VALUES (107);
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+BEGIN;
+INSERT INTO tndb VALUES (108);
+INSERT INTO tinnodb VALUES (109);
+INSERT INTO tmyisam VALUES (110);
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+---- committed with AUTOCOMMIT = 0 ----
+SET AUTOCOMMIT = 0;
+INSERT INTO tmyisam VALUES (111);
+INSERT INTO tinnodb VALUES (112);
+INSERT INTO tndb VALUES (113);
+COMMIT;
+INSERT INTO tmyisam VALUES (114);
+INSERT INTO tndb VALUES (115);
+INSERT INTO tinnodb VALUES (116);
+COMMIT;
+INSERT INTO tinnodb VALUES (117);
+INSERT INTO tmyisam VALUES (118);
+INSERT INTO tndb VALUES (119);
+COMMIT;
+INSERT INTO tinnodb VALUES (120);
+INSERT INTO tndb VALUES (121);
+INSERT INTO tmyisam VALUES (122);
+COMMIT;
+INSERT INTO tndb VALUES (123);
+INSERT INTO tmyisam VALUES (124);
+INSERT INTO tinnodb VALUES (125);
+COMMIT;
+INSERT INTO tndb VALUES (126);
+INSERT INTO tinnodb VALUES (127);
+INSERT INTO tmyisam VALUES (128);
+COMMIT;
+---- rolled back with AUTOCOMMIT = 0 ----
+INSERT INTO tmyisam VALUES (129);
+INSERT INTO tinnodb VALUES (130);
+INSERT INTO tndb VALUES (131);
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+INSERT INTO tmyisam VALUES (132);
+INSERT INTO tndb VALUES (133);
+INSERT INTO tinnodb VALUES (134);
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+INSERT INTO tinnodb VALUES (135);
+INSERT INTO tmyisam VALUES (136);
+INSERT INTO tndb VALUES (137);
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+INSERT INTO tinnodb VALUES (138);
+INSERT INTO tndb VALUES (139);
+INSERT INTO tmyisam VALUES (140);
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+INSERT INTO tndb VALUES (141);
+INSERT INTO tmyisam VALUES (142);
+INSERT INTO tinnodb VALUES (143);
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+INSERT INTO tndb VALUES (144);
+INSERT INTO tinnodb VALUES (145);
+INSERT INTO tmyisam VALUES (146);
+ROLLBACK;
+Warnings:
+Warning 1196 Some non-transactional changed tables couldn't be rolled back
+SET AUTOCOMMIT = 1;
+==== Verify the result ====
+SELECT * FROM tmyisam ORDER BY a;
a
-1
-2
+0
3
4
-SELECT * FROM tmyisam ORDER BY a;
+9
+10
+15
+16
+21
+22
+27
+30
+31
+34
+35
+38
+39
+42
+43
+46
+47
+50
+51
+54
+55
+58
+75
+78
+82
+86
+88
+92
+93
+96
+100
+104
+106
+110
+111
+114
+118
+122
+124
+128
+129
+132
+136
+140
+142
+146
+SELECT * FROM tinnodb ORDER BY a;
a
1
-2
-3
-4
5
6
-7
---- on slave ---
+17
+18
+28
+29
+36
+37
+59
+62
+67
+70
+76
+80
+81
+84
+89
+91
+112
+116
+117
+120
+125
+127
SELECT * FROM tndb ORDER BY a;
a
-1
-2
-3
-4
-SELECT * FROM tmyisam 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;
-a
---- on master ---
-DROP TABLE tmyisam, tinnodb, tndb;
+8
+19
+20
+44
+45
+52
+53
+60
+61
+68
+69
+77
+79
+83
+85
+87
+90
+113
+115
+119
+121
+123
+126
+[on slave]
+Comparing tables master:test.tmyisam and slave:test.tmyisam
+Comparing tables master:test.tinnodb and slave:test.tinnodb
+Comparing tables master:test.tndb and slave: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-07-03 09:28:39 +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-04-03 09:50:43 +0000
+++ b/mysql-test/suite/rpl_ndb/t/rpl_ndb_transaction.test 2008-07-03 09:28:39 +0000
@@ -1,13 +1,28 @@
-# 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 the following:
+# - Committed/rollback transactions.
+# - Transactions started by AUTOCOMMIT = 0 or BEGIN.
+# - Transactions using myisam, innodb, or ndb tables, or combinations
+# of them. For combinations, we use the engines in all possible
+# orders.
+# For single-engine transactions, we also try with AUTOCOMMIT = 1.
+#
+#
+# ==== 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_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;
@@ -18,115 +33,416 @@ 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 ---
+--echo ==== Single-engine transactions ====
+
+--echo ---- autocommitted ----
SET AUTOCOMMIT = 1;
-INSERT INTO tndb VALUES (1);
-INSERT INTO tmyisam VALUES (1);
+INSERT INTO tmyisam VALUES (0);
+INSERT INTO tinnodb VALUES (1);
+INSERT INTO tndb VALUES (2);
+
+--echo ---- committed with BEGIN ----
BEGIN;
-INSERT INTO tndb VALUES (2);
-INSERT INTO tndb VALUES (3);
+INSERT INTO tmyisam VALUES (3);
+INSERT INTO tmyisam VALUES (4);
COMMIT;
BEGIN;
-INSERT INTO tmyisam VALUES (2);
-INSERT INTO tmyisam VALUES (3);
+INSERT INTO tinnodb VALUES (5);
+INSERT INTO tinnodb VALUES (6);
COMMIT;
BEGIN;
-INSERT INTO tndb VALUES (4);
-INSERT INTO tmyisam VALUES (4);
+INSERT INTO tndb VALUES (7);
+INSERT INTO tndb VALUES (8);
COMMIT;
+--echo ---- rolled back with BEGIN ----
+
BEGIN;
-INSERT INTO tndb VALUES (5);
-INSERT INTO tndb VALUES (6);
+INSERT INTO tmyisam VALUES (9);
+INSERT INTO tmyisam VALUES (10);
ROLLBACK;
BEGIN;
-INSERT INTO tmyisam VALUES (5);
-INSERT INTO tmyisam VALUES (6);
-#--warning 1196
+INSERT INTO tinnodb VALUES (11);
+INSERT INTO tinnodb VALUES (12);
ROLLBACK;
BEGIN;
-INSERT INTO tndb VALUES (7);
-INSERT INTO tmyisam VALUES (7);
-#--warning 1196
+INSERT INTO tndb VALUES (13);
+INSERT INTO tndb VALUES (14);
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 ---- committed with AUTOCOMMIT = 0 ----
+SET AUTOCOMMIT = 0;
---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.
+INSERT INTO tmyisam VALUES (15);
+INSERT INTO tmyisam VALUES (16);
+COMMIT;
-# Note: since this puts the master binlog in an inconsistent state,
-# this should be the last test of the file.
+INSERT INTO tinnodb VALUES (17);
+INSERT INTO tinnodb VALUES (18);
+COMMIT;
---echo --- on master ---
---connection master
+INSERT INTO tndb VALUES (19);
+INSERT INTO tndb VALUES (20);
+COMMIT;
-INSERT INTO tinnodb VALUES (1);
-SELECT * FROM tinnodb ORDER BY a;
+--echo ---- rolled back with AUTOCOMMIT = 0 ----
+
+INSERT INTO tmyisam VALUES (21);
+INSERT INTO tmyisam VALUES (22);
+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;
+INSERT INTO tinnodb VALUES (23);
+INSERT INTO tinnodb VALUES (24);
+ROLLBACK;
+
+INSERT INTO tndb VALUES (25);
+INSERT INTO tndb VALUES (26);
+ROLLBACK;
+
+SET AUTOCOMMIT = 1;
+
+
+--echo ==== MyISAM + InnoDB ====
+
+--echo ---- committed with BEGIN ----
+
+BEGIN;
+INSERT INTO tmyisam VALUES (27);
+INSERT INTO tinnodb VALUES (28);
+COMMIT;
+
+BEGIN;
+INSERT INTO tinnodb VALUES (29);
+INSERT INTO tmyisam VALUES (30);
+COMMIT;
+
+--echo ---- rolled back with BEGIN ----
+
+BEGIN;
+INSERT INTO tmyisam VALUES (31);
+INSERT INTO tinnodb VALUES (32);
+ROLLBACK;
+
+BEGIN;
+INSERT INTO tinnodb VALUES (33);
+INSERT INTO tmyisam VALUES (34);
+ROLLBACK;
+
+--echo ---- committed with AUTOCOMMIT = 0 ----
+
+SET AUTOCOMMIT = 0;
+
+INSERT INTO tmyisam VALUES (35);
+INSERT INTO tinnodb VALUES (36);
+COMMIT;
+
+INSERT INTO tinnodb VALUES (37);
+INSERT INTO tmyisam VALUES (38);
+COMMIT;
+
+--echo ---- rolled back with AUTOCOMMIT = 0 ----
+
+INSERT INTO tmyisam VALUES (39);
+INSERT INTO tinnodb VALUES (40);
+ROLLBACK;
+
+INSERT INTO tinnodb VALUES (41);
+INSERT INTO tmyisam VALUES (42);
+ROLLBACK;
+
+SET AUTOCOMMIT = 1;
+
+
+--echo ==== MyISAM + NDB ====
+
+--echo ---- committed with BEGIN----
+
+BEGIN;
+INSERT INTO tmyisam VALUES (43);
+INSERT INTO tndb VALUES (44);
+COMMIT;
+
+BEGIN;
+INSERT INTO tndb VALUES (45);
+INSERT INTO tmyisam VALUES (46);
+COMMIT;
+
+--echo ---- rolled back with BEGIN ----
+
+BEGIN;
+INSERT INTO tmyisam VALUES (47);
+INSERT INTO tndb VALUES (48);
+ROLLBACK;
+
+BEGIN;
+INSERT INTO tndb VALUES (49);
+INSERT INTO tmyisam VALUES (50);
+ROLLBACK;
+
+--echo ---- committed with AUTOCOMMIT = 0 ----
+
+SET AUTOCOMMIT = 0;
+
+INSERT INTO tmyisam VALUES (51);
+INSERT INTO tndb VALUES (52);
+COMMIT;
+
+INSERT INTO tndb VALUES (53);
+INSERT INTO tmyisam VALUES (54);
+COMMIT;
+
+--echo ---- rolled back with AUTOCOMMIT = 0 ----
+
+INSERT INTO tmyisam VALUES (55);
+INSERT INTO tndb VALUES (56);
+ROLLBACK;
+
+INSERT INTO tndb VALUES (57);
+INSERT INTO tmyisam VALUES (58);
+ROLLBACK;
+
+SET AUTOCOMMIT = 1;
+
+
+--echo ==== InnoDB + NDB ====
+
+--echo ---- committed with BEGIN ----
+
+BEGIN;
+INSERT INTO tinnodb VALUES (59);
+INSERT INTO tndb VALUES (60);
+COMMIT;
+
+BEGIN;
+INSERT INTO tndb VALUES (61);
+INSERT INTO tinnodb VALUES (62);
+COMMIT;
+
+--echo ---- rolled back with BEGIN ----
+
+BEGIN;
+INSERT INTO tinnodb VALUES (63);
+INSERT INTO tndb VALUES (64);
+ROLLBACK;
+
+BEGIN;
+INSERT INTO tndb VALUES (65);
+INSERT INTO tinnodb VALUES (66);
+ROLLBACK;
+
+--echo ---- committed with AUTOCOMMIT = 0 ----
+
+SET AUTOCOMMIT = 0;
+
+INSERT INTO tinnodb VALUES (67);
+INSERT INTO tndb VALUES (68);
+COMMIT;
+
+INSERT INTO tndb VALUES (69);
+INSERT INTO tinnodb VALUES (70);
+COMMIT;
+
+--echo ---- rolled back with AUTOCOMMIT = 0 ----
+
+INSERT INTO tinnodb VALUES (71);
+INSERT INTO tndb VALUES (72);
+ROLLBACK;
+
+INSERT INTO tndb VALUES (73);
+INSERT INTO tinnodb VALUES (74);
+ROLLBACK;
+
+SET AUTOCOMMIT = 1;
+
+
+--echo ==== MyISAM + InnoDB + NDB ====
+
+--echo ---- committed with BEGIN ----
+
+BEGIN;
+INSERT INTO tmyisam VALUES (75);
+INSERT INTO tinnodb VALUES (76);
+INSERT INTO tndb VALUES (77);
+COMMIT;
+
+BEGIN;
+INSERT INTO tmyisam VALUES (78);
+INSERT INTO tndb VALUES (79);
+INSERT INTO tinnodb VALUES (80);
+COMMIT;
+
+BEGIN;
+INSERT INTO tinnodb VALUES (81);
+INSERT INTO tmyisam VALUES (82);
+INSERT INTO tndb VALUES (83);
+COMMIT;
+
+BEGIN;
+INSERT INTO tinnodb VALUES (84);
+INSERT INTO tndb VALUES (85);
+INSERT INTO tmyisam VALUES (86);
+COMMIT;
+
+BEGIN;
+INSERT INTO tndb VALUES (87);
+INSERT INTO tmyisam VALUES (88);
+INSERT INTO tinnodb VALUES (89);
+COMMIT;
+
+BEGIN;
+INSERT INTO tndb VALUES (90);
+INSERT INTO tinnodb VALUES (91);
+INSERT INTO tmyisam VALUES (92);
+COMMIT;
+
+--echo ---- rolled back with BEGIN ----
+
+BEGIN;
+INSERT INTO tmyisam VALUES (93);
+INSERT INTO tinnodb VALUES (94);
+INSERT INTO tndb VALUES (95);
+ROLLBACK;
+
+BEGIN;
+INSERT INTO tmyisam VALUES (96);
+INSERT INTO tndb VALUES (97);
+INSERT INTO tinnodb VALUES (98);
+ROLLBACK;
+
+BEGIN;
+INSERT INTO tinnodb VALUES (99);
+INSERT INTO tmyisam VALUES (100);
+INSERT INTO tndb VALUES (101);
+ROLLBACK;
+
+BEGIN;
+INSERT INTO tinnodb VALUES (102);
+INSERT INTO tndb VALUES (103);
+INSERT INTO tmyisam VALUES (104);
+ROLLBACK;
+
+BEGIN;
+INSERT INTO tndb VALUES (105);
+INSERT INTO tmyisam VALUES (106);
+INSERT INTO tinnodb VALUES (107);
+ROLLBACK;
+
+BEGIN;
+INSERT INTO tndb VALUES (108);
+INSERT INTO tinnodb VALUES (109);
+INSERT INTO tmyisam VALUES (110);
+ROLLBACK;
+
+--echo ---- committed with AUTOCOMMIT = 0 ----
+
+SET AUTOCOMMIT = 0;
+
+INSERT INTO tmyisam VALUES (111);
+INSERT INTO tinnodb VALUES (112);
+INSERT INTO tndb VALUES (113);
+COMMIT;
+
+INSERT INTO tmyisam VALUES (114);
+INSERT INTO tndb VALUES (115);
+INSERT INTO tinnodb VALUES (116);
+COMMIT;
+
+INSERT INTO tinnodb VALUES (117);
+INSERT INTO tmyisam VALUES (118);
+INSERT INTO tndb VALUES (119);
+COMMIT;
+
+INSERT INTO tinnodb VALUES (120);
+INSERT INTO tndb VALUES (121);
+INSERT INTO tmyisam VALUES (122);
+COMMIT;
+
+INSERT INTO tndb VALUES (123);
+INSERT INTO tmyisam VALUES (124);
+INSERT INTO tinnodb VALUES (125);
+COMMIT;
+
+INSERT INTO tndb VALUES (126);
+INSERT INTO tinnodb VALUES (127);
+INSERT INTO tmyisam VALUES (128);
+COMMIT;
+
+--echo ---- rolled back with AUTOCOMMIT = 0 ----
+
+INSERT INTO tmyisam VALUES (129);
+INSERT INTO tinnodb VALUES (130);
+INSERT INTO tndb VALUES (131);
+ROLLBACK;
+
+INSERT INTO tmyisam VALUES (132);
+INSERT INTO tndb VALUES (133);
+INSERT INTO tinnodb VALUES (134);
+ROLLBACK;
+
+INSERT INTO tinnodb VALUES (135);
+INSERT INTO tmyisam VALUES (136);
+INSERT INTO tndb VALUES (137);
+ROLLBACK;
+
+INSERT INTO tinnodb VALUES (138);
+INSERT INTO tndb VALUES (139);
+INSERT INTO tmyisam VALUES (140);
+ROLLBACK;
+
+INSERT INTO tndb VALUES (141);
+INSERT INTO tmyisam VALUES (142);
+INSERT INTO tinnodb VALUES (143);
+ROLLBACK;
+
+INSERT INTO tndb VALUES (144);
+INSERT INTO tinnodb VALUES (145);
+INSERT INTO tmyisam VALUES (146);
+ROLLBACK;
+
+SET AUTOCOMMIT = 1;
+
+
+--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
+
+let $diff_table_1=master:test.tmyisam;
+let $diff_table_2=slave:test.tmyisam;
+source include/diff_tables.inc;
-# 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.tinnodb;
+let $diff_table_2=slave:test.tinnodb;
+source include/diff_tables.inc;
+
+let $diff_table_1=master:test.tndb;
+let $diff_table_2=slave: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;
| Thread |
|---|
| • bzr commit into mysql-5.1 branch (sven:2618) Bug#37373 | Sven Sandberg | 3 Jul |
