Debian Codegraph @

mysql-test/suite/rpl_gtid/t/rpl_mixed_temp_table_stored_function.test

raw
1 # ==== Purpose ====
2 #
3 # In MIXED mode, if a function contains operations on temporary tables
4 # verify that the function call is binlogged in ROW format.
5 #
6 # ==== Implementation ====
7 #
8 # 1. Create functions with
9 # a. only CREATE TEMPORARY TABLE.
10 # b. CREATE TEMPORARY TABLE and DMLs.
11 # 2. Create functions with
12 # a. DMLs on only temporary tables.
13 # b. DMLs on temporary tables as well as regular tables.
14 # 3. Create functions with
15 # a. only DROP TEMPORARY TABLE.
16 # b. DROP TEMPORARY TABLE and DMLs.
17 # 4. Call the functions and verify that the function call is written to the
18 # binary log in ROW format.
19 # 5. Verify that CREATE TEMPORARY TABLE with function is written to the
20 # binary log in ROW format.
21 # 6. Verify that a function invoking another function which has temporary table
22 # DDL, is binlogged in ROW format.
23 # a. Ensure that the function call is logged in STATEMENT format if the
24 # function is recreated with no access to temporary tables.
25 # b. Verify the same with prepared statements also.
26 # 7. Verify that a function invoking a procedure that has temporary table DDL is
27 # binlogged in ROW format.
28 # 8. Verify that a Event invoking a stored routine that has temporary table DDL
29 # is binlogged in ROW format.
30 #
31 # ==== References ====
32 #
33 # BUG#30395151: CREATE TEMPORARY TABLE OPERATION WITH FUNCTIONS RECORDED IN BIN LOG
34 # BUG#28258992: FUNCTION CALL NOT WRITTEN TO BINLOG IF IT CONTAIN DML ALONG WITH DROP TEMP TABLE
35 # BUG#30320009: ASSERTION TRX_IS_REGISTERED_FOR_2PC(TRX)
36 # BUG#28220751: REPLICATION BREAKS ON CREATE/DROP TEMPORARY TABLE OPERATION WITHIN FUNCTION(MBR)
37
38 --source include/not_group_replication_plugin.inc
39 --source include/set_privilege_checks_user_as_system_user.inc
40 --source include/have_binlog_format_mixed.inc
41 --source include/master-slave.inc
42
43 CREATE TABLE t1 (a INT);
44
45 --echo # Function with only CREATE TEMPORARY TABLE.
46 DELIMITER //;
47 CREATE FUNCTION create_temp_table() RETURNS INT DETERMINISTIC
48 BEGIN
49 CREATE TEMPORARY TABLE temp1 (i INT);
50 RETURN 1;
51 END//
52
53 --echo # Function with DMLs on temporary tables.
54 CREATE FUNCTION dmls_on_temp_tables() RETURNS INT DETERMINISTIC
55 BEGIN
56 INSERT INTO temp1 VALUES(2);
57 UPDATE temp1 SET i= 4;
58 DELETE FROM temp1;
59 RETURN 1;
60 END//
61
62 --echo # Function with only DROP TEMPORARY TABLE.
63 CREATE FUNCTION drop_temp_table() RETURNS INT DETERMINISTIC
64 BEGIN
65 DROP TEMPORARY TABLE temp1;
66 RETURN 1;
67 END//
68
69 --echo # Function with CREATE TEMPORARY TABLE and DMLs.
70 CREATE FUNCTION create_temp_table_with_dmls() RETURNS INT DETERMINISTIC
71 BEGIN
72 CREATE TEMPORARY TABLE temp2 (i INT);
73 INSERT INTO t1 VALUES(1);
74 INSERT INTO temp2 VALUES(1);
75 RETURN 2;
76 END//
77
78 --echo # Function with DMLs on temporary tables as well as regular tables.
79 CREATE FUNCTION dmls_on_temp_and_regular_tables() RETURNS INT DETERMINISTIC
80 BEGIN
81 INSERT INTO t1 VALUES(2);
82 UPDATE t1 SET a= 4;
83 DELETE FROM t1;
84 INSERT INTO temp2 VALUES(2);
85 UPDATE temp2 SET i= 4;
86 DELETE FROM temp2;
87 RETURN 2;
88 END//
89
90 --echo # Function with DROP TEMPORARY TABLE and DMLs.
91 CREATE FUNCTION drop_temp_table_with_dmls() RETURNS INT DETERMINISTIC
92 BEGIN
93 INSERT INTO t1 VALUES(3);
94 INSERT INTO temp2 VALUES(3);
95 DROP TEMPORARY TABLE temp2;
96 RETURN 2;
97 END//
98 DELIMITER ;//
99
100 --echo #
101 --echo # Verify that a function call is written to binary log in ROW
102 --echo # format if the function contains operations on temporary tables.
103 --echo # case1: Function call inside a transaction with ROLLBACK.
104 --echo #
105 --let $saved_master_pos=query_get_value('SHOW MASTER STATUS', Position, 1)
106 START TRANSACTION;
107 SELECT create_temp_table();
108 ROLLBACK;
109 START TRANSACTION;
110 SELECT dmls_on_temp_tables();
111 ROLLBACK;
112 START TRANSACTION;
113 SELECT drop_temp_table();
114 ROLLBACK;
115 START TRANSACTION;
116 SELECT create_temp_table_with_dmls();
117 ROLLBACK;
118 START TRANSACTION;
119 SELECT dmls_on_temp_and_regular_tables();
120 ROLLBACK;
121 START TRANSACTION;
122 SELECT drop_temp_table_with_dmls();
123 ROLLBACK;
124 --let $binlog_start= $saved_master_pos
125 --source include/show_binlog_events.inc
126 --source include/sync_slave_sql_with_master.inc
127 --source include/rpl_connection_master.inc
128
129 --echo #
130 --echo # Verify that a function call is written to binary log in ROW
131 --echo # format if the function contains operations on temporary tables.
132 --echo # case2: Function call inside a transaction with COMMIT.
133 --echo #
134 --let $saved_master_pos=query_get_value('SHOW MASTER STATUS', Position, 1)
135 START TRANSACTION;
136 SELECT create_temp_table();
137 COMMIT;
138 START TRANSACTION;
139 SELECT dmls_on_temp_tables();
140 COMMIT;
141 START TRANSACTION;
142 SELECT drop_temp_table();
143 COMMIT;
144 --let $assert_text= Functions with operations on temporary tables are binlogged in ROW format when binlog_format= ROW/MIXED.
145 --let $assert_cond= [SHOW MASTER STATUS, Position, 1] = $saved_master_pos
146 --source include/assert.inc
147
148 --let $saved_master_pos=query_get_value('SHOW MASTER STATUS', Position, 1)
149 START TRANSACTION;
150 SELECT create_temp_table_with_dmls();
151 COMMIT;
152 START TRANSACTION;
153 SELECT dmls_on_temp_and_regular_tables();
154 COMMIT;
155 START TRANSACTION;
156 SELECT drop_temp_table_with_dmls();
157 COMMIT;
158 --let $binlog_start= $saved_master_pos
159 --source include/show_binlog_events.inc
160 --source include/sync_slave_sql_with_master.inc
161 --source include/rpl_connection_master.inc
162
163 --echo #
164 --echo # Verify that a function call is written to binary log in ROW
165 --echo # format if the function contains operations on temporary tables.
166 --echo #
167 --let $saved_master_pos=query_get_value('SHOW MASTER STATUS', Position, 1)
168 SELECT create_temp_table();
169 SELECT dmls_on_temp_tables();
170 SELECT drop_temp_table();
171 --let $assert_text= Functions with operations on temporary tables are binlogged in ROW format when binlog_format= ROW/MIXED.
172 --let $assert_cond= [SHOW MASTER STATUS, Position, 1] = $saved_master_pos
173 --source include/assert.inc
174
175 --let $saved_master_pos=query_get_value('SHOW MASTER STATUS', Position, 1)
176 SELECT create_temp_table_with_dmls();
177 SELECT dmls_on_temp_and_regular_tables();
178 SELECT drop_temp_table_with_dmls();
179 --let $binlog_start= $saved_master_pos
180 --source include/show_binlog_events.inc
181 --source include/sync_slave_sql_with_master.inc
182 --source include/rpl_connection_master.inc
183
184 --echo # Verify that CREATE TEMPORARY TABLE with function is written to
185 --echo # the binary log in ROW format.
186 CREATE FUNCTION dummy_function() RETURNS INT DETERMINISTIC RETURN 1;
187 CREATE TEMPORARY TABLE temp3(a int);
188 INSERT INTO temp3 VALUES(1);
189
190 --let $saved_master_pos=query_get_value('SHOW MASTER STATUS', Position, 1)
191 CREATE TEMPORARY TABLE temp4 SELECT dummy_function() as result FROM temp3;
192 --let $assert_text= CREATE TEMPORARY TABLE with function is binlogged in ROW format when binlog_format= ROW/MIXED.
193 --let $assert_cond= [SHOW MASTER STATUS, Position, 1] = $saved_master_pos
194 --source include/assert.inc
195 --source include/sync_slave_sql_with_master.inc
196 --source include/rpl_connection_master.inc
197
198 --echo #
199 --echo # Verify that a function invoking another function that has temporary
200 --echo # table DDL is binlogged in ROW format.
201 --echo #
202 CREATE TABLE t2 (a int);
203 CREATE TABLE t3 (a int);
204 CREATE TEMPORARY TABLE temp1(a INT);
205
206 DELIMITER //;
207 CREATE FUNCTION drop_temp_table_if_exists() RETURNS INT DETERMINISTIC
208 BEGIN
209 DROP TEMPORARY TABLE IF EXISTS temp1;
210 RETURN 2;
211 END//
212
213 CREATE FUNCTION call_drop_temp_table_if_exists() RETURNS INT DETERMINISTIC
214 BEGIN
215 INSERT INTO t2 VALUES(1);
216 INSERT INTO t3 SELECT drop_temp_table_if_exists();
217 RETURN 3;
218 END //
219 DELIMITER ;//
220
221 --echo # case 1: Calling the function call_drop_temp_table_if_exists() twice so
222 --echo # that the temporary table does not exist during the second call and ensuring
223 --echo # that the functions are binlogged in ROW format for both the calls.
224 --let $saved_master_pos=query_get_value('SHOW MASTER STATUS', Position, 1)
225 SELECT call_drop_temp_table_if_exists();
226 SELECT call_drop_temp_table_if_exists();
227 --let $binlog_start= $saved_master_pos
228 --source include/show_binlog_events.inc
229
230 --echo # case 2: Calling the function call_drop_temp_table_if_exists() once and
231 --echo # recreating the function drop_temp_table_if_exists() with a different
232 --echo # definition and calling call_drop_temp_table_if_exists() again. Ensure
233 --echo # that the first call is binlogged in ROW format and the second call in
234 --echo # STATEMENT format.
235 --let $saved_master_pos=query_get_value('SHOW MASTER STATUS', Position, 1)
236 CREATE TEMPORARY TABLE temp1(a INT);
237 SELECT call_drop_temp_table_if_exists();
238
239 DROP FUNCTION drop_temp_table_if_exists;
240 CREATE FUNCTION drop_temp_table_if_exists() RETURNS INT RETURN 2;
241 SELECT call_drop_temp_table_if_exists();
242 --let $binlog_start= $saved_master_pos
243 --source include/show_binlog_events.inc
244
245 --echo # case 3: prepare the function call call_drop_temp_table_if_exists() once and
246 --echo # execute twice. Ensure that in both the executions the function is binlogged
247 --echo # in ROW format.
248 CREATE TEMPORARY TABLE temp1(a INT);
249 DROP FUNCTION drop_temp_table_if_exists;
250 DELIMITER //;
251 CREATE FUNCTION drop_temp_table_if_exists() RETURNS INT DETERMINISTIC
252 BEGIN
253 DROP TEMPORARY TABLE IF EXISTS temp1;
254 RETURN 2;
255 END //
256 DELIMITER ;//
257
258 PREPARE function_call FROM 'SELECT call_drop_temp_table_if_exists()';
259 --let $saved_master_pos=query_get_value('SHOW MASTER STATUS', Position, 1)
260 EXECUTE function_call;
261 EXECUTE function_call;
262 --let $binlog_start= $saved_master_pos
263 --source include/show_binlog_events.inc
264
265 --echo # case 4: prepare the function call call_drop_temp_table_if_exists() once and
266 --echo # execute once. Recreate the function drop_temp_table_if_exists() and the
267 --echo # prepared statement again. Ensure that in the first execution the function is
268 --echo # binlogged in ROW format and in the second execution, the function is binlogged
269 --echo # in STATEMENT format.
270 CREATE TEMPORARY TABLE temp1(a INT);
271 --let $saved_master_pos=query_get_value('SHOW MASTER STATUS', Position, 1)
272 EXECUTE function_call;
273 DROP FUNCTION drop_temp_table_if_exists;
274 CREATE FUNCTION drop_temp_table_if_exists() RETURNS INT RETURN 2;
275 EXECUTE function_call;
276 --let $binlog_start= $saved_master_pos
277 --source include/show_binlog_events.inc
278
279 --echo #
280 --echo # Verify that a function invoking a procedure that has temporary table DDL is
281 --echo # binlogged in ROW format.
282 --echo #
283
284 CREATE TABLE t4(a int);
285
286 DELIMITER //;
287 CREATE PROCEDURE proc_with_temp_table_ddl()
288 BEGIN
289 DROP TEMPORARY TABLE IF EXISTS t1;
290 CREATE TEMPORARY TABLE temp5 (a int);
291 INSERT INTO temp5 VALUES(10);
292 END //
293
294 CREATE FUNCTION call_proc_with_temp_table_ddl() RETURNS INT
295 BEGIN
296 CALL proc_with_temp_table_ddl();
297 INSERT INTO t4 SELECT * FROM temp5;
298 RETURN 1;
299 END //
300 DELIMITER ;//
301
302 --let $saved_master_pos=query_get_value('SHOW MASTER STATUS', Position, 1)
303 SELECT call_proc_with_temp_table_ddl();
304 --let $binlog_start= $saved_master_pos
305 --source include/show_binlog_events.inc
306
307 --echo #
308 --echo # Verify that a Event invoking a stored routine that has temporary table DDL
309 --echo # is binlogged in ROW format.
310 --echo #
311
312 CREATE EVENT call_routine_with_temp_table_ddl
313 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 SECOND
314 DO SELECT call_proc_with_temp_table_ddl();
315
316 DELETE FROM t4;
317 --let $saved_master_pos=query_get_value('SHOW MASTER STATUS', Position, 1)
318 # Wait for the event to execute once.
319 --let $wait_condition= select count(*) = 1 from t4
320 --source include/wait_condition_or_abort.inc
321 --let $binlog_start= $saved_master_pos
322 --source include/show_binlog_events.inc
323
324 # Cleanup
325 --source include/rpl_connection_master.inc
326 DROP TABLE t1, t2, t3, t4;
327 DROP TEMPORARY TABLE temp3, temp4, temp5;
328 DROP FUNCTION create_temp_table;
329 DROP FUNCTION create_temp_table_with_dmls;
330 DROP FUNCTION dmls_on_temp_tables;
331 DROP FUNCTION dmls_on_temp_and_regular_tables;
332 DROP FUNCTION drop_temp_table;
333 DROP FUNCTION drop_temp_table_with_dmls;
334 DROP FUNCTION dummy_function;
335 DROP FUNCTION call_drop_temp_table_if_exists;
336 DROP FUNCTION drop_temp_table_if_exists;
337 DROP PROCEDURE proc_with_temp_table_ddl;
338 DROP FUNCTION call_proc_with_temp_table_ddl;
339
340 --source include/sync_slave_sql_with_master.inc
341 --source include/rpl_end.inc