1--source include/have_innodb.inc 2--source include/count_sessions.inc 3--echo '# SET STATEMENT ..... FOR .... TEST' 4############################ STATEMENT_SET ############################# 5# # 6# Testing working functionality of SET STATEMENT # 7# # 8# # 9# There is important documentation within # 10# # 11# # 12# Author: Joe Lukas # 13# Creation: # 14# 2009-08-02 Implement this test as part of # 15# WL#681 Per query variable settings # 16# # 17######################################################################## 18 19--disable_warnings 20DROP TABLE IF EXISTS t1; 21DROP FUNCTION IF EXISTS myProc; 22DROP PROCEDURE IF EXISTS p1; 23DROP PROCEDURE IF EXISTS p2; 24DROP PROCEDURE IF EXISTS p3; 25DROP PROCEDURE IF EXISTS p4; 26DROP PROCEDURE IF EXISTS p5; 27DROP TABLE IF EXISTS STATEMENT; 28--enable_warnings 29#################################################################### 30#Set up current database 31#################################################################### 32--echo '# Setup database' 33CREATE TABLE t1 (v1 INT, v2 INT) ENGINE=MyISAM; 34INSERT INTO t1 VALUES (1,2); 35INSERT INTO t1 VALUES (3,4); 36--echo '' 37--echo '#------------------ STATEMENT Test 1 -----------------------#' 38#################################################################### 39# Checks with variable value type ulong # 40#################################################################### 41--echo '# Initialize variables to known setting' 42SET SESSION sort_buffer_size=100000; 43--echo '' 44--echo '# Pre-STATEMENT variable value' 45SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; 46SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t1; 47--echo '' 48--echo '# Post-STATEMENT variable value' 49SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; 50--echo '' 51--echo '#------------------ STATEMENT Test 2 -----------------------#' 52#################################################################### 53# Checks for multiple set values inside STATEMENT ... FOR # 54#################################################################### 55--echo '# Initialize variables to known setting' 56SET SESSION binlog_format=mixed; 57SET SESSION sort_buffer_size=100000; 58--echo '# Pre-STATEMENT variable value' 59SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; 60SHOW SESSION VARIABLES LIKE 'binlog_format'; 61SET STATEMENT sort_buffer_size=150000, binlog_format=row 62 FOR SELECT * FROM t1; 63--echo '# Post-STATEMENT variable value' 64SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; 65SHOW SESSION VARIABLES LIKE 'binlog_format'; 66 67--echo '' 68--echo '#------------------ STATEMENT Test 3 -----------------------#' 69#################################################################### 70# Check current variable value is stored in using stored # 71# statements. # 72#################################################################### 73--echo '# set initial variable value, make prepared statement 74SET SESSION binlog_format=row; 75PREPARE stmt1 FROM 'SET STATEMENT binlog_format=row FOR SELECT * FROM t1'; 76--echo '' 77--echo '# Change variable setting' 78SET SESSION binlog_format=mixed; 79--echo '' 80--echo '# Pre-STATEMENT variable value' 81--echo '' 82SHOW SESSION VARIABLES LIKE 'binlog_format'; 83--echo '' 84EXECUTE stmt1; 85--echo '' 86--echo '# Post-STATEMENT variable value' 87SHOW SESSION VARIABLES LIKE 'binlog_format'; 88 89--echo '' 90DEALLOCATE PREPARE stmt1; 91--echo '#------------------ STATEMENT Test 4 -----------------------#' 92#################################################################### 93# Check works with OPTIMIZE TABLE command # 94# Checks works with a variable value of type INT # 95# Checks works with variable type ULONGLONG # 96#################################################################### 97--echo '# set initial variable value, make prepared statement 98SET SESSION myisam_sort_buffer_size=500000, myisam_repair_threads=1; 99--echo '' 100--echo '# Pre-STATEMENT variable value' 101SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size'; 102SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; 103--echo '' 104SET STATEMENT myisam_sort_buffer_size=800000, 105 myisam_repair_threads=2 FOR OPTIMIZE TABLE t1; 106--echo '' 107--echo '# Post-STATEMENT variable value' 108SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size'; 109SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; 110 111--echo '' 112--echo '#------------------ STATEMENT Test 5 -----------------------#' 113#################################################################### 114# Checks if variable reset after error in statement after FOR # 115#################################################################### 116--echo '# Initialize variables to known setting' 117SET SESSION sort_buffer_size=100000; 118--echo '' 119--echo '# Pre-STATEMENT variable value' 120SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; 121--echo '' 122--error ER_NO_SUCH_TABLE 123SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t2; 124--echo '' 125--echo '# Post-STATEMENT variable value' 126SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; 127 128--echo '' 129--echo '#------------------ STATEMENT Test 6 -----------------------#' 130#################################################################### 131# Checks works with variable type MY_BOOL # 132#################################################################### 133--echo '# Initialize variables to known setting' 134SET SESSION keep_files_on_create=ON; 135--echo '' 136--echo '# Pre-STATEMENT variable value' 137SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; 138--echo '' 139SET STATEMENT keep_files_on_create=OFF FOR SELECT * FROM t1; 140--echo '' 141--echo '# Post-STATEMENT variable value' 142SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; 143 144--echo '' 145--echo '#------------------ STATEMENT Test 7 -----------------------#' 146#################################################################### 147# Checks works with variable type HA_ROWS # 148#################################################################### 149--echo '# Initialize variables to known setting' 150SET SESSION max_join_size=2222220000000; 151--echo '' 152--echo '# Pre-STATEMENT variable value' 153SHOW SESSION VARIABLES LIKE 'max_join_size'; 154--echo '' 155SET STATEMENT max_join_size=1000000000000 FOR SELECT * FROM t1; 156--echo '' 157--echo '# Post-STATEMENT variable value' 158SHOW SESSION VARIABLES LIKE 'max_join_size'; 159 160--echo '' 161--echo '#------------------Test 8-----------------------#' 162#################################################################### 163# Ensure variable of each type is set to proper value during # 164# statement after FOR execution # 165#################################################################### 166--echo '# Initialize test variables' 167SET SESSION myisam_sort_buffer_size=500000, 168 myisam_repair_threads=1, 169 sort_buffer_size = 200000, 170 max_join_size=2222220000000, 171 keep_files_on_create=ON; 172 173--echo '' 174--echo '# LONG ' 175SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; 176SET STATEMENT sort_buffer_size = 100000 177 FOR SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; 178SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; 179--echo '' 180--echo '# MY_BOOL ' 181SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; 182SET STATEMENT keep_files_on_create=OFF 183 FOR SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; 184SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; 185 186--echo '' 187--echo '# INT/LONG ' 188SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; 189SET STATEMENT myisam_repair_threads=2 190 FOR SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; 191SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; 192--echo '' 193--echo '# ULONGLONG ' 194SHOW SESSION VARIABLES LIKE 'max_join_size'; 195SET STATEMENT max_join_size=2000000000000 196 FOR SHOW SESSION VARIABLES LIKE 'max_join_size'; 197SHOW SESSION VARIABLES LIKE 'max_join_size'; 198 199--echo '' 200--echo '#------------------Test 9-----------------------#' 201#################################################################### 202# No 1 - Check works with CREATE ... BEGIN ... END command # 203# Display variables during execution # 204# No 2 - Test with DROP command # 205#################################################################### 206--echo '# set initial variable values 207SET SESSION myisam_sort_buffer_size=500000, 208 myisam_repair_threads=1, 209 sort_buffer_size=100000, 210 binlog_format=mixed, 211 keep_files_on_create=ON, 212 max_join_size=2222220000000; 213--echo '' 214--echo '' 215--echo '# Pre-STATEMENT variable value 216SELECT @@myisam_sort_buffer_size, 217 @@myisam_repair_threads, 218 @@sort_buffer_size, 219 @@binlog_format, 220 @@keep_files_on_create, 221 @@max_join_size; 222--echo '' 223--echo '' 224DELIMITER |; 225CREATE FUNCTION myProc (cost DECIMAL(10,2)) 226 RETURNS DECIMAL(10,2) 227 228 SQL SECURITY DEFINER 229 230 tax: BEGIN 231 DECLARE order_tax DECIMAL(10,2); 232 SET order_tax = cost * .05; 233 RETURN order_tax; 234 END| 235DELIMITER ;| 236--echo '' 237--echo '# During Execution values 238SET STATEMENT myisam_sort_buffer_size=400000, 239 myisam_repair_threads=2, 240 sort_buffer_size=200000, 241 binlog_format=row, 242 keep_files_on_create=OFF, 243 max_join_size=4444440000000 FOR 244 SELECT myProc(123.45); 245--echo '' 246--echo '# Post-STATEMENT No 1 variable value Pre-STATEMENT for No 2' 247SELECT @@myisam_sort_buffer_size, 248 @@myisam_repair_threads, 249 @@sort_buffer_size, 250 @@binlog_format, 251 @@keep_files_on_create, 252 @@max_join_size; 253--echo '' 254SET STATEMENT myisam_sort_buffer_size=400000, 255 myisam_repair_threads=2, 256 sort_buffer_size=200000, 257 binlog_format=row, 258 keep_files_on_create=OFF, 259 max_join_size=4444440000000 FOR 260 DROP FUNCTION myProc; 261--echo '' 262--echo '# Post-STATEMENT No 2 variable value 263SELECT @@myisam_sort_buffer_size, 264 @@myisam_repair_threads, 265 @@sort_buffer_size, 266 @@binlog_format, 267 @@keep_files_on_create, 268 @@max_join_size; 269 270--echo '' 271--echo '#------------------Test 10-----------------------#' 272#################################################################### 273# No 1 - Check with PREPARE statement # 274# with STATEMENT inside with same variable as outside # 275# No 2 - Check with EXECUTE statement # 276#################################################################### 277--echo '# set initial variable values 278SET SESSION myisam_sort_buffer_size=500000, 279 myisam_repair_threads=1, 280 sort_buffer_size=100000, 281 binlog_format=mixed, 282 keep_files_on_create=ON, 283 max_join_size=2222220000000; 284--echo '' 285--echo '# Pre-STATEMENT variable value 286SELECT @@myisam_sort_buffer_size, 287 @@myisam_repair_threads, 288 @@sort_buffer_size, 289 @@binlog_format, 290 @@keep_files_on_create, 291 @@max_join_size; 292--echo '' 293--echo '' 294SET STATEMENT myisam_sort_buffer_size=400000, 295 myisam_repair_threads=2, 296 sort_buffer_size=200000, 297 binlog_format=row, 298 keep_files_on_create=OFF, 299 max_join_size=4444440000000 FOR 300 PREPARE stmt2 301 FROM 'SELECT * FROM t1'; 302--echo '' 303--echo 'Test No 1 Post Value & Test 2 Pre values' 304SELECT @@myisam_sort_buffer_size, 305 @@myisam_repair_threads, 306 @@sort_buffer_size, 307 @@binlog_format, 308 @@keep_files_on_create, 309 @@max_join_size; 310--echo '' 311--echo '' 312SET STATEMENT myisam_sort_buffer_size=400000, 313 myisam_repair_threads=2, 314 sort_buffer_size=200000, 315 binlog_format=row, 316 keep_files_on_create=OFF, 317 max_join_size=4444440000000 FOR 318 EXECUTE stmt2; 319--echo '' 320--echo '# Post-STATEMENT No 2 321SELECT @@myisam_sort_buffer_size, 322 @@myisam_repair_threads, 323 @@sort_buffer_size, 324 @@binlog_format, 325 @@keep_files_on_create, 326 @@max_join_size; 327--echo '' 328DEALLOCATE PREPARE stmt2; 329--echo '' 330--echo '#------------------Test 11-----------------------#' 331#################################################################### 332# No 1 - Check with PREPARE statement # 333# check with different variable on inside PREPARE # 334# No 2 - Check with EXECUTE statement # 335#################################################################### 336--echo '# set initial variable values 337SET SESSION myisam_sort_buffer_size=500000, 338 myisam_repair_threads=1, 339 sort_buffer_size=100000, 340 binlog_format=mixed, 341 keep_files_on_create=ON, 342 max_join_size=2222220000000; 343--echo '' 344--echo '' 345--echo '# Pre-STATEMENT variable value 346SELECT @@myisam_sort_buffer_size, 347 @@myisam_repair_threads, 348 @@sort_buffer_size, 349 @@binlog_format, 350 @@keep_files_on_create, 351 @@max_join_size; 352--echo '' 353--echo '' 354SET STATEMENT myisam_sort_buffer_size=400000, 355 myisam_repair_threads=2, 356 sort_buffer_size=200000, 357 keep_files_on_create=OFF, 358 max_join_size=4444440000000 FOR 359 PREPARE stmt1 FROM 360 'SET STATEMENT binlog_format=row FOR SELECT * FROM t1'; 361--echo '' 362--echo 'Test No 1 Post Value & Test 2 Pre values' 363SELECT @@myisam_sort_buffer_size, 364 @@myisam_repair_threads, 365 @@sort_buffer_size, 366 @@binlog_format, 367 @@keep_files_on_create, 368 @@max_join_size; 369--echo '' 370--echo '' 371SET STATEMENT myisam_sort_buffer_size=400000, 372 myisam_repair_threads=2, 373 sort_buffer_size=200000, 374 keep_files_on_create=OFF, 375 max_join_size=4444440000000 FOR 376 EXECUTE stmt1; 377--echo '' 378--echo '# Post-STATEMENT No 2 379SELECT @@myisam_sort_buffer_size, 380 @@myisam_repair_threads, 381 @@sort_buffer_size, 382 @@binlog_format, 383 @@keep_files_on_create, 384 @@max_join_size; 385--echo '' 386--echo '' 387--echo '#------------------Test 12-----------------------#' 388#################################################################### 389# No 1 - Check with PROCEDURE (show variables in procedure) # 390# No 2 - Check with CALL statement show variables in PROCEDURE # 391#################################################################### 392--echo '# set initial variable values 393SET SESSION myisam_sort_buffer_size=500000, 394 myisam_repair_threads=1, 395 sort_buffer_size=100000, 396 binlog_format=mixed, 397 keep_files_on_create=ON, 398 max_join_size=2222220000000; 399--echo '' 400--echo '' 401--echo '# Pre-STATEMENT variable value 402SELECT @@myisam_sort_buffer_size, 403 @@myisam_repair_threads, 404 @@sort_buffer_size, 405 @@binlog_format, 406 @@keep_files_on_create, 407 @@max_join_size; 408--echo '' 409--echo '' 410DELIMITER |; 411SET STATEMENT myisam_sort_buffer_size=400000, 412 myisam_repair_threads=2, 413 sort_buffer_size=200000, 414 binlog_format=row, 415 keep_files_on_create=OFF, 416 max_join_size=4444440000000 FOR 417 CREATE PROCEDURE p1() BEGIN 418 SELECT @@myisam_sort_buffer_size, 419 @@myisam_repair_threads, 420 @@sort_buffer_size, 421 @@binlog_format, 422 @@keep_files_on_create, 423 @@max_join_size; 424 END| 425DELIMITER ;| 426--echo '' 427--echo 'Test No 1 Post Value & Test 2 Pre values' 428SELECT @@myisam_sort_buffer_size, 429 @@myisam_repair_threads, 430 @@sort_buffer_size, 431 @@binlog_format, 432 @@keep_files_on_create, 433 @@max_join_size; 434--echo '' 435--echo '' 436SET STATEMENT myisam_sort_buffer_size=400000, 437 myisam_repair_threads=2, 438 sort_buffer_size=200000, 439 binlog_format=row, 440 keep_files_on_create=OFF, 441 max_join_size=4444440000000 FOR 442 CALL p1(); 443--echo '' 444--echo '# Post-STATEMENT No 2 445SELECT @@myisam_sort_buffer_size, 446 @@myisam_repair_threads, 447 @@sort_buffer_size, 448 @@binlog_format, 449 @@keep_files_on_create, 450 @@max_join_size; 451--echo '' 452--echo '' 453 454--echo '#------------------Test 13-----------------------#' 455#################################################################### 456# Check PROCEDURE containing SET STATEMENT FOR # 457# p1() from test 12 will be used to display variables # 458#################################################################### 459--echo '# set initial variable values 460SET SESSION myisam_sort_buffer_size=500000, 461 myisam_repair_threads=1, 462 sort_buffer_size=100000, 463 binlog_format=mixed, 464 keep_files_on_create=ON, 465 max_join_size=2222220000000; 466--echo '' 467--echo '' 468DELIMITER |; 469CREATE PROCEDURE p2() BEGIN 470 SET STATEMENT myisam_sort_buffer_size=400000, 471 myisam_repair_threads=3, 472 sort_buffer_size=300000, 473 binlog_format=mixed, 474 keep_files_on_create=OFF, 475 max_join_size=3333330000000 FOR 476 CALL p1(); 477 END| 478DELIMITER ;| 479--echo '' 480--echo '# Pre-STATEMENT variable value 481SELECT @@myisam_sort_buffer_size, 482 @@myisam_repair_threads, 483 @@sort_buffer_size, 484 @@binlog_format, 485 @@keep_files_on_create, 486 @@max_join_size; 487--echo '' 488--echo '' 489SET STATEMENT myisam_sort_buffer_size=400000, 490 myisam_repair_threads=2, 491 sort_buffer_size=200000, 492 binlog_format=row, 493 keep_files_on_create=OFF, 494 max_join_size=4444440000000 FOR 495 CALL p2(); 496--echo '' 497--echo '# Post-STATEMENT 498SELECT @@myisam_sort_buffer_size, 499 @@myisam_repair_threads, 500 @@sort_buffer_size, 501 @@binlog_format, 502 @@keep_files_on_create, 503 @@max_join_size; 504--echo '' 505--echo '' 506--echo '#------------------Test 14-----------------------#' 507#################################################################### 508# Check PROCEDURE containing compound SET STATEMENT FOR # 509# p2() will be used as compounding statement from test 13 # 510#################################################################### 511--echo '# set initial variable values 512SET SESSION myisam_sort_buffer_size=500000, 513 myisam_repair_threads=1, 514 sort_buffer_size=100000, 515 binlog_format=mixed, 516 keep_files_on_create=ON, 517 max_join_size=2222220000000; 518--echo '' 519--echo '' 520DELIMITER |; 521CREATE PROCEDURE p3() BEGIN 522 SELECT @@myisam_sort_buffer_size, 523 @@myisam_repair_threads, 524 @@sort_buffer_size, 525 @@binlog_format, 526 @@keep_files_on_create, 527 @@max_join_size; 528 SET STATEMENT myisam_sort_buffer_size=320000, 529 myisam_repair_threads=2, 530 sort_buffer_size=220022, 531 binlog_format=row, 532 keep_files_on_create=ON, 533 max_join_size=2222220000000 FOR 534 CALL p2(); 535 END| 536DELIMITER ;| 537--echo '' 538--echo '# Pre-STATEMENT variable value 539SELECT @@myisam_sort_buffer_size, 540 @@myisam_repair_threads, 541 @@sort_buffer_size, 542 @@binlog_format, 543 @@keep_files_on_create, 544 @@max_join_size; 545--echo '' 546--echo '' 547SET STATEMENT myisam_sort_buffer_size=400000, 548 myisam_repair_threads=2, 549 sort_buffer_size=200000, 550 binlog_format=row, 551 keep_files_on_create=OFF, 552 max_join_size=4444440000000 FOR 553 CALL p3(); 554--echo '' 555--echo '# Post-STATEMENT 556SELECT @@myisam_sort_buffer_size, 557 @@myisam_repair_threads, 558 @@sort_buffer_size, 559 @@binlog_format, 560 @@keep_files_on_create, 561 @@max_join_size; 562--echo '' 563--echo '' 564 565 --echo '' 566--echo '' 567--echo '#------------------Test 15-----------------------#' 568#################################################################### 569# Check PROCEDURE containing compound SET STATEMENT FOR # 570# call multiple SET STATEMENT .. FOR showing SELECT # 571#################################################################### 572--echo '# set initial variable values 573SET SESSION myisam_sort_buffer_size=500000, 574 myisam_repair_threads=1, 575 sort_buffer_size=100000, 576 binlog_format=mixed, 577 keep_files_on_create=ON, 578 max_join_size=2222220000000; 579--echo '' 580--echo '' 581DELIMITER |; 582CREATE PROCEDURE p4() BEGIN 583 SELECT @@myisam_sort_buffer_size, 584 @@myisam_repair_threads, 585 @@sort_buffer_size, 586 @@binlog_format, 587 @@keep_files_on_create, 588 @@max_join_size; 589 SET STATEMENT myisam_sort_buffer_size=320000, 590 myisam_repair_threads=2, 591 sort_buffer_size=220022, 592 binlog_format=row, 593 keep_files_on_create=ON, 594 max_join_size=2222220000000 FOR 595 SELECT @@myisam_sort_buffer_size, 596 @@myisam_repair_threads, 597 @@sort_buffer_size, 598 @@binlog_format, 599 @@keep_files_on_create, 600 @@max_join_size; 601 SET STATEMENT myisam_sort_buffer_size=320000, 602 myisam_repair_threads=2, 603 sort_buffer_size=220022, 604 binlog_format=row, 605 keep_files_on_create=ON, 606 max_join_size=2222220000000 FOR 607 SELECT @@myisam_sort_buffer_size, 608 @@myisam_repair_threads, 609 @@sort_buffer_size, 610 @@binlog_format, 611 @@keep_files_on_create, 612 @@max_join_size; 613 SET STATEMENT myisam_sort_buffer_size=320000, 614 myisam_repair_threads=2, 615 sort_buffer_size=220022, 616 binlog_format=row, 617 keep_files_on_create=ON, 618 max_join_size=2222220000000 FOR 619 SELECT @@myisam_sort_buffer_size, 620 @@myisam_repair_threads, 621 @@sort_buffer_size, 622 @@binlog_format, 623 @@keep_files_on_create, 624 @@max_join_size; 625 END| 626DELIMITER ;| 627--echo '' 628--echo '# Pre-STATEMENT variable value 629SELECT @@myisam_sort_buffer_size, 630 @@myisam_repair_threads, 631 @@sort_buffer_size, 632 @@binlog_format, 633 @@keep_files_on_create, 634 @@max_join_size; 635--echo '' 636--echo '' 637SET STATEMENT myisam_sort_buffer_size=400000, 638 myisam_repair_threads=2, 639 sort_buffer_size=200000, 640 binlog_format=row, 641 keep_files_on_create=OFF, 642 max_join_size=4444440000000 FOR 643 CALL p4(); 644--echo '' 645--echo '# Post-STATEMENT 646SELECT @@myisam_sort_buffer_size, 647 @@myisam_repair_threads, 648 @@sort_buffer_size, 649 @@binlog_format, 650 @@keep_files_on_create, 651 @@max_join_size; 652 653--echo '' 654--echo '' 655--echo '#------------------Test 16-----------------------#' 656#################################################################### 657# Test Effect on parsing # 658#################################################################### 659--echo '' 660--echo '# Pre-STATEMENT variable value 661SELECT @@sql_mode; 662--echo '' 663--echo '' 664SET STATEMENT sql_mode='ansi' FOR SELECT * FROM t1; 665 666--echo '' 667--echo '# Post-STATEMENT 668SELECT @@sql_mode; 669--echo '' 670--echo '' 671--echo '#------------------Test 17-----------------------#' 672#################################################################### 673# Test effect of SET STATEMENT FOR with SET SESSION modifying # 674# the same variables as the SET STATEMENT # 675#################################################################### 676--echo '# set initial variable values 677SET SESSION myisam_sort_buffer_size=500000, 678 myisam_repair_threads=1, 679 sort_buffer_size=100000, 680 binlog_format=mixed, 681 keep_files_on_create=ON, 682 max_join_size=2222220000000; 683--echo '' 684--echo '# Pre-STATEMENT variable value 685SELECT @@myisam_sort_buffer_size, 686 @@myisam_repair_threads, 687 @@sort_buffer_size, 688 @@binlog_format, 689 @@keep_files_on_create, 690 @@max_join_size; 691--echo '' 692--echo '' 693SET STATEMENT myisam_sort_buffer_size=320000, 694 myisam_repair_threads=2, 695 sort_buffer_size=220022, 696 binlog_format=row, 697 keep_files_on_create=ON, 698 max_join_size=2222220000000 699 FOR SET SESSION 700 myisam_sort_buffer_size=260000, 701 myisam_repair_threads=3, 702 sort_buffer_size=230013, 703 binlog_format=row, 704 keep_files_on_create=ON, 705 max_join_size=2323230000000; 706 707--echo '' 708--echo '# Post-STATEMENT 709SELECT @@myisam_sort_buffer_size, 710 @@myisam_repair_threads, 711 @@sort_buffer_size, 712 @@binlog_format, 713 @@keep_files_on_create, 714 @@max_join_size; 715 716--echo '' 717--echo '' 718--echo '#------------------Test 18-----------------------#' 719#################################################################### 720# Test effect of SET SESSION inside a stored procedure with # 721# with a SET STATEMENT on outside variables # 722#################################################################### 723--echo '# set initial variable values 724SET SESSION myisam_sort_buffer_size=500000, 725 myisam_repair_threads=1, 726 sort_buffer_size=100000, 727 binlog_format=mixed, 728 keep_files_on_create=ON, 729 max_join_size=2222220000000; 730--echo '' 731--echo '# Pre-STATEMENT variable value 732SELECT @@myisam_sort_buffer_size, 733 @@myisam_repair_threads, 734 @@sort_buffer_size, 735 @@binlog_format, 736 @@keep_files_on_create, 737 @@max_join_size; 738--echo '' 739--echo '' 740DELIMITER |; 741CREATE PROCEDURE p5() BEGIN 742 SELECT @@myisam_sort_buffer_size, 743 @@myisam_repair_threads, 744 @@sort_buffer_size, 745 @@binlog_format, 746 @@keep_files_on_create, 747 @@max_join_size; 748 SET SESSION 749 myisam_sort_buffer_size=260000, 750 myisam_repair_threads=3, 751 sort_buffer_size=230013, 752 binlog_format=row, 753 keep_files_on_create=ON, 754 max_join_size=2323230000000; 755 SELECT @@myisam_sort_buffer_size, 756 @@myisam_repair_threads, 757 @@sort_buffer_size, 758 @@binlog_format, 759 @@keep_files_on_create, 760 @@max_join_size; 761 END| 762DELIMITER ;| 763--echo '' 764--echo '' 765SET STATEMENT myisam_sort_buffer_size=400000, 766 myisam_repair_threads=2, 767 sort_buffer_size=200000, 768 binlog_format=row, 769 keep_files_on_create=OFF, 770 max_join_size=4444440000000 FOR 771 CALL p5(); 772 773--echo '' 774--echo '# Post-STATEMENT 775SELECT @@myisam_sort_buffer_size, 776 @@myisam_repair_threads, 777 @@sort_buffer_size, 778 @@binlog_format, 779 @@keep_files_on_create, 780 @@max_join_size; 781 782--echo '' 783--echo '' 784--echo '#------------------Test 19-----------------------#' 785#Test for bad syntax 786--error ER_PARSE_ERROR 787SET STATEMENT max_error_count=100 FOR; 788--error ER_PARSE_ERROR 789SET STATEMENT max_error_count=100 INSERT t1 VALUES (1,2); 790--error ER_PARSE_ERROR 791SET STATEMENT FOR INSERT INTO t1 VALUES (1,2); 792--error ER_PARSE_ERROR 793SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); 794--error ER_PARSE_ERROR 795SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); 796--error ER_UNKNOWN_SYSTEM_VARIABLE 797SET STATEMENT GLOBAL max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); 798--error ER_PARSE_ERROR 799SET STATEMENT @@global.max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); 800 801--echo '' 802--echo '' 803--echo '#------------------Test 20-----------------------#' 804#Test for global-only variables 805--error ER_GLOBAL_VARIABLE 806SET STATEMENT connect_timeout=100 FOR INSERT INTO t1 VALUES (1,2); 807 808--echo '' 809--echo '' 810--echo '#------------------Test 21-----------------------#' 811#Test for recursion 812SELECT @@myisam_sort_buffer_size, @@sort_buffer_size; 813SET STATEMENT myisam_sort_buffer_size = 700000, sort_buffer_size = 3000000 814 FOR SET STATEMENT myisam_sort_buffer_size=200000 815 FOR SELECT @@myisam_sort_buffer_size, @@sort_buffer_size; 816SELECT @@myisam_sort_buffer_size, @@sort_buffer_size; 817 818--echo '' 819--echo '' 820--echo '#------------------Test 22-----------------------#' 821#Test for STATEMENT keyword 822CREATE TABLE STATEMENT(a INT); 823DROP TABLE STATEMENT; 824 825--echo Test for bug 1418049: SET STATEMENT ... FOR <statement> crashes server 826--echo if <statement> needs to commit implicitly and fails 827 828CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB; 829 830SET @old_lock_wait_timeout = @@session.lock_wait_timeout; 831SET SESSION lock_wait_timeout = 1; 832 833BEGIN; 834INSERT INTO t2 VALUES (5); 835 836--connect(con1,localhost,root,,) 837--connection con1 838FLUSH TABLES WITH READ LOCK; 839 840--connection default 841--error ER_LOCK_WAIT_TIMEOUT 842SET STATEMENT max_join_size = 0 FOR DROP TABLE t2; 843 844--connection con1 845UNLOCK TABLES; 846--disconnect con1 847--connection default 848COMMIT; 849SET SESSION lock_wait_timeout = @old_lock_wait_timeout; 850 851--echo Test for bug 1387951: SET STATEMENT ... FOR <statement> crashes server 852--echo if <statement> is RW in a RO transaction 853SET @old_tx_read_only = @@session.tx_read_only; 854SET SESSION tx_read_only = TRUE; 855--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION 856SET STATEMENT myisam_repair_threads=0 FOR OPTIMIZE TABLE t0; 857SET SESSION tx_read_only = @old_tx_read_only; 858 859--echo Test for bug 1412423: SET STATEMENT ... FOR <statement> crashes server 860--echo if <statement> needs to re-open a temp table and fails 861CREATE TEMPORARY TABLE t3 (a INT PRIMARY KEY) ENGINE=InnoDB; 862HANDLER t3 OPEN; 863--error ER_CANT_REOPEN_TABLE 864SET STATEMENT max_join_size=1000 FOR SELECT * FROM t3; 865DROP TABLE t3; 866 867--echo # 868--echo # Bug 1392375: Crashing repeated execution of SET STATEMENT ... FOR <SELECT FROM view> 869--echo # 870 871CREATE VIEW t3 AS SELECT 1 AS a; 872PREPARE stmt1 FROM 'SET STATEMENT binlog_format=row FOR SELECT * FROM t3'; 873EXECUTE stmt1; 874EXECUTE stmt1; 875 876DEALLOCATE PREPARE stmt1; 877DROP VIEW t3; 878 879--echo # 880--echo # Bug 1635927: Memory leak when using per-query variables with subquery temp tables 881--echo # 882 883CREATE TABLE t3 (row_key INT NOT NULL DEFAULT 0, ref_key BIGINT(20) NOT NULL); 884 885INSERT INTO t3 (ref_key) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); 886set @row_id := 0; 887INSERT INTO t3 (ref_key) SELECT @row_id := @row_id + 11 as ref_key 888 FROM t3 a1, t3 a2, t3 a3; 889 890SET STATEMENT myisam_repair_threads=1 FOR 891SELECT count(1) FROM t3 s 892 JOIN (SELECT row_key, ref_key AS ref_key FROM t3 t 893 WHERE t.row_key IN (SELECT row_key FROM t3)) AS r; 894 895DROP TABLE t3; 896 897--echo '' 898--echo '# Cleanup' 899DROP TABLE t1, t2; 900DROP PROCEDURE p1; 901DROP PROCEDURE p2; 902DROP PROCEDURE p3; 903DROP PROCEDURE p4; 904DROP PROCEDURE p5; 905 906#################################################################### 907# lp:1626519 memory allocation for PLUGIN_VAR_MEMALLOC # 908#################################################################### 909 910let $MYSQL_TMP_DIR= `select @@tmpdir`; 911--replace_result $MYSQL_TMP_DIR TMPDIR 912--eval SET STATEMENT innodb_tmpdir=@@global.tmpdir FOR SELECT @@innodb_tmpdir 913SET SESSION innodb_tmpdir = @@global.tmpdir; 914--replace_result $MYSQL_TMP_DIR TMPDIR 915SELECT @@innodb_tmpdir; 916--replace_result $MYSQL_TMP_DIR TMPDIR 917SET STATEMENT sql_mode='' FOR SELECT @@innodb_tmpdir; 918--replace_result $MYSQL_TMP_DIR TMPDIR 919SELECT @@innodb_tmpdir; 920--replace_result $MYSQL_TEST_DIR TESTDIR 921--eval SET STATEMENT innodb_tmpdir='$MYSQL_TEST_DIR' FOR SELECT @@innodb_tmpdir 922--replace_result $MYSQL_TMP_DIR TMPDIR 923SELECT @@innodb_tmpdir; 924 925--echo # 926--echo # Bug 1385352: SET STATEMENT does not work after SET GLOBAL / SHOW GLOBAL STATUS 927--echo # and affects the global value 928--echo # 929 930--source include/wait_until_count_sessions.inc 931 932SHOW GLOBAL STATUS LIKE 'Threads_connected'; 933 934SELECT @@GLOBAL.sql_mode; 935SELECT @@GLOBAL.auto_increment_offset; 936SET STATEMENT sql_mode = 'NO_AUTO_CREATE_USER', auto_increment_offset = 123 FOR 937 SELECT @@SESSION.sql_mode, @@SESSION.auto_increment_offset; 938SELECT @@GLOBAL.sql_mode; 939SELECT @@GLOBAL.auto_increment_offset; 940 941SET @saved_general_log = @@GLOBAL.general_log; 942SET GLOBAL general_log = 0; 943 944SET STATEMENT sql_mode='NO_AUTO_CREATE_USER', auto_increment_offset=123 FOR 945 SELECT @@SESSION.sql_mode, @@SESSION.auto_increment_offset; 946SELECT @@GLOBAL.sql_mode; 947SELECT @@GLOBAL.auto_increment_offset; 948 949SET GLOBAL general_log = @saved_general_log; 950