Debian Codegraph @

mysql-test/suite/rpl/include/rpl_stm_create_if_not_exists.test

raw
1 --echo
2 --echo
3 connection master;
4
5 if ($is_temporary)
6 {
7 --let $_temporary=TEMPORARY
8 }
9
10 CREATE TABLE t2(c1 INT, c2 char(10));
11 INSERT INTO t2 VALUES(1, 'abc'), (2, 'abc');
12
13 --echo
14 --echo # The original query should be binlogged if the table does not exist.
15 --echo # ------------------------------------------------------------------
16 --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;
21
22 --sync_slave_with_master
23 --connection master
24
25 if (!$is_temporary)
26 {
27 --let $diff_tables= master:t1,slave:t1
28 --source include/diff_tables.inc
29 }
30
31 --echo
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 # ------------------------------------------------------------------
36 --echo
37 let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
38 eval CREATE $_temporary TABLE IF NOT EXISTS t1
39 SELECT 'abc', 2;
40 source include/show_binlog_events.inc;
41
42 --sync_slave_with_master
43 --connection master
44
45 if (!$is_temporary)
46 {
47 --let $diff_tables= master:t1,slave:t1
48 --source include/diff_tables.inc
49 }
50
51 --echo
52 --echo # Verify if it can be binlogged with right database name when the table
53 --echo # is not in the default database
54 --echo
55 --disable_warnings
56 DROP DATABASE IF EXISTS db1;
57 --enable_warnings
58 CREATE DATABASE db1;
59 USE db1;
60
61 let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
62 eval CREATE $_temporary TABLE IF NOT EXISTS test.t1
63 SELECT 'abc', 20;
64 source include/show_binlog_events.inc;
65
66 --sync_slave_with_master
67 --connection master
68
69 if (!$is_temporary)
70 {
71 --let $diff_tables= master:test.t1,slave:test.t1
72 --source include/diff_tables.inc
73 }
74 USE test;
75 DROP DATABASE db1;
76
77 --echo
78 --echo # It should be binlogged as 'REPLACE ... SELECT'
79 --echo # if the original statement has option REPLACE
80 --echo
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;
85
86 --sync_slave_with_master
87 --connection master
88
89 if (!$is_temporary)
90 {
91 --let $diff_tables= master:t1,slave:t1
92 --source include/diff_tables.inc
93 }
94
95 --echo
96 --echo # It should be binlogged as 'INSERT IGNORE... SELECT'
97 --echo # if the original statement has option IGNORE
98 --echo
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;
103
104 --sync_slave_with_master
105 --connection master
106
107 if (!$is_temporary)
108 {
109 --let $diff_tables= master:t1,slave:t1
110 --source include/diff_tables.inc
111 }
112
113 --echo
114 --echo # Nothing should be binlogged if error happens and no any row is inserted
115 --echo
116 let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
117 --error ER_DUP_ENTRY
118 eval CREATE $_temporary TABLE IF NOT EXISTS t1
119 SELECT '123', 2;
120 source include/show_binlog_events.inc;
121
122 --sync_slave_with_master
123 --connection master
124
125 if (!$is_temporary)
126 {
127 --let $diff_tables= master:t1,slave:t1
128 --source include/diff_tables.inc
129 }
130
131 --echo
132 --echo # Verify it can binlog well when there are some braces('(')
133 --echo
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;
142
143 --sync_slave_with_master
144 --connection master
145
146 if (!$is_temporary)
147 {
148 --let $diff_tables= master:t1,slave:t1
149 --source include/diff_tables.inc
150 }
151
152 if (!$is_temporary)
153 {
154 --echo
155 --echo # Throw a warning that table already exists and don't insert anything
156 --echo
157 CREATE VIEW t3 AS SELECT * FROM t2;
158 let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
159
160 CREATE TABLE IF NOT EXISTS t3
161 SELECT '123', 2;
162 source include/show_binlog_events.inc;
163 DROP VIEW t3;
164 }
165
166 --echo
167 --echo # The statement can be binlogged correctly when it is in a SP/EVENT/TRIGGER
168 --echo
169
170 --disable_warnings
171 DROP PROCEDURE IF EXISTS p1;
172 --enable_warnings
173 eval CREATE PROCEDURE p1(IN a INT)
174 CREATE $_temporary TABLE IF NOT EXISTS t1 SELECT '123', a;
175
176 let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
177 call p1(500);
178 call p1(600);
179 source include/show_binlog_events.inc;
180
181 --sync_slave_with_master
182 --connection master
183
184 if (!$is_temporary)
185 {
186 --let $diff_tables= master:t1,slave:t1
187 --source include/diff_tables.inc
188 }
189 DROP PROCEDURE p1;
190
191 --echo
192 --echo # The statement can be binlogged correctly when it is in a prepared statement
193 --echo
194 eval PREPARE stm FROM "CREATE $_temporary TABLE IF NOT EXISTS t1 SELECT '123', ?";
195
196 let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
197 SET @a= 700;
198 EXECUTE stm USING @a;
199 SET @a= 800;
200 EXECUTE stm USING @a;
201 source include/show_binlog_events.inc;
202
203 --sync_slave_with_master
204 --connection master
205
206 if (!$is_temporary)
207 {
208 --let $diff_tables= master:t1,slave:t1
209 --source include/diff_tables.inc
210 }
211
212 --echo
213 --echo # The statement can be binlogged correctly when it is in a conditional comment
214 --echo
215 let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
216
217 --echo # The whole statement in a conditional comment
218 eval /*!CREATE $_temporary TABLE IF NOT EXISTS t1
219 SELECT 'abc', 900*/;
220 source include/show_binlog_events.inc;
221 let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
222
223 --echo
224 --echo # There is an long comment before SELECT
225 eval /*!CREATE $_temporary /*blabla*/ TABLE IF NOT EXISTS t1
226 SELECT 'abc', 901*/;
227 source include/show_binlog_events.inc;
228 let binlog_start= query_get_value(SHOW MASTER STATUS, Position, 1);
229
230 --echo
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);
236
237 --echo
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);
243
244 --echo
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);
250
251 --echo
252 --echo # Conditional comment ends just before SELECT keyword
253 eval /*!CREATE $_temporary TABLE IF NOT EXISTS t1
254 */SELECT 'abc', 905;
255 source include/show_binlog_events.inc;
256
257 --sync_slave_with_master
258 --connection master
259
260 if (!$is_temporary)
261 {
262 --let $diff_tables= master:t1,slave:t1
263 --source include/diff_tables.inc
264 }
265
266 DROP TABLE t2;
267 eval DROP $_temporary TABLE t1;
268