7
--let $_temporary=TEMPORARY
10
CREATE TABLE t2(c1 INT, c2 char(10));
11
INSERT INTO t2 VALUES(1, 'abc'), (2, 'abc');
14
--echo # The original query should be binlogged if the table does not exist.
15
--echo # ------------------------------------------------------------------
17
let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
18
eval CREATE $_temporary TABLE IF NOT EXISTS t1 (c1 INT , c2 INT, c3 char(10), c4 INT KEY)
19
SELECT 'abc' AS c3, 1 AS c4;
20
source include/show_binlog_events.inc;
22
--sync_slave_with_master
27
--let $diff_tables= master:t1,slave:t1
28
--source include/diff_tables.inc
32
--echo # The statement should be binlogged as two events. one is
33
--echo # 'CREATE $_temporary TABLE IF NOT EXISTS ..', another one is
34
--echo # 'INSERT ... SELECT'.
35
--echo # ------------------------------------------------------------------
37
let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
38
eval CREATE $_temporary TABLE IF NOT EXISTS t1
40
source include/show_binlog_events.inc;
42
--sync_slave_with_master
47
--let $diff_tables= master:t1,slave:t1
48
--source include/diff_tables.inc
52
--echo # Verify if it can be binlogged with right database name when the table
53
--echo # is not in the default database
56
DROP DATABASE IF EXISTS db1;
61
let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
62
eval CREATE $_temporary TABLE IF NOT EXISTS test.t1
64
source include/show_binlog_events.inc;
66
--sync_slave_with_master
71
--let $diff_tables= master:test.t1,slave:test.t1
72
--source include/diff_tables.inc
78
--echo # It should be binlogged as 'REPLACE ... SELECT'
79
--echo # if the original statement has option REPLACE
81
let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
82
eval CREATE $_temporary TABLE IF NOT EXISTS t1
83
REPLACE SELECT '123', 2;
84
source include/show_binlog_events.inc;
86
--sync_slave_with_master
91
--let $diff_tables= master:t1,slave:t1
92
--source include/diff_tables.inc
96
--echo # It should be binlogged as 'INSERT IGNORE... SELECT'
97
--echo # if the original statement has option IGNORE
99
let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
100
eval CREATE $_temporary TABLE IF NOT EXISTS t1
101
IGNORE SELECT '123', 2;
102
source include/show_binlog_events.inc;
104
--sync_slave_with_master
109
--let $diff_tables= master:t1,slave:t1
110
--source include/diff_tables.inc
114
--echo # Nothing should be binlogged if error happens and no any row is inserted
116
let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
118
eval CREATE $_temporary TABLE IF NOT EXISTS t1
120
source include/show_binlog_events.inc;
122
--sync_slave_with_master
127
--let $diff_tables= master:t1,slave:t1
128
--source include/diff_tables.inc
132
--echo # Verify it can binlog well when there are some braces('(')
134
let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
135
eval CREATE $_temporary TABLE IF NOT EXISTS t1
136
(SELECT '123', 3) UNION (SELECT '123', 4);
137
eval CREATE $_temporary TABLE IF NOT EXISTS t1
138
REPLACE (SELECT 'abc', 3) UNION (SELECT 'abc', 4);
139
eval CREATE $_temporary TABLE IF NOT EXISTS t1
140
IGNORE (SELECT '123', 3) UNION (SELECT '123', 4);
141
source include/show_binlog_events.inc;
143
--sync_slave_with_master
148
--let $diff_tables= master:t1,slave:t1
149
--source include/diff_tables.inc
155
--echo # Throw a warning that table already exists and don't insert anything
157
CREATE VIEW t3 AS SELECT * FROM t2;
158
let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
160
CREATE TABLE IF NOT EXISTS t3
162
source include/show_binlog_events.inc;
167
--echo # The statement can be binlogged correctly when it is in a SP/EVENT/TRIGGER
171
DROP PROCEDURE IF EXISTS p1;
173
eval CREATE PROCEDURE p1(IN a INT)
174
CREATE $_temporary TABLE IF NOT EXISTS t1 SELECT '123', a;
176
let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
179
source include/show_binlog_events.inc;
181
--sync_slave_with_master
186
--let $diff_tables= master:t1,slave:t1
187
--source include/diff_tables.inc
192
--echo # The statement can be binlogged correctly when it is in a prepared statement
194
eval PREPARE stm FROM "CREATE $_temporary TABLE IF NOT EXISTS t1 SELECT '123', ?";
196
let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
198
EXECUTE stm USING @a;
200
EXECUTE stm USING @a;
201
source include/show_binlog_events.inc;
203
--sync_slave_with_master
208
--let $diff_tables= master:t1,slave:t1
209
--source include/diff_tables.inc
213
--echo # The statement can be binlogged correctly when it is in a conditional comment
215
let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
217
--echo # The whole statement in a conditional comment
218
eval /*!CREATE $_temporary TABLE IF NOT EXISTS t1
220
source include/show_binlog_events.inc;
221
let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
224
--echo # There is an long comment before SELECT
225
eval /*!CREATE $_temporary /*blabla*/ TABLE IF NOT EXISTS t1
227
source include/show_binlog_events.inc;
228
let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
231
--echo # Conditional comment starts just from SELECT
232
eval CREATE $_temporary TABLE IF NOT EXISTS t1
233
/*!SELECT 'abc',*/ 902;
234
source include/show_binlog_events.inc;
235
let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
238
--echo # Only SELECT keyword is in the conditional comment
239
eval CREATE $_temporary TABLE IF NOT EXISTS t1
240
/*!SELECT*/ /*!'abc',*/ 904;
241
source include/show_binlog_events.inc;
242
let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
245
--echo # Conditional comment is after SELECT keyword
246
eval CREATE $_temporary TABLE IF NOT EXISTS t1
247
SELECT /*!'abc',*/ 903;
248
source include/show_binlog_events.inc;
249
let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
252
--echo # Conditional comment ends just before SELECT keyword
253
eval /*!CREATE $_temporary TABLE IF NOT EXISTS t1
255
source include/show_binlog_events.inc;
257
--sync_slave_with_master
262
--let $diff_tables= master:t1,slave:t1
263
--source include/diff_tables.inc
267
eval DROP $_temporary TABLE t1;