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