1'# SET STATEMENT ..... FOR .... TEST' 2DROP TABLE IF EXISTS t1; 3DROP FUNCTION IF EXISTS myProc; 4DROP PROCEDURE IF EXISTS p1; 5DROP PROCEDURE IF EXISTS p2; 6DROP PROCEDURE IF EXISTS p3; 7DROP PROCEDURE IF EXISTS p4; 8DROP PROCEDURE IF EXISTS p5; 9DROP TABLE IF EXISTS STATEMENT; 10'# Setup database' 11CREATE TABLE t1 (v1 INT, v2 INT) ENGINE=MyISAM; 12INSERT INTO t1 VALUES (1,2); 13INSERT INTO t1 VALUES (3,4); 14'' 15'#------------------ STATEMENT Test 1 -----------------------#' 16'# Initialize variables to known setting' 17SET SESSION sort_buffer_size=100000; 18'' 19'# Pre-STATEMENT variable value' 20SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; 21Variable_name Value 22sort_buffer_size 100000 23SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t1; 24v1 v2 251 2 263 4 27'' 28'# Post-STATEMENT variable value' 29SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; 30Variable_name Value 31sort_buffer_size 100000 32'' 33'#------------------ STATEMENT Test 2 -----------------------#' 34'# Initialize variables to known setting' 35SET SESSION binlog_format=mixed; 36SET SESSION sort_buffer_size=100000; 37'# Pre-STATEMENT variable value' 38SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; 39Variable_name Value 40sort_buffer_size 100000 41SHOW SESSION VARIABLES LIKE 'binlog_format'; 42Variable_name Value 43binlog_format MIXED 44SET STATEMENT sort_buffer_size=150000, binlog_format=row 45FOR SELECT * FROM t1; 46v1 v2 471 2 483 4 49'# Post-STATEMENT variable value' 50SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; 51Variable_name Value 52sort_buffer_size 100000 53SHOW SESSION VARIABLES LIKE 'binlog_format'; 54Variable_name Value 55binlog_format MIXED 56'' 57'#------------------ STATEMENT Test 3 -----------------------#' 58'# set initial variable value, make prepared statement 59SET SESSION binlog_format=row; 60PREPARE stmt1 FROM 'SET STATEMENT binlog_format=row FOR SELECT * FROM t1'; 61'' 62'# Change variable setting' 63SET SESSION binlog_format=mixed; 64'' 65'# Pre-STATEMENT variable value' 66'' 67SHOW SESSION VARIABLES LIKE 'binlog_format'; 68Variable_name Value 69binlog_format MIXED 70'' 71EXECUTE stmt1; 72v1 v2 731 2 743 4 75'' 76'# Post-STATEMENT variable value' 77SHOW SESSION VARIABLES LIKE 'binlog_format'; 78Variable_name Value 79binlog_format MIXED 80'' 81DEALLOCATE PREPARE stmt1; 82'#------------------ STATEMENT Test 4 -----------------------#' 83'# set initial variable value, make prepared statement 84SET SESSION myisam_sort_buffer_size=500000, myisam_repair_threads=1; 85'' 86'# Pre-STATEMENT variable value' 87SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size'; 88Variable_name Value 89myisam_sort_buffer_size 500000 90SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; 91Variable_name Value 92myisam_repair_threads 1 93'' 94SET STATEMENT myisam_sort_buffer_size=800000, 95myisam_repair_threads=2 FOR OPTIMIZE TABLE t1; 96Table Op Msg_type Msg_text 97test.t1 optimize status OK 98'' 99'# Post-STATEMENT variable value' 100SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size'; 101Variable_name Value 102myisam_sort_buffer_size 500000 103SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; 104Variable_name Value 105myisam_repair_threads 1 106'' 107'#------------------ STATEMENT Test 5 -----------------------#' 108'# Initialize variables to known setting' 109SET SESSION sort_buffer_size=100000; 110'' 111'# Pre-STATEMENT variable value' 112SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; 113Variable_name Value 114sort_buffer_size 100000 115'' 116SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t2; 117ERROR 42S02: Table 'test.t2' doesn't exist 118'' 119'# Post-STATEMENT variable value' 120SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; 121Variable_name Value 122sort_buffer_size 100000 123'' 124'#------------------ STATEMENT Test 6 -----------------------#' 125'# Initialize variables to known setting' 126SET SESSION keep_files_on_create=ON; 127'' 128'# Pre-STATEMENT variable value' 129SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; 130Variable_name Value 131keep_files_on_create ON 132'' 133SET STATEMENT keep_files_on_create=OFF FOR SELECT * FROM t1; 134v1 v2 1351 2 1363 4 137'' 138'# Post-STATEMENT variable value' 139SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; 140Variable_name Value 141keep_files_on_create ON 142'' 143'#------------------ STATEMENT Test 7 -----------------------#' 144'# Initialize variables to known setting' 145SET SESSION max_join_size=2222220000000; 146'' 147'# Pre-STATEMENT variable value' 148SHOW SESSION VARIABLES LIKE 'max_join_size'; 149Variable_name Value 150max_join_size 2222220000000 151'' 152SET STATEMENT max_join_size=1000000000000 FOR SELECT * FROM t1; 153v1 v2 1541 2 1553 4 156'' 157'# Post-STATEMENT variable value' 158SHOW SESSION VARIABLES LIKE 'max_join_size'; 159Variable_name Value 160max_join_size 2222220000000 161'' 162'#------------------Test 8-----------------------#' 163'# Initialize test variables' 164SET SESSION myisam_sort_buffer_size=500000, 165myisam_repair_threads=1, 166sort_buffer_size = 200000, 167max_join_size=2222220000000, 168keep_files_on_create=ON; 169'' 170'# LONG ' 171SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; 172Variable_name Value 173sort_buffer_size 200000 174SET STATEMENT sort_buffer_size = 100000 175FOR SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; 176Variable_name Value 177sort_buffer_size 100000 178SHOW SESSION VARIABLES LIKE 'sort_buffer_size'; 179Variable_name Value 180sort_buffer_size 200000 181'' 182'# MY_BOOL ' 183SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; 184Variable_name Value 185keep_files_on_create ON 186SET STATEMENT keep_files_on_create=OFF 187FOR SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; 188Variable_name Value 189keep_files_on_create OFF 190SHOW SESSION VARIABLES LIKE 'keep_files_on_create'; 191Variable_name Value 192keep_files_on_create ON 193'' 194'# INT/LONG ' 195SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; 196Variable_name Value 197myisam_repair_threads 1 198SET STATEMENT myisam_repair_threads=2 199FOR SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; 200Variable_name Value 201myisam_repair_threads 2 202SHOW SESSION VARIABLES LIKE 'myisam_repair_threads'; 203Variable_name Value 204myisam_repair_threads 1 205'' 206'# ULONGLONG ' 207SHOW SESSION VARIABLES LIKE 'max_join_size'; 208Variable_name Value 209max_join_size 2222220000000 210SET STATEMENT max_join_size=2000000000000 211FOR SHOW SESSION VARIABLES LIKE 'max_join_size'; 212Variable_name Value 213max_join_size 2000000000000 214SHOW SESSION VARIABLES LIKE 'max_join_size'; 215Variable_name Value 216max_join_size 2222220000000 217'' 218'#------------------Test 9-----------------------#' 219'# set initial variable values 220SET SESSION myisam_sort_buffer_size=500000, 221myisam_repair_threads=1, 222sort_buffer_size=100000, 223binlog_format=mixed, 224keep_files_on_create=ON, 225max_join_size=2222220000000; 226'' 227'' 228'# Pre-STATEMENT variable value 229SELECT @@myisam_sort_buffer_size, 230@@myisam_repair_threads, 231@@sort_buffer_size, 232@@binlog_format, 233@@keep_files_on_create, 234@@max_join_size; 235@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 236500000 1 100000 MIXED 1 2222220000000 237'' 238'' 239CREATE FUNCTION myProc (cost DECIMAL(10,2)) 240RETURNS DECIMAL(10,2) 241SQL SECURITY DEFINER 242tax: BEGIN 243DECLARE order_tax DECIMAL(10,2); 244SET order_tax = cost * .05; 245RETURN order_tax; 246END| 247'' 248'# During Execution values 249SET STATEMENT myisam_sort_buffer_size=400000, 250myisam_repair_threads=2, 251sort_buffer_size=200000, 252binlog_format=row, 253keep_files_on_create=OFF, 254max_join_size=4444440000000 FOR 255SELECT myProc(123.45); 256myProc(123.45) 2576.17 258'' 259'# Post-STATEMENT No 1 variable value Pre-STATEMENT for No 2' 260SELECT @@myisam_sort_buffer_size, 261@@myisam_repair_threads, 262@@sort_buffer_size, 263@@binlog_format, 264@@keep_files_on_create, 265@@max_join_size; 266@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 267500000 1 100000 MIXED 1 2222220000000 268'' 269SET STATEMENT myisam_sort_buffer_size=400000, 270myisam_repair_threads=2, 271sort_buffer_size=200000, 272binlog_format=row, 273keep_files_on_create=OFF, 274max_join_size=4444440000000 FOR 275DROP FUNCTION myProc; 276'' 277'# Post-STATEMENT No 2 variable value 278SELECT @@myisam_sort_buffer_size, 279@@myisam_repair_threads, 280@@sort_buffer_size, 281@@binlog_format, 282@@keep_files_on_create, 283@@max_join_size; 284@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 285500000 1 100000 MIXED 1 2222220000000 286'' 287'#------------------Test 10-----------------------#' 288'# set initial variable values 289SET SESSION myisam_sort_buffer_size=500000, 290myisam_repair_threads=1, 291sort_buffer_size=100000, 292binlog_format=mixed, 293keep_files_on_create=ON, 294max_join_size=2222220000000; 295'' 296'# Pre-STATEMENT variable value 297SELECT @@myisam_sort_buffer_size, 298@@myisam_repair_threads, 299@@sort_buffer_size, 300@@binlog_format, 301@@keep_files_on_create, 302@@max_join_size; 303@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 304500000 1 100000 MIXED 1 2222220000000 305'' 306'' 307SET STATEMENT myisam_sort_buffer_size=400000, 308myisam_repair_threads=2, 309sort_buffer_size=200000, 310binlog_format=row, 311keep_files_on_create=OFF, 312max_join_size=4444440000000 FOR 313PREPARE stmt2 314FROM 'SELECT * FROM t1'; 315'' 316'Test No 1 Post Value & Test 2 Pre values' 317SELECT @@myisam_sort_buffer_size, 318@@myisam_repair_threads, 319@@sort_buffer_size, 320@@binlog_format, 321@@keep_files_on_create, 322@@max_join_size; 323@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 324500000 1 100000 MIXED 1 2222220000000 325'' 326'' 327SET STATEMENT myisam_sort_buffer_size=400000, 328myisam_repair_threads=2, 329sort_buffer_size=200000, 330binlog_format=row, 331keep_files_on_create=OFF, 332max_join_size=4444440000000 FOR 333EXECUTE stmt2; 334v1 v2 3351 2 3363 4 337'' 338'# Post-STATEMENT No 2 339SELECT @@myisam_sort_buffer_size, 340@@myisam_repair_threads, 341@@sort_buffer_size, 342@@binlog_format, 343@@keep_files_on_create, 344@@max_join_size; 345@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 346500000 1 100000 MIXED 1 2222220000000 347'' 348DEALLOCATE PREPARE stmt2; 349'' 350'#------------------Test 11-----------------------#' 351'# set initial variable values 352SET SESSION myisam_sort_buffer_size=500000, 353myisam_repair_threads=1, 354sort_buffer_size=100000, 355binlog_format=mixed, 356keep_files_on_create=ON, 357max_join_size=2222220000000; 358'' 359'' 360'# Pre-STATEMENT variable value 361SELECT @@myisam_sort_buffer_size, 362@@myisam_repair_threads, 363@@sort_buffer_size, 364@@binlog_format, 365@@keep_files_on_create, 366@@max_join_size; 367@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 368500000 1 100000 MIXED 1 2222220000000 369'' 370'' 371SET STATEMENT myisam_sort_buffer_size=400000, 372myisam_repair_threads=2, 373sort_buffer_size=200000, 374keep_files_on_create=OFF, 375max_join_size=4444440000000 FOR 376PREPARE stmt1 FROM 377'SET STATEMENT binlog_format=row FOR SELECT * FROM t1'; 378'' 379'Test No 1 Post Value & Test 2 Pre values' 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@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 387500000 1 100000 MIXED 1 2222220000000 388'' 389'' 390SET STATEMENT myisam_sort_buffer_size=400000, 391myisam_repair_threads=2, 392sort_buffer_size=200000, 393keep_files_on_create=OFF, 394max_join_size=4444440000000 FOR 395EXECUTE stmt1; 396v1 v2 3971 2 3983 4 399'' 400'# Post-STATEMENT No 2 401SELECT @@myisam_sort_buffer_size, 402@@myisam_repair_threads, 403@@sort_buffer_size, 404@@binlog_format, 405@@keep_files_on_create, 406@@max_join_size; 407@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 408500000 1 100000 MIXED 1 2222220000000 409'' 410'' 411'#------------------Test 12-----------------------#' 412'# set initial variable values 413SET SESSION myisam_sort_buffer_size=500000, 414myisam_repair_threads=1, 415sort_buffer_size=100000, 416binlog_format=mixed, 417keep_files_on_create=ON, 418max_join_size=2222220000000; 419'' 420'' 421'# Pre-STATEMENT variable value 422SELECT @@myisam_sort_buffer_size, 423@@myisam_repair_threads, 424@@sort_buffer_size, 425@@binlog_format, 426@@keep_files_on_create, 427@@max_join_size; 428@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 429500000 1 100000 MIXED 1 2222220000000 430'' 431'' 432SET STATEMENT myisam_sort_buffer_size=400000, 433myisam_repair_threads=2, 434sort_buffer_size=200000, 435binlog_format=row, 436keep_files_on_create=OFF, 437max_join_size=4444440000000 FOR 438CREATE PROCEDURE p1() BEGIN 439SELECT @@myisam_sort_buffer_size, 440@@myisam_repair_threads, 441@@sort_buffer_size, 442@@binlog_format, 443@@keep_files_on_create, 444@@max_join_size; 445END| 446'' 447'Test No 1 Post Value & Test 2 Pre values' 448SELECT @@myisam_sort_buffer_size, 449@@myisam_repair_threads, 450@@sort_buffer_size, 451@@binlog_format, 452@@keep_files_on_create, 453@@max_join_size; 454@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 455500000 1 100000 MIXED 1 2222220000000 456'' 457'' 458SET STATEMENT myisam_sort_buffer_size=400000, 459myisam_repair_threads=2, 460sort_buffer_size=200000, 461binlog_format=row, 462keep_files_on_create=OFF, 463max_join_size=4444440000000 FOR 464CALL p1(); 465@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 466400000 2 200000 ROW 0 4444440000000 467'' 468'# Post-STATEMENT No 2 469SELECT @@myisam_sort_buffer_size, 470@@myisam_repair_threads, 471@@sort_buffer_size, 472@@binlog_format, 473@@keep_files_on_create, 474@@max_join_size; 475@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 476500000 1 100000 MIXED 1 2222220000000 477'' 478'' 479'#------------------Test 13-----------------------#' 480'# set initial variable values 481SET SESSION myisam_sort_buffer_size=500000, 482myisam_repair_threads=1, 483sort_buffer_size=100000, 484binlog_format=mixed, 485keep_files_on_create=ON, 486max_join_size=2222220000000; 487'' 488'' 489CREATE PROCEDURE p2() BEGIN 490SET STATEMENT myisam_sort_buffer_size=400000, 491myisam_repair_threads=3, 492sort_buffer_size=300000, 493binlog_format=mixed, 494keep_files_on_create=OFF, 495max_join_size=3333330000000 FOR 496CALL p1(); 497END| 498'' 499'# Pre-STATEMENT variable value 500SELECT @@myisam_sort_buffer_size, 501@@myisam_repair_threads, 502@@sort_buffer_size, 503@@binlog_format, 504@@keep_files_on_create, 505@@max_join_size; 506@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 507500000 1 100000 MIXED 1 2222220000000 508'' 509'' 510SET STATEMENT myisam_sort_buffer_size=400000, 511myisam_repair_threads=2, 512sort_buffer_size=200000, 513binlog_format=row, 514keep_files_on_create=OFF, 515max_join_size=4444440000000 FOR 516CALL p2(); 517@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 518400000 3 300000 MIXED 0 3333330000000 519'' 520'# Post-STATEMENT 521SELECT @@myisam_sort_buffer_size, 522@@myisam_repair_threads, 523@@sort_buffer_size, 524@@binlog_format, 525@@keep_files_on_create, 526@@max_join_size; 527@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 528500000 1 100000 MIXED 1 2222220000000 529'' 530'' 531'#------------------Test 14-----------------------#' 532'# set initial variable values 533SET SESSION myisam_sort_buffer_size=500000, 534myisam_repair_threads=1, 535sort_buffer_size=100000, 536binlog_format=mixed, 537keep_files_on_create=ON, 538max_join_size=2222220000000; 539'' 540'' 541CREATE PROCEDURE p3() BEGIN 542SELECT @@myisam_sort_buffer_size, 543@@myisam_repair_threads, 544@@sort_buffer_size, 545@@binlog_format, 546@@keep_files_on_create, 547@@max_join_size; 548SET STATEMENT myisam_sort_buffer_size=320000, 549myisam_repair_threads=2, 550sort_buffer_size=220022, 551binlog_format=row, 552keep_files_on_create=ON, 553max_join_size=2222220000000 FOR 554CALL p2(); 555END| 556'' 557'# Pre-STATEMENT variable value 558SELECT @@myisam_sort_buffer_size, 559@@myisam_repair_threads, 560@@sort_buffer_size, 561@@binlog_format, 562@@keep_files_on_create, 563@@max_join_size; 564@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 565500000 1 100000 MIXED 1 2222220000000 566'' 567'' 568SET STATEMENT myisam_sort_buffer_size=400000, 569myisam_repair_threads=2, 570sort_buffer_size=200000, 571binlog_format=row, 572keep_files_on_create=OFF, 573max_join_size=4444440000000 FOR 574CALL p3(); 575@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 576400000 2 200000 ROW 0 4444440000000 577@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 578400000 3 300000 MIXED 0 3333330000000 579'' 580'# Post-STATEMENT 581SELECT @@myisam_sort_buffer_size, 582@@myisam_repair_threads, 583@@sort_buffer_size, 584@@binlog_format, 585@@keep_files_on_create, 586@@max_join_size; 587@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 588500000 1 100000 MIXED 1 2222220000000 589'' 590'' 591'' 592'' 593'#------------------Test 15-----------------------#' 594'# set initial variable values 595SET SESSION myisam_sort_buffer_size=500000, 596myisam_repair_threads=1, 597sort_buffer_size=100000, 598binlog_format=mixed, 599keep_files_on_create=ON, 600max_join_size=2222220000000; 601'' 602'' 603CREATE PROCEDURE p4() BEGIN 604SELECT @@myisam_sort_buffer_size, 605@@myisam_repair_threads, 606@@sort_buffer_size, 607@@binlog_format, 608@@keep_files_on_create, 609@@max_join_size; 610SET STATEMENT myisam_sort_buffer_size=320000, 611myisam_repair_threads=2, 612sort_buffer_size=220022, 613binlog_format=row, 614keep_files_on_create=ON, 615max_join_size=2222220000000 FOR 616SELECT @@myisam_sort_buffer_size, 617@@myisam_repair_threads, 618@@sort_buffer_size, 619@@binlog_format, 620@@keep_files_on_create, 621@@max_join_size; 622SET STATEMENT myisam_sort_buffer_size=320000, 623myisam_repair_threads=2, 624sort_buffer_size=220022, 625binlog_format=row, 626keep_files_on_create=ON, 627max_join_size=2222220000000 FOR 628SELECT @@myisam_sort_buffer_size, 629@@myisam_repair_threads, 630@@sort_buffer_size, 631@@binlog_format, 632@@keep_files_on_create, 633@@max_join_size; 634SET STATEMENT myisam_sort_buffer_size=320000, 635myisam_repair_threads=2, 636sort_buffer_size=220022, 637binlog_format=row, 638keep_files_on_create=ON, 639max_join_size=2222220000000 FOR 640SELECT @@myisam_sort_buffer_size, 641@@myisam_repair_threads, 642@@sort_buffer_size, 643@@binlog_format, 644@@keep_files_on_create, 645@@max_join_size; 646END| 647'' 648'# Pre-STATEMENT variable value 649SELECT @@myisam_sort_buffer_size, 650@@myisam_repair_threads, 651@@sort_buffer_size, 652@@binlog_format, 653@@keep_files_on_create, 654@@max_join_size; 655@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 656500000 1 100000 MIXED 1 2222220000000 657'' 658'' 659SET STATEMENT myisam_sort_buffer_size=400000, 660myisam_repair_threads=2, 661sort_buffer_size=200000, 662binlog_format=row, 663keep_files_on_create=OFF, 664max_join_size=4444440000000 FOR 665CALL p4(); 666@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 667400000 2 200000 ROW 0 4444440000000 668@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 669320000 2 220022 ROW 1 2222220000000 670@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 671320000 2 220022 ROW 1 2222220000000 672@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 673320000 2 220022 ROW 1 2222220000000 674'' 675'# Post-STATEMENT 676SELECT @@myisam_sort_buffer_size, 677@@myisam_repair_threads, 678@@sort_buffer_size, 679@@binlog_format, 680@@keep_files_on_create, 681@@max_join_size; 682@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 683500000 1 100000 MIXED 1 2222220000000 684'' 685'' 686'#------------------Test 16-----------------------#' 687'' 688'# Pre-STATEMENT variable value 689SELECT @@sql_mode; 690@@sql_mode 691ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 692'' 693'' 694SET STATEMENT sql_mode='ansi' FOR SELECT * FROM t1; 695v1 v2 6961 2 6973 4 698Warnings: 699Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 700'' 701'# Post-STATEMENT 702SELECT @@sql_mode; 703@@sql_mode 704ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 705'' 706'' 707'#------------------Test 17-----------------------#' 708'# set initial variable values 709SET SESSION myisam_sort_buffer_size=500000, 710myisam_repair_threads=1, 711sort_buffer_size=100000, 712binlog_format=mixed, 713keep_files_on_create=ON, 714max_join_size=2222220000000; 715'' 716'# Pre-STATEMENT variable value 717SELECT @@myisam_sort_buffer_size, 718@@myisam_repair_threads, 719@@sort_buffer_size, 720@@binlog_format, 721@@keep_files_on_create, 722@@max_join_size; 723@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 724500000 1 100000 MIXED 1 2222220000000 725'' 726'' 727SET STATEMENT myisam_sort_buffer_size=320000, 728myisam_repair_threads=2, 729sort_buffer_size=220022, 730binlog_format=row, 731keep_files_on_create=ON, 732max_join_size=2222220000000 733FOR SET SESSION 734myisam_sort_buffer_size=260000, 735myisam_repair_threads=3, 736sort_buffer_size=230013, 737binlog_format=row, 738keep_files_on_create=ON, 739max_join_size=2323230000000; 740'' 741'# Post-STATEMENT 742SELECT @@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@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 749500000 1 100000 MIXED 1 2222220000000 750'' 751'' 752'#------------------Test 18-----------------------#' 753'# set initial variable values 754SET SESSION myisam_sort_buffer_size=500000, 755myisam_repair_threads=1, 756sort_buffer_size=100000, 757binlog_format=mixed, 758keep_files_on_create=ON, 759max_join_size=2222220000000; 760'' 761'# Pre-STATEMENT variable value 762SELECT @@myisam_sort_buffer_size, 763@@myisam_repair_threads, 764@@sort_buffer_size, 765@@binlog_format, 766@@keep_files_on_create, 767@@max_join_size; 768@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 769500000 1 100000 MIXED 1 2222220000000 770'' 771'' 772CREATE PROCEDURE p5() BEGIN 773SELECT @@myisam_sort_buffer_size, 774@@myisam_repair_threads, 775@@sort_buffer_size, 776@@binlog_format, 777@@keep_files_on_create, 778@@max_join_size; 779SET SESSION 780myisam_sort_buffer_size=260000, 781myisam_repair_threads=3, 782sort_buffer_size=230013, 783binlog_format=row, 784keep_files_on_create=ON, 785max_join_size=2323230000000; 786SELECT @@myisam_sort_buffer_size, 787@@myisam_repair_threads, 788@@sort_buffer_size, 789@@binlog_format, 790@@keep_files_on_create, 791@@max_join_size; 792END| 793'' 794'' 795SET STATEMENT myisam_sort_buffer_size=400000, 796myisam_repair_threads=2, 797sort_buffer_size=200000, 798binlog_format=row, 799keep_files_on_create=OFF, 800max_join_size=4444440000000 FOR 801CALL p5(); 802@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 803400000 2 200000 ROW 0 4444440000000 804@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 805260000 3 230013 ROW 1 2323230000000 806'' 807'# Post-STATEMENT 808SELECT @@myisam_sort_buffer_size, 809@@myisam_repair_threads, 810@@sort_buffer_size, 811@@binlog_format, 812@@keep_files_on_create, 813@@max_join_size; 814@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size 815500000 1 100000 MIXED 1 2222220000000 816'' 817'' 818'#------------------Test 19-----------------------#' 819SET STATEMENT max_error_count=100 FOR; 820ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 821SET STATEMENT max_error_count=100 INSERT t1 VALUES (1,2); 822ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT t1 VALUES (1,2)' at line 1 823SET STATEMENT FOR INSERT INTO t1 VALUES (1,2); 824ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOR INSERT INTO t1 VALUES (1,2)' at line 1 825SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); 826ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOR INSERT INTO t1 VALUES (1,2)' at line 1 827SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); 828ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOR INSERT INTO t1 VALUES (1,2)' at line 1 829SET STATEMENT GLOBAL max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); 830ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'max_error_count=100 FOR INSERT INTO t1 VALUES (1,2)' at line 1 831SET STATEMENT @@global.max_error_count=100 FOR INSERT INTO t1 VALUES (1,2); 832ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@@global.max_error_count=100 FOR INSERT INTO t1 VALUES (1,2)' at line 1 833'' 834'' 835'#------------------Test 20-----------------------#' 836SET STATEMENT connect_timeout=100 FOR INSERT INTO t1 VALUES (1,2); 837ERROR HY000: Variable 'connect_timeout' is a GLOBAL variable and should be set with SET GLOBAL 838'' 839'' 840'#------------------Test 21-----------------------#' 841SELECT @@myisam_sort_buffer_size, @@sort_buffer_size; 842@@myisam_sort_buffer_size @@sort_buffer_size 843500000 100000 844SET STATEMENT myisam_sort_buffer_size = 700000, sort_buffer_size = 3000000 845FOR SET STATEMENT myisam_sort_buffer_size=200000 846FOR SELECT @@myisam_sort_buffer_size, @@sort_buffer_size; 847@@myisam_sort_buffer_size @@sort_buffer_size 848700000 3000000 849SELECT @@myisam_sort_buffer_size, @@sort_buffer_size; 850@@myisam_sort_buffer_size @@sort_buffer_size 851500000 100000 852'' 853'' 854'#------------------Test 22-----------------------#' 855CREATE TABLE STATEMENT(a INT); 856DROP TABLE STATEMENT; 857Test for bug 1418049: SET STATEMENT ... FOR <statement> crashes server 858if <statement> needs to commit implicitly and fails 859CREATE TABLE t2 (a INT PRIMARY KEY) ENGINE=InnoDB; 860SET @old_lock_wait_timeout = @@session.lock_wait_timeout; 861SET SESSION lock_wait_timeout = 1; 862BEGIN; 863INSERT INTO t2 VALUES (5); 864FLUSH TABLES WITH READ LOCK; 865SET STATEMENT max_join_size = 0 FOR DROP TABLE t2; 866ERROR HY000: Lock wait timeout exceeded; try restarting transaction 867UNLOCK TABLES; 868COMMIT; 869SET SESSION lock_wait_timeout = @old_lock_wait_timeout; 870Test for bug 1387951: SET STATEMENT ... FOR <statement> crashes server 871if <statement> is RW in a RO transaction 872SET SESSION transaction_read_only = TRUE; 873SET STATEMENT myisam_repair_threads=0 FOR OPTIMIZE TABLE t0; 874ERROR 25006: Cannot execute statement in a READ ONLY transaction. 875SET SESSION transaction_read_only = FALSE; 876Test for bug 1412423: SET STATEMENT ... FOR <statement> crashes server 877if <statement> needs to re-open a temp table and fails 878CREATE TEMPORARY TABLE t3 (a INT PRIMARY KEY) ENGINE=InnoDB; 879HANDLER t3 OPEN; 880SET STATEMENT max_join_size=1000 FOR SELECT * FROM t3; 881ERROR HY000: Can't reopen table: 't3' 882DROP TABLE t3; 883# 884# Bug 1392375: Crashing repeated execution of SET STATEMENT ... FOR <SELECT FROM view> 885# 886CREATE VIEW t3 AS SELECT 1 AS a; 887PREPARE stmt1 FROM 'SET STATEMENT binlog_format=row FOR SELECT * FROM t3'; 888EXECUTE stmt1; 889a 8901 891EXECUTE stmt1; 892a 8931 894DEALLOCATE PREPARE stmt1; 895DROP VIEW t3; 896# 897# Bug 1635927: Memory leak when using per-query variables with subquery temp tables 898# 899CREATE TABLE t3 (row_key INT NOT NULL DEFAULT 0, ref_key BIGINT(20) NOT NULL); 900INSERT INTO t3 (ref_key) VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10); 901set @row_id := 0; 902INSERT INTO t3 (ref_key) SELECT @row_id := @row_id + 11 as ref_key 903FROM t3 a1, t3 a2, t3 a3; 904SET STATEMENT myisam_repair_threads=1 FOR 905SELECT count(1) FROM t3 s 906JOIN (SELECT row_key, ref_key AS ref_key FROM t3 t 907WHERE t.row_key IN (SELECT row_key FROM t3)) AS r; 908count(1) 9091020100 910DROP TABLE t3; 911'' 912'# Cleanup' 913DROP TABLE t1, t2; 914DROP PROCEDURE p1; 915DROP PROCEDURE p2; 916DROP PROCEDURE p3; 917DROP PROCEDURE p4; 918DROP PROCEDURE p5; 919SET STATEMENT innodb_tmpdir=@@global.tmpdir FOR SELECT @@innodb_tmpdir; 920@@innodb_tmpdir 921TMPDIR 922SET SESSION innodb_tmpdir = @@global.tmpdir; 923SELECT @@innodb_tmpdir; 924@@innodb_tmpdir 925TMPDIR 926SET STATEMENT sql_mode='' FOR SELECT @@innodb_tmpdir; 927@@innodb_tmpdir 928TMPDIR 929Warnings: 930Warning 3090 Changing sql mode 'NO_AUTO_CREATE_USER' is deprecated. It will be removed in a future release. 931SELECT @@innodb_tmpdir; 932@@innodb_tmpdir 933TMPDIR 934SET STATEMENT innodb_tmpdir='TESTDIR' FOR SELECT @@innodb_tmpdir; 935@@innodb_tmpdir 936TESTDIR 937SELECT @@innodb_tmpdir; 938@@innodb_tmpdir 939TMPDIR 940# 941# Bug 1385352: SET STATEMENT does not work after SET GLOBAL / SHOW GLOBAL STATUS 942# and affects the global value 943# 944SHOW GLOBAL STATUS LIKE 'Threads_connected'; 945Variable_name Value 946Threads_connected 1 947SELECT @@GLOBAL.sql_mode; 948@@GLOBAL.sql_mode 949ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 950SELECT @@GLOBAL.auto_increment_offset; 951@@GLOBAL.auto_increment_offset 9521 953SET STATEMENT sql_mode = 'NO_AUTO_CREATE_USER', auto_increment_offset = 123 FOR 954SELECT @@SESSION.sql_mode, @@SESSION.auto_increment_offset; 955@@SESSION.sql_mode @@SESSION.auto_increment_offset 956NO_AUTO_CREATE_USER 123 957SELECT @@GLOBAL.sql_mode; 958@@GLOBAL.sql_mode 959ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 960SELECT @@GLOBAL.auto_increment_offset; 961@@GLOBAL.auto_increment_offset 9621 963SET @saved_general_log = @@GLOBAL.general_log; 964SET GLOBAL general_log = 0; 965SET STATEMENT sql_mode='NO_AUTO_CREATE_USER', auto_increment_offset=123 FOR 966SELECT @@SESSION.sql_mode, @@SESSION.auto_increment_offset; 967@@SESSION.sql_mode @@SESSION.auto_increment_offset 968NO_AUTO_CREATE_USER 123 969SELECT @@GLOBAL.sql_mode; 970@@GLOBAL.sql_mode 971ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION 972SELECT @@GLOBAL.auto_increment_offset; 973@@GLOBAL.auto_increment_offset 9741 975SET GLOBAL general_log = @saved_general_log; 976