| 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 |