Debian Codegraph @

mysql-test/suite/rpl_gtid/r/rpl_mixed_temp_table_stored_function.result

raw
1 include/master-slave.inc
2 Warnings:
3 Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4 Note #### Storing MySQL user name or password information in the connection metadata repository is not secure and is therefore not recommended. Please consider using the USER and PASSWORD connection options for START REPLICA; see the 'START REPLICA Syntax' in the MySQL Manual for more information.
5 [connection master]
6 CREATE TABLE t1 (a INT);
7 # Function with only CREATE TEMPORARY TABLE.
8 CREATE FUNCTION create_temp_table() RETURNS INT DETERMINISTIC
9 BEGIN
10 CREATE TEMPORARY TABLE temp1 (i INT);
11 RETURN 1;
12 END//
13 # Function with DMLs on temporary tables.
14 CREATE FUNCTION dmls_on_temp_tables() RETURNS INT DETERMINISTIC
15 BEGIN
16 INSERT INTO temp1 VALUES(2);
17 UPDATE temp1 SET i= 4;
18 DELETE FROM temp1;
19 RETURN 1;
20 END//
21 # Function with only DROP TEMPORARY TABLE.
22 CREATE FUNCTION drop_temp_table() RETURNS INT DETERMINISTIC
23 BEGIN
24 DROP TEMPORARY TABLE temp1;
25 RETURN 1;
26 END//
27 # Function with CREATE TEMPORARY TABLE and DMLs.
28 CREATE FUNCTION create_temp_table_with_dmls() RETURNS INT DETERMINISTIC
29 BEGIN
30 CREATE TEMPORARY TABLE temp2 (i INT);
31 INSERT INTO t1 VALUES(1);
32 INSERT INTO temp2 VALUES(1);
33 RETURN 2;
34 END//
35 # Function with DMLs on temporary tables as well as regular tables.
36 CREATE FUNCTION dmls_on_temp_and_regular_tables() RETURNS INT DETERMINISTIC
37 BEGIN
38 INSERT INTO t1 VALUES(2);
39 UPDATE t1 SET a= 4;
40 DELETE FROM t1;
41 INSERT INTO temp2 VALUES(2);
42 UPDATE temp2 SET i= 4;
43 DELETE FROM temp2;
44 RETURN 2;
45 END//
46 # Function with DROP TEMPORARY TABLE and DMLs.
47 CREATE FUNCTION drop_temp_table_with_dmls() RETURNS INT DETERMINISTIC
48 BEGIN
49 INSERT INTO t1 VALUES(3);
50 INSERT INTO temp2 VALUES(3);
51 DROP TEMPORARY TABLE temp2;
52 RETURN 2;
53 END//
54 #
55 # Verify that a function call is written to binary log in ROW
56 # format if the function contains operations on temporary tables.
57 # case1: Function call inside a transaction with ROLLBACK.
58 #
59 START TRANSACTION;
60 SELECT create_temp_table();
61 create_temp_table()
62 1
63 ROLLBACK;
64 Warnings:
65 Warning 1751 The creation of some temporary tables could not be rolled back.
66 START TRANSACTION;
67 SELECT dmls_on_temp_tables();
68 dmls_on_temp_tables()
69 1
70 ROLLBACK;
71 START TRANSACTION;
72 SELECT drop_temp_table();
73 drop_temp_table()
74 1
75 ROLLBACK;
76 Warnings:
77 Warning 1752 Some temporary tables were dropped, but these operations could not be rolled back.
78 START TRANSACTION;
79 SELECT create_temp_table_with_dmls();
80 create_temp_table_with_dmls()
81 2
82 ROLLBACK;
83 Warnings:
84 Warning 1751 The creation of some temporary tables could not be rolled back.
85 START TRANSACTION;
86 SELECT dmls_on_temp_and_regular_tables();
87 dmls_on_temp_and_regular_tables()
88 2
89 ROLLBACK;
90 START TRANSACTION;
91 SELECT drop_temp_table_with_dmls();
92 drop_temp_table_with_dmls()
93 2
94 ROLLBACK;
95 Warnings:
96 Warning 1752 Some temporary tables were dropped, but these operations could not be rolled back.
97 include/show_binlog_events.inc
98 include/sync_slave_sql_with_master.inc
99 [connection master]
100 #
101 # Verify that a function call is written to binary log in ROW
102 # format if the function contains operations on temporary tables.
103 # case2: Function call inside a transaction with COMMIT.
104 #
105 START TRANSACTION;
106 SELECT create_temp_table();
107 create_temp_table()
108 1
109 COMMIT;
110 START TRANSACTION;
111 SELECT dmls_on_temp_tables();
112 dmls_on_temp_tables()
113 1
114 COMMIT;
115 START TRANSACTION;
116 SELECT drop_temp_table();
117 drop_temp_table()
118 1
119 COMMIT;
120 include/assert.inc [Functions with operations on temporary tables are binlogged in ROW format when binlog_format= ROW/MIXED.]
121 START TRANSACTION;
122 SELECT create_temp_table_with_dmls();
123 create_temp_table_with_dmls()
124 2
125 COMMIT;
126 START TRANSACTION;
127 SELECT dmls_on_temp_and_regular_tables();
128 dmls_on_temp_and_regular_tables()
129 2
130 COMMIT;
131 START TRANSACTION;
132 SELECT drop_temp_table_with_dmls();
133 drop_temp_table_with_dmls()
134 2
135 COMMIT;
136 include/show_binlog_events.inc
137 Log_name Pos Event_type Server_id End_log_pos Info
138 master-bin.000001 # Query # # BEGIN
139 master-bin.000001 # Table_map # # table_id: # (test.t1)
140 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
141 master-bin.000001 # Xid # # COMMIT /* XID */
142 master-bin.000001 # Query # # BEGIN
143 master-bin.000001 # Table_map # # table_id: # (test.t1)
144 master-bin.000001 # Write_rows # # table_id: #
145 master-bin.000001 # Update_rows # # table_id: #
146 master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F
147 master-bin.000001 # Xid # # COMMIT /* XID */
148 master-bin.000001 # Query # # BEGIN
149 master-bin.000001 # Table_map # # table_id: # (test.t1)
150 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
151 master-bin.000001 # Xid # # COMMIT /* XID */
152 include/sync_slave_sql_with_master.inc
153 [connection master]
154 #
155 # Verify that a function call is written to binary log in ROW
156 # format if the function contains operations on temporary tables.
157 #
158 SELECT create_temp_table();
159 create_temp_table()
160 1
161 SELECT dmls_on_temp_tables();
162 dmls_on_temp_tables()
163 1
164 SELECT drop_temp_table();
165 drop_temp_table()
166 1
167 include/assert.inc [Functions with operations on temporary tables are binlogged in ROW format when binlog_format= ROW/MIXED.]
168 SELECT create_temp_table_with_dmls();
169 create_temp_table_with_dmls()
170 2
171 SELECT dmls_on_temp_and_regular_tables();
172 dmls_on_temp_and_regular_tables()
173 2
174 SELECT drop_temp_table_with_dmls();
175 drop_temp_table_with_dmls()
176 2
177 include/show_binlog_events.inc
178 Log_name Pos Event_type Server_id End_log_pos Info
179 master-bin.000001 # Query # # BEGIN
180 master-bin.000001 # Table_map # # table_id: # (test.t1)
181 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
182 master-bin.000001 # Xid # # COMMIT /* XID */
183 master-bin.000001 # Query # # BEGIN
184 master-bin.000001 # Table_map # # table_id: # (test.t1)
185 master-bin.000001 # Write_rows # # table_id: #
186 master-bin.000001 # Update_rows # # table_id: #
187 master-bin.000001 # Delete_rows # # table_id: # flags: STMT_END_F
188 master-bin.000001 # Xid # # COMMIT /* XID */
189 master-bin.000001 # Query # # BEGIN
190 master-bin.000001 # Table_map # # table_id: # (test.t1)
191 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
192 master-bin.000001 # Xid # # COMMIT /* XID */
193 include/sync_slave_sql_with_master.inc
194 [connection master]
195 # Verify that CREATE TEMPORARY TABLE with function is written to
196 # the binary log in ROW format.
197 CREATE FUNCTION dummy_function() RETURNS INT DETERMINISTIC RETURN 1;
198 CREATE TEMPORARY TABLE temp3(a int);
199 INSERT INTO temp3 VALUES(1);
200 CREATE TEMPORARY TABLE temp4 SELECT dummy_function() as result FROM temp3;
201 include/assert.inc [CREATE TEMPORARY TABLE with function is binlogged in ROW format when binlog_format= ROW/MIXED.]
202 include/sync_slave_sql_with_master.inc
203 [connection master]
204 #
205 # Verify that a function invoking another function that has temporary
206 # table DDL is binlogged in ROW format.
207 #
208 CREATE TABLE t2 (a int);
209 CREATE TABLE t3 (a int);
210 CREATE TEMPORARY TABLE temp1(a INT);
211 CREATE FUNCTION drop_temp_table_if_exists() RETURNS INT DETERMINISTIC
212 BEGIN
213 DROP TEMPORARY TABLE IF EXISTS temp1;
214 RETURN 2;
215 END//
216 CREATE FUNCTION call_drop_temp_table_if_exists() RETURNS INT DETERMINISTIC
217 BEGIN
218 INSERT INTO t2 VALUES(1);
219 INSERT INTO t3 SELECT drop_temp_table_if_exists();
220 RETURN 3;
221 END //
222 # case 1: Calling the function call_drop_temp_table_if_exists() twice so
223 # that the temporary table does not exist during the second call and ensuring
224 # that the functions are binlogged in ROW format for both the calls.
225 SELECT call_drop_temp_table_if_exists();
226 call_drop_temp_table_if_exists()
227 3
228 SELECT call_drop_temp_table_if_exists();
229 call_drop_temp_table_if_exists()
230 3
231 include/show_binlog_events.inc
232 Log_name Pos Event_type Server_id End_log_pos Info
233 master-bin.000001 # Query # # BEGIN
234 master-bin.000001 # Table_map # # table_id: # (test.t2)
235 master-bin.000001 # Table_map # # table_id: # (test.t3)
236 master-bin.000001 # Write_rows # # table_id: #
237 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
238 master-bin.000001 # Xid # # COMMIT /* XID */
239 master-bin.000001 # Query # # BEGIN
240 master-bin.000001 # Table_map # # table_id: # (test.t2)
241 master-bin.000001 # Table_map # # table_id: # (test.t3)
242 master-bin.000001 # Write_rows # # table_id: #
243 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
244 master-bin.000001 # Xid # # COMMIT /* XID */
245 # case 2: Calling the function call_drop_temp_table_if_exists() once and
246 # recreating the function drop_temp_table_if_exists() with a different
247 # definition and calling call_drop_temp_table_if_exists() again. Ensure
248 # that the first call is binlogged in ROW format and the second call in
249 # STATEMENT format.
250 CREATE TEMPORARY TABLE temp1(a INT);
251 SELECT call_drop_temp_table_if_exists();
252 call_drop_temp_table_if_exists()
253 3
254 DROP FUNCTION drop_temp_table_if_exists;
255 CREATE FUNCTION drop_temp_table_if_exists() RETURNS INT RETURN 2;
256 SELECT call_drop_temp_table_if_exists();
257 call_drop_temp_table_if_exists()
258 3
259 include/show_binlog_events.inc
260 Log_name Pos Event_type Server_id End_log_pos Info
261 master-bin.000001 # Query # # BEGIN
262 master-bin.000001 # Table_map # # table_id: # (test.t2)
263 master-bin.000001 # Table_map # # table_id: # (test.t3)
264 master-bin.000001 # Write_rows # # table_id: #
265 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
266 master-bin.000001 # Xid # # COMMIT /* XID */
267 master-bin.000001 # Query # # use `test`; DROP FUNCTION drop_temp_table_if_exists
268 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `drop_temp_table_if_exists`() RETURNS int
269 RETURN 2
270 master-bin.000001 # Query # # BEGIN
271 master-bin.000001 # Query # # use `test`; SELECT `test`.`call_drop_temp_table_if_exists`()
272 master-bin.000001 # Xid # # COMMIT /* XID */
273 # case 3: prepare the function call call_drop_temp_table_if_exists() once and
274 # execute twice. Ensure that in both the executions the function is binlogged
275 # in ROW format.
276 CREATE TEMPORARY TABLE temp1(a INT);
277 DROP FUNCTION drop_temp_table_if_exists;
278 CREATE FUNCTION drop_temp_table_if_exists() RETURNS INT DETERMINISTIC
279 BEGIN
280 DROP TEMPORARY TABLE IF EXISTS temp1;
281 RETURN 2;
282 END //
283 PREPARE function_call FROM 'SELECT call_drop_temp_table_if_exists()';
284 EXECUTE function_call;
285 call_drop_temp_table_if_exists()
286 3
287 EXECUTE function_call;
288 call_drop_temp_table_if_exists()
289 3
290 include/show_binlog_events.inc
291 Log_name Pos Event_type Server_id End_log_pos Info
292 master-bin.000001 # Query # # BEGIN
293 master-bin.000001 # Table_map # # table_id: # (test.t2)
294 master-bin.000001 # Table_map # # table_id: # (test.t3)
295 master-bin.000001 # Write_rows # # table_id: #
296 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
297 master-bin.000001 # Xid # # COMMIT /* XID */
298 master-bin.000001 # Query # # BEGIN
299 master-bin.000001 # Table_map # # table_id: # (test.t2)
300 master-bin.000001 # Table_map # # table_id: # (test.t3)
301 master-bin.000001 # Write_rows # # table_id: #
302 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
303 master-bin.000001 # Xid # # COMMIT /* XID */
304 # case 4: prepare the function call call_drop_temp_table_if_exists() once and
305 # execute once. Recreate the function drop_temp_table_if_exists() and the
306 # prepared statement again. Ensure that in the first execution the function is
307 # binlogged in ROW format and in the second execution, the function is binlogged
308 # in STATEMENT format.
309 CREATE TEMPORARY TABLE temp1(a INT);
310 EXECUTE function_call;
311 call_drop_temp_table_if_exists()
312 3
313 DROP FUNCTION drop_temp_table_if_exists;
314 CREATE FUNCTION drop_temp_table_if_exists() RETURNS INT RETURN 2;
315 EXECUTE function_call;
316 call_drop_temp_table_if_exists()
317 3
318 include/show_binlog_events.inc
319 Log_name Pos Event_type Server_id End_log_pos Info
320 master-bin.000001 # Query # # BEGIN
321 master-bin.000001 # Table_map # # table_id: # (test.t2)
322 master-bin.000001 # Table_map # # table_id: # (test.t3)
323 master-bin.000001 # Write_rows # # table_id: #
324 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
325 master-bin.000001 # Xid # # COMMIT /* XID */
326 master-bin.000001 # Query # # use `test`; DROP FUNCTION drop_temp_table_if_exists
327 master-bin.000001 # Query # # use `test`; CREATE DEFINER=`root`@`localhost` FUNCTION `drop_temp_table_if_exists`() RETURNS int
328 RETURN 2
329 master-bin.000001 # Query # # BEGIN
330 master-bin.000001 # Query # # use `test`; SELECT `test`.`call_drop_temp_table_if_exists`()
331 master-bin.000001 # Xid # # COMMIT /* XID */
332 #
333 # Verify that a function invoking a procedure that has temporary table DDL is
334 # binlogged in ROW format.
335 #
336 CREATE TABLE t4(a int);
337 CREATE PROCEDURE proc_with_temp_table_ddl()
338 BEGIN
339 DROP TEMPORARY TABLE IF EXISTS t1;
340 CREATE TEMPORARY TABLE temp5 (a int);
341 INSERT INTO temp5 VALUES(10);
342 END //
343 CREATE FUNCTION call_proc_with_temp_table_ddl() RETURNS INT
344 BEGIN
345 CALL proc_with_temp_table_ddl();
346 INSERT INTO t4 SELECT * FROM temp5;
347 RETURN 1;
348 END //
349 SELECT call_proc_with_temp_table_ddl();
350 call_proc_with_temp_table_ddl()
351 1
352 include/show_binlog_events.inc
353 Log_name Pos Event_type Server_id End_log_pos Info
354 master-bin.000001 # Query # # BEGIN
355 master-bin.000001 # Table_map # # table_id: # (test.t4)
356 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
357 master-bin.000001 # Xid # # COMMIT /* XID */
358 #
359 # Verify that a Event invoking a stored routine that has temporary table DDL
360 # is binlogged in ROW format.
361 #
362 CREATE EVENT call_routine_with_temp_table_ddl
363 ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 SECOND
364 DO SELECT call_proc_with_temp_table_ddl();
365 DELETE FROM t4;
366 include/show_binlog_events.inc
367 Log_name Pos Event_type Server_id End_log_pos Info
368 master-bin.000001 # Query # # BEGIN
369 master-bin.000001 # Table_map # # table_id: # (test.t4)
370 master-bin.000001 # Write_rows # # table_id: # flags: STMT_END_F
371 master-bin.000001 # Xid # # COMMIT /* XID */
372 master-bin.000001 # Query # # use `test`; DROP EVENT IF EXISTS`test`.`call_routine_with_temp_table_ddl`
373 [connection master]
374 DROP TABLE t1, t2, t3, t4;
375 DROP TEMPORARY TABLE temp3, temp4, temp5;
376 DROP FUNCTION create_temp_table;
377 DROP FUNCTION create_temp_table_with_dmls;
378 DROP FUNCTION dmls_on_temp_tables;
379 DROP FUNCTION dmls_on_temp_and_regular_tables;
380 DROP FUNCTION drop_temp_table;
381 DROP FUNCTION drop_temp_table_with_dmls;
382 DROP FUNCTION dummy_function;
383 DROP FUNCTION call_drop_temp_table_if_exists;
384 DROP FUNCTION drop_temp_table_if_exists;
385 DROP PROCEDURE proc_with_temp_table_ddl;
386 DROP FUNCTION call_proc_with_temp_table_ddl;
387 include/sync_slave_sql_with_master.inc
388 include/rpl_end.inc