1# Test to test how logging is done depending on the capabilities of
2# the engines. Unfortunately, we don't have a good row-only logging
3# engine, and NDB does not really cut is since it is also
4# self-logging. I'm using it nevertheless.
5
6source include/have_blackhole.inc;
7source include/have_ndb.inc;
8source include/have_log_bin.inc;
9
10call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT");
11
12CREATE TABLE t1m (m INT, n INT) ENGINE=MYISAM;
13CREATE TABLE t1b (b INT, c INT) ENGINE=BLACKHOLE;
14CREATE TABLE t1n (e INT, f INT) ENGINE=NDB;
15
16RESET MASTER;
17
18SET SESSION BINLOG_FORMAT=STATEMENT;
19
20INSERT INTO t1b VALUES (1,1), (1,2), (2,1), (2,2);
21INSERT INTO t1m VALUES (1,1), (1,2), (2,1), (2,2);
22
23UPDATE t1m, t1b SET m = 2, b = 3 WHERE n = c;
24
25# Here and below we need to wait when some event appears in binlog
26# to avoid unsrted mixing local events and from NDB
27let $wait_binlog_event= COMMIT;
28source include/wait_for_binlog_event.inc;
29let $event= query_get_value(SHOW BINLOG EVENTS, Info, 9);
30--echo The last event before the COMMIT is $event
31
32echo *** Please look in binlog_multi_engine.test if you have a diff here ****;
33START TRANSACTION;
34INSERT INTO t1n VALUES (1,1), (1,2), (2,1), (2,2);
35UPDATE t1m, t1n SET m = 2, e = 3 WHERE n = f;
36UPDATE t1n, t1b SET e = 2, b = 3 WHERE f = c;
37COMMIT;
38
39let $wait_binlog_event= COMMIT;
40source include/wait_for_binlog_event.inc;
41
42TRUNCATE t1m;
43TRUNCATE t1b;
44TRUNCATE t1n;
45
46let $wait_binlog_event= t1n;
47source include/wait_for_binlog_event.inc;
48
49source include/show_binlog_events.inc;
50
51RESET MASTER;
52
53SET SESSION BINLOG_FORMAT=MIXED;
54
55INSERT INTO t1b VALUES (1,1), (1,2), (2,1), (2,2);
56INSERT INTO t1m VALUES (1,1), (1,2), (2,1), (2,2);
57
58let $wait_binlog_event= COMMIT;
59source include/wait_for_binlog_event.inc;
60let $event= query_get_value(SHOW BINLOG EVENTS, Info, 6);
61--echo The last event before the COMMIT is $event
62
63INSERT INTO t1n VALUES (1,1), (1,2), (2,1), (2,2);
64
65let $wait_binlog_event= COMMIT;
66source include/wait_for_binlog_event.inc;
67
68UPDATE t1m, t1b SET m = 2, b = 3 WHERE n = c;
69error ER_BINLOG_MULTIPLE_ENGINES_AND_SELF_LOGGING_ENGINE;
70UPDATE t1m, t1n SET m = 2, e = 3 WHERE n = f;
71
72# Not possible to test this since NDB writes its own binlog, which
73# might cause it to be out of sync with the results from MyISAM.
74# This will generate an error once BUG#28722 is fixed.
75
76#UPDATE t1m, t1n SET m = 2, e = 3 WHERE n = f;
77
78TRUNCATE t1m;
79TRUNCATE t1b;
80TRUNCATE t1n;
81
82source include/show_binlog_events.inc;
83
84RESET MASTER;
85
86SET SESSION BINLOG_FORMAT=ROW;
87
88INSERT INTO t1m VALUES (1,1), (1,2), (2,1), (2,2);
89
90INSERT INTO t1b VALUES (1,1), (1,2), (2,1), (2,2);
91INSERT INTO t1n VALUES (1,1), (1,2), (2,1), (2,2);
92
93error ER_BINLOG_MULTIPLE_ENGINES_AND_SELF_LOGGING_ENGINE;
94UPDATE t1m, t1n SET m = 2, e = 3 WHERE n = f;
95
96# Not possible to test this since NDB writes its own binlog, which
97# might cause it to be out of sync with the results from MyISAM.
98# This will generate an error once BUG#28722 is fixed.
99
100#UPDATE t1m, t1n SET m = 2, e = 3 WHERE n = f;
101
102error ER_BINLOG_MULTIPLE_ENGINES_AND_SELF_LOGGING_ENGINE;
103UPDATE t1n, t1b SET e = 2, b = 3 WHERE f = c;
104
105source include/show_binlog_events.inc;
106
107RESET MASTER;
108
109DROP TABLE t1m, t1b, t1n;
110
111