MariaDB

Merge lp:~pomyks/maria/10.0-per-query-variables into lp:maria

  1. 10.0-per-query-variables
  2. Merge into 10.0
Proposed by Patryk Pomykalski
Status: Needs review
Proposed branch: lp:~pomyks/maria/10.0-per-query-variables
Merge into: lp:maria
Diff against target: 2265 lines (+2080/-16)
13 files modified
mysql-test/suite/percona/percona_rpl_set_statement_variable_test.inc (+31/-0)
mysql-test/suite/percona/percona_rpl_stm_per_query_variables_settings.result (+140/-0)
mysql-test/suite/percona/percona_rpl_stm_per_query_variables_settings.test (+57/-0)
mysql-test/suite/percona/percona_statement_set.result (+862/-0)
mysql-test/suite/percona/percona_statement_set.test (+830/-0)
sql/lex.h (+1/-0)
sql/set_var.h (+3/-0)
sql/sql_lex.cc (+1/-0)
sql/sql_lex.h (+2/-0)
sql/sql_parse.cc (+46/-0)
sql/sql_plugin.cc (+42/-0)
sql/sql_plugin.h (+8/-0)
sql/sql_yacc.yy (+57/-16)
To merge this branch: bzr merge lp:~pomyks/maria/10.0-per-query-variables
Reviewer Review Type Date Requested Status
Maria-captains Pending
Review via email: mp+194193@code.launchpad.net
To post a comment you must log in.

Unmerged revisions

3880. By Patryk Pomykalski

Port of per query variables feature from Percona Server.
Allows changing variables for duration of the query.

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== added file 'mysql-test/suite/percona/percona_rpl_set_statement_variable_test.inc'
2--- mysql-test/suite/percona/percona_rpl_set_statement_variable_test.inc 1970-01-01 00:00:00 +0000
3+++ mysql-test/suite/percona/percona_rpl_set_statement_variable_test.inc 2013-11-06 17:03:07 +0000
4@@ -0,0 +1,31 @@
5+#Check if the variable is replicated correctly with "SET STATEMENT"
6+# Usage:
7+# $rpl_ssvt_var_name - the name of tested variable;
8+# $rpl_ssvt_var_value - the value to set;
9+# $rpl_ssvt_table - the table name to insert values.
10+
11+--connection master
12+--echo [connection master]
13+eval SELECT @@$rpl_ssvt_var_name;
14+
15+--connection slave
16+--echo [connection slave]
17+eval SELECT @@$rpl_ssvt_var_name;
18+
19+--connection master
20+--echo [connection master]
21+--disable_result_log
22+eval SET STATEMENT $rpl_ssvt_var_name=$rpl_ssvt_var_value FOR
23+ INSERT INTO $rpl_ssvt_table VALUES(@@$rpl_ssvt_var_name);
24+--enable_result_log
25+eval SELECT @@$rpl_ssvt_var_name;
26+
27+--sync_slave_with_master
28+--echo [connection slave]
29+eval SELECT * FROM $rpl_ssvt_table;
30+eval SELECT @@$rpl_ssvt_var_name;
31+
32+--connection master
33+--echo [connection master]
34+eval DELETE FROM $rpl_ssvt_table;
35+
36
37=== added file 'mysql-test/suite/percona/percona_rpl_stm_per_query_variables_settings.result'
38--- mysql-test/suite/percona/percona_rpl_stm_per_query_variables_settings.result 1970-01-01 00:00:00 +0000
39+++ mysql-test/suite/percona/percona_rpl_stm_per_query_variables_settings.result 2013-11-06 17:03:07 +0000
40@@ -0,0 +1,140 @@
41+include/master-slave.inc
42+[connection master]
43+DROP TABLE IF EXISTS t1;
44+DROP TABLE IF EXISTS t2;
45+call mtr.add_suppression("Unsafe statement written to the binary log*");
46+CREATE TABLE t1 (a bigint unsigned not null);
47+CREATE TABLE t2 (a char(255) not null);
48+
49+There are the following types of variables:
50+1) variables that are NOT replicated correctly when using STATEMENT mode;
51+
52+[connection master]
53+SELECT @@max_join_size;
54+@@max_join_size
55+18446744073709551615
56+[connection slave]
57+SELECT @@max_join_size;
58+@@max_join_size
59+18446744073709551615
60+[connection master]
61+SET STATEMENT max_join_size=2 FOR
62+INSERT INTO t1 VALUES(@@max_join_size);
63+SELECT @@max_join_size;
64+@@max_join_size
65+18446744073709551615
66+[connection slave]
67+SELECT * FROM t1;
68+a
69+18446744073709551615
70+SELECT @@max_join_size;
71+@@max_join_size
72+18446744073709551615
73+[connection master]
74+DELETE FROM t1;
75+
76+2) variables thar ARE replicated correctly
77+They must be replicated correctly with "SET STATEMENT" too.
78+
79+[connection master]
80+SELECT @@auto_increment_increment;
81+@@auto_increment_increment
82+1
83+[connection slave]
84+SELECT @@auto_increment_increment;
85+@@auto_increment_increment
86+1
87+[connection master]
88+SET STATEMENT auto_increment_increment=10 FOR
89+INSERT INTO t1 VALUES(@@auto_increment_increment);
90+SELECT @@auto_increment_increment;
91+@@auto_increment_increment
92+1
93+[connection slave]
94+SELECT * FROM t1;
95+a
96+10
97+SELECT @@auto_increment_increment;
98+@@auto_increment_increment
99+1
100+[connection master]
101+DELETE FROM t1;
102+
103+3) sql_mode which is replicated correctly exept NO_DIR_IN_CREATE value;
104+
105+[connection master]
106+SELECT @@sql_mode;
107+@@sql_mode
108+
109+[connection slave]
110+SELECT @@sql_mode;
111+@@sql_mode
112+
113+[connection master]
114+SET STATEMENT sql_mode='ERROR_FOR_DIVISION_BY_ZERO' FOR
115+INSERT INTO t2 VALUES(@@sql_mode);
116+SELECT @@sql_mode;
117+@@sql_mode
118+
119+[connection slave]
120+SELECT * FROM t2;
121+a
122+ERROR_FOR_DIVISION_BY_ZERO
123+SELECT @@sql_mode;
124+@@sql_mode
125+
126+[connection master]
127+DELETE FROM t2;
128+[connection master]
129+SELECT @@sql_mode;
130+@@sql_mode
131+
132+[connection slave]
133+SELECT @@sql_mode;
134+@@sql_mode
135+
136+[connection master]
137+SET STATEMENT sql_mode='NO_DIR_IN_CREATE' FOR
138+INSERT INTO t2 VALUES(@@sql_mode);
139+SELECT @@sql_mode;
140+@@sql_mode
141+
142+[connection slave]
143+SELECT * FROM t2;
144+a
145+
146+SELECT @@sql_mode;
147+@@sql_mode
148+
149+[connection master]
150+DELETE FROM t2;
151+
152+4) variables that are not replicated at all:
153+default_storage_engine, storage_engine, max_heap_table_size
154+
155+[connection master]
156+SELECT @@max_heap_table_size;
157+@@max_heap_table_size
158+1048576
159+[connection slave]
160+SELECT @@max_heap_table_size;
161+@@max_heap_table_size
162+1048576
163+[connection master]
164+SET STATEMENT max_heap_table_size=16384 FOR
165+INSERT INTO t1 VALUES(@@max_heap_table_size);
166+SELECT @@max_heap_table_size;
167+@@max_heap_table_size
168+1048576
169+[connection slave]
170+SELECT * FROM t1;
171+a
172+1048576
173+SELECT @@max_heap_table_size;
174+@@max_heap_table_size
175+1048576
176+[connection master]
177+DELETE FROM t1;
178+DROP TABLE t1;
179+DROP TABLE t2;
180+include/stop_slave.inc
181
182=== added file 'mysql-test/suite/percona/percona_rpl_stm_per_query_variables_settings.test'
183--- mysql-test/suite/percona/percona_rpl_stm_per_query_variables_settings.test 1970-01-01 00:00:00 +0000
184+++ mysql-test/suite/percona/percona_rpl_stm_per_query_variables_settings.test 2013-11-06 17:03:07 +0000
185@@ -0,0 +1,57 @@
186+--source include/master-slave.inc
187+--source include/have_binlog_format_statement.inc
188+
189+--disable_warnings
190+DROP TABLE IF EXISTS t1;
191+DROP TABLE IF EXISTS t2;
192+--enable_warnings
193+
194+call mtr.add_suppression("Unsafe statement written to the binary log*");
195+CREATE TABLE t1 (a bigint unsigned not null);
196+CREATE TABLE t2 (a char(255) not null);
197+
198+--echo
199+--echo There are the following types of variables:
200+--echo 1) variables that are NOT replicated correctly when using STATEMENT mode;
201+--echo
202+
203+--let $rpl_ssvt_var_name=max_join_size
204+--let $rpl_ssvt_var_value=2
205+--let $rpl_ssvt_table=t1
206+--source suite/percona/percona_rpl_set_statement_variable_test.inc
207+
208+--echo
209+--echo 2) variables thar ARE replicated correctly
210+--echo They must be replicated correctly with "SET STATEMENT" too.
211+--echo
212+--let $rpl_ssvt_var_name=auto_increment_increment
213+--let $rpl_ssvt_var_value=10
214+--let $rpl_ssvt_table=t1
215+--source suite/percona/percona_rpl_set_statement_variable_test.inc
216+
217+--echo
218+--echo 3) sql_mode which is replicated correctly exept NO_DIR_IN_CREATE value;
219+--echo
220+--let $rpl_ssvt_var_name=sql_mode
221+--let $rpl_ssvt_var_value='ERROR_FOR_DIVISION_BY_ZERO'
222+--let $rpl_ssvt_table=t2
223+--source suite/percona/percona_rpl_set_statement_variable_test.inc
224+--let $rpl_ssvt_var_name=sql_mode
225+--let $rpl_ssvt_var_value='NO_DIR_IN_CREATE'
226+--let $rpl_ssvt_table=t2
227+--source suite/percona/percona_rpl_set_statement_variable_test.inc
228+
229+--echo
230+--echo 4) variables that are not replicated at all:
231+--echo default_storage_engine, storage_engine, max_heap_table_size
232+--echo
233+--let $rpl_ssvt_var_name=max_heap_table_size
234+--let $rpl_ssvt_var_value=16384
235+--let $rpl_ssvt_table=t1
236+--source suite/percona/percona_rpl_set_statement_variable_test.inc
237+
238+connection master;
239+DROP TABLE t1;
240+DROP TABLE t2;
241+sync_slave_with_master;
242+source include/stop_slave.inc;
243
244=== added file 'mysql-test/suite/percona/percona_statement_set.result'
245--- mysql-test/suite/percona/percona_statement_set.result 1970-01-01 00:00:00 +0000
246+++ mysql-test/suite/percona/percona_statement_set.result 2013-11-06 17:03:07 +0000
247@@ -0,0 +1,862 @@
248+'# SET STATEMENT ..... FOR .... TEST'
249+DROP TABLE IF EXISTS t1;
250+DROP FUNCTION IF EXISTS myProc;
251+DROP PROCEDURE IF EXISTS p1;
252+DROP PROCEDURE IF EXISTS p2;
253+DROP PROCEDURE IF EXISTS p3;
254+DROP PROCEDURE IF EXISTS p4;
255+DROP PROCEDURE IF EXISTS p5;
256+DROP TABLE IF EXISTS STATEMENT;
257+'# Setup database'
258+CREATE TABLE t1 (v1 INT, v2 INT);
259+INSERT INTO t1 VALUES (1,2);
260+INSERT INTO t1 VALUES (3,4);
261+''
262+'#------------------ STATEMENT Test 1 -----------------------#'
263+'# Initialize variables to known setting'
264+SET SESSION sort_buffer_size=100000;
265+''
266+'# Pre-STATEMENT variable value'
267+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
268+Variable_name Value
269+sort_buffer_size 100000
270+SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t1;
271+v1 v2
272+1 2
273+3 4
274+''
275+'# Post-STATEMENT variable value'
276+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
277+Variable_name Value
278+sort_buffer_size 100000
279+''
280+'#------------------ STATEMENT Test 2 -----------------------#'
281+'# Initialize variables to known setting'
282+SET SESSION binlog_format=mixed;
283+SET SESSION sort_buffer_size=100000;
284+'# Pre-STATEMENT variable value'
285+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
286+Variable_name Value
287+sort_buffer_size 100000
288+SHOW SESSION VARIABLES LIKE 'binlog_format';
289+Variable_name Value
290+binlog_format MIXED
291+SET STATEMENT sort_buffer_size=150000, binlog_format=row
292+FOR SELECT * FROM t1;
293+v1 v2
294+1 2
295+3 4
296+'# Post-STATEMENT variable value'
297+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
298+Variable_name Value
299+sort_buffer_size 100000
300+SHOW SESSION VARIABLES LIKE 'binlog_format';
301+Variable_name Value
302+binlog_format MIXED
303+''
304+'#------------------ STATEMENT Test 3 -----------------------#'
305+'# set initial variable value, make prepared statement
306+SET SESSION binlog_format=row;
307+PREPARE stmt1 FROM 'SET STATEMENT binlog_format=row FOR SELECT * FROM t1';
308+''
309+'# Change variable setting'
310+SET SESSION binlog_format=mixed;
311+''
312+'# Pre-STATEMENT variable value'
313+''
314+SHOW SESSION VARIABLES LIKE 'binlog_format';
315+Variable_name Value
316+binlog_format MIXED
317+''
318+EXECUTE stmt1;
319+v1 v2
320+1 2
321+3 4
322+''
323+'# Post-STATEMENT variable value'
324+SHOW SESSION VARIABLES LIKE 'binlog_format';
325+Variable_name Value
326+binlog_format MIXED
327+''
328+DEALLOCATE PREPARE stmt1;
329+'#------------------ STATEMENT Test 4 -----------------------#'
330+'# set initial variable value, make prepared statement
331+SET SESSION myisam_sort_buffer_size=500000, myisam_repair_threads=1;
332+''
333+'# Pre-STATEMENT variable value'
334+SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
335+Variable_name Value
336+myisam_sort_buffer_size 500000
337+SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
338+Variable_name Value
339+myisam_repair_threads 1
340+''
341+SET STATEMENT myisam_sort_buffer_size=800000,
342+myisam_repair_threads=2 FOR OPTIMIZE TABLE t1;
343+Table Op Msg_type Msg_text
344+test.t1 optimize status OK
345+''
346+'# Post-STATEMENT variable value'
347+SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
348+Variable_name Value
349+myisam_sort_buffer_size 500000
350+SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
351+Variable_name Value
352+myisam_repair_threads 1
353+''
354+'#------------------ STATEMENT Test 5 -----------------------#'
355+'# Initialize variables to known setting'
356+SET SESSION sort_buffer_size=100000;
357+''
358+'# Pre-STATEMENT variable value'
359+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
360+Variable_name Value
361+sort_buffer_size 100000
362+''
363+SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t2;
364+ERROR 42S02: Table 'test.t2' doesn't exist
365+''
366+'# Post-STATEMENT variable value'
367+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
368+Variable_name Value
369+sort_buffer_size 100000
370+''
371+'#------------------ STATEMENT Test 6 -----------------------#'
372+'# Initialize variables to known setting'
373+SET SESSION keep_files_on_create=ON;
374+''
375+'# Pre-STATEMENT variable value'
376+SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
377+Variable_name Value
378+keep_files_on_create ON
379+''
380+SET STATEMENT keep_files_on_create=OFF FOR SELECT * FROM t1;
381+v1 v2
382+1 2
383+3 4
384+''
385+'# Post-STATEMENT variable value'
386+SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
387+Variable_name Value
388+keep_files_on_create ON
389+''
390+'#------------------ STATEMENT Test 7 -----------------------#'
391+'# Initialize variables to known setting'
392+SET SESSION max_join_size=2222220000000;
393+''
394+'# Pre-STATEMENT variable value'
395+SHOW SESSION VARIABLES LIKE 'max_join_size';
396+Variable_name Value
397+max_join_size 2222220000000
398+''
399+SET STATEMENT max_join_size=1000000000000 FOR SELECT * FROM t1;
400+v1 v2
401+1 2
402+3 4
403+''
404+'# Post-STATEMENT variable value'
405+SHOW SESSION VARIABLES LIKE 'max_join_size';
406+Variable_name Value
407+max_join_size 2222220000000
408+''
409+'#------------------Test 8-----------------------#'
410+'# Initialize test variables'
411+SET SESSION myisam_sort_buffer_size=500000,
412+myisam_repair_threads=1,
413+sort_buffer_size = 200000,
414+max_join_size=2222220000000,
415+keep_files_on_create=ON;
416+''
417+'# LONG '
418+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
419+Variable_name Value
420+sort_buffer_size 200000
421+SET STATEMENT sort_buffer_size = 100000
422+FOR SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
423+Variable_name Value
424+sort_buffer_size 100000
425+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
426+Variable_name Value
427+sort_buffer_size 200000
428+''
429+'# MY_BOOL '
430+SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
431+Variable_name Value
432+keep_files_on_create ON
433+SET STATEMENT keep_files_on_create=OFF
434+FOR SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
435+Variable_name Value
436+keep_files_on_create OFF
437+SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
438+Variable_name Value
439+keep_files_on_create ON
440+''
441+'# INT/LONG '
442+SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
443+Variable_name Value
444+myisam_repair_threads 1
445+SET STATEMENT myisam_repair_threads=2
446+FOR SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
447+Variable_name Value
448+myisam_repair_threads 2
449+SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
450+Variable_name Value
451+myisam_repair_threads 1
452+''
453+'# ULONGLONG '
454+SHOW SESSION VARIABLES LIKE 'max_join_size';
455+Variable_name Value
456+max_join_size 2222220000000
457+SET STATEMENT max_join_size=2000000000000
458+FOR SHOW SESSION VARIABLES LIKE 'max_join_size';
459+Variable_name Value
460+max_join_size 2000000000000
461+SHOW SESSION VARIABLES LIKE 'max_join_size';
462+Variable_name Value
463+max_join_size 2222220000000
464+''
465+'#------------------Test 9-----------------------#'
466+'# set initial variable values
467+SET SESSION myisam_sort_buffer_size=500000,
468+myisam_repair_threads=1,
469+sort_buffer_size=100000,
470+binlog_format=mixed,
471+keep_files_on_create=ON,
472+max_join_size=2222220000000;
473+''
474+''
475+'# Pre-STATEMENT variable value
476+SELECT @@myisam_sort_buffer_size,
477+@@myisam_repair_threads,
478+@@sort_buffer_size,
479+@@binlog_format,
480+@@keep_files_on_create,
481+@@max_join_size;
482+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
483+500000 1 100000 MIXED 1 2222220000000
484+''
485+''
486+CREATE FUNCTION myProc (cost DECIMAL(10,2))
487+RETURNS DECIMAL(10,2)
488+SQL SECURITY DEFINER
489+tax: BEGIN
490+DECLARE order_tax DECIMAL(10,2);
491+SET order_tax = cost * .05;
492+RETURN order_tax;
493+END|
494+''
495+'# During Execution values
496+SET STATEMENT myisam_sort_buffer_size=400000,
497+myisam_repair_threads=2,
498+sort_buffer_size=200000,
499+binlog_format=row,
500+keep_files_on_create=OFF,
501+max_join_size=4444440000000 FOR
502+SELECT myProc(123.45);
503+myProc(123.45)
504+6.17
505+''
506+'# Post-STATEMENT No 1 variable value Pre-STATEMENT for No 2'
507+SELECT @@myisam_sort_buffer_size,
508+@@myisam_repair_threads,
509+@@sort_buffer_size,
510+@@binlog_format,
511+@@keep_files_on_create,
512+@@max_join_size;
513+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
514+500000 1 100000 MIXED 1 2222220000000
515+''
516+SET STATEMENT myisam_sort_buffer_size=400000,
517+myisam_repair_threads=2,
518+sort_buffer_size=200000,
519+binlog_format=row,
520+keep_files_on_create=OFF,
521+max_join_size=4444440000000 FOR
522+DROP FUNCTION myProc;
523+''
524+'# Post-STATEMENT No 2 variable value
525+SELECT @@myisam_sort_buffer_size,
526+@@myisam_repair_threads,
527+@@sort_buffer_size,
528+@@binlog_format,
529+@@keep_files_on_create,
530+@@max_join_size;
531+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
532+500000 1 100000 MIXED 1 2222220000000
533+''
534+'#------------------Test 10-----------------------#'
535+'# set initial variable values
536+SET SESSION myisam_sort_buffer_size=500000,
537+myisam_repair_threads=1,
538+sort_buffer_size=100000,
539+binlog_format=mixed,
540+keep_files_on_create=ON,
541+max_join_size=2222220000000;
542+''
543+'# Pre-STATEMENT variable value
544+SELECT @@myisam_sort_buffer_size,
545+@@myisam_repair_threads,
546+@@sort_buffer_size,
547+@@binlog_format,
548+@@keep_files_on_create,
549+@@max_join_size;
550+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
551+500000 1 100000 MIXED 1 2222220000000
552+''
553+''
554+SET STATEMENT myisam_sort_buffer_size=400000,
555+myisam_repair_threads=2,
556+sort_buffer_size=200000,
557+binlog_format=row,
558+keep_files_on_create=OFF,
559+max_join_size=4444440000000 FOR
560+PREPARE stmt2
561+FROM 'SELECT * FROM t1';
562+''
563+'Test No 1 Post Value & Test 2 Pre values'
564+SELECT @@myisam_sort_buffer_size,
565+@@myisam_repair_threads,
566+@@sort_buffer_size,
567+@@binlog_format,
568+@@keep_files_on_create,
569+@@max_join_size;
570+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
571+500000 1 100000 MIXED 1 2222220000000
572+''
573+''
574+SET STATEMENT myisam_sort_buffer_size=400000,
575+myisam_repair_threads=2,
576+sort_buffer_size=200000,
577+binlog_format=row,
578+keep_files_on_create=OFF,
579+max_join_size=4444440000000 FOR
580+EXECUTE stmt2;
581+v1 v2
582+1 2
583+3 4
584+''
585+'# Post-STATEMENT No 2
586+SELECT @@myisam_sort_buffer_size,
587+@@myisam_repair_threads,
588+@@sort_buffer_size,
589+@@binlog_format,
590+@@keep_files_on_create,
591+@@max_join_size;
592+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
593+500000 1 100000 MIXED 1 2222220000000
594+''
595+DEALLOCATE PREPARE stmt2;
596+''
597+'#------------------Test 11-----------------------#'
598+'# set initial variable values
599+SET SESSION myisam_sort_buffer_size=500000,
600+myisam_repair_threads=1,
601+sort_buffer_size=100000,
602+binlog_format=mixed,
603+keep_files_on_create=ON,
604+max_join_size=2222220000000;
605+''
606+''
607+'# Pre-STATEMENT variable value
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+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
615+500000 1 100000 MIXED 1 2222220000000
616+''
617+''
618+SET STATEMENT myisam_sort_buffer_size=400000,
619+myisam_repair_threads=2,
620+sort_buffer_size=200000,
621+keep_files_on_create=OFF,
622+max_join_size=4444440000000 FOR
623+PREPARE stmt1 FROM
624+'SET STATEMENT binlog_format=row FOR SELECT * FROM t1';
625+''
626+'Test No 1 Post Value & Test 2 Pre values'
627+SELECT @@myisam_sort_buffer_size,
628+@@myisam_repair_threads,
629+@@sort_buffer_size,
630+@@binlog_format,
631+@@keep_files_on_create,
632+@@max_join_size;
633+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
634+500000 1 100000 MIXED 1 2222220000000
635+''
636+''
637+SET STATEMENT myisam_sort_buffer_size=400000,
638+myisam_repair_threads=2,
639+sort_buffer_size=200000,
640+keep_files_on_create=OFF,
641+max_join_size=4444440000000 FOR
642+EXECUTE stmt1;
643+v1 v2
644+1 2
645+3 4
646+''
647+'# Post-STATEMENT No 2
648+SELECT @@myisam_sort_buffer_size,
649+@@myisam_repair_threads,
650+@@sort_buffer_size,
651+@@binlog_format,
652+@@keep_files_on_create,
653+@@max_join_size;
654+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
655+500000 1 100000 MIXED 1 2222220000000
656+''
657+''
658+'#------------------Test 12-----------------------#'
659+'# set initial variable values
660+SET SESSION myisam_sort_buffer_size=500000,
661+myisam_repair_threads=1,
662+sort_buffer_size=100000,
663+binlog_format=mixed,
664+keep_files_on_create=ON,
665+max_join_size=2222220000000;
666+''
667+''
668+'# Pre-STATEMENT variable value
669+SELECT @@myisam_sort_buffer_size,
670+@@myisam_repair_threads,
671+@@sort_buffer_size,
672+@@binlog_format,
673+@@keep_files_on_create,
674+@@max_join_size;
675+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
676+500000 1 100000 MIXED 1 2222220000000
677+''
678+''
679+SET STATEMENT myisam_sort_buffer_size=400000,
680+myisam_repair_threads=2,
681+sort_buffer_size=200000,
682+binlog_format=row,
683+keep_files_on_create=OFF,
684+max_join_size=4444440000000 FOR
685+CREATE PROCEDURE p1() BEGIN
686+SELECT @@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+END|
693+''
694+'Test No 1 Post Value & Test 2 Pre values'
695+SELECT @@myisam_sort_buffer_size,
696+@@myisam_repair_threads,
697+@@sort_buffer_size,
698+@@binlog_format,
699+@@keep_files_on_create,
700+@@max_join_size;
701+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
702+500000 1 100000 MIXED 1 2222220000000
703+''
704+''
705+SET STATEMENT myisam_sort_buffer_size=400000,
706+myisam_repair_threads=2,
707+sort_buffer_size=200000,
708+binlog_format=row,
709+keep_files_on_create=OFF,
710+max_join_size=4444440000000 FOR
711+CALL p1();
712+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
713+400000 2 200000 ROW 0 4444440000000
714+''
715+'# Post-STATEMENT No 2
716+SELECT @@myisam_sort_buffer_size,
717+@@myisam_repair_threads,
718+@@sort_buffer_size,
719+@@binlog_format,
720+@@keep_files_on_create,
721+@@max_join_size;
722+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
723+500000 1 100000 MIXED 1 2222220000000
724+''
725+''
726+'#------------------Test 13-----------------------#'
727+'# set initial variable values
728+SET SESSION myisam_sort_buffer_size=500000,
729+myisam_repair_threads=1,
730+sort_buffer_size=100000,
731+binlog_format=mixed,
732+keep_files_on_create=ON,
733+max_join_size=2222220000000;
734+''
735+''
736+CREATE PROCEDURE p2() BEGIN
737+SET STATEMENT myisam_sort_buffer_size=400000,
738+myisam_repair_threads=3,
739+sort_buffer_size=300000,
740+binlog_format=mixed,
741+keep_files_on_create=OFF,
742+max_join_size=3333330000000 FOR
743+CALL p1();
744+END|
745+''
746+'# Pre-STATEMENT variable value
747+SELECT @@myisam_sort_buffer_size,
748+@@myisam_repair_threads,
749+@@sort_buffer_size,
750+@@binlog_format,
751+@@keep_files_on_create,
752+@@max_join_size;
753+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
754+500000 1 100000 MIXED 1 2222220000000
755+''
756+''
757+SET STATEMENT myisam_sort_buffer_size=400000,
758+myisam_repair_threads=2,
759+sort_buffer_size=200000,
760+binlog_format=row,
761+keep_files_on_create=OFF,
762+max_join_size=4444440000000 FOR
763+CALL p2();
764+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
765+400000 3 300000 MIXED 0 3333330000000
766+''
767+'# Post-STATEMENT
768+SELECT @@myisam_sort_buffer_size,
769+@@myisam_repair_threads,
770+@@sort_buffer_size,
771+@@binlog_format,
772+@@keep_files_on_create,
773+@@max_join_size;
774+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
775+500000 1 100000 MIXED 1 2222220000000
776+''
777+''
778+'#------------------Test 14-----------------------#'
779+'# set initial variable values
780+SET SESSION myisam_sort_buffer_size=500000,
781+myisam_repair_threads=1,
782+sort_buffer_size=100000,
783+binlog_format=mixed,
784+keep_files_on_create=ON,
785+max_join_size=2222220000000;
786+''
787+''
788+CREATE PROCEDURE p3() BEGIN
789+SELECT @@myisam_sort_buffer_size,
790+@@myisam_repair_threads,
791+@@sort_buffer_size,
792+@@binlog_format,
793+@@keep_files_on_create,
794+@@max_join_size;
795+SET STATEMENT myisam_sort_buffer_size=320000,
796+myisam_repair_threads=2,
797+sort_buffer_size=220022,
798+binlog_format=row,
799+keep_files_on_create=ON,
800+max_join_size=2222220000000 FOR
801+CALL p2();
802+END|
803+''
804+'# Pre-STATEMENT variable value
805+SELECT @@myisam_sort_buffer_size,
806+@@myisam_repair_threads,
807+@@sort_buffer_size,
808+@@binlog_format,
809+@@keep_files_on_create,
810+@@max_join_size;
811+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
812+500000 1 100000 MIXED 1 2222220000000
813+''
814+''
815+SET STATEMENT myisam_sort_buffer_size=400000,
816+myisam_repair_threads=2,
817+sort_buffer_size=200000,
818+binlog_format=row,
819+keep_files_on_create=OFF,
820+max_join_size=4444440000000 FOR
821+CALL p3();
822+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
823+400000 2 200000 ROW 0 4444440000000
824+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
825+400000 3 300000 MIXED 0 3333330000000
826+''
827+'# Post-STATEMENT
828+SELECT @@myisam_sort_buffer_size,
829+@@myisam_repair_threads,
830+@@sort_buffer_size,
831+@@binlog_format,
832+@@keep_files_on_create,
833+@@max_join_size;
834+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
835+500000 1 100000 MIXED 1 2222220000000
836+''
837+''
838+''
839+''
840+'#------------------Test 15-----------------------#'
841+'# set initial variable values
842+SET SESSION myisam_sort_buffer_size=500000,
843+myisam_repair_threads=1,
844+sort_buffer_size=100000,
845+binlog_format=mixed,
846+keep_files_on_create=ON,
847+max_join_size=2222220000000;
848+''
849+''
850+CREATE PROCEDURE p4() BEGIN
851+SELECT @@myisam_sort_buffer_size,
852+@@myisam_repair_threads,
853+@@sort_buffer_size,
854+@@binlog_format,
855+@@keep_files_on_create,
856+@@max_join_size;
857+SET STATEMENT myisam_sort_buffer_size=320000,
858+myisam_repair_threads=2,
859+sort_buffer_size=220022,
860+binlog_format=row,
861+keep_files_on_create=ON,
862+max_join_size=2222220000000 FOR
863+SELECT @@myisam_sort_buffer_size,
864+@@myisam_repair_threads,
865+@@sort_buffer_size,
866+@@binlog_format,
867+@@keep_files_on_create,
868+@@max_join_size;
869+SET STATEMENT myisam_sort_buffer_size=320000,
870+myisam_repair_threads=2,
871+sort_buffer_size=220022,
872+binlog_format=row,
873+keep_files_on_create=ON,
874+max_join_size=2222220000000 FOR
875+SELECT @@myisam_sort_buffer_size,
876+@@myisam_repair_threads,
877+@@sort_buffer_size,
878+@@binlog_format,
879+@@keep_files_on_create,
880+@@max_join_size;
881+SET STATEMENT myisam_sort_buffer_size=320000,
882+myisam_repair_threads=2,
883+sort_buffer_size=220022,
884+binlog_format=row,
885+keep_files_on_create=ON,
886+max_join_size=2222220000000 FOR
887+SELECT @@myisam_sort_buffer_size,
888+@@myisam_repair_threads,
889+@@sort_buffer_size,
890+@@binlog_format,
891+@@keep_files_on_create,
892+@@max_join_size;
893+END|
894+''
895+'# Pre-STATEMENT variable value
896+SELECT @@myisam_sort_buffer_size,
897+@@myisam_repair_threads,
898+@@sort_buffer_size,
899+@@binlog_format,
900+@@keep_files_on_create,
901+@@max_join_size;
902+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
903+500000 1 100000 MIXED 1 2222220000000
904+''
905+''
906+SET STATEMENT myisam_sort_buffer_size=400000,
907+myisam_repair_threads=2,
908+sort_buffer_size=200000,
909+binlog_format=row,
910+keep_files_on_create=OFF,
911+max_join_size=4444440000000 FOR
912+CALL p4();
913+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
914+400000 2 200000 ROW 0 4444440000000
915+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
916+320000 2 220022 ROW 1 2222220000000
917+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
918+320000 2 220022 ROW 1 2222220000000
919+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
920+320000 2 220022 ROW 1 2222220000000
921+''
922+'# Post-STATEMENT
923+SELECT @@myisam_sort_buffer_size,
924+@@myisam_repair_threads,
925+@@sort_buffer_size,
926+@@binlog_format,
927+@@keep_files_on_create,
928+@@max_join_size;
929+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
930+500000 1 100000 MIXED 1 2222220000000
931+''
932+''
933+'#------------------Test 16-----------------------#'
934+''
935+'# Pre-STATEMENT variable value
936+SELECT @@sql_mode;
937+@@sql_mode
938+
939+''
940+''
941+SET STATEMENT sql_mode='ansi' FOR SELECT * FROM t1;
942+v1 v2
943+1 2
944+3 4
945+''
946+'# Post-STATEMENT
947+SELECT @@sql_mode;
948+@@sql_mode
949+
950+''
951+''
952+'#------------------Test 17-----------------------#'
953+'# set initial variable values
954+SET SESSION myisam_sort_buffer_size=500000,
955+myisam_repair_threads=1,
956+sort_buffer_size=100000,
957+binlog_format=mixed,
958+keep_files_on_create=ON,
959+max_join_size=2222220000000;
960+''
961+'# Pre-STATEMENT variable value
962+SELECT @@myisam_sort_buffer_size,
963+@@myisam_repair_threads,
964+@@sort_buffer_size,
965+@@binlog_format,
966+@@keep_files_on_create,
967+@@max_join_size;
968+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
969+500000 1 100000 MIXED 1 2222220000000
970+''
971+''
972+SET STATEMENT myisam_sort_buffer_size=320000,
973+myisam_repair_threads=2,
974+sort_buffer_size=220022,
975+binlog_format=row,
976+keep_files_on_create=ON,
977+max_join_size=2222220000000
978+FOR SET SESSION
979+myisam_sort_buffer_size=260000,
980+myisam_repair_threads=3,
981+sort_buffer_size=230013,
982+binlog_format=row,
983+keep_files_on_create=ON,
984+max_join_size=2323230000000;
985+''
986+'# Post-STATEMENT
987+SELECT @@myisam_sort_buffer_size,
988+@@myisam_repair_threads,
989+@@sort_buffer_size,
990+@@binlog_format,
991+@@keep_files_on_create,
992+@@max_join_size;
993+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
994+500000 1 100000 MIXED 1 2222220000000
995+''
996+''
997+'#------------------Test 18-----------------------#'
998+'# set initial variable values
999+SET SESSION myisam_sort_buffer_size=500000,
1000+myisam_repair_threads=1,
1001+sort_buffer_size=100000,
1002+binlog_format=mixed,
1003+keep_files_on_create=ON,
1004+max_join_size=2222220000000;
1005+''
1006+'# Pre-STATEMENT variable value
1007+SELECT @@myisam_sort_buffer_size,
1008+@@myisam_repair_threads,
1009+@@sort_buffer_size,
1010+@@binlog_format,
1011+@@keep_files_on_create,
1012+@@max_join_size;
1013+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
1014+500000 1 100000 MIXED 1 2222220000000
1015+''
1016+''
1017+CREATE PROCEDURE p5() BEGIN
1018+SELECT @@myisam_sort_buffer_size,
1019+@@myisam_repair_threads,
1020+@@sort_buffer_size,
1021+@@binlog_format,
1022+@@keep_files_on_create,
1023+@@max_join_size;
1024+SET SESSION
1025+myisam_sort_buffer_size=260000,
1026+myisam_repair_threads=3,
1027+sort_buffer_size=230013,
1028+binlog_format=row,
1029+keep_files_on_create=ON,
1030+max_join_size=2323230000000;
1031+SELECT @@myisam_sort_buffer_size,
1032+@@myisam_repair_threads,
1033+@@sort_buffer_size,
1034+@@binlog_format,
1035+@@keep_files_on_create,
1036+@@max_join_size;
1037+END|
1038+''
1039+''
1040+SET STATEMENT myisam_sort_buffer_size=400000,
1041+myisam_repair_threads=2,
1042+sort_buffer_size=200000,
1043+binlog_format=row,
1044+keep_files_on_create=OFF,
1045+max_join_size=4444440000000 FOR
1046+CALL p5();
1047+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
1048+400000 2 200000 ROW 0 4444440000000
1049+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
1050+260000 3 230013 ROW 1 2323230000000
1051+''
1052+'# Post-STATEMENT
1053+SELECT @@myisam_sort_buffer_size,
1054+@@myisam_repair_threads,
1055+@@sort_buffer_size,
1056+@@binlog_format,
1057+@@keep_files_on_create,
1058+@@max_join_size;
1059+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
1060+500000 1 100000 MIXED 1 2222220000000
1061+''
1062+''
1063+'#------------------Test 19-----------------------#'
1064+SET STATEMENT max_error_count=100 FOR;
1065+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
1066+SET STATEMENT max_error_count=100 INSERT t1 VALUES (1,2);
1067+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'INSERT t1 VALUES (1,2)' at line 1
1068+SET STATEMENT FOR INSERT INTO t1 VALUES (1,2);
1069+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FOR INSERT INTO t1 VALUES (1,2)' at line 1
1070+SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
1071+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FOR INSERT INTO t1 VALUES (1,2)' at line 1
1072+SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
1073+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'FOR INSERT INTO t1 VALUES (1,2)' at line 1
1074+SET STATEMENT GLOBAL max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
1075+ERROR HY000: Unknown system variable 'GLOBAL'
1076+SET STATEMENT @@global.max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
1077+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '@@global.max_error_count=100 FOR INSERT INTO t1 VALUES (1,2)' at line 1
1078+''
1079+''
1080+'#------------------Test 20-----------------------#'
1081+SET STATEMENT connect_timeout=100 FOR INSERT INTO t1 VALUES (1,2);
1082+ERROR HY000: Variable 'connect_timeout' is a GLOBAL variable and should be set with SET GLOBAL
1083+''
1084+''
1085+'#------------------Test 21-----------------------#'
1086+SELECT @@myisam_sort_buffer_size, @@sort_buffer_size;
1087+@@myisam_sort_buffer_size @@sort_buffer_size
1088+500000 100000
1089+SET STATEMENT myisam_sort_buffer_size = 700000, sort_buffer_size = 3000000
1090+FOR SET STATEMENT myisam_sort_buffer_size=200000
1091+FOR SELECT @@myisam_sort_buffer_size, @@sort_buffer_size;
1092+@@myisam_sort_buffer_size @@sort_buffer_size
1093+200000 3000000
1094+SELECT @@myisam_sort_buffer_size, @@sort_buffer_size;
1095+@@myisam_sort_buffer_size @@sort_buffer_size
1096+500000 100000
1097+''
1098+''
1099+'#------------------Test 22-----------------------#'
1100+CREATE TABLE STATEMENT(a INT);
1101+DROP TABLE STATEMENT;
1102+''
1103+'# Cleanup'
1104+DROP TABLE t1;
1105+DROP PROCEDURE p1;
1106+DROP PROCEDURE p2;
1107+DROP PROCEDURE p3;
1108+DROP PROCEDURE p4;
1109+DROP PROCEDURE p5;
1110
1111=== added file 'mysql-test/suite/percona/percona_statement_set.test'
1112--- mysql-test/suite/percona/percona_statement_set.test 1970-01-01 00:00:00 +0000
1113+++ mysql-test/suite/percona/percona_statement_set.test 2013-11-06 17:03:07 +0000
1114@@ -0,0 +1,830 @@
1115+--echo '# SET STATEMENT ..... FOR .... TEST'
1116+############################ STATEMENT_SET #############################
1117+# #
1118+# Testing working functionality of SET STATEMENT #
1119+# #
1120+# #
1121+# There is important documentation within #
1122+# #
1123+# #
1124+# Author: Joe Lukas #
1125+# Creation: #
1126+# 2009-08-02 Implement this test as part of #
1127+# WL#681 Per query variable settings #
1128+# #
1129+########################################################################
1130+
1131+--disable_warnings
1132+DROP TABLE IF EXISTS t1;
1133+DROP FUNCTION IF EXISTS myProc;
1134+DROP PROCEDURE IF EXISTS p1;
1135+DROP PROCEDURE IF EXISTS p2;
1136+DROP PROCEDURE IF EXISTS p3;
1137+DROP PROCEDURE IF EXISTS p4;
1138+DROP PROCEDURE IF EXISTS p5;
1139+DROP TABLE IF EXISTS STATEMENT;
1140+--enable_warnings
1141+####################################################################
1142+#Set up current database
1143+####################################################################
1144+--echo '# Setup database'
1145+CREATE TABLE t1 (v1 INT, v2 INT);
1146+INSERT INTO t1 VALUES (1,2);
1147+INSERT INTO t1 VALUES (3,4);
1148+--echo ''
1149+--echo '#------------------ STATEMENT Test 1 -----------------------#'
1150+####################################################################
1151+# Checks with variable value type ulong #
1152+####################################################################
1153+--echo '# Initialize variables to known setting'
1154+SET SESSION sort_buffer_size=100000;
1155+--echo ''
1156+--echo '# Pre-STATEMENT variable value'
1157+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
1158+SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t1;
1159+--echo ''
1160+--echo '# Post-STATEMENT variable value'
1161+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
1162+--echo ''
1163+--echo '#------------------ STATEMENT Test 2 -----------------------#'
1164+####################################################################
1165+# Checks for multiple set values inside STATEMENT ... FOR #
1166+####################################################################
1167+--echo '# Initialize variables to known setting'
1168+SET SESSION binlog_format=mixed;
1169+SET SESSION sort_buffer_size=100000;
1170+--echo '# Pre-STATEMENT variable value'
1171+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
1172+SHOW SESSION VARIABLES LIKE 'binlog_format';
1173+SET STATEMENT sort_buffer_size=150000, binlog_format=row
1174+ FOR SELECT * FROM t1;
1175+--echo '# Post-STATEMENT variable value'
1176+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
1177+SHOW SESSION VARIABLES LIKE 'binlog_format';
1178+
1179+--echo ''
1180+--echo '#------------------ STATEMENT Test 3 -----------------------#'
1181+####################################################################
1182+# Check current variable value is stored in using stored #
1183+# statements. #
1184+####################################################################
1185+--echo '# set initial variable value, make prepared statement
1186+SET SESSION binlog_format=row;
1187+PREPARE stmt1 FROM 'SET STATEMENT binlog_format=row FOR SELECT * FROM t1';
1188+--echo ''
1189+--echo '# Change variable setting'
1190+SET SESSION binlog_format=mixed;
1191+--echo ''
1192+--echo '# Pre-STATEMENT variable value'
1193+--echo ''
1194+SHOW SESSION VARIABLES LIKE 'binlog_format';
1195+--echo ''
1196+EXECUTE stmt1;
1197+--echo ''
1198+--echo '# Post-STATEMENT variable value'
1199+SHOW SESSION VARIABLES LIKE 'binlog_format';
1200+
1201+--echo ''
1202+DEALLOCATE PREPARE stmt1;
1203+--echo '#------------------ STATEMENT Test 4 -----------------------#'
1204+####################################################################
1205+# Check works with OPTIMIZE TABLE command #
1206+# Checks works with a variable value of type INT #
1207+# Checks works with variable type ULONGLONG #
1208+####################################################################
1209+--echo '# set initial variable value, make prepared statement
1210+SET SESSION myisam_sort_buffer_size=500000, myisam_repair_threads=1;
1211+--echo ''
1212+--echo '# Pre-STATEMENT variable value'
1213+SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
1214+SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
1215+--echo ''
1216+SET STATEMENT myisam_sort_buffer_size=800000,
1217+ myisam_repair_threads=2 FOR OPTIMIZE TABLE t1;
1218+--echo ''
1219+--echo '# Post-STATEMENT variable value'
1220+SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
1221+SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
1222+
1223+--echo ''
1224+--echo '#------------------ STATEMENT Test 5 -----------------------#'
1225+####################################################################
1226+# Checks if variable reset after error in statement after FOR #
1227+####################################################################
1228+--echo '# Initialize variables to known setting'
1229+SET SESSION sort_buffer_size=100000;
1230+--echo ''
1231+--echo '# Pre-STATEMENT variable value'
1232+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
1233+--echo ''
1234+--error ER_NO_SUCH_TABLE
1235+SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t2;
1236+--echo ''
1237+--echo '# Post-STATEMENT variable value'
1238+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
1239+
1240+--echo ''
1241+--echo '#------------------ STATEMENT Test 6 -----------------------#'
1242+####################################################################
1243+# Checks works with variable type MY_BOOL #
1244+####################################################################
1245+--echo '# Initialize variables to known setting'
1246+SET SESSION keep_files_on_create=ON;
1247+--echo ''
1248+--echo '# Pre-STATEMENT variable value'
1249+SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
1250+--echo ''
1251+SET STATEMENT keep_files_on_create=OFF FOR SELECT * FROM t1;
1252+--echo ''
1253+--echo '# Post-STATEMENT variable value'
1254+SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
1255+
1256+--echo ''
1257+--echo '#------------------ STATEMENT Test 7 -----------------------#'
1258+####################################################################
1259+# Checks works with variable type HA_ROWS #
1260+####################################################################
1261+--echo '# Initialize variables to known setting'
1262+SET SESSION max_join_size=2222220000000;
1263+--echo ''
1264+--echo '# Pre-STATEMENT variable value'
1265+SHOW SESSION VARIABLES LIKE 'max_join_size';
1266+--echo ''
1267+SET STATEMENT max_join_size=1000000000000 FOR SELECT * FROM t1;
1268+--echo ''
1269+--echo '# Post-STATEMENT variable value'
1270+SHOW SESSION VARIABLES LIKE 'max_join_size';
1271+
1272+--echo ''
1273+--echo '#------------------Test 8-----------------------#'
1274+####################################################################
1275+# Ensure variable of each type is set to proper value during #
1276+# statement after FOR execution #
1277+####################################################################
1278+--echo '# Initialize test variables'
1279+SET SESSION myisam_sort_buffer_size=500000,
1280+ myisam_repair_threads=1,
1281+ sort_buffer_size = 200000,
1282+ max_join_size=2222220000000,
1283+ keep_files_on_create=ON;
1284+
1285+--echo ''
1286+--echo '# LONG '
1287+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
1288+SET STATEMENT sort_buffer_size = 100000
1289+ FOR SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
1290+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
1291+--echo ''
1292+--echo '# MY_BOOL '
1293+SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
1294+SET STATEMENT keep_files_on_create=OFF
1295+ FOR SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
1296+SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
1297+
1298+--echo ''
1299+--echo '# INT/LONG '
1300+SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
1301+SET STATEMENT myisam_repair_threads=2
1302+ FOR SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
1303+SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
1304+--echo ''
1305+--echo '# ULONGLONG '
1306+SHOW SESSION VARIABLES LIKE 'max_join_size';
1307+SET STATEMENT max_join_size=2000000000000
1308+ FOR SHOW SESSION VARIABLES LIKE 'max_join_size';
1309+SHOW SESSION VARIABLES LIKE 'max_join_size';
1310+
1311+--echo ''
1312+--echo '#------------------Test 9-----------------------#'
1313+####################################################################
1314+# No 1 - Check works with CREATE ... BEGIN ... END command #
1315+# Display variables during execution #
1316+# No 2 - Test with DROP command #
1317+####################################################################
1318+--echo '# set initial variable values
1319+SET SESSION myisam_sort_buffer_size=500000,
1320+ myisam_repair_threads=1,
1321+ sort_buffer_size=100000,
1322+ binlog_format=mixed,
1323+ keep_files_on_create=ON,
1324+ max_join_size=2222220000000;
1325+--echo ''
1326+--echo ''
1327+--echo '# Pre-STATEMENT variable value
1328+SELECT @@myisam_sort_buffer_size,
1329+ @@myisam_repair_threads,
1330+ @@sort_buffer_size,
1331+ @@binlog_format,
1332+ @@keep_files_on_create,
1333+ @@max_join_size;
1334+--echo ''
1335+--echo ''
1336+DELIMITER |;
1337+CREATE FUNCTION myProc (cost DECIMAL(10,2))
1338+ RETURNS DECIMAL(10,2)
1339+
1340+ SQL SECURITY DEFINER
1341+
1342+ tax: BEGIN
1343+ DECLARE order_tax DECIMAL(10,2);
1344+ SET order_tax = cost * .05;
1345+ RETURN order_tax;
1346+ END|
1347+DELIMITER ;|
1348+--echo ''
1349+--echo '# During Execution values
1350+SET STATEMENT myisam_sort_buffer_size=400000,
1351+ myisam_repair_threads=2,
1352+ sort_buffer_size=200000,
1353+ binlog_format=row,
1354+ keep_files_on_create=OFF,
1355+ max_join_size=4444440000000 FOR
1356+ SELECT myProc(123.45);
1357+--echo ''
1358+--echo '# Post-STATEMENT No 1 variable value Pre-STATEMENT for No 2'
1359+SELECT @@myisam_sort_buffer_size,
1360+ @@myisam_repair_threads,
1361+ @@sort_buffer_size,
1362+ @@binlog_format,
1363+ @@keep_files_on_create,
1364+ @@max_join_size;
1365+--echo ''
1366+SET STATEMENT myisam_sort_buffer_size=400000,
1367+ myisam_repair_threads=2,
1368+ sort_buffer_size=200000,
1369+ binlog_format=row,
1370+ keep_files_on_create=OFF,
1371+ max_join_size=4444440000000 FOR
1372+ DROP FUNCTION myProc;
1373+--echo ''
1374+--echo '# Post-STATEMENT No 2 variable value
1375+SELECT @@myisam_sort_buffer_size,
1376+ @@myisam_repair_threads,
1377+ @@sort_buffer_size,
1378+ @@binlog_format,
1379+ @@keep_files_on_create,
1380+ @@max_join_size;
1381+
1382+--echo ''
1383+--echo '#------------------Test 10-----------------------#'
1384+####################################################################
1385+# No 1 - Check with PREPARE statement #
1386+# with STATEMENT inside with same variable as outside #
1387+# No 2 - Check with EXECUTE statement #
1388+####################################################################
1389+--echo '# set initial variable values
1390+SET SESSION myisam_sort_buffer_size=500000,
1391+ myisam_repair_threads=1,
1392+ sort_buffer_size=100000,
1393+ binlog_format=mixed,
1394+ keep_files_on_create=ON,
1395+ max_join_size=2222220000000;
1396+--echo ''
1397+--echo '# Pre-STATEMENT variable value
1398+SELECT @@myisam_sort_buffer_size,
1399+ @@myisam_repair_threads,
1400+ @@sort_buffer_size,
1401+ @@binlog_format,
1402+ @@keep_files_on_create,
1403+ @@max_join_size;
1404+--echo ''
1405+--echo ''
1406+SET STATEMENT myisam_sort_buffer_size=400000,
1407+ myisam_repair_threads=2,
1408+ sort_buffer_size=200000,
1409+ binlog_format=row,
1410+ keep_files_on_create=OFF,
1411+ max_join_size=4444440000000 FOR
1412+ PREPARE stmt2
1413+ FROM 'SELECT * FROM t1';
1414+--echo ''
1415+--echo 'Test No 1 Post Value & Test 2 Pre values'
1416+SELECT @@myisam_sort_buffer_size,
1417+ @@myisam_repair_threads,
1418+ @@sort_buffer_size,
1419+ @@binlog_format,
1420+ @@keep_files_on_create,
1421+ @@max_join_size;
1422+--echo ''
1423+--echo ''
1424+SET STATEMENT myisam_sort_buffer_size=400000,
1425+ myisam_repair_threads=2,
1426+ sort_buffer_size=200000,
1427+ binlog_format=row,
1428+ keep_files_on_create=OFF,
1429+ max_join_size=4444440000000 FOR
1430+ EXECUTE stmt2;
1431+--echo ''
1432+--echo '# Post-STATEMENT No 2
1433+SELECT @@myisam_sort_buffer_size,
1434+ @@myisam_repair_threads,
1435+ @@sort_buffer_size,
1436+ @@binlog_format,
1437+ @@keep_files_on_create,
1438+ @@max_join_size;
1439+--echo ''
1440+DEALLOCATE PREPARE stmt2;
1441+--echo ''
1442+--echo '#------------------Test 11-----------------------#'
1443+####################################################################
1444+# No 1 - Check with PREPARE statement #
1445+# check with different variable on inside PREPARE #
1446+# No 2 - Check with EXECUTE statement #
1447+####################################################################
1448+--echo '# set initial variable values
1449+SET SESSION myisam_sort_buffer_size=500000,
1450+ myisam_repair_threads=1,
1451+ sort_buffer_size=100000,
1452+ binlog_format=mixed,
1453+ keep_files_on_create=ON,
1454+ max_join_size=2222220000000;
1455+--echo ''
1456+--echo ''
1457+--echo '# Pre-STATEMENT variable value
1458+SELECT @@myisam_sort_buffer_size,
1459+ @@myisam_repair_threads,
1460+ @@sort_buffer_size,
1461+ @@binlog_format,
1462+ @@keep_files_on_create,
1463+ @@max_join_size;
1464+--echo ''
1465+--echo ''
1466+SET STATEMENT myisam_sort_buffer_size=400000,
1467+ myisam_repair_threads=2,
1468+ sort_buffer_size=200000,
1469+ keep_files_on_create=OFF,
1470+ max_join_size=4444440000000 FOR
1471+ PREPARE stmt1 FROM
1472+ 'SET STATEMENT binlog_format=row FOR SELECT * FROM t1';
1473+--echo ''
1474+--echo 'Test No 1 Post Value & Test 2 Pre values'
1475+SELECT @@myisam_sort_buffer_size,
1476+ @@myisam_repair_threads,
1477+ @@sort_buffer_size,
1478+ @@binlog_format,
1479+ @@keep_files_on_create,
1480+ @@max_join_size;
1481+--echo ''
1482+--echo ''
1483+SET STATEMENT myisam_sort_buffer_size=400000,
1484+ myisam_repair_threads=2,
1485+ sort_buffer_size=200000,
1486+ keep_files_on_create=OFF,
1487+ max_join_size=4444440000000 FOR
1488+ EXECUTE stmt1;
1489+--echo ''
1490+--echo '# Post-STATEMENT No 2
1491+SELECT @@myisam_sort_buffer_size,
1492+ @@myisam_repair_threads,
1493+ @@sort_buffer_size,
1494+ @@binlog_format,
1495+ @@keep_files_on_create,
1496+ @@max_join_size;
1497+--echo ''
1498+--echo ''
1499+--echo '#------------------Test 12-----------------------#'
1500+####################################################################
1501+# No 1 - Check with PROCEDURE (show variables in procedure) #
1502+# No 2 - Check with CALL statement show variables in PROCEDURE #
1503+####################################################################
1504+--echo '# set initial variable values
1505+SET SESSION myisam_sort_buffer_size=500000,
1506+ myisam_repair_threads=1,
1507+ sort_buffer_size=100000,
1508+ binlog_format=mixed,
1509+ keep_files_on_create=ON,
1510+ max_join_size=2222220000000;
1511+--echo ''
1512+--echo ''
1513+--echo '# Pre-STATEMENT variable value
1514+SELECT @@myisam_sort_buffer_size,
1515+ @@myisam_repair_threads,
1516+ @@sort_buffer_size,
1517+ @@binlog_format,
1518+ @@keep_files_on_create,
1519+ @@max_join_size;
1520+--echo ''
1521+--echo ''
1522+DELIMITER |;
1523+SET STATEMENT myisam_sort_buffer_size=400000,
1524+ myisam_repair_threads=2,
1525+ sort_buffer_size=200000,
1526+ binlog_format=row,
1527+ keep_files_on_create=OFF,
1528+ max_join_size=4444440000000 FOR
1529+ CREATE PROCEDURE p1() BEGIN
1530+ SELECT @@myisam_sort_buffer_size,
1531+ @@myisam_repair_threads,
1532+ @@sort_buffer_size,
1533+ @@binlog_format,
1534+ @@keep_files_on_create,
1535+ @@max_join_size;
1536+ END|
1537+DELIMITER ;|
1538+--echo ''
1539+--echo 'Test No 1 Post Value & Test 2 Pre values'
1540+SELECT @@myisam_sort_buffer_size,
1541+ @@myisam_repair_threads,
1542+ @@sort_buffer_size,
1543+ @@binlog_format,
1544+ @@keep_files_on_create,
1545+ @@max_join_size;
1546+--echo ''
1547+--echo ''
1548+SET STATEMENT myisam_sort_buffer_size=400000,
1549+ myisam_repair_threads=2,
1550+ sort_buffer_size=200000,
1551+ binlog_format=row,
1552+ keep_files_on_create=OFF,
1553+ max_join_size=4444440000000 FOR
1554+ CALL p1();
1555+--echo ''
1556+--echo '# Post-STATEMENT No 2
1557+SELECT @@myisam_sort_buffer_size,
1558+ @@myisam_repair_threads,
1559+ @@sort_buffer_size,
1560+ @@binlog_format,
1561+ @@keep_files_on_create,
1562+ @@max_join_size;
1563+--echo ''
1564+--echo ''
1565+
1566+--echo '#------------------Test 13-----------------------#'
1567+####################################################################
1568+# Check PROCEDURE containing SET STATEMENT FOR #
1569+# p1() from test 12 will be used to display variables #
1570+####################################################################
1571+--echo '# set initial variable values
1572+SET SESSION myisam_sort_buffer_size=500000,
1573+ myisam_repair_threads=1,
1574+ sort_buffer_size=100000,
1575+ binlog_format=mixed,
1576+ keep_files_on_create=ON,
1577+ max_join_size=2222220000000;
1578+--echo ''
1579+--echo ''
1580+DELIMITER |;
1581+CREATE PROCEDURE p2() BEGIN
1582+ SET STATEMENT myisam_sort_buffer_size=400000,
1583+ myisam_repair_threads=3,
1584+ sort_buffer_size=300000,
1585+ binlog_format=mixed,
1586+ keep_files_on_create=OFF,
1587+ max_join_size=3333330000000 FOR
1588+ CALL p1();
1589+ END|
1590+DELIMITER ;|
1591+--echo ''
1592+--echo '# Pre-STATEMENT variable value
1593+SELECT @@myisam_sort_buffer_size,
1594+ @@myisam_repair_threads,
1595+ @@sort_buffer_size,
1596+ @@binlog_format,
1597+ @@keep_files_on_create,
1598+ @@max_join_size;
1599+--echo ''
1600+--echo ''
1601+SET STATEMENT myisam_sort_buffer_size=400000,
1602+ myisam_repair_threads=2,
1603+ sort_buffer_size=200000,
1604+ binlog_format=row,
1605+ keep_files_on_create=OFF,
1606+ max_join_size=4444440000000 FOR
1607+ CALL p2();
1608+--echo ''
1609+--echo '# Post-STATEMENT
1610+SELECT @@myisam_sort_buffer_size,
1611+ @@myisam_repair_threads,
1612+ @@sort_buffer_size,
1613+ @@binlog_format,
1614+ @@keep_files_on_create,
1615+ @@max_join_size;
1616+--echo ''
1617+--echo ''
1618+--echo '#------------------Test 14-----------------------#'
1619+####################################################################
1620+# Check PROCEDURE containing compound SET STATEMENT FOR #
1621+# p2() will be used as compounding statement from test 13 #
1622+####################################################################
1623+--echo '# set initial variable values
1624+SET SESSION myisam_sort_buffer_size=500000,
1625+ myisam_repair_threads=1,
1626+ sort_buffer_size=100000,
1627+ binlog_format=mixed,
1628+ keep_files_on_create=ON,
1629+ max_join_size=2222220000000;
1630+--echo ''
1631+--echo ''
1632+DELIMITER |;
1633+CREATE PROCEDURE p3() BEGIN
1634+ SELECT @@myisam_sort_buffer_size,
1635+ @@myisam_repair_threads,
1636+ @@sort_buffer_size,
1637+ @@binlog_format,
1638+ @@keep_files_on_create,
1639+ @@max_join_size;
1640+ SET STATEMENT myisam_sort_buffer_size=320000,
1641+ myisam_repair_threads=2,
1642+ sort_buffer_size=220022,
1643+ binlog_format=row,
1644+ keep_files_on_create=ON,
1645+ max_join_size=2222220000000 FOR
1646+ CALL p2();
1647+ END|
1648+DELIMITER ;|
1649+--echo ''
1650+--echo '# Pre-STATEMENT variable value
1651+SELECT @@myisam_sort_buffer_size,
1652+ @@myisam_repair_threads,
1653+ @@sort_buffer_size,
1654+ @@binlog_format,
1655+ @@keep_files_on_create,
1656+ @@max_join_size;
1657+--echo ''
1658+--echo ''
1659+SET STATEMENT myisam_sort_buffer_size=400000,
1660+ myisam_repair_threads=2,
1661+ sort_buffer_size=200000,
1662+ binlog_format=row,
1663+ keep_files_on_create=OFF,
1664+ max_join_size=4444440000000 FOR
1665+ CALL p3();
1666+--echo ''
1667+--echo '# Post-STATEMENT
1668+SELECT @@myisam_sort_buffer_size,
1669+ @@myisam_repair_threads,
1670+ @@sort_buffer_size,
1671+ @@binlog_format,
1672+ @@keep_files_on_create,
1673+ @@max_join_size;
1674+--echo ''
1675+--echo ''
1676+
1677+ --echo ''
1678+--echo ''
1679+--echo '#------------------Test 15-----------------------#'
1680+####################################################################
1681+# Check PROCEDURE containing compound SET STATEMENT FOR #
1682+# call multiple SET STATEMENT .. FOR showing SELECT #
1683+####################################################################
1684+--echo '# set initial variable values
1685+SET SESSION myisam_sort_buffer_size=500000,
1686+ myisam_repair_threads=1,
1687+ sort_buffer_size=100000,
1688+ binlog_format=mixed,
1689+ keep_files_on_create=ON,
1690+ max_join_size=2222220000000;
1691+--echo ''
1692+--echo ''
1693+DELIMITER |;
1694+CREATE PROCEDURE p4() BEGIN
1695+ SELECT @@myisam_sort_buffer_size,
1696+ @@myisam_repair_threads,
1697+ @@sort_buffer_size,
1698+ @@binlog_format,
1699+ @@keep_files_on_create,
1700+ @@max_join_size;
1701+ SET STATEMENT myisam_sort_buffer_size=320000,
1702+ myisam_repair_threads=2,
1703+ sort_buffer_size=220022,
1704+ binlog_format=row,
1705+ keep_files_on_create=ON,
1706+ max_join_size=2222220000000 FOR
1707+ SELECT @@myisam_sort_buffer_size,
1708+ @@myisam_repair_threads,
1709+ @@sort_buffer_size,
1710+ @@binlog_format,
1711+ @@keep_files_on_create,
1712+ @@max_join_size;
1713+ SET STATEMENT myisam_sort_buffer_size=320000,
1714+ myisam_repair_threads=2,
1715+ sort_buffer_size=220022,
1716+ binlog_format=row,
1717+ keep_files_on_create=ON,
1718+ max_join_size=2222220000000 FOR
1719+ SELECT @@myisam_sort_buffer_size,
1720+ @@myisam_repair_threads,
1721+ @@sort_buffer_size,
1722+ @@binlog_format,
1723+ @@keep_files_on_create,
1724+ @@max_join_size;
1725+ SET STATEMENT myisam_sort_buffer_size=320000,
1726+ myisam_repair_threads=2,
1727+ sort_buffer_size=220022,
1728+ binlog_format=row,
1729+ keep_files_on_create=ON,
1730+ max_join_size=2222220000000 FOR
1731+ SELECT @@myisam_sort_buffer_size,
1732+ @@myisam_repair_threads,
1733+ @@sort_buffer_size,
1734+ @@binlog_format,
1735+ @@keep_files_on_create,
1736+ @@max_join_size;
1737+ END|
1738+DELIMITER ;|
1739+--echo ''
1740+--echo '# Pre-STATEMENT variable value
1741+SELECT @@myisam_sort_buffer_size,
1742+ @@myisam_repair_threads,
1743+ @@sort_buffer_size,
1744+ @@binlog_format,
1745+ @@keep_files_on_create,
1746+ @@max_join_size;
1747+--echo ''
1748+--echo ''
1749+SET STATEMENT myisam_sort_buffer_size=400000,
1750+ myisam_repair_threads=2,
1751+ sort_buffer_size=200000,
1752+ binlog_format=row,
1753+ keep_files_on_create=OFF,
1754+ max_join_size=4444440000000 FOR
1755+ CALL p4();
1756+--echo ''
1757+--echo '# Post-STATEMENT
1758+SELECT @@myisam_sort_buffer_size,
1759+ @@myisam_repair_threads,
1760+ @@sort_buffer_size,
1761+ @@binlog_format,
1762+ @@keep_files_on_create,
1763+ @@max_join_size;
1764+
1765+--echo ''
1766+--echo ''
1767+--echo '#------------------Test 16-----------------------#'
1768+####################################################################
1769+# Test Effect on parsing #
1770+####################################################################
1771+--echo ''
1772+--echo '# Pre-STATEMENT variable value
1773+SELECT @@sql_mode;
1774+--echo ''
1775+--echo ''
1776+SET STATEMENT sql_mode='ansi' FOR SELECT * FROM t1;
1777+
1778+--echo ''
1779+--echo '# Post-STATEMENT
1780+SELECT @@sql_mode;
1781+--echo ''
1782+--echo ''
1783+--echo '#------------------Test 17-----------------------#'
1784+####################################################################
1785+# Test effect of SET STATEMENT FOR with SET SESSION modifying #
1786+# the same variables as the SET STATEMENT #
1787+####################################################################
1788+--echo '# set initial variable values
1789+SET SESSION myisam_sort_buffer_size=500000,
1790+ myisam_repair_threads=1,
1791+ sort_buffer_size=100000,
1792+ binlog_format=mixed,
1793+ keep_files_on_create=ON,
1794+ max_join_size=2222220000000;
1795+--echo ''
1796+--echo '# Pre-STATEMENT variable value
1797+SELECT @@myisam_sort_buffer_size,
1798+ @@myisam_repair_threads,
1799+ @@sort_buffer_size,
1800+ @@binlog_format,
1801+ @@keep_files_on_create,
1802+ @@max_join_size;
1803+--echo ''
1804+--echo ''
1805+SET STATEMENT myisam_sort_buffer_size=320000,
1806+ myisam_repair_threads=2,
1807+ sort_buffer_size=220022,
1808+ binlog_format=row,
1809+ keep_files_on_create=ON,
1810+ max_join_size=2222220000000
1811+ FOR SET SESSION
1812+ myisam_sort_buffer_size=260000,
1813+ myisam_repair_threads=3,
1814+ sort_buffer_size=230013,
1815+ binlog_format=row,
1816+ keep_files_on_create=ON,
1817+ max_join_size=2323230000000;
1818+
1819+--echo ''
1820+--echo '# Post-STATEMENT
1821+SELECT @@myisam_sort_buffer_size,
1822+ @@myisam_repair_threads,
1823+ @@sort_buffer_size,
1824+ @@binlog_format,
1825+ @@keep_files_on_create,
1826+ @@max_join_size;
1827+
1828+--echo ''
1829+--echo ''
1830+--echo '#------------------Test 18-----------------------#'
1831+####################################################################
1832+# Test effect of SET SESSION inside a stored procedure with #
1833+# with a SET STATEMENT on outside variables #
1834+####################################################################
1835+--echo '# set initial variable values
1836+SET SESSION myisam_sort_buffer_size=500000,
1837+ myisam_repair_threads=1,
1838+ sort_buffer_size=100000,
1839+ binlog_format=mixed,
1840+ keep_files_on_create=ON,
1841+ max_join_size=2222220000000;
1842+--echo ''
1843+--echo '# Pre-STATEMENT variable value
1844+SELECT @@myisam_sort_buffer_size,
1845+ @@myisam_repair_threads,
1846+ @@sort_buffer_size,
1847+ @@binlog_format,
1848+ @@keep_files_on_create,
1849+ @@max_join_size;
1850+--echo ''
1851+--echo ''
1852+DELIMITER |;
1853+CREATE PROCEDURE p5() BEGIN
1854+ SELECT @@myisam_sort_buffer_size,
1855+ @@myisam_repair_threads,
1856+ @@sort_buffer_size,
1857+ @@binlog_format,
1858+ @@keep_files_on_create,
1859+ @@max_join_size;
1860+ SET SESSION
1861+ myisam_sort_buffer_size=260000,
1862+ myisam_repair_threads=3,
1863+ sort_buffer_size=230013,
1864+ binlog_format=row,
1865+ keep_files_on_create=ON,
1866+ max_join_size=2323230000000;
1867+ SELECT @@myisam_sort_buffer_size,
1868+ @@myisam_repair_threads,
1869+ @@sort_buffer_size,
1870+ @@binlog_format,
1871+ @@keep_files_on_create,
1872+ @@max_join_size;
1873+ END|
1874+DELIMITER ;|
1875+--echo ''
1876+--echo ''
1877+SET STATEMENT myisam_sort_buffer_size=400000,
1878+ myisam_repair_threads=2,
1879+ sort_buffer_size=200000,
1880+ binlog_format=row,
1881+ keep_files_on_create=OFF,
1882+ max_join_size=4444440000000 FOR
1883+ CALL p5();
1884+
1885+--echo ''
1886+--echo '# Post-STATEMENT
1887+SELECT @@myisam_sort_buffer_size,
1888+ @@myisam_repair_threads,
1889+ @@sort_buffer_size,
1890+ @@binlog_format,
1891+ @@keep_files_on_create,
1892+ @@max_join_size;
1893+
1894+--echo ''
1895+--echo ''
1896+--echo '#------------------Test 19-----------------------#'
1897+#Test for bad syntax
1898+--error ER_PARSE_ERROR
1899+SET STATEMENT max_error_count=100 FOR;
1900+--error ER_PARSE_ERROR
1901+SET STATEMENT max_error_count=100 INSERT t1 VALUES (1,2);
1902+--error ER_PARSE_ERROR
1903+SET STATEMENT FOR INSERT INTO t1 VALUES (1,2);
1904+--error ER_PARSE_ERROR
1905+SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
1906+--error ER_PARSE_ERROR
1907+SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
1908+--error ER_UNKNOWN_SYSTEM_VARIABLE
1909+SET STATEMENT GLOBAL max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
1910+--error ER_PARSE_ERROR
1911+SET STATEMENT @@global.max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
1912+
1913+--echo ''
1914+--echo ''
1915+--echo '#------------------Test 20-----------------------#'
1916+#Test for global-only variables
1917+--error ER_GLOBAL_VARIABLE
1918+SET STATEMENT connect_timeout=100 FOR INSERT INTO t1 VALUES (1,2);
1919+
1920+--echo ''
1921+--echo ''
1922+--echo '#------------------Test 21-----------------------#'
1923+#Test for recursion
1924+SELECT @@myisam_sort_buffer_size, @@sort_buffer_size;
1925+SET STATEMENT myisam_sort_buffer_size = 700000, sort_buffer_size = 3000000
1926+ FOR SET STATEMENT myisam_sort_buffer_size=200000
1927+ FOR SELECT @@myisam_sort_buffer_size, @@sort_buffer_size;
1928+SELECT @@myisam_sort_buffer_size, @@sort_buffer_size;
1929+
1930+--echo ''
1931+--echo ''
1932+--echo '#------------------Test 22-----------------------#'
1933+#Test for STATEMENT keyword
1934+CREATE TABLE STATEMENT(a INT);
1935+DROP TABLE STATEMENT;
1936+
1937+--echo ''
1938+--echo '# Cleanup'
1939+DROP TABLE t1;
1940+DROP PROCEDURE p1;
1941+DROP PROCEDURE p2;
1942+DROP PROCEDURE p3;
1943+DROP PROCEDURE p4;
1944+DROP PROCEDURE p5;
1945
1946=== modified file 'sql/lex.h'
1947--- sql/lex.h 2013-10-29 14:08:44 +0000
1948+++ sql/lex.h 2013-11-06 17:03:07 +0000
1949@@ -568,6 +568,7 @@
1950 { "START", SYM(START_SYM)},
1951 { "STARTING", SYM(STARTING)},
1952 { "STARTS", SYM(STARTS_SYM)},
1953+ { "STATEMENT", SYM(STATEMENT_SYM)},
1954 { "STATS_AUTO_RECALC",SYM(STATS_AUTO_RECALC_SYM)},
1955 { "STATS_PERSISTENT", SYM(STATS_PERSISTENT_SYM)},
1956 { "STATS_SAMPLE_PAGES",SYM(STATS_SAMPLE_PAGES_SYM)},
1957
1958=== modified file 'sql/set_var.h'
1959--- sql/set_var.h 2013-10-29 14:08:44 +0000
1960+++ sql/set_var.h 2013-11-06 17:03:07 +0000
1961@@ -113,6 +113,9 @@
1962 */
1963 bool set_default(THD *thd, set_var *var);
1964 bool update(THD *thd, set_var *var);
1965+ void stmt_update(THD *thd) {
1966+ on_update && on_update(this, thd, OPT_SESSION);
1967+ }
1968
1969 longlong val_int(bool *is_null, THD *thd, enum_var_type type, LEX_STRING *base);
1970 String *val_str(String *str, THD *thd, enum_var_type type, LEX_STRING *base);
1971
1972=== modified file 'sql/sql_lex.cc'
1973--- sql/sql_lex.cc 2013-10-16 09:38:42 +0000
1974+++ sql/sql_lex.cc 2013-11-06 17:03:07 +0000
1975@@ -542,6 +542,7 @@
1976 lex->reset_slave_info.all= false;
1977 lex->limit_rows_examined= 0;
1978 lex->limit_rows_examined_cnt= ULONGLONG_MAX;
1979+ lex->set_statement= false;
1980 DBUG_VOID_RETURN;
1981 }
1982
1983
1984=== modified file 'sql/sql_lex.h'
1985--- sql/sql_lex.h 2013-10-29 14:08:44 +0000
1986+++ sql/sql_lex.h 2013-11-06 17:03:07 +0000
1987@@ -2382,6 +2382,8 @@
1988 required a local context, the parser pops the top-most context.
1989 */
1990 List<Name_resolution_context> context_stack;
1991+ /* true if SET STATEMENT ... FOR ... statement is use, false otherwise */
1992+ bool set_statement;
1993
1994 SQL_I_List<ORDER> proc_list;
1995 SQL_I_List<TABLE_LIST> auxiliary_table_list, save_list;
1996
1997=== modified file 'sql/sql_parse.cc'
1998--- sql/sql_parse.cc 2013-11-01 11:00:11 +0000
1999+++ sql/sql_parse.cc 2013-11-06 17:03:07 +0000
2000@@ -2201,6 +2201,8 @@
2001 /* */
2002 Rpl_filter *rpl_filter= thd->rpl_filter;
2003 #endif
2004+ struct system_variables *per_query_variables_backup;
2005+
2006 DBUG_ENTER("mysql_execute_command");
2007 #ifdef WITH_PARTITION_STORAGE_ENGINE
2008 thd->work_part_info= 0;
2009@@ -2388,6 +2390,21 @@
2010 thd->get_binlog_format(&orig_binlog_format,
2011 &orig_current_stmt_binlog_format);
2012
2013+ if (lex->set_statement && !lex->var_list.is_empty()) {
2014+ per_query_variables_backup= copy_system_variables(&thd->variables);
2015+ if ((res= sql_set_variables(thd, &lex->var_list)))
2016+ {
2017+ /*
2018+ We encountered some sort of error, but no message was sent.
2019+ Send something semi-generic here since we don't know which
2020+ assignment in the list caused the error.
2021+ */
2022+ if (!thd->is_error())
2023+ my_error(ER_WRONG_ARGUMENTS, MYF(0), "SET");
2024+ goto error;
2025+ }
2026+ }
2027+
2028 /*
2029 Force statement logging for DDL commands to allow us to update
2030 privilege, system or statistic tables directly without the updates
2031@@ -2552,6 +2569,12 @@
2032 }
2033 case SQLCOM_EXECUTE:
2034 {
2035+ /*
2036+ Deallocate free_list here to avoid assertion failure later in
2037+ Prepared_statement::execute_loop
2038+ */
2039+ free_items(thd->free_list);
2040+ thd->free_list= NULL;
2041 mysql_sql_stmt_execute(thd);
2042 break;
2043 }
2044@@ -5064,6 +5087,29 @@
2045 thd->in_multi_stmt_transaction_mode());
2046
2047
2048+ if (lex->set_statement && !lex->var_list.is_empty()) {
2049+ List_iterator_fast<set_var_base> it(thd->lex->var_list);
2050+ set_var *var;
2051+
2052+ free_system_variables(&thd->variables);
2053+ thd->variables= *per_query_variables_backup;
2054+ my_free(per_query_variables_backup);
2055+ /*
2056+ When variables are restored after "SET STATEMENT ... FOR ..." statement
2057+ execution an update callback must be invoked for the system variables
2058+ to save special logic if it is. set_var_base class does not contain
2059+ refference to variable as it is just an interface class. But only
2060+ system variables are allowed to be used in "SET STATEMENT ... FOR ..."
2061+ statement, so cast from set_var_base* to set_var* can be used here.
2062+ */
2063+ while ((var=(set_var *)it++))
2064+ {
2065+ var->var->stmt_update(thd);
2066+ }
2067+
2068+ thd->lex->set_statement= false;
2069+ }
2070+
2071 if (! thd->in_sub_stmt)
2072 {
2073 if (thd->killed != NOT_KILLED)
2074
2075=== modified file 'sql/sql_plugin.cc'
2076--- sql/sql_plugin.cc 2013-09-21 08:14:42 +0000
2077+++ sql/sql_plugin.cc 2013-11-06 17:03:07 +0000
2078@@ -4017,3 +4017,45 @@
2079 return 0;
2080 }
2081
2082+
2083+/**
2084+ Create deep copy of system_variables instance.
2085+*/
2086+struct system_variables *
2087+copy_system_variables(const struct system_variables *src)
2088+{
2089+ struct system_variables *dst;
2090+
2091+ DBUG_ASSERT(src);
2092+
2093+ dst= (struct system_variables *)
2094+ my_malloc(sizeof(struct system_variables), MYF(MY_WME | MY_FAE));
2095+ *dst = *src;
2096+
2097+ if (dst->dynamic_variables_ptr)
2098+ {
2099+ dst->dynamic_variables_ptr=
2100+ (char *)my_malloc(dst->dynamic_variables_size, MYF(MY_WME | MY_FAE));
2101+ memcpy(dst->dynamic_variables_ptr,
2102+ src->dynamic_variables_ptr,
2103+ src->dynamic_variables_size);
2104+ }
2105+
2106+ mysql_mutex_lock(&LOCK_plugin);
2107+ dst->table_plugin=
2108+ intern_plugin_lock(NULL, src->table_plugin);
2109+ mysql_mutex_unlock(&LOCK_plugin);
2110+
2111+ return dst;
2112+}
2113+
2114+void free_system_variables(struct system_variables *v)
2115+{
2116+ DBUG_ASSERT(v);
2117+
2118+ mysql_mutex_lock(&LOCK_plugin);
2119+ intern_plugin_unlock(NULL, v->table_plugin);
2120+ mysql_mutex_unlock(&LOCK_plugin);
2121+
2122+ my_free(v->dynamic_variables_ptr);
2123+}
2124
2125=== modified file 'sql/sql_plugin.h'
2126--- sql/sql_plugin.h 2013-06-03 07:57:34 +0000
2127+++ sql/sql_plugin.h 2013-11-06 17:03:07 +0000
2128@@ -178,4 +178,12 @@
2129 int type, uint state_mask, void *arg);
2130 extern bool plugin_dl_foreach(THD *thd, const LEX_STRING *dl,
2131 plugin_foreach_func *func, void *arg);
2132+
2133+/**
2134+ Create deep copy of system_variables instance.
2135+*/
2136+extern
2137+struct system_variables *
2138+copy_system_variables(const struct system_variables *src);
2139+extern void free_system_variables(struct system_variables *v);
2140 #endif
2141
2142=== modified file 'sql/sql_yacc.yy'
2143--- sql/sql_yacc.yy 2013-11-02 16:59:16 +0000
2144+++ sql/sql_yacc.yy 2013-11-06 17:03:07 +0000
2145@@ -813,8 +813,10 @@
2146 lex->one_shot_set= 0;
2147 lex->autocommit= 0;
2148 /* get_ptr() is only correct with no lookahead. */
2149- DBUG_ASSERT(no_lookahead);
2150- lex->sphead->m_tmp_query= lip->get_ptr();
2151+ if (no_lookahead)
2152+ lex->sphead->m_tmp_query= lip->get_ptr();
2153+ else
2154+ lex->sphead->m_tmp_query= lip->get_tok_end();
2155 /* Inherit from outer lex. */
2156 lex->option_type= old_lex->option_type;
2157 }
2158@@ -955,10 +957,10 @@
2159 %parse-param { THD *thd }
2160 %lex-param { THD *thd }
2161 /*
2162- Currently there are 186 shift/reduce conflicts.
2163+ Currently there are 187 shift/reduce conflicts.
2164 We should not introduce new conflicts any more.
2165 */
2166-%expect 186
2167+%expect 187
2168
2169 /*
2170 Comments for TOKENS.
2171@@ -1504,6 +1506,7 @@
2172 %token STARTING
2173 %token STARTS_SYM
2174 %token START_SYM /* SQL-2003-R */
2175+%token STATEMENT_SYM
2176 %token STATS_AUTO_RECALC_SYM
2177 %token STATS_PERSISTENT_SYM
2178 %token STATS_SAMPLE_PAGES_SYM
2179@@ -14287,6 +14290,7 @@
2180 | SQL_NO_CACHE_SYM {}
2181 | SQL_THREAD {}
2182 | STARTS_SYM {}
2183+ | STATEMENT_SYM {}
2184 | STATUS_SYM {}
2185 | STORAGE_SYM {}
2186 | STRING_SYM {}
2187@@ -14364,8 +14368,36 @@
2188 }
2189 start_option_value_list
2190 {}
2191+ | SET STATEMENT_SYM
2192+ {
2193+ LEX *lex= Lex;
2194+ mysql_init_select(lex);
2195+ lex->sql_command= SQLCOM_SET_OPTION;
2196+ /* Don't clear var_list in the case of recursive statement */
2197+ if (!lex->set_statement)
2198+ lex->var_list.empty();
2199+ lex->one_shot_set= 0;
2200+ lex->autocommit= 0;
2201+ lex->set_statement= true;
2202+ sp_head *sp= lex->sphead;
2203+ if (sp && !sp->is_invoked())
2204+ {
2205+ sp->m_param_begin= ((yychar == YYEMPTY) ? YYLIP->get_ptr() : YYLIP->get_tok_start());
2206+ sp->m_param_end= ((yychar == YYEMPTY) ? YYLIP->get_ptr() : YYLIP->get_tok_end());
2207+ }
2208+ }
2209+ set_stmt_option_value_following_option_type_list FOR_SYM statement
2210+ {}
2211 ;
2212
2213+set_stmt_option_value_following_option_type_list:
2214+ /*
2215+ Only system variables can be used here. If this condition is changed
2216+ please check careful code under lex->option_type == OPT_STATEMENT
2217+ condition on wrong type casts.
2218+ */
2219+ option_value_following_option_type
2220+ | set_stmt_option_value_following_option_type_list ',' option_value_following_option_type
2221
2222 // Start of option value list
2223 start_option_value_list:
2224@@ -14470,20 +14502,29 @@
2225 {
2226 LEX *lex= Lex;
2227
2228- if ($1.var && $1.var != trg_new_row_fake_var)
2229+ /*
2230+ Ignore SET STATEMENT variables list on slaves because system
2231+ variables are not replicated except certain variables set the
2232+ values of whose are written to binlog event header and nothing
2233+ additional is required to set them.
2234+ */
2235+ if (!thd->slave_thread || !lex->set_statement)
2236 {
2237- /* It is a system variable. */
2238- if (set_system_variable(thd, &$1, lex->option_type, $3))
2239+ if ($1.var && $1.var != trg_new_row_fake_var)
2240+ {
2241+ /* It is a system variable. */
2242+ if (set_system_variable(thd, &$1, lex->option_type, $3))
2243+ MYSQL_YYABORT;
2244+ }
2245+ else
2246+ {
2247+ /*
2248+ Not in trigger assigning value to new row,
2249+ and option_type preceeding local variable is illegal.
2250+ */
2251+ my_parse_error(ER(ER_SYNTAX_ERROR));
2252 MYSQL_YYABORT;
2253- }
2254- else
2255- {
2256- /*
2257- Not in trigger assigning value to new row,
2258- and option_type preceeding local variable is illegal.
2259- */
2260- my_parse_error(ER(ER_SYNTAX_ERROR));
2261- MYSQL_YYABORT;
2262+ }
2263 }
2264 }
2265 ;

Subscribers

People subscribed via source and target branches