1############################################################################### 2# In this test case, we check how changes to temporary tables are written 3# to the binary log. 4# 5# 6# B - Begin 7# C - Commit 8# R - Rollback 9# 10# 11# T - Inserts a row into a T-table 12# N-Temp - Inserts a row into a temporary N-table. 13# 14# 15# T-SELECT-N-Temp - Selects from a temporary N-table and inserts 16# into a T-table. 17# N-SELECT-N-Temp - Selects from a temporary N-table and inserts 18# into a N-table. 19# T-SELECT-T-Temp - Selects from a temporary T-table and inserts 20# into a T-table. 21# N-SELECT-T-Temp - Selects from a temporary T-table and inserts 22# into a N-table. 23 24# Create-N-Temp - Creates a temporary N-table if a temporary N-table 25# was dropped before 26# Create-T-Temp - Creates a temporary T-table if a temporary T-table 27# was dropped before 28# 29# 30# Drop-Temp-T-Temp - Drops a temporary T-table if there is any 31# Drop-Temp-N-Temp - Drops a temporary N-table if there is any 32# Drop-Temp-TN-Temp - Drops both a temporary T-table and N-table if there 33# is any 34# Drop-Temp-TT-Temp - Drops two temporary T-tables if there is any 35# Drop-Temp-NN-Temp - Drops two temporary N-tables if there is any 36# Drop-Temp-Xe-Temp - Tries to drop a temporary table that does not exist 37# Drop-Temp-NXe-Temp - Drops a temporary N-table if there is any and 38# a temporary table that does not exist 39# Drop-Temp-TXe-Temp - Drops a temporary T-table if there is any and 40# a temporary table that does not exist 41# 42# 43# Drop-Temp-If-Xe-Temp - Tries to drop a temporary table that does not exist 44# Drop-Temp-If-TXe-Temp - Drops a temporary T-table if there is any and 45# a temporary table that does not exist 46# 47# 48# Drop-T - Drops a T-table if there is any 49# Drop-N - Drops a N-table if there is any 50# Drop-Xe - Tries to drop a table that does not exist 51# Drop-TXe - Drops a T-table if there is any and a table that does 52# not exist 53# Drop-NXe - Drops a N-table if there is any and a table that does 54# not exist 55# Drop-TN - Drops both a T-table and N-table if there is any 56# Drop-TT - Drops two T-tables if there is any 57# Drop-NN - Drops two N-tables if there is any 58# Drop-N-TN-Temp - Drops a N-table and both a temporary T-table and 59# N-table if there is any 60# 61# 62# Drop-If-Xe - Tries to drop a table that does not exist 63# Drop-If-TXe - Drops a T-table if there is any and a table that does 64# not exist 65# 66# 67# This test uses the commands available at: 68# extra/rpl_tests/rpl_drop_create_temp_table.inc 69# 70############################################################################### 71 72 73--echo ######################################################################### 74--echo # CONFIGURATION 75--echo ######################################################################### 76 77call mtr.add_suppression("Unsafe statement written to the binary log using statement format since BINLOG_FORMAT = STATEMENT."); 78 79--let $tot_table= 2 80SET @commands= 'configure'; 81--source extra/rpl_tests/rpl_drop_create_temp_table.inc 82 83--echo ######################################################################### 84--echo # 1 - Tables dropped by "DROP TEMPORARY TABLE" 85--echo ######################################################################### 86connection master; 87 88--echo 89--echo # 90--echo #1) Generates in the binlog what follows: 91--echo # 92SET @commands= 'Drop-Temp-T-Temp'; 93--source extra/rpl_tests/rpl_drop_create_temp_table.inc 94 95SET @commands= 'Drop-Temp-N-Temp'; 96--source extra/rpl_tests/rpl_drop_create_temp_table.inc 97 98SET @commands= 'Drop-Temp-Xe-Temp'; 99--source extra/rpl_tests/rpl_drop_create_temp_table.inc 100 101SET @commands= 'Drop-Temp-If-Xe-Temp'; 102--source extra/rpl_tests/rpl_drop_create_temp_table.inc 103 104SET @commands= 'Drop-Temp-TXe-Temp'; 105--source extra/rpl_tests/rpl_drop_create_temp_table.inc 106 107SET @commands= 'Drop-Temp-If-TXe-Temp'; 108--source extra/rpl_tests/rpl_drop_create_temp_table.inc 109 110SET @commands= 'Drop-Temp-NXe-Temp'; 111--source extra/rpl_tests/rpl_drop_create_temp_table.inc 112 113SET @commands= 'Drop-Temp-If-NXe-Temp'; 114--source extra/rpl_tests/rpl_drop_create_temp_table.inc 115 116SET @commands= 'Drop-Temp-TN-Temp'; 117--source extra/rpl_tests/rpl_drop_create_temp_table.inc 118 119SET @commands= 'Drop-Temp-TT-Temp'; 120--source extra/rpl_tests/rpl_drop_create_temp_table.inc 121 122SET @commands= 'Drop-Temp-NN-Temp'; 123--source extra/rpl_tests/rpl_drop_create_temp_table.inc 124 125--echo 126--echo # 127--echo #2) Generates in the binlog what follows: 128--echo # 129SET @commands= 'B T Drop-Temp-T-Temp C'; 130--source extra/rpl_tests/rpl_drop_create_temp_table.inc 131 132SET @commands= 'B T Drop-Temp-T-Temp N Drop-Temp-T-Temp C'; 133--source extra/rpl_tests/rpl_drop_create_temp_table.inc 134 135SET @commands= 'B T Drop-Temp-N-Temp C'; 136--source extra/rpl_tests/rpl_drop_create_temp_table.inc 137 138SET @commands= 'B T Drop-Temp-N-Temp N Drop-Temp-N-Temp C'; 139--source extra/rpl_tests/rpl_drop_create_temp_table.inc 140 141SET @commands= 'B T Drop-Temp-Xe-Temp C'; 142--source extra/rpl_tests/rpl_drop_create_temp_table.inc 143 144SET @commands= 'B T Drop-Temp-Xe-Temp N Drop-Temp-Xe-Temp C'; 145--source extra/rpl_tests/rpl_drop_create_temp_table.inc 146 147SET @commands= 'B T Drop-Temp-If-Xe-Temp C'; 148--source extra/rpl_tests/rpl_drop_create_temp_table.inc 149 150SET @commands= 'B T Drop-Temp-If-Xe-Temp N Drop-Temp-If-Xe-Temp C'; 151--source extra/rpl_tests/rpl_drop_create_temp_table.inc 152 153SET @commands= 'B T Drop-Temp-TXe-Temp C'; 154--source extra/rpl_tests/rpl_drop_create_temp_table.inc 155 156SET @commands= 'B T Drop-Temp-TXe-Temp N Drop-Temp-TXe-Temp C'; 157--source extra/rpl_tests/rpl_drop_create_temp_table.inc 158 159SET @commands= 'B T Drop-Temp-If-TXe-Temp C'; 160--source extra/rpl_tests/rpl_drop_create_temp_table.inc 161 162SET @commands= 'B T Drop-Temp-If-TXe-Temp N Drop-Temp-If-TXe-Temp C'; 163--source extra/rpl_tests/rpl_drop_create_temp_table.inc 164 165SET @commands= 'B T Drop-Temp-NXe-Temp C'; 166--source extra/rpl_tests/rpl_drop_create_temp_table.inc 167 168SET @commands= 'B T Drop-Temp-NXe-Temp N Drop-Temp-NXe-Temp C'; 169--source extra/rpl_tests/rpl_drop_create_temp_table.inc 170 171SET @commands= 'B T Drop-Temp-If-NXe-Temp C'; 172--source extra/rpl_tests/rpl_drop_create_temp_table.inc 173 174SET @commands= 'B T Drop-Temp-If-NXe-Temp N Drop-Temp-If-NXe-Temp C'; 175--source extra/rpl_tests/rpl_drop_create_temp_table.inc 176 177SET @commands= 'B T Drop-Temp-TN-Temp C'; 178--source extra/rpl_tests/rpl_drop_create_temp_table.inc 179 180SET @commands= 'B T Drop-Temp-TN-Temp N Drop-Temp-TN-Temp C'; 181--source extra/rpl_tests/rpl_drop_create_temp_table.inc 182 183SET @commands= 'B T Drop-Temp-TT-Temp C'; 184--source extra/rpl_tests/rpl_drop_create_temp_table.inc 185 186SET @commands= 'B T Drop-Temp-TT-Temp N Drop-Temp-TT-Temp C'; 187--source extra/rpl_tests/rpl_drop_create_temp_table.inc 188 189SET @commands= 'B T Drop-Temp-NN-Temp C'; 190--source extra/rpl_tests/rpl_drop_create_temp_table.inc 191 192SET @commands= 'B T Drop-Temp-NN-Temp N Drop-Temp-NN-Temp C'; 193--source extra/rpl_tests/rpl_drop_create_temp_table.inc 194 195SET @commands= 'B T Drop-Temp-NN-Temp N Drop-Temp-NN-Temp Ne C'; 196--source extra/rpl_tests/rpl_drop_create_temp_table.inc 197 198SET @commands= 'B T Drop-Temp-NN-Temp N Drop-Temp-NN-Temp Te C'; 199--source extra/rpl_tests/rpl_drop_create_temp_table.inc 200 201SET @commands= 'B T Drop-Temp-NN-Temp N Drop-Temp-NN-Temp NeT-trig C'; 202--source extra/rpl_tests/rpl_drop_create_temp_table.inc 203 204--echo 205--echo # 206--echo #3) Generates in the binlog what follows: 207--echo # 208SET @commands= 'B T Drop-Temp-T-Temp R'; 209--source extra/rpl_tests/rpl_drop_create_temp_table.inc 210 211SET @commands= 'B T Drop-Temp-T-Temp N Drop-Temp-T-Temp R'; 212--source extra/rpl_tests/rpl_drop_create_temp_table.inc 213 214SET @commands= 'B T Drop-Temp-N-Temp R'; 215--source extra/rpl_tests/rpl_drop_create_temp_table.inc 216 217SET @commands= 'B T Drop-Temp-N-Temp N Drop-Temp-N-Temp R'; 218--source extra/rpl_tests/rpl_drop_create_temp_table.inc 219 220SET @commands= 'B T Drop-Temp-Xe-Temp R'; 221--source extra/rpl_tests/rpl_drop_create_temp_table.inc 222 223SET @commands= 'B T Drop-Temp-Xe-Temp N Drop-Temp-Xe-Temp R'; 224--source extra/rpl_tests/rpl_drop_create_temp_table.inc 225 226SET @commands= 'B T Drop-Temp-If-Xe-Temp R'; 227--source extra/rpl_tests/rpl_drop_create_temp_table.inc 228 229SET @commands= 'B T Drop-Temp-If-Xe-Temp N Drop-Temp-If-Xe-Temp R'; 230--source extra/rpl_tests/rpl_drop_create_temp_table.inc 231 232SET @commands= 'B T Drop-Temp-TXe-Temp R'; 233--source extra/rpl_tests/rpl_drop_create_temp_table.inc 234 235SET @commands= 'B T Drop-Temp-TXe-Temp N Drop-Temp-TXe-Temp R'; 236--source extra/rpl_tests/rpl_drop_create_temp_table.inc 237 238SET @commands= 'B T Drop-Temp-If-TXe-Temp R'; 239--source extra/rpl_tests/rpl_drop_create_temp_table.inc 240 241SET @commands= 'B T Drop-Temp-If-TXe-Temp N Drop-Temp-If-TXe-Temp R'; 242--source extra/rpl_tests/rpl_drop_create_temp_table.inc 243 244SET @commands= 'B T Drop-Temp-NXe-Temp R'; 245--source extra/rpl_tests/rpl_drop_create_temp_table.inc 246 247SET @commands= 'B T Drop-Temp-NXe-Temp N Drop-Temp-NXe-Temp R'; 248--source extra/rpl_tests/rpl_drop_create_temp_table.inc 249 250SET @commands= 'B T Drop-Temp-If-NXe-Temp R'; 251--source extra/rpl_tests/rpl_drop_create_temp_table.inc 252 253SET @commands= 'B T Drop-Temp-If-NXe-Temp N Drop-Temp-If-NXe-Temp R'; 254--source extra/rpl_tests/rpl_drop_create_temp_table.inc 255 256SET @commands= 'B T Drop-Temp-TN-Temp R'; 257--source extra/rpl_tests/rpl_drop_create_temp_table.inc 258 259SET @commands= 'B T Drop-Temp-TN-Temp N Drop-Temp-TN-Temp R'; 260--source extra/rpl_tests/rpl_drop_create_temp_table.inc 261 262SET @commands= 'B T Drop-Temp-TT-Temp R'; 263--source extra/rpl_tests/rpl_drop_create_temp_table.inc 264 265SET @commands= 'B T Drop-Temp-TT-Temp N Drop-Temp-TT-Temp R'; 266--source extra/rpl_tests/rpl_drop_create_temp_table.inc 267 268SET @commands= 'B T Drop-Temp-NN-Temp R'; 269--source extra/rpl_tests/rpl_drop_create_temp_table.inc 270 271SET @commands= 'B T Drop-Temp-NN-Temp N Drop-Temp-NN-Temp R'; 272--source extra/rpl_tests/rpl_drop_create_temp_table.inc 273 274SET @commands= 'B T Drop-Temp-NN-Temp N Drop-Temp-NN-Temp Ne R'; 275--source extra/rpl_tests/rpl_drop_create_temp_table.inc 276 277SET @commands= 'B T Drop-Temp-NN-Temp N Drop-Temp-NN-Temp Te R'; 278--source extra/rpl_tests/rpl_drop_create_temp_table.inc 279 280SET @commands= 'B T Drop-Temp-NN-Temp N Drop-Temp-NN-Temp NeT-trig R'; 281--source extra/rpl_tests/rpl_drop_create_temp_table.inc 282 283--echo ######################################################################### 284--echo # 2 - Tables dropped by "DROP TABLE" 285--echo ######################################################################### 286connection master; 287 288--echo 289--echo # 290--echo #1) Generates in the binlog what follows: 291--echo # 292SET @commands= 'Drop-T'; 293--source extra/rpl_tests/rpl_drop_create_temp_table.inc 294 295SET @commands= 'Drop-N'; 296--source extra/rpl_tests/rpl_drop_create_temp_table.inc 297 298SET @commands= 'Drop-Xe'; 299--source extra/rpl_tests/rpl_drop_create_temp_table.inc 300 301SET @commands= 'Drop-If-Xe'; 302--source extra/rpl_tests/rpl_drop_create_temp_table.inc 303 304SET @commands= 'Drop-TXe'; 305--source extra/rpl_tests/rpl_drop_create_temp_table.inc 306 307SET @commands= 'Drop-If-TXe'; 308--source extra/rpl_tests/rpl_drop_create_temp_table.inc 309 310SET @commands= 'Drop-NXe'; 311--source extra/rpl_tests/rpl_drop_create_temp_table.inc 312 313SET @commands= 'Drop-If-NXe'; 314--source extra/rpl_tests/rpl_drop_create_temp_table.inc 315 316SET @commands= 'Drop-TN'; 317--source extra/rpl_tests/rpl_drop_create_temp_table.inc 318 319SET @commands= 'Drop-TT'; 320--source extra/rpl_tests/rpl_drop_create_temp_table.inc 321 322SET @commands= 'Drop-NN'; 323--source extra/rpl_tests/rpl_drop_create_temp_table.inc 324 325SET @commands= 'Drop-N-TN-Temp'; 326--source extra/rpl_tests/rpl_drop_create_temp_table.inc 327 328SET @commands= 'Drop-TN-Temp'; 329--source extra/rpl_tests/rpl_drop_create_temp_table.inc 330 331--echo 332--echo # 333--echo #2) Generates in the binlog what follows: 334--echo # 335SET @commands= 'B T Drop-T'; 336--source extra/rpl_tests/rpl_drop_create_temp_table.inc 337 338SET @commands= 'B T Drop-N'; 339--source extra/rpl_tests/rpl_drop_create_temp_table.inc 340 341SET @commands= 'B T Drop-Xe'; 342--source extra/rpl_tests/rpl_drop_create_temp_table.inc 343 344SET @commands= 'B T Drop-If-Xe'; 345--source extra/rpl_tests/rpl_drop_create_temp_table.inc 346 347SET @commands= 'B T Drop-TXe'; 348--source extra/rpl_tests/rpl_drop_create_temp_table.inc 349 350SET @commands= 'B T Drop-If-TXe'; 351--source extra/rpl_tests/rpl_drop_create_temp_table.inc 352 353SET @commands= 'B T Drop-NXe'; 354--source extra/rpl_tests/rpl_drop_create_temp_table.inc 355 356SET @commands= 'B T Drop-If-NXe'; 357--source extra/rpl_tests/rpl_drop_create_temp_table.inc 358 359SET @commands= 'B T Drop-TN'; 360--source extra/rpl_tests/rpl_drop_create_temp_table.inc 361 362SET @commands= 'B T Drop-TT'; 363--source extra/rpl_tests/rpl_drop_create_temp_table.inc 364 365SET @commands= 'B T Drop-NN'; 366--source extra/rpl_tests/rpl_drop_create_temp_table.inc 367 368SET @commands= 'B T Drop-N-TN-Temp'; 369--source extra/rpl_tests/rpl_drop_create_temp_table.inc 370 371SET @commands= 'B T Drop-TN-Temp'; 372--source extra/rpl_tests/rpl_drop_create_temp_table.inc 373 374--echo ######################################################################### 375--echo # 3 - CREATE TEMPORARY TABLE 376--echo ######################################################################### 377connection master; 378 379--echo 380--echo # 381--echo #1) Generates in the binlog what follows: 382--echo # 383SET @commands= 'Drop-Temp-N-Temp Create-N-Temp Drop-Temp-N-Temp Drop-Temp-N-Temp'; 384--source extra/rpl_tests/rpl_drop_create_temp_table.inc 385 386SET @commands= 'Drop-Temp-T-Temp Create-T-Temp Drop-Temp-T-Temp Drop-Temp-T-Temp'; 387--source extra/rpl_tests/rpl_drop_create_temp_table.inc 388 389--echo 390--echo # 391--echo #2) Generates in the binlog what follows: 392--echo # 393SET @commands= 'B T Drop-Temp-N-Temp Create-N-Temp Drop-Temp-N-Temp Drop-Temp-N-Temp C'; 394--source extra/rpl_tests/rpl_drop_create_temp_table.inc 395 396SET @commands= 'B Drop-Temp-T-Temp Create-T-Temp Drop-Temp-T-Temp Drop-Temp-T-Temp C'; 397--source extra/rpl_tests/rpl_drop_create_temp_table.inc 398 399SET @commands= 'B Drop-Temp-T-Temp Create-T-Temp Drop-Temp-T-Temp Drop-Temp-T-Temp Ne C'; 400--source extra/rpl_tests/rpl_drop_create_temp_table.inc 401 402SET @commands= 'B Drop-Temp-T-Temp Create-T-Temp Drop-Temp-T-Temp Drop-Temp-T-Temp Te C'; 403--source extra/rpl_tests/rpl_drop_create_temp_table.inc 404 405SET @commands= 'B Drop-Temp-T-Temp Create-T-Temp Drop-Temp-T-Temp Drop-Temp-T-Temp NeT-trig C'; 406--source extra/rpl_tests/rpl_drop_create_temp_table.inc 407 408--echo 409--echo # 410--echo #3) Generates in the binlog what follows: 411--echo # 412SET @commands= 'B T Drop-Temp-N-Temp Create-N-Temp Drop-Temp-N-Temp Drop-Temp-N-Temp R'; 413--source extra/rpl_tests/rpl_drop_create_temp_table.inc 414 415SET @commands= 'B Drop-Temp-T-Temp Create-T-Temp Drop-Temp-T-Temp Drop-Temp-T-Temp R'; 416--source extra/rpl_tests/rpl_drop_create_temp_table.inc 417 418SET @commands= 'B Drop-Temp-T-Temp Create-T-Temp Drop-Temp-T-Temp Drop-Temp-T-Temp Ne R'; 419--source extra/rpl_tests/rpl_drop_create_temp_table.inc 420 421SET @commands= 'B Drop-Temp-T-Temp Create-T-Temp Drop-Temp-T-Temp Drop-Temp-T-Temp Te R'; 422--source extra/rpl_tests/rpl_drop_create_temp_table.inc 423 424SET @commands= 'B Drop-Temp-T-Temp Create-T-Temp Drop-Temp-T-Temp Drop-Temp-T-Temp NeT-trig R'; 425--source extra/rpl_tests/rpl_drop_create_temp_table.inc 426 427--echo ######################################################################### 428--echo # 4 - CHANGING TEMPORARY TABLES 429--echo ######################################################################### 430connection master; 431 432--echo 433--echo # 434--echo #1) Generates in the binlog what follows: 435--echo # 436SET @commands= 'B N N-Temp N-SELECT-N-Temp N-Temp C'; 437--source extra/rpl_tests/rpl_drop_create_temp_table.inc 438 439SET @commands= 'B N N-Temp T-SELECT-N-Temp N-Temp C'; 440--source extra/rpl_tests/rpl_drop_create_temp_table.inc 441 442SET @commands= 'B N N-Temp N-SELECT-T-Temp N-Temp C'; 443--source extra/rpl_tests/rpl_drop_create_temp_table.inc 444 445SET @commands= 'B N N-Temp T-SELECT-T-Temp N-Temp C'; 446--source extra/rpl_tests/rpl_drop_create_temp_table.inc 447 448SET @commands= 'B N N-Temp T-SELECT-T-Temp N-Temp Ne C'; 449--source extra/rpl_tests/rpl_drop_create_temp_table.inc 450 451SET @commands= 'B N N-Temp T-SELECT-T-Temp N-Temp Te C'; 452--source extra/rpl_tests/rpl_drop_create_temp_table.inc 453 454SET @commands= 'B N N-Temp T-SELECT-T-Temp N-Temp NeT-trig C'; 455--source extra/rpl_tests/rpl_drop_create_temp_table.inc 456 457--echo 458--echo 459--echo # 460--echo #2) Generates in the binlog what follows: 461--echo # 462SET @commands= 'B N N-Temp N-SELECT-N-Temp N-Temp R'; 463--source extra/rpl_tests/rpl_drop_create_temp_table.inc 464 465SET @commands= 'B N N-Temp T-SELECT-N-Temp N-Temp R'; 466--source extra/rpl_tests/rpl_drop_create_temp_table.inc 467 468SET @commands= 'B N N-Temp N-SELECT-T-Temp N-Temp R'; 469--source extra/rpl_tests/rpl_drop_create_temp_table.inc 470 471SET @commands= 'B N N-Temp T-SELECT-T-Temp N-Temp R'; 472--source extra/rpl_tests/rpl_drop_create_temp_table.inc 473 474SET @commands= 'B N N-Temp T-SELECT-T-Temp N-Temp Ne R'; 475--source extra/rpl_tests/rpl_drop_create_temp_table.inc 476 477SET @commands= 'B N N-Temp T-SELECT-T-Temp N-Temp Te R'; 478--source extra/rpl_tests/rpl_drop_create_temp_table.inc 479 480SET @commands= 'B N N-Temp T-SELECT-T-Temp N-Temp NeT-trig R'; 481--source extra/rpl_tests/rpl_drop_create_temp_table.inc 482 483--echo ################################################################################### 484--echo # CHECK CONSISTENCY 485--echo ################################################################################### 486connection master; 487sync_slave_with_master; 488 489let $MYSQLD_DATADIR= `SELECT @@datadir`; 490 491if (`select @@session.binlog_format != 'STATEMENT'`) 492{ 493 --exec $MYSQL_DUMP --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLD_DATADIR/test-temporary-master.sql 494 --exec $MYSQL_DUMP_SLAVE --compact --order-by-primary --skip-extended-insert --no-create-info test > $MYSQLD_DATADIR/test-temporary-slave.sql 495 --diff_files $MYSQLD_DATADIR/test-temporary-master.sql $MYSQLD_DATADIR/test-temporary-slave.sql 496} 497 498--echo ######################################################################### 499--echo # CLEAN 500--echo ######################################################################### 501SET @commands= 'clean'; 502--source extra/rpl_tests/rpl_drop_create_temp_table.inc 503