Percona Server moved to https://jira.percona.com/projects/PS

Merge lp:~vlad-lesin/percona-server/per_query_variables_setting into lp:percona-server/5.5

  1. per_query_variables_setting
  2. Merge into 5.5
Proposed by Vlad Lesin
Status: Work in progress
Proposed branch: lp:~vlad-lesin/percona-server/per_query_variables_setting
Merge into: lp:percona-server/5.5
Diff against target: 2067 lines (+1910/-5)
10 files modified
Percona-Server/mysql-test/r/percona_statement_set.result (+815/-0)
Percona-Server/mysql-test/t/percona_statement_set.test (+778/-0)
Percona-Server/sql/lex.h (+1/-0)
Percona-Server/sql/set_var.cc (+178/-0)
Percona-Server/sql/set_var.h (+5/-0)
Percona-Server/sql/sql_lex.cc (+1/-0)
Percona-Server/sql/sql_lex.h (+5/-0)
Percona-Server/sql/sql_parse.cc (+84/-0)
Percona-Server/sql/sql_prepare.cc (+6/-2)
Percona-Server/sql/sql_yacc.yy (+37/-3)
To merge this branch: bzr merge lp:~vlad-lesin/percona-server/per_query_variables_setting
Reviewer Review Type Date Requested Status
Laurynas Biveinis (community) Needs Fixing
Vlad Lesin (community) Needs Resubmitting
Alexey Kopytov (community) Needs Fixing
Review via email: mp+103425@code.launchpad.net

Description of the change

Per query variable settings.

Example:

SET PER_STATEMENT
              myisam_sort_buffer_size=40000,
              myisam_repair_threads=2
FOR
              SELECT * FROM t;

The variables "myisam_sort_buffer_size" and "myisam_repair_threads" will have the values 40000 and 2 respectively for the query "SELECT * FROM t". After the query execution the variables will restore their values.

The global-only variables can't be used in this statement.

If variables are changed during the query, their values will be restored to the pre-"set per_statement" state. For example:

SET myisam_sort_buffer_size=40000;
SET PER_STATEMENT myisam_sort_buffer_size=10000 FOR SET myisam_sort_buffer_size=20000;
SELECT @@myisam_sort_buffer_size;
+---------------------------+
| @@myisam_sort_buffer_size |
+---------------------------+
| 40000 |
+---------------------------+
1 row in set (0.02 sec)

Jenkins build:
http://jenkins.percona.com/view/Percona%20Server%205.5/job/percona-server-5.5-param/331/

To post a comment you must log in.
Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :

The lp:percona-server is now a proper branch instead of set of patches, please submit direct code changes instead of a new patch.

Also, please merge all the revisions into a single revision with a descriptive commit comment (see history of lp:mysql-server for examples of comments)

review: Needs Fixing
Reply
Revision history for this message
Alexey Kopytov (akopytov) wrote :

Vlad,

Also why was the syntax changed to "SET PER_STATEMENT", whereas the original patch had "SET STATEMENT"?

SET STATEMENT follows the current logic of possible SET modifiers (i.e. "GLOBAL", "SESSION"), i.e. you specify the scope of the variable being set. But PER_STATEMENT is not in line with that naming.

review: Needs Fixing
Reply
Revision history for this message
Vlad Lesin (vlad-lesin) wrote :

> Vlad,
>
> Also why was the syntax changed to "SET PER_STATEMENT", whereas the original
> patch had "SET STATEMENT"?
>
> SET STATEMENT follows the current logic of possible SET modifiers (i.e.
> "GLOBAL", "SESSION"), i.e. you specify the scope of the variable being set.
> But PER_STATEMENT is not in line with that naming.

That's because of some tests were failed due to intersection the "STATEMENT" keyword with one of the possible values of binlog_format variable (set binlog_format=STATEMENT).

Reply
Revision history for this message
Alexey Kopytov (akopytov) wrote :

Vlad,

> That's because of some tests were failed due to intersection the "STATEMENT" keyword with one of the possible values of binlog_format variable (set binlog_format=STATEMENT).

Can you please give more details? What specific tests and an example of
a failure?

Reply
Revision history for this message
Vlad Lesin (vlad-lesin) wrote :

> Can you please give more details? What specific tests and an example of
> a failure?

Unfortunately I've removed jenkins build with such errors. But I reproduced this error on my workstation. There is an example of failed test:

=====

TEST RESULT TIME (ms) or COMMENT
--------------------------------------------------------------------------

worker[1] Using MTR_BUILD_THREAD 300, with reserved ports 13000..13009
binlog.binlog_database 'mix' [ fail ]
        Test ended at 2012-04-26 12:02:14

CURRENT_TEST: binlog.binlog_database
mysqltest: At line 5: query 'set binlog_format=statement' failed: 1064: 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 'statement' at line 1

 - saving '/home/vlesin/src/percona-server-per_query_variables/Percona-Server/build/mysql-test/var/log/binlog.binlog_database-mix/' to '/home/vlesin/src/percona-server-per_query_variables/Percona-Server/build/mysql-test/var/log/binlog.binlog_database-mix/'

=====

And all tests which contain "set binlog_format=statement" fail.

Reply
Revision history for this message
Vlad Lesin (vlad-lesin) wrote :

> The lp:percona-server is now a proper branch instead of set of patches, please
> submit direct code changes instead of a new patch.
>
> Also, please merge all the revisions into a single revision with a descriptive
> commit comment (see history of lp:mysql-server for examples of comments)

Done.

Reply
Revision history for this message
Vlad Lesin (vlad-lesin) wrote :

> Vlad,
>
> Also why was the syntax changed to "SET PER_STATEMENT", whereas the original
> patch had "SET STATEMENT"?
>
> SET STATEMENT follows the current logic of possible SET modifiers (i.e.
> "GLOBAL", "SESSION"), i.e. you specify the scope of the variable being set.
> But PER_STATEMENT is not in line with that naming.

Now STATEMENT keyword is used.

Reply
Revision history for this message
Vlad Lesin (vlad-lesin) wrote :

http://jenkins.percona.com/view/Percona%20Server%205.5/job/percona-server-5.5-param/335

review: Needs Resubmitting
Reply
Revision history for this message
Stewart Smith (stewart) wrote :

On Mon, 30 Apr 2012 12:15:47 -0000, Vladislav Lesin <email address hidden> wrote:
> Review: Resubmit

Rather than setting the review status to resubmit, you want to hit the
link on the top right that says "resubmit proposal", as this creates a
new merge req, redoes the diff and everything. The review "resubmit"
just is as if it's your review of your own code telling yourself to
resubmit it.

The proper resubmit also sends people email again in a new thread to bug
them about reviewing ti.

--
Stewart Smith

Reply
Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :

Is there a blueprint for this work?

review: Needs Information
Reply
Revision history for this message
Vlad Lesin (vlad-lesin) wrote :

> Is there a blueprint for this work?

Yes. It can be found here https://blueprints.launchpad.net/percona-server/+spec/per-query-variables-statement.

Reply
Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :

This is not a full review unfortunately as I cannot review the code part without spending too much time right now.

   The commit message describes the feature but not the changes made
   to the server.

   Lines 834--837 probably apply to the test case that served as a
   starting point for the current one. I'd replace them with
   "testcase adopted from foo.test".

   The testcase is missing the --disable_warnings-DROP TABLE IF
   EXISTS-enable_warnings at the start.

   Some parts of the test manipulates various MyISAM variables such as
   myisam_sort_buffer_size, myisam_repair_threads. But table t1 is an
   InnoDB table on 5.5, thus while of course it's possible to adjust
   the MyISAM variable values, a better test would be to adjust
   something relevant.

   I'd prefer to see more feature tests:
   1) Set some session variable to some value that does not allow some
      statement to complete without an error or warning. Issue that
      statement, record the warning. Then set the variable for the
      statement to a value that allows the statement to complete, for
      example join_buffer_size.

   2) autocommit is an interesting variable. What happens if you have
      autocommit=0, start a transaction with a statement set with
      autocommit=1 there? Note that for this MP, if any dark corner
      cases are found, there is no need to work out any additional
      semantics beyond what would a regular session autocommit setting
      do in the same situation.

   3) Set auto_increment_* for statement and check the resulting id.

   4) storage_engine for CREATE TABLE statement, check the resulting
      table type.

   5) query_cache_type, that it does not crash.

   If you disagree that so much testing is required, then I don't have
   that strong feelings about 1), 3) and 4). But I do want to see 2)
   and 5) very much.

   The header comment for set_stmt_get_reset_vars should be not in
   "will do smth" style, but "do smth", thus "retrieves the current
   value of ... ".

   s/will be change to/will be changed to

   Comma after "occurs" in @return.

   char str[var->var->name.length]; is a VLA and thus not a valid C++
   unfortunately.

   Line 1658: s/ans/and

   Is it possible to avoid the new yacc shift/reduce conflict?

review: Needs Fixing
Reply

Unmerged revisions

237. By Vlad Lesin

Per query variable settings allow to assign some values to variables only for
the certain query after execution of which the variables restore their previous
values.
Example:
SET STATEMENT myisam_sort_buffer_size=40000, myisam_repair_threads=2
    FOR SELECT * FROM t;

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== added file 'Percona-Server/mysql-test/r/percona_statement_set.result'
2--- Percona-Server/mysql-test/r/percona_statement_set.result 1970-01-01 00:00:00 +0000
3+++ Percona-Server/mysql-test/r/percona_statement_set.result 2012-04-26 09:29:20 +0000
4@@ -0,0 +1,815 @@
5+'# SET STATEMENT ..... FOR .... TEST'
6+'# Setup database'
7+CREATE TABLE t1 (v1 INT, v2 INT);
8+INSERT INTO t1 VALUES (1,2);
9+INSERT INTO t1 VALUES (3,4);
10+''
11+'#------------------ STATEMENT Test 1 -----------------------#'
12+'# Initialize variables to known setting'
13+SET SESSION sort_buffer_size=100000;
14+''
15+'# Pre-STATEMENT variable value'
16+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
17+Variable_name Value
18+sort_buffer_size 100000
19+SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t1;
20+v1 v2
21+1 2
22+3 4
23+''
24+'# Post-STATEMENT variable value'
25+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
26+Variable_name Value
27+sort_buffer_size 100000
28+''
29+'#------------------ STATEMENT Test 2 -----------------------#'
30+'# Initialize variables to known setting'
31+SET SESSION binlog_format=mixed;
32+SET SESSION sort_buffer_size=100000;
33+'# Pre-STATEMENT variable value'
34+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
35+Variable_name Value
36+sort_buffer_size 100000
37+SHOW SESSION VARIABLES LIKE 'binlog_format';
38+Variable_name Value
39+binlog_format MIXED
40+SET STATEMENT sort_buffer_size=150000, binlog_format=row
41+FOR SELECT * FROM t1;
42+v1 v2
43+1 2
44+3 4
45+'# Post-STATEMENT variable value'
46+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
47+Variable_name Value
48+sort_buffer_size 100000
49+SHOW SESSION VARIABLES LIKE 'binlog_format';
50+Variable_name Value
51+binlog_format MIXED
52+''
53+'#------------------ STATEMENT Test 3 -----------------------#'
54+'# set initial variable value, make prepared statement
55+SET SESSION binlog_format=row;
56+PREPARE stmt1 FROM 'SET STATEMENT binlog_format=row FOR SELECT * FROM t1';
57+''
58+'# Change variable setting'
59+SET SESSION binlog_format=mixed;
60+''
61+'# Pre-STATEMENT variable value'
62+''
63+SHOW SESSION VARIABLES LIKE 'binlog_format';
64+Variable_name Value
65+binlog_format MIXED
66+''
67+EXECUTE stmt1;
68+v1 v2
69+1 2
70+3 4
71+''
72+'# Post-STATEMENT variable value'
73+SHOW SESSION VARIABLES LIKE 'binlog_format';
74+Variable_name Value
75+binlog_format MIXED
76+''
77+DEALLOCATE PREPARE stmt1;
78+'#------------------ STATEMENT Test 4 -----------------------#'
79+'# set initial variable value, make prepared statement
80+SET SESSION myisam_sort_buffer_size=500000, myisam_repair_threads=1;
81+''
82+'# Pre-STATEMENT variable value'
83+SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
84+Variable_name Value
85+myisam_sort_buffer_size 500000
86+SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
87+Variable_name Value
88+myisam_repair_threads 1
89+''
90+SET STATEMENT myisam_sort_buffer_size=800000,
91+myisam_repair_threads=2 FOR OPTIMIZE TABLE t1;
92+Table Op Msg_type Msg_text
93+test.t1 optimize status OK
94+''
95+'# Post-STATEMENT variable value'
96+SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
97+Variable_name Value
98+myisam_sort_buffer_size 500000
99+SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
100+Variable_name Value
101+myisam_repair_threads 1
102+''
103+'#------------------ STATEMENT Test 5 -----------------------#'
104+'# Initialize variables to known setting'
105+SET SESSION sort_buffer_size=100000;
106+''
107+'# Pre-STATEMENT variable value'
108+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
109+Variable_name Value
110+sort_buffer_size 100000
111+''
112+SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t2;
113+ERROR 42S02: Table 'test.t2' doesn't exist
114+''
115+'# Post-STATEMENT variable value'
116+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
117+Variable_name Value
118+sort_buffer_size 100000
119+''
120+'#------------------ STATEMENT Test 6 -----------------------#'
121+'# Initialize variables to known setting'
122+SET SESSION keep_files_on_create=ON;
123+''
124+'# Pre-STATEMENT variable value'
125+SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
126+Variable_name Value
127+keep_files_on_create ON
128+''
129+SET STATEMENT keep_files_on_create=OFF FOR SELECT * FROM t1;
130+v1 v2
131+1 2
132+3 4
133+''
134+'# Post-STATEMENT variable value'
135+SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
136+Variable_name Value
137+keep_files_on_create ON
138+''
139+'#------------------ STATEMENT Test 7 -----------------------#'
140+'# Initialize variables to known setting'
141+SET SESSION max_join_size=2222220000000;
142+''
143+'# Pre-STATEMENT variable value'
144+SHOW SESSION VARIABLES LIKE 'max_join_size';
145+Variable_name Value
146+max_join_size 2222220000000
147+''
148+SET STATEMENT max_join_size=1000000000000 FOR SELECT * FROM t1;
149+v1 v2
150+1 2
151+3 4
152+''
153+'# Post-STATEMENT variable value'
154+SHOW SESSION VARIABLES LIKE 'max_join_size';
155+Variable_name Value
156+max_join_size 2222220000000
157+''
158+'#------------------Test 8-----------------------#'
159+'# Initialize test variables'
160+SET SESSION myisam_sort_buffer_size=500000,
161+myisam_repair_threads=1,
162+sort_buffer_size = 200000,
163+max_join_size=2222220000000,
164+keep_files_on_create=ON;
165+''
166+'# LONG '
167+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
168+Variable_name Value
169+sort_buffer_size 200000
170+SET STATEMENT sort_buffer_size = 100000
171+FOR SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
172+Variable_name Value
173+sort_buffer_size 100000
174+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
175+Variable_name Value
176+sort_buffer_size 200000
177+''
178+'# MY_BOOL '
179+SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
180+Variable_name Value
181+keep_files_on_create ON
182+SET STATEMENT keep_files_on_create=OFF
183+FOR SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
184+Variable_name Value
185+keep_files_on_create OFF
186+SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
187+Variable_name Value
188+keep_files_on_create ON
189+''
190+'# INT/LONG '
191+SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
192+Variable_name Value
193+myisam_repair_threads 1
194+SET STATEMENT myisam_repair_threads=2
195+FOR SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
196+Variable_name Value
197+myisam_repair_threads 2
198+SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
199+Variable_name Value
200+myisam_repair_threads 1
201+''
202+'# ULONGLONG '
203+SHOW SESSION VARIABLES LIKE 'max_join_size';
204+Variable_name Value
205+max_join_size 2222220000000
206+SET STATEMENT max_join_size=2000000000000
207+FOR SHOW SESSION VARIABLES LIKE 'max_join_size';
208+Variable_name Value
209+max_join_size 2000000000000
210+SHOW SESSION VARIABLES LIKE 'max_join_size';
211+Variable_name Value
212+max_join_size 2222220000000
213+''
214+'#------------------Test 9-----------------------#'
215+'# set initial variable values
216+SET SESSION myisam_sort_buffer_size=500000,
217+myisam_repair_threads=1,
218+sort_buffer_size=100000,
219+binlog_format=mixed,
220+keep_files_on_create=ON,
221+max_join_size=2222220000000;
222+''
223+''
224+'# Pre-STATEMENT variable value
225+SELECT @@myisam_sort_buffer_size,
226+@@myisam_repair_threads,
227+@@sort_buffer_size,
228+@@binlog_format,
229+@@keep_files_on_create,
230+@@max_join_size;
231+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
232+500000 1 100000 MIXED 1 2222220000000
233+''
234+''
235+CREATE FUNCTION myProc (cost DECIMAL(10,2))
236+RETURNS DECIMAL(10,2)
237+SQL SECURITY DEFINER
238+tax: BEGIN
239+DECLARE order_tax DECIMAL(10,2);
240+SET order_tax = cost * .05;
241+RETURN order_tax;
242+END|
243+''
244+'# During Execution values
245+SET STATEMENT myisam_sort_buffer_size=400000,
246+myisam_repair_threads=2,
247+sort_buffer_size=200000,
248+binlog_format=row,
249+keep_files_on_create=OFF,
250+max_join_size=4444440000000 FOR
251+SELECT myProc(123.45);
252+myProc(123.45)
253+6.17
254+Warnings:
255+Note 1265 Data truncated for column 'order_tax' at row 1
256+''
257+'# Post-STATEMENT No 1 variable value Pre-STATEMENT for No 2'
258+SELECT @@myisam_sort_buffer_size,
259+@@myisam_repair_threads,
260+@@sort_buffer_size,
261+@@binlog_format,
262+@@keep_files_on_create,
263+@@max_join_size;
264+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
265+500000 1 100000 MIXED 1 2222220000000
266+''
267+SET STATEMENT myisam_sort_buffer_size=400000,
268+myisam_repair_threads=2,
269+sort_buffer_size=200000,
270+binlog_format=row,
271+keep_files_on_create=OFF,
272+max_join_size=4444440000000 FOR
273+DROP FUNCTION myProc;
274+''
275+'# Post-STATEMENT No 2 variable value
276+SELECT @@myisam_sort_buffer_size,
277+@@myisam_repair_threads,
278+@@sort_buffer_size,
279+@@binlog_format,
280+@@keep_files_on_create,
281+@@max_join_size;
282+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
283+500000 1 100000 MIXED 1 2222220000000
284+''
285+'#------------------Test 10-----------------------#'
286+'# set initial variable values
287+SET SESSION myisam_sort_buffer_size=500000,
288+myisam_repair_threads=1,
289+sort_buffer_size=100000,
290+binlog_format=mixed,
291+keep_files_on_create=ON,
292+max_join_size=2222220000000;
293+''
294+'# Pre-STATEMENT variable value
295+SELECT @@myisam_sort_buffer_size,
296+@@myisam_repair_threads,
297+@@sort_buffer_size,
298+@@binlog_format,
299+@@keep_files_on_create,
300+@@max_join_size;
301+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
302+500000 1 100000 MIXED 1 2222220000000
303+''
304+''
305+SET STATEMENT myisam_sort_buffer_size=400000,
306+myisam_repair_threads=2,
307+sort_buffer_size=200000,
308+binlog_format=row,
309+keep_files_on_create=OFF,
310+max_join_size=4444440000000 FOR
311+PREPARE stmt2
312+FROM 'SELECT * FROM t1';
313+''
314+'Test No 1 Post Value & Test 2 Pre values'
315+SELECT @@myisam_sort_buffer_size,
316+@@myisam_repair_threads,
317+@@sort_buffer_size,
318+@@binlog_format,
319+@@keep_files_on_create,
320+@@max_join_size;
321+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
322+500000 1 100000 MIXED 1 2222220000000
323+''
324+''
325+SET STATEMENT myisam_sort_buffer_size=400000,
326+myisam_repair_threads=2,
327+sort_buffer_size=200000,
328+binlog_format=row,
329+keep_files_on_create=OFF,
330+max_join_size=4444440000000 FOR
331+EXECUTE stmt2;
332+v1 v2
333+1 2
334+3 4
335+''
336+'# Post-STATEMENT No 2
337+SELECT @@myisam_sort_buffer_size,
338+@@myisam_repair_threads,
339+@@sort_buffer_size,
340+@@binlog_format,
341+@@keep_files_on_create,
342+@@max_join_size;
343+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
344+500000 1 100000 MIXED 1 2222220000000
345+''
346+DEALLOCATE PREPARE stmt2;
347+''
348+'#------------------Test 11-----------------------#'
349+'# set initial variable values
350+SET SESSION myisam_sort_buffer_size=500000,
351+myisam_repair_threads=1,
352+sort_buffer_size=100000,
353+binlog_format=mixed,
354+keep_files_on_create=ON,
355+max_join_size=2222220000000;
356+''
357+''
358+'# Pre-STATEMENT variable value
359+SELECT @@myisam_sort_buffer_size,
360+@@myisam_repair_threads,
361+@@sort_buffer_size,
362+@@binlog_format,
363+@@keep_files_on_create,
364+@@max_join_size;
365+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
366+500000 1 100000 MIXED 1 2222220000000
367+''
368+''
369+SET STATEMENT myisam_sort_buffer_size=400000,
370+myisam_repair_threads=2,
371+sort_buffer_size=200000,
372+keep_files_on_create=OFF,
373+max_join_size=4444440000000 FOR
374+PREPARE stmt1 FROM
375+'SET STATEMENT binlog_format=row FOR SELECT * FROM t1';
376+''
377+'Test No 1 Post Value & Test 2 Pre values'
378+SELECT @@myisam_sort_buffer_size,
379+@@myisam_repair_threads,
380+@@sort_buffer_size,
381+@@binlog_format,
382+@@keep_files_on_create,
383+@@max_join_size;
384+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
385+500000 1 100000 MIXED 1 2222220000000
386+''
387+''
388+SET STATEMENT myisam_sort_buffer_size=400000,
389+myisam_repair_threads=2,
390+sort_buffer_size=200000,
391+keep_files_on_create=OFF,
392+max_join_size=4444440000000 FOR
393+EXECUTE stmt1;
394+v1 v2
395+1 2
396+3 4
397+''
398+'# Post-STATEMENT No 2
399+SELECT @@myisam_sort_buffer_size,
400+@@myisam_repair_threads,
401+@@sort_buffer_size,
402+@@binlog_format,
403+@@keep_files_on_create,
404+@@max_join_size;
405+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
406+500000 1 100000 MIXED 1 2222220000000
407+''
408+''
409+'#------------------Test 12-----------------------#'
410+'# set initial variable values
411+SET SESSION myisam_sort_buffer_size=500000,
412+myisam_repair_threads=1,
413+sort_buffer_size=100000,
414+binlog_format=mixed,
415+keep_files_on_create=ON,
416+max_join_size=2222220000000;
417+''
418+''
419+'# Pre-STATEMENT variable value
420+SELECT @@myisam_sort_buffer_size,
421+@@myisam_repair_threads,
422+@@sort_buffer_size,
423+@@binlog_format,
424+@@keep_files_on_create,
425+@@max_join_size;
426+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
427+500000 1 100000 MIXED 1 2222220000000
428+''
429+''
430+SET STATEMENT myisam_sort_buffer_size=400000,
431+myisam_repair_threads=2,
432+sort_buffer_size=200000,
433+binlog_format=row,
434+keep_files_on_create=OFF,
435+max_join_size=4444440000000 FOR
436+CREATE PROCEDURE p1() BEGIN
437+SELECT @@myisam_sort_buffer_size,
438+@@myisam_repair_threads,
439+@@sort_buffer_size,
440+@@binlog_format,
441+@@keep_files_on_create,
442+@@max_join_size;
443+END|
444+''
445+'Test No 1 Post Value & Test 2 Pre values'
446+SELECT @@myisam_sort_buffer_size,
447+@@myisam_repair_threads,
448+@@sort_buffer_size,
449+@@binlog_format,
450+@@keep_files_on_create,
451+@@max_join_size;
452+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
453+500000 1 100000 MIXED 1 2222220000000
454+''
455+''
456+SET STATEMENT myisam_sort_buffer_size=400000,
457+myisam_repair_threads=2,
458+sort_buffer_size=200000,
459+binlog_format=row,
460+keep_files_on_create=OFF,
461+max_join_size=4444440000000 FOR
462+CALL p1();
463+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
464+400000 2 200000 ROW 0 4444440000000
465+''
466+'# Post-STATEMENT No 2
467+SELECT @@myisam_sort_buffer_size,
468+@@myisam_repair_threads,
469+@@sort_buffer_size,
470+@@binlog_format,
471+@@keep_files_on_create,
472+@@max_join_size;
473+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
474+500000 1 100000 MIXED 1 2222220000000
475+''
476+''
477+'#------------------Test 13-----------------------#'
478+'# set initial variable values
479+SET SESSION myisam_sort_buffer_size=500000,
480+myisam_repair_threads=1,
481+sort_buffer_size=100000,
482+binlog_format=mixed,
483+keep_files_on_create=ON,
484+max_join_size=2222220000000;
485+''
486+''
487+CREATE PROCEDURE p2() BEGIN
488+SET STATEMENT myisam_sort_buffer_size=400000,
489+myisam_repair_threads=3,
490+sort_buffer_size=300000,
491+binlog_format=mixed,
492+keep_files_on_create=OFF,
493+max_join_size=3333330000000 FOR
494+CALL p1();
495+END|
496+''
497+'# Pre-STATEMENT variable value
498+SELECT @@myisam_sort_buffer_size,
499+@@myisam_repair_threads,
500+@@sort_buffer_size,
501+@@binlog_format,
502+@@keep_files_on_create,
503+@@max_join_size;
504+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
505+500000 1 100000 MIXED 1 2222220000000
506+''
507+''
508+SET STATEMENT myisam_sort_buffer_size=400000,
509+myisam_repair_threads=2,
510+sort_buffer_size=200000,
511+binlog_format=row,
512+keep_files_on_create=OFF,
513+max_join_size=4444440000000 FOR
514+CALL p2();
515+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
516+400000 3 300000 MIXED 0 3333330000000
517+''
518+'# Post-STATEMENT
519+SELECT @@myisam_sort_buffer_size,
520+@@myisam_repair_threads,
521+@@sort_buffer_size,
522+@@binlog_format,
523+@@keep_files_on_create,
524+@@max_join_size;
525+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
526+500000 1 100000 MIXED 1 2222220000000
527+''
528+''
529+'#------------------Test 14-----------------------#'
530+'# set initial variable values
531+SET SESSION myisam_sort_buffer_size=500000,
532+myisam_repair_threads=1,
533+sort_buffer_size=100000,
534+binlog_format=mixed,
535+keep_files_on_create=ON,
536+max_join_size=2222220000000;
537+''
538+''
539+CREATE PROCEDURE p3() BEGIN
540+SELECT @@myisam_sort_buffer_size,
541+@@myisam_repair_threads,
542+@@sort_buffer_size,
543+@@binlog_format,
544+@@keep_files_on_create,
545+@@max_join_size;
546+SET STATEMENT myisam_sort_buffer_size=320000,
547+myisam_repair_threads=2,
548+sort_buffer_size=220022,
549+binlog_format=row,
550+keep_files_on_create=ON,
551+max_join_size=2222220000000 FOR
552+CALL p2();
553+END|
554+''
555+'# Pre-STATEMENT variable value
556+SELECT @@myisam_sort_buffer_size,
557+@@myisam_repair_threads,
558+@@sort_buffer_size,
559+@@binlog_format,
560+@@keep_files_on_create,
561+@@max_join_size;
562+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
563+500000 1 100000 MIXED 1 2222220000000
564+''
565+''
566+SET STATEMENT myisam_sort_buffer_size=400000,
567+myisam_repair_threads=2,
568+sort_buffer_size=200000,
569+binlog_format=row,
570+keep_files_on_create=OFF,
571+max_join_size=4444440000000 FOR
572+CALL p3();
573+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
574+400000 2 200000 ROW 0 4444440000000
575+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
576+400000 3 300000 MIXED 0 3333330000000
577+''
578+'# Post-STATEMENT
579+SELECT @@myisam_sort_buffer_size,
580+@@myisam_repair_threads,
581+@@sort_buffer_size,
582+@@binlog_format,
583+@@keep_files_on_create,
584+@@max_join_size;
585+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
586+500000 1 100000 MIXED 1 2222220000000
587+''
588+''
589+''
590+''
591+'#------------------Test 15-----------------------#'
592+'# set initial variable values
593+SET SESSION myisam_sort_buffer_size=500000,
594+myisam_repair_threads=1,
595+sort_buffer_size=100000,
596+binlog_format=mixed,
597+keep_files_on_create=ON,
598+max_join_size=2222220000000;
599+''
600+''
601+CREATE PROCEDURE p4() BEGIN
602+SELECT @@myisam_sort_buffer_size,
603+@@myisam_repair_threads,
604+@@sort_buffer_size,
605+@@binlog_format,
606+@@keep_files_on_create,
607+@@max_join_size;
608+SET STATEMENT myisam_sort_buffer_size=320000,
609+myisam_repair_threads=2,
610+sort_buffer_size=220022,
611+binlog_format=row,
612+keep_files_on_create=ON,
613+max_join_size=2222220000000 FOR
614+SELECT @@myisam_sort_buffer_size,
615+@@myisam_repair_threads,
616+@@sort_buffer_size,
617+@@binlog_format,
618+@@keep_files_on_create,
619+@@max_join_size;
620+SET STATEMENT myisam_sort_buffer_size=320000,
621+myisam_repair_threads=2,
622+sort_buffer_size=220022,
623+binlog_format=row,
624+keep_files_on_create=ON,
625+max_join_size=2222220000000 FOR
626+SELECT @@myisam_sort_buffer_size,
627+@@myisam_repair_threads,
628+@@sort_buffer_size,
629+@@binlog_format,
630+@@keep_files_on_create,
631+@@max_join_size;
632+SET STATEMENT myisam_sort_buffer_size=320000,
633+myisam_repair_threads=2,
634+sort_buffer_size=220022,
635+binlog_format=row,
636+keep_files_on_create=ON,
637+max_join_size=2222220000000 FOR
638+SELECT @@myisam_sort_buffer_size,
639+@@myisam_repair_threads,
640+@@sort_buffer_size,
641+@@binlog_format,
642+@@keep_files_on_create,
643+@@max_join_size;
644+END|
645+''
646+'# Pre-STATEMENT variable value
647+SELECT @@myisam_sort_buffer_size,
648+@@myisam_repair_threads,
649+@@sort_buffer_size,
650+@@binlog_format,
651+@@keep_files_on_create,
652+@@max_join_size;
653+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
654+500000 1 100000 MIXED 1 2222220000000
655+''
656+''
657+SET STATEMENT myisam_sort_buffer_size=400000,
658+myisam_repair_threads=2,
659+sort_buffer_size=200000,
660+binlog_format=row,
661+keep_files_on_create=OFF,
662+max_join_size=4444440000000 FOR
663+CALL p4();
664+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
665+400000 2 200000 ROW 0 4444440000000
666+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
667+320000 2 220022 ROW 1 2222220000000
668+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
669+320000 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
671+320000 2 220022 ROW 1 2222220000000
672+''
673+'# Post-STATEMENT
674+SELECT @@myisam_sort_buffer_size,
675+@@myisam_repair_threads,
676+@@sort_buffer_size,
677+@@binlog_format,
678+@@keep_files_on_create,
679+@@max_join_size;
680+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
681+500000 1 100000 MIXED 1 2222220000000
682+''
683+''
684+'#------------------Test 16-----------------------#'
685+''
686+'# Pre-STATEMENT variable value
687+SELECT @@sql_mode;
688+@@sql_mode
689+
690+''
691+''
692+SET STATEMENT sql_mode='ansi' FOR SELECT * FROM t1;
693+v1 v2
694+1 2
695+3 4
696+''
697+'# Post-STATEMENT
698+SELECT @@sql_mode;
699+@@sql_mode
700+
701+''
702+''
703+'#------------------Test 17-----------------------#'
704+'# set initial variable values
705+SET SESSION myisam_sort_buffer_size=500000,
706+myisam_repair_threads=1,
707+sort_buffer_size=100000,
708+binlog_format=mixed,
709+keep_files_on_create=ON,
710+max_join_size=2222220000000;
711+''
712+'# Pre-STATEMENT variable value
713+SELECT @@myisam_sort_buffer_size,
714+@@myisam_repair_threads,
715+@@sort_buffer_size,
716+@@binlog_format,
717+@@keep_files_on_create,
718+@@max_join_size;
719+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
720+500000 1 100000 MIXED 1 2222220000000
721+''
722+''
723+SET STATEMENT myisam_sort_buffer_size=320000,
724+myisam_repair_threads=2,
725+sort_buffer_size=220022,
726+binlog_format=row,
727+keep_files_on_create=ON,
728+max_join_size=2222220000000
729+FOR SET SESSION
730+myisam_sort_buffer_size=260000,
731+myisam_repair_threads=3,
732+sort_buffer_size=230013,
733+binlog_format=row,
734+keep_files_on_create=ON,
735+max_join_size=2323230000000;
736+''
737+'# Post-STATEMENT
738+SELECT @@myisam_sort_buffer_size,
739+@@myisam_repair_threads,
740+@@sort_buffer_size,
741+@@binlog_format,
742+@@keep_files_on_create,
743+@@max_join_size;
744+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
745+500000 1 100000 MIXED 1 2222220000000
746+''
747+''
748+'#------------------Test 18-----------------------#'
749+'# set initial variable values
750+SET SESSION myisam_sort_buffer_size=500000,
751+myisam_repair_threads=1,
752+sort_buffer_size=100000,
753+binlog_format=mixed,
754+keep_files_on_create=ON,
755+max_join_size=2222220000000;
756+''
757+'# Pre-STATEMENT variable value
758+SELECT @@myisam_sort_buffer_size,
759+@@myisam_repair_threads,
760+@@sort_buffer_size,
761+@@binlog_format,
762+@@keep_files_on_create,
763+@@max_join_size;
764+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
765+500000 1 100000 MIXED 1 2222220000000
766+''
767+''
768+CREATE PROCEDURE p5() BEGIN
769+SELECT @@myisam_sort_buffer_size,
770+@@myisam_repair_threads,
771+@@sort_buffer_size,
772+@@binlog_format,
773+@@keep_files_on_create,
774+@@max_join_size;
775+SET SESSION
776+myisam_sort_buffer_size=260000,
777+myisam_repair_threads=3,
778+sort_buffer_size=230013,
779+binlog_format=row,
780+keep_files_on_create=ON,
781+max_join_size=2323230000000;
782+SELECT @@myisam_sort_buffer_size,
783+@@myisam_repair_threads,
784+@@sort_buffer_size,
785+@@binlog_format,
786+@@keep_files_on_create,
787+@@max_join_size;
788+END|
789+''
790+''
791+SET STATEMENT myisam_sort_buffer_size=400000,
792+myisam_repair_threads=2,
793+sort_buffer_size=200000,
794+binlog_format=row,
795+keep_files_on_create=OFF,
796+max_join_size=4444440000000 FOR
797+CALL p5();
798+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
799+400000 2 200000 ROW 0 4444440000000
800+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
801+260000 3 230013 ROW 1 2323230000000
802+''
803+'# Post-STATEMENT
804+SELECT @@myisam_sort_buffer_size,
805+@@myisam_repair_threads,
806+@@sort_buffer_size,
807+@@binlog_format,
808+@@keep_files_on_create,
809+@@max_join_size;
810+@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
811+500000 1 100000 MIXED 1 2222220000000
812+''
813+'# Cleanup'
814+DROP TABLE t1;
815+DROP PROCEDURE p1;
816+DROP PROCEDURE p2;
817+DROP PROCEDURE p3;
818+DROP PROCEDURE p4;
819+DROP PROCEDURE p5;
820
821=== added file 'Percona-Server/mysql-test/t/percona_statement_set.test'
822--- Percona-Server/mysql-test/t/percona_statement_set.test 1970-01-01 00:00:00 +0000
823+++ Percona-Server/mysql-test/t/percona_statement_set.test 2012-04-26 09:29:20 +0000
824@@ -0,0 +1,778 @@
825+--echo '# SET STATEMENT ..... FOR .... TEST'
826+############################ STATEMENT_SET #############################
827+# #
828+# Testing working functionality of SET STATEMENT #
829+# #
830+# #
831+# There is important documentation within #
832+# #
833+# #
834+# Author: Joe Lukas #
835+# Creation: #
836+# 2009-08-02 Implement this test as part of #
837+# WL#681 Per query variable settings #
838+# #
839+########################################################################
840+
841+
842+####################################################################
843+# Set up current database #
844+####################################################################
845+--echo '# Setup database'
846+CREATE TABLE t1 (v1 INT, v2 INT);
847+INSERT INTO t1 VALUES (1,2);
848+INSERT INTO t1 VALUES (3,4);
849+--echo ''
850+--echo '#------------------ STATEMENT Test 1 -----------------------#'
851+####################################################################
852+# Checks with variable value type ulong #
853+####################################################################
854+--echo '# Initialize variables to known setting'
855+SET SESSION sort_buffer_size=100000;
856+--echo ''
857+--echo '# Pre-STATEMENT variable value'
858+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
859+SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t1;
860+--echo ''
861+--echo '# Post-STATEMENT variable value'
862+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
863+--echo ''
864+--echo '#------------------ STATEMENT Test 2 -----------------------#'
865+####################################################################
866+# Checks for multiple set values inside STATEMENT ... FOR #
867+####################################################################
868+--echo '# Initialize variables to known setting'
869+SET SESSION binlog_format=mixed;
870+SET SESSION sort_buffer_size=100000;
871+--echo '# Pre-STATEMENT variable value'
872+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
873+SHOW SESSION VARIABLES LIKE 'binlog_format';
874+SET STATEMENT sort_buffer_size=150000, binlog_format=row
875+ FOR SELECT * FROM t1;
876+--echo '# Post-STATEMENT variable value'
877+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
878+SHOW SESSION VARIABLES LIKE 'binlog_format';
879+
880+--echo ''
881+--echo '#------------------ STATEMENT Test 3 -----------------------#'
882+####################################################################
883+# Check current variable value is stored in using stored #
884+# statements. #
885+####################################################################
886+--echo '# set initial variable value, make prepared statement
887+SET SESSION binlog_format=row;
888+PREPARE stmt1 FROM 'SET STATEMENT binlog_format=row FOR SELECT * FROM t1';
889+--echo ''
890+--echo '# Change variable setting'
891+SET SESSION binlog_format=mixed;
892+--echo ''
893+--echo '# Pre-STATEMENT variable value'
894+--echo ''
895+SHOW SESSION VARIABLES LIKE 'binlog_format';
896+--echo ''
897+EXECUTE stmt1;
898+--echo ''
899+--echo '# Post-STATEMENT variable value'
900+SHOW SESSION VARIABLES LIKE 'binlog_format';
901+
902+--echo ''
903+DEALLOCATE PREPARE stmt1;
904+--echo '#------------------ STATEMENT Test 4 -----------------------#'
905+####################################################################
906+# Check works with OPTIMIZE TABLE command #
907+# Checks works with a variable value of type INT #
908+# Checks works with variable type ULONGLONG #
909+####################################################################
910+--echo '# set initial variable value, make prepared statement
911+SET SESSION myisam_sort_buffer_size=500000, myisam_repair_threads=1;
912+--echo ''
913+--echo '# Pre-STATEMENT variable value'
914+SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
915+SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
916+--echo ''
917+SET STATEMENT myisam_sort_buffer_size=800000,
918+ myisam_repair_threads=2 FOR OPTIMIZE TABLE t1;
919+--echo ''
920+--echo '# Post-STATEMENT variable value'
921+SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
922+SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
923+
924+--echo ''
925+--echo '#------------------ STATEMENT Test 5 -----------------------#'
926+####################################################################
927+# Checks if variable reset after error in statement after FOR #
928+####################################################################
929+--echo '# Initialize variables to known setting'
930+SET SESSION sort_buffer_size=100000;
931+--echo ''
932+--echo '# Pre-STATEMENT variable value'
933+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
934+--echo ''
935+--error ER_NO_SUCH_TABLE
936+SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t2;
937+--echo ''
938+--echo '# Post-STATEMENT variable value'
939+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
940+
941+--echo ''
942+--echo '#------------------ STATEMENT Test 6 -----------------------#'
943+####################################################################
944+# Checks works with variable type MY_BOOL #
945+####################################################################
946+--echo '# Initialize variables to known setting'
947+SET SESSION keep_files_on_create=ON;
948+--echo ''
949+--echo '# Pre-STATEMENT variable value'
950+SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
951+--echo ''
952+SET STATEMENT keep_files_on_create=OFF FOR SELECT * FROM t1;
953+--echo ''
954+--echo '# Post-STATEMENT variable value'
955+SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
956+
957+--echo ''
958+--echo '#------------------ STATEMENT Test 7 -----------------------#'
959+####################################################################
960+# Checks works with variable type HA_ROWS #
961+####################################################################
962+--echo '# Initialize variables to known setting'
963+SET SESSION max_join_size=2222220000000;
964+--echo ''
965+--echo '# Pre-STATEMENT variable value'
966+SHOW SESSION VARIABLES LIKE 'max_join_size';
967+--echo ''
968+SET STATEMENT max_join_size=1000000000000 FOR SELECT * FROM t1;
969+--echo ''
970+--echo '# Post-STATEMENT variable value'
971+SHOW SESSION VARIABLES LIKE 'max_join_size';
972+
973+--echo ''
974+--echo '#------------------Test 8-----------------------#'
975+####################################################################
976+# Ensure variable of each type is set to proper value during #
977+# statement after FOR execution #
978+####################################################################
979+--echo '# Initialize test variables'
980+SET SESSION myisam_sort_buffer_size=500000,
981+ myisam_repair_threads=1,
982+ sort_buffer_size = 200000,
983+ max_join_size=2222220000000,
984+ keep_files_on_create=ON;
985+
986+--echo ''
987+--echo '# LONG '
988+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
989+SET STATEMENT sort_buffer_size = 100000
990+ FOR SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
991+SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
992+--echo ''
993+--echo '# MY_BOOL '
994+SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
995+SET STATEMENT keep_files_on_create=OFF
996+ FOR SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
997+SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
998+
999+--echo ''
1000+--echo '# INT/LONG '
1001+SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
1002+SET STATEMENT myisam_repair_threads=2
1003+ FOR SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
1004+SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
1005+--echo ''
1006+--echo '# ULONGLONG '
1007+SHOW SESSION VARIABLES LIKE 'max_join_size';
1008+SET STATEMENT max_join_size=2000000000000
1009+ FOR SHOW SESSION VARIABLES LIKE 'max_join_size';
1010+SHOW SESSION VARIABLES LIKE 'max_join_size';
1011+
1012+--echo ''
1013+--echo '#------------------Test 9-----------------------#'
1014+####################################################################
1015+# No 1 - Check works with CREATE ... BEGIN ... END command #
1016+# Display variables during execution #
1017+# No 2 - Test with DROP command #
1018+####################################################################
1019+--echo '# set initial variable values
1020+SET SESSION myisam_sort_buffer_size=500000,
1021+ myisam_repair_threads=1,
1022+ sort_buffer_size=100000,
1023+ binlog_format=mixed,
1024+ keep_files_on_create=ON,
1025+ max_join_size=2222220000000;
1026+--echo ''
1027+--echo ''
1028+--echo '# Pre-STATEMENT variable value
1029+SELECT @@myisam_sort_buffer_size,
1030+ @@myisam_repair_threads,
1031+ @@sort_buffer_size,
1032+ @@binlog_format,
1033+ @@keep_files_on_create,
1034+ @@max_join_size;
1035+--echo ''
1036+--echo ''
1037+DELIMITER |;
1038+CREATE FUNCTION myProc (cost DECIMAL(10,2))
1039+ RETURNS DECIMAL(10,2)
1040+
1041+ SQL SECURITY DEFINER
1042+
1043+ tax: BEGIN
1044+ DECLARE order_tax DECIMAL(10,2);
1045+ SET order_tax = cost * .05;
1046+ RETURN order_tax;
1047+ END|
1048+DELIMITER ;|
1049+--echo ''
1050+--echo '# During Execution values
1051+SET STATEMENT myisam_sort_buffer_size=400000,
1052+ myisam_repair_threads=2,
1053+ sort_buffer_size=200000,
1054+ binlog_format=row,
1055+ keep_files_on_create=OFF,
1056+ max_join_size=4444440000000 FOR
1057+ SELECT myProc(123.45);
1058+--echo ''
1059+--echo '# Post-STATEMENT No 1 variable value Pre-STATEMENT for No 2'
1060+SELECT @@myisam_sort_buffer_size,
1061+ @@myisam_repair_threads,
1062+ @@sort_buffer_size,
1063+ @@binlog_format,
1064+ @@keep_files_on_create,
1065+ @@max_join_size;
1066+--echo ''
1067+SET STATEMENT myisam_sort_buffer_size=400000,
1068+ myisam_repair_threads=2,
1069+ sort_buffer_size=200000,
1070+ binlog_format=row,
1071+ keep_files_on_create=OFF,
1072+ max_join_size=4444440000000 FOR
1073+ DROP FUNCTION myProc;
1074+--echo ''
1075+--echo '# Post-STATEMENT No 2 variable value
1076+SELECT @@myisam_sort_buffer_size,
1077+ @@myisam_repair_threads,
1078+ @@sort_buffer_size,
1079+ @@binlog_format,
1080+ @@keep_files_on_create,
1081+ @@max_join_size;
1082+
1083+--echo ''
1084+--echo '#------------------Test 10-----------------------#'
1085+####################################################################
1086+# No 1 - Check with PREPARE statement #
1087+# with STATEMENT inside with same variable as outside #
1088+# No 2 - Check with EXECUTE statement #
1089+####################################################################
1090+--echo '# set initial variable values
1091+SET SESSION myisam_sort_buffer_size=500000,
1092+ myisam_repair_threads=1,
1093+ sort_buffer_size=100000,
1094+ binlog_format=mixed,
1095+ keep_files_on_create=ON,
1096+ max_join_size=2222220000000;
1097+--echo ''
1098+--echo '# Pre-STATEMENT variable value
1099+SELECT @@myisam_sort_buffer_size,
1100+ @@myisam_repair_threads,
1101+ @@sort_buffer_size,
1102+ @@binlog_format,
1103+ @@keep_files_on_create,
1104+ @@max_join_size;
1105+--echo ''
1106+--echo ''
1107+SET STATEMENT myisam_sort_buffer_size=400000,
1108+ myisam_repair_threads=2,
1109+ sort_buffer_size=200000,
1110+ binlog_format=row,
1111+ keep_files_on_create=OFF,
1112+ max_join_size=4444440000000 FOR
1113+ PREPARE stmt2
1114+ FROM 'SELECT * FROM t1';
1115+--echo ''
1116+--echo 'Test No 1 Post Value & Test 2 Pre values'
1117+SELECT @@myisam_sort_buffer_size,
1118+ @@myisam_repair_threads,
1119+ @@sort_buffer_size,
1120+ @@binlog_format,
1121+ @@keep_files_on_create,
1122+ @@max_join_size;
1123+--echo ''
1124+--echo ''
1125+SET STATEMENT myisam_sort_buffer_size=400000,
1126+ myisam_repair_threads=2,
1127+ sort_buffer_size=200000,
1128+ binlog_format=row,
1129+ keep_files_on_create=OFF,
1130+ max_join_size=4444440000000 FOR
1131+ EXECUTE stmt2;
1132+--echo ''
1133+--echo '# Post-STATEMENT No 2
1134+SELECT @@myisam_sort_buffer_size,
1135+ @@myisam_repair_threads,
1136+ @@sort_buffer_size,
1137+ @@binlog_format,
1138+ @@keep_files_on_create,
1139+ @@max_join_size;
1140+--echo ''
1141+DEALLOCATE PREPARE stmt2;
1142+--echo ''
1143+--echo '#------------------Test 11-----------------------#'
1144+####################################################################
1145+# No 1 - Check with PREPARE statement #
1146+# check with different variable on inside PREPARE #
1147+# No 2 - Check with EXECUTE statement #
1148+####################################################################
1149+--echo '# set initial variable values
1150+SET SESSION myisam_sort_buffer_size=500000,
1151+ myisam_repair_threads=1,
1152+ sort_buffer_size=100000,
1153+ binlog_format=mixed,
1154+ keep_files_on_create=ON,
1155+ max_join_size=2222220000000;
1156+--echo ''
1157+--echo ''
1158+--echo '# Pre-STATEMENT variable value
1159+SELECT @@myisam_sort_buffer_size,
1160+ @@myisam_repair_threads,
1161+ @@sort_buffer_size,
1162+ @@binlog_format,
1163+ @@keep_files_on_create,
1164+ @@max_join_size;
1165+--echo ''
1166+--echo ''
1167+SET STATEMENT myisam_sort_buffer_size=400000,
1168+ myisam_repair_threads=2,
1169+ sort_buffer_size=200000,
1170+ keep_files_on_create=OFF,
1171+ max_join_size=4444440000000 FOR
1172+ PREPARE stmt1 FROM
1173+ 'SET STATEMENT binlog_format=row FOR SELECT * FROM t1';
1174+--echo ''
1175+--echo 'Test No 1 Post Value & Test 2 Pre values'
1176+SELECT @@myisam_sort_buffer_size,
1177+ @@myisam_repair_threads,
1178+ @@sort_buffer_size,
1179+ @@binlog_format,
1180+ @@keep_files_on_create,
1181+ @@max_join_size;
1182+--echo ''
1183+--echo ''
1184+SET STATEMENT myisam_sort_buffer_size=400000,
1185+ myisam_repair_threads=2,
1186+ sort_buffer_size=200000,
1187+ keep_files_on_create=OFF,
1188+ max_join_size=4444440000000 FOR
1189+ EXECUTE stmt1;
1190+--echo ''
1191+--echo '# Post-STATEMENT No 2
1192+SELECT @@myisam_sort_buffer_size,
1193+ @@myisam_repair_threads,
1194+ @@sort_buffer_size,
1195+ @@binlog_format,
1196+ @@keep_files_on_create,
1197+ @@max_join_size;
1198+--echo ''
1199+--echo ''
1200+--echo '#------------------Test 12-----------------------#'
1201+####################################################################
1202+# No 1 - Check with PROCEDURE (show variables in procedure) #
1203+# No 2 - Check with CALL statement show variables in PROCEDURE #
1204+####################################################################
1205+--echo '# set initial variable values
1206+SET SESSION myisam_sort_buffer_size=500000,
1207+ myisam_repair_threads=1,
1208+ sort_buffer_size=100000,
1209+ binlog_format=mixed,
1210+ keep_files_on_create=ON,
1211+ max_join_size=2222220000000;
1212+--echo ''
1213+--echo ''
1214+--echo '# Pre-STATEMENT variable value
1215+SELECT @@myisam_sort_buffer_size,
1216+ @@myisam_repair_threads,
1217+ @@sort_buffer_size,
1218+ @@binlog_format,
1219+ @@keep_files_on_create,
1220+ @@max_join_size;
1221+--echo ''
1222+--echo ''
1223+DELIMITER |;
1224+SET STATEMENT myisam_sort_buffer_size=400000,
1225+ myisam_repair_threads=2,
1226+ sort_buffer_size=200000,
1227+ binlog_format=row,
1228+ keep_files_on_create=OFF,
1229+ max_join_size=4444440000000 FOR
1230+ CREATE PROCEDURE p1() BEGIN
1231+ SELECT @@myisam_sort_buffer_size,
1232+ @@myisam_repair_threads,
1233+ @@sort_buffer_size,
1234+ @@binlog_format,
1235+ @@keep_files_on_create,
1236+ @@max_join_size;
1237+ END|
1238+DELIMITER ;|
1239+--echo ''
1240+--echo 'Test No 1 Post Value & Test 2 Pre values'
1241+SELECT @@myisam_sort_buffer_size,
1242+ @@myisam_repair_threads,
1243+ @@sort_buffer_size,
1244+ @@binlog_format,
1245+ @@keep_files_on_create,
1246+ @@max_join_size;
1247+--echo ''
1248+--echo ''
1249+SET STATEMENT myisam_sort_buffer_size=400000,
1250+ myisam_repair_threads=2,
1251+ sort_buffer_size=200000,
1252+ binlog_format=row,
1253+ keep_files_on_create=OFF,
1254+ max_join_size=4444440000000 FOR
1255+ CALL p1();
1256+--echo ''
1257+--echo '# Post-STATEMENT No 2
1258+SELECT @@myisam_sort_buffer_size,
1259+ @@myisam_repair_threads,
1260+ @@sort_buffer_size,
1261+ @@binlog_format,
1262+ @@keep_files_on_create,
1263+ @@max_join_size;
1264+--echo ''
1265+--echo ''
1266+
1267+--echo '#------------------Test 13-----------------------#'
1268+####################################################################
1269+# Check PROCEDURE containing SET STATEMENT FOR #
1270+# p1() from test 12 will be used to display variables #
1271+####################################################################
1272+--echo '# set initial variable values
1273+SET SESSION myisam_sort_buffer_size=500000,
1274+ myisam_repair_threads=1,
1275+ sort_buffer_size=100000,
1276+ binlog_format=mixed,
1277+ keep_files_on_create=ON,
1278+ max_join_size=2222220000000;
1279+--echo ''
1280+--echo ''
1281+DELIMITER |;
1282+CREATE PROCEDURE p2() BEGIN
1283+ SET STATEMENT myisam_sort_buffer_size=400000,
1284+ myisam_repair_threads=3,
1285+ sort_buffer_size=300000,
1286+ binlog_format=mixed,
1287+ keep_files_on_create=OFF,
1288+ max_join_size=3333330000000 FOR
1289+ CALL p1();
1290+ END|
1291+DELIMITER ;|
1292+--echo ''
1293+--echo '# Pre-STATEMENT variable value
1294+SELECT @@myisam_sort_buffer_size,
1295+ @@myisam_repair_threads,
1296+ @@sort_buffer_size,
1297+ @@binlog_format,
1298+ @@keep_files_on_create,
1299+ @@max_join_size;
1300+--echo ''
1301+--echo ''
1302+SET STATEMENT myisam_sort_buffer_size=400000,
1303+ myisam_repair_threads=2,
1304+ sort_buffer_size=200000,
1305+ binlog_format=row,
1306+ keep_files_on_create=OFF,
1307+ max_join_size=4444440000000 FOR
1308+ CALL p2();
1309+--echo ''
1310+--echo '# Post-STATEMENT
1311+SELECT @@myisam_sort_buffer_size,
1312+ @@myisam_repair_threads,
1313+ @@sort_buffer_size,
1314+ @@binlog_format,
1315+ @@keep_files_on_create,
1316+ @@max_join_size;
1317+--echo ''
1318+--echo ''
1319+--echo '#------------------Test 14-----------------------#'
1320+####################################################################
1321+# Check PROCEDURE containing compound SET STATEMENT FOR #
1322+# p2() will be used as compounding statement from test 13 #
1323+####################################################################
1324+--echo '# set initial variable values
1325+SET SESSION myisam_sort_buffer_size=500000,
1326+ myisam_repair_threads=1,
1327+ sort_buffer_size=100000,
1328+ binlog_format=mixed,
1329+ keep_files_on_create=ON,
1330+ max_join_size=2222220000000;
1331+--echo ''
1332+--echo ''
1333+DELIMITER |;
1334+CREATE PROCEDURE p3() BEGIN
1335+ SELECT @@myisam_sort_buffer_size,
1336+ @@myisam_repair_threads,
1337+ @@sort_buffer_size,
1338+ @@binlog_format,
1339+ @@keep_files_on_create,
1340+ @@max_join_size;
1341+ SET STATEMENT myisam_sort_buffer_size=320000,
1342+ myisam_repair_threads=2,
1343+ sort_buffer_size=220022,
1344+ binlog_format=row,
1345+ keep_files_on_create=ON,
1346+ max_join_size=2222220000000 FOR
1347+ CALL p2();
1348+ END|
1349+DELIMITER ;|
1350+--echo ''
1351+--echo '# Pre-STATEMENT variable value
1352+SELECT @@myisam_sort_buffer_size,
1353+ @@myisam_repair_threads,
1354+ @@sort_buffer_size,
1355+ @@binlog_format,
1356+ @@keep_files_on_create,
1357+ @@max_join_size;
1358+--echo ''
1359+--echo ''
1360+SET STATEMENT myisam_sort_buffer_size=400000,
1361+ myisam_repair_threads=2,
1362+ sort_buffer_size=200000,
1363+ binlog_format=row,
1364+ keep_files_on_create=OFF,
1365+ max_join_size=4444440000000 FOR
1366+ CALL p3();
1367+--echo ''
1368+--echo '# Post-STATEMENT
1369+SELECT @@myisam_sort_buffer_size,
1370+ @@myisam_repair_threads,
1371+ @@sort_buffer_size,
1372+ @@binlog_format,
1373+ @@keep_files_on_create,
1374+ @@max_join_size;
1375+--echo ''
1376+--echo ''
1377+
1378+ --echo ''
1379+--echo ''
1380+--echo '#------------------Test 15-----------------------#'
1381+####################################################################
1382+# Check PROCEDURE containing compound SET STATEMENT FOR #
1383+# call multiple SET STATEMENT .. FOR showing SELECT #
1384+####################################################################
1385+--echo '# set initial variable values
1386+SET SESSION myisam_sort_buffer_size=500000,
1387+ myisam_repair_threads=1,
1388+ sort_buffer_size=100000,
1389+ binlog_format=mixed,
1390+ keep_files_on_create=ON,
1391+ max_join_size=2222220000000;
1392+--echo ''
1393+--echo ''
1394+DELIMITER |;
1395+CREATE PROCEDURE p4() BEGIN
1396+ SELECT @@myisam_sort_buffer_size,
1397+ @@myisam_repair_threads,
1398+ @@sort_buffer_size,
1399+ @@binlog_format,
1400+ @@keep_files_on_create,
1401+ @@max_join_size;
1402+ SET STATEMENT myisam_sort_buffer_size=320000,
1403+ myisam_repair_threads=2,
1404+ sort_buffer_size=220022,
1405+ binlog_format=row,
1406+ keep_files_on_create=ON,
1407+ max_join_size=2222220000000 FOR
1408+ SELECT @@myisam_sort_buffer_size,
1409+ @@myisam_repair_threads,
1410+ @@sort_buffer_size,
1411+ @@binlog_format,
1412+ @@keep_files_on_create,
1413+ @@max_join_size;
1414+ SET STATEMENT myisam_sort_buffer_size=320000,
1415+ myisam_repair_threads=2,
1416+ sort_buffer_size=220022,
1417+ binlog_format=row,
1418+ keep_files_on_create=ON,
1419+ max_join_size=2222220000000 FOR
1420+ SELECT @@myisam_sort_buffer_size,
1421+ @@myisam_repair_threads,
1422+ @@sort_buffer_size,
1423+ @@binlog_format,
1424+ @@keep_files_on_create,
1425+ @@max_join_size;
1426+ SET STATEMENT myisam_sort_buffer_size=320000,
1427+ myisam_repair_threads=2,
1428+ sort_buffer_size=220022,
1429+ binlog_format=row,
1430+ keep_files_on_create=ON,
1431+ max_join_size=2222220000000 FOR
1432+ SELECT @@myisam_sort_buffer_size,
1433+ @@myisam_repair_threads,
1434+ @@sort_buffer_size,
1435+ @@binlog_format,
1436+ @@keep_files_on_create,
1437+ @@max_join_size;
1438+ END|
1439+DELIMITER ;|
1440+--echo ''
1441+--echo '# Pre-STATEMENT variable value
1442+SELECT @@myisam_sort_buffer_size,
1443+ @@myisam_repair_threads,
1444+ @@sort_buffer_size,
1445+ @@binlog_format,
1446+ @@keep_files_on_create,
1447+ @@max_join_size;
1448+--echo ''
1449+--echo ''
1450+SET STATEMENT myisam_sort_buffer_size=400000,
1451+ myisam_repair_threads=2,
1452+ sort_buffer_size=200000,
1453+ binlog_format=row,
1454+ keep_files_on_create=OFF,
1455+ max_join_size=4444440000000 FOR
1456+ CALL p4();
1457+--echo ''
1458+--echo '# Post-STATEMENT
1459+SELECT @@myisam_sort_buffer_size,
1460+ @@myisam_repair_threads,
1461+ @@sort_buffer_size,
1462+ @@binlog_format,
1463+ @@keep_files_on_create,
1464+ @@max_join_size;
1465+
1466+--echo ''
1467+--echo ''
1468+--echo '#------------------Test 16-----------------------#'
1469+####################################################################
1470+# Test Effect on parsing #
1471+####################################################################
1472+--echo ''
1473+--echo '# Pre-STATEMENT variable value
1474+SELECT @@sql_mode;
1475+--echo ''
1476+--echo ''
1477+SET STATEMENT sql_mode='ansi' FOR SELECT * FROM t1;
1478+
1479+--echo ''
1480+--echo '# Post-STATEMENT
1481+SELECT @@sql_mode;
1482+--echo ''
1483+--echo ''
1484+--echo '#------------------Test 17-----------------------#'
1485+####################################################################
1486+# Test effect of SET STATEMENT FOR with SET SESSION modifying #
1487+# the same variables as the SET STATEMENT #
1488+####################################################################
1489+--echo '# set initial variable values
1490+SET SESSION myisam_sort_buffer_size=500000,
1491+ myisam_repair_threads=1,
1492+ sort_buffer_size=100000,
1493+ binlog_format=mixed,
1494+ keep_files_on_create=ON,
1495+ max_join_size=2222220000000;
1496+--echo ''
1497+--echo '# Pre-STATEMENT variable value
1498+SELECT @@myisam_sort_buffer_size,
1499+ @@myisam_repair_threads,
1500+ @@sort_buffer_size,
1501+ @@binlog_format,
1502+ @@keep_files_on_create,
1503+ @@max_join_size;
1504+--echo ''
1505+--echo ''
1506+SET STATEMENT myisam_sort_buffer_size=320000,
1507+ myisam_repair_threads=2,
1508+ sort_buffer_size=220022,
1509+ binlog_format=row,
1510+ keep_files_on_create=ON,
1511+ max_join_size=2222220000000
1512+ FOR SET SESSION
1513+ myisam_sort_buffer_size=260000,
1514+ myisam_repair_threads=3,
1515+ sort_buffer_size=230013,
1516+ binlog_format=row,
1517+ keep_files_on_create=ON,
1518+ max_join_size=2323230000000;
1519+
1520+--echo ''
1521+--echo '# Post-STATEMENT
1522+SELECT @@myisam_sort_buffer_size,
1523+ @@myisam_repair_threads,
1524+ @@sort_buffer_size,
1525+ @@binlog_format,
1526+ @@keep_files_on_create,
1527+ @@max_join_size;
1528+
1529+--echo ''
1530+--echo ''
1531+--echo '#------------------Test 18-----------------------#'
1532+####################################################################
1533+# Test effect of SET SESSION inside a stored procedure with #
1534+# with a SET STATEMENT on outside variables #
1535+####################################################################
1536+--echo '# set initial variable values
1537+SET SESSION myisam_sort_buffer_size=500000,
1538+ myisam_repair_threads=1,
1539+ sort_buffer_size=100000,
1540+ binlog_format=mixed,
1541+ keep_files_on_create=ON,
1542+ max_join_size=2222220000000;
1543+--echo ''
1544+--echo '# Pre-STATEMENT variable value
1545+SELECT @@myisam_sort_buffer_size,
1546+ @@myisam_repair_threads,
1547+ @@sort_buffer_size,
1548+ @@binlog_format,
1549+ @@keep_files_on_create,
1550+ @@max_join_size;
1551+--echo ''
1552+--echo ''
1553+DELIMITER |;
1554+CREATE PROCEDURE p5() BEGIN
1555+ SELECT @@myisam_sort_buffer_size,
1556+ @@myisam_repair_threads,
1557+ @@sort_buffer_size,
1558+ @@binlog_format,
1559+ @@keep_files_on_create,
1560+ @@max_join_size;
1561+ SET SESSION
1562+ myisam_sort_buffer_size=260000,
1563+ myisam_repair_threads=3,
1564+ sort_buffer_size=230013,
1565+ binlog_format=row,
1566+ keep_files_on_create=ON,
1567+ max_join_size=2323230000000;
1568+ SELECT @@myisam_sort_buffer_size,
1569+ @@myisam_repair_threads,
1570+ @@sort_buffer_size,
1571+ @@binlog_format,
1572+ @@keep_files_on_create,
1573+ @@max_join_size;
1574+ END|
1575+DELIMITER ;|
1576+--echo ''
1577+--echo ''
1578+SET STATEMENT myisam_sort_buffer_size=400000,
1579+ myisam_repair_threads=2,
1580+ sort_buffer_size=200000,
1581+ binlog_format=row,
1582+ keep_files_on_create=OFF,
1583+ max_join_size=4444440000000 FOR
1584+ CALL p5();
1585+
1586+--echo ''
1587+--echo '# Post-STATEMENT
1588+SELECT @@myisam_sort_buffer_size,
1589+ @@myisam_repair_threads,
1590+ @@sort_buffer_size,
1591+ @@binlog_format,
1592+ @@keep_files_on_create,
1593+ @@max_join_size;
1594+
1595+--echo ''
1596+--echo '# Cleanup'
1597+DROP TABLE t1;
1598+DROP PROCEDURE p1;
1599+DROP PROCEDURE p2;
1600+DROP PROCEDURE p3;
1601+DROP PROCEDURE p4;
1602+DROP PROCEDURE p5;
1603
1604=== modified file 'Percona-Server/sql/lex.h'
1605--- Percona-Server/sql/lex.h 2012-04-18 23:26:01 +0000
1606+++ Percona-Server/sql/lex.h 2012-04-26 09:29:20 +0000
1607@@ -534,6 +534,7 @@
1608 { "START", SYM(START_SYM)},
1609 { "STARTING", SYM(STARTING)},
1610 { "STARTS", SYM(STARTS_SYM)},
1611+ { "STATEMENT", SYM(STATEMENT_SYM)},
1612 { "STATUS", SYM(STATUS_SYM)},
1613 { "STOP", SYM(STOP_SYM)},
1614 { "STORAGE", SYM(STORAGE_SYM)},
1615
1616=== modified file 'Percona-Server/sql/set_var.cc'
1617--- Percona-Server/sql/set_var.cc 2011-07-03 23:48:19 +0000
1618+++ Percona-Server/sql/set_var.cc 2012-04-26 09:29:20 +0000
1619@@ -797,3 +797,181 @@
1620 return 0;
1621 }
1622
1623+
1624+/**
1625+ set_stmt_get_reset_vars function will retrieve the current value of
1626+ a SESSION variable and create a new set_var_base class item containing
1627+ the value and necessary update & check functions to be used to reset the
1628+ variable to a previous setting.
1629+
1630+ @param var set_var containing value of variable that will be change
1631+ too and holds name, name length, SESSION variable type, and
1632+ value_ptr() function to retrieve current value.
1633+
1634+ @param lex_var_list list of set_var_bases that are created to hold the
1635+ previous values and the update & check functions to be
1636+ used to return the variable to a previous setting
1637+
1638+ @return true if error occurs false if successful
1639+
1640+*/
1641+
1642+bool set_stmt_get_reset_vars(
1643+ THD *thd,
1644+ set_var * var,
1645+ List<set_var_base> *lex_var_list)
1646+{
1647+ bool res= false;
1648+ set_var *old_var= NULL;
1649+ LEX_STRING base;
1650+ char str[var->var->name.length];
1651+
1652+ strcpy(str, var->var->name.str);
1653+ base.str= str;
1654+ base.length= var->var->name.length;
1655+ /*
1656+ When per query variables are set, the "set_var" structure is created and
1657+ pushed into array. During "mysql_execute_command" this array is used for
1658+ preserving old variables values ans setting new values. But the main
1659+ trouble here is to get the old variable value. The matter is we can
1660+ operate only with "sys_var" structure which describes system variable.
1661+ This structure contains "value_ptr" method which uses the offset to count
1662+ the address of the variable. And the only field in "sys_var" which shows
1663+ the length of a variable is "const SHOW_TYPE show_val_type;". The current
1664+ implementation uses the "switch" for the "const SHOW_TYPE show_val_type"
1665+ field and considers only "int" and "char" types. I don't like this
1666+ approach because we have to add the processing of the new type into
1667+ several places. But it seems there is no another way except using "switch"
1668+ for all possible values of "SHOW_TYPE" enumeration.
1669+ */
1670+ switch (var->var->show_type())
1671+ {
1672+ case SHOW_INT:
1673+ {
1674+ uint value;
1675+ Item_int *item;
1676+ /*
1677+ We don't need to lock LOCK_global_system_variables here,
1678+ because it's impossible to have global-only variable in
1679+ var parameter due to syntax rules. So
1680+ SELECT STATEMENT GLOBAL variable_name FOR ...
1681+ or
1682+ SELECT STATEMENT @@global.variable_name FOR ...
1683+ is a wrong syntax.
1684+ */
1685+ value= *(uint*) var->var->value_ptr(thd, OPT_SESSION, &base);
1686+ item= new Item_uint((ulonglong) value);
1687+ old_var = new set_var(OPT_SESSION, var->var, &base, item);
1688+ break;
1689+ }
1690+ case SHOW_LONG:
1691+ {
1692+ ulong value;
1693+ Item_int *item;
1694+
1695+ value= *(ulong*) var->var->value_ptr(thd, OPT_SESSION, &base);
1696+ item= new Item_uint((ulonglong) value);
1697+ old_var= new set_var(OPT_SESSION, var->var, &base, item);
1698+ break;
1699+ }
1700+ case SHOW_LONGLONG:
1701+ {
1702+ longlong value;
1703+ Item_int *item;
1704+
1705+ value= *(longlong*) var->var->value_ptr(thd, OPT_SESSION, &base);
1706+ item= new Item_int(value);
1707+ old_var= new set_var(OPT_SESSION, var->var, &base, item);
1708+ break;
1709+ }
1710+ case SHOW_HA_ROWS:
1711+ {
1712+ ha_rows value;
1713+ Item_int *item;
1714+
1715+ value= *(ha_rows*) var->var->value_ptr(thd, OPT_SESSION, &base);
1716+ item= new Item_int((longlong) value);
1717+ old_var= new set_var(OPT_SESSION, var->var, &base, item);
1718+ break;
1719+ }
1720+ case SHOW_MY_BOOL:
1721+ {
1722+ Item_int *item;
1723+ item= new Item_int((int32) *(my_bool*) var->var->value_ptr(thd,
1724+ OPT_SESSION, &base), 1);
1725+ old_var= new set_var(OPT_SESSION, var->var, &base, item);
1726+ break;
1727+ }
1728+ case SHOW_CHAR:
1729+ {
1730+ Item *item;
1731+ char *str= (char*) var->var->value_ptr(thd, OPT_SESSION, &base);
1732+ if (str)
1733+ item= new Item_string(str, strlen(str), system_charset_info,
1734+ DERIVATION_SYSCONST);
1735+ else
1736+ {
1737+ item= new Item_null();
1738+ item->collation.set(system_charset_info, DERIVATION_SYSCONST);
1739+ }
1740+ old_var= new set_var(OPT_SESSION, var->var, &base, item);
1741+ break;
1742+ }
1743+ default:
1744+ my_error(ER_NOT_SUPPORTED_YET, MYF(0), base.str);
1745+ }
1746+
1747+ if (old_var == NULL)
1748+ res= true;
1749+ else
1750+ lex_var_list->push_back(old_var);
1751+
1752+ return res;
1753+}
1754+
1755+
1756+/**
1757+ set_stmt_reset_vars function will check and update SESSION variables to the
1758+ previous setting for SET STATEMENT ... FOR. Please use sql_set_variables
1759+ for setting variables as this version is designed for SET STATEMENT and
1760+ removes checks and procedures that should be followed in normal SET
1761+ operations. This function will also do partial sets of any passing comment
1762+ instead of all or nothing
1763+
1764+ @param var_list list of set_var_bases that contain previous SESSION
1765+ variables that where previously changed temporarily and now
1766+ need to be set back using the check and update functions.
1767+
1768+ @return true if error occurs false if successful
1769+
1770+*/
1771+
1772+bool set_stmt_reset_vars(THD *thd, List<set_var_base> *var_list)
1773+{
1774+ int error;
1775+ bool err= false;
1776+ set_var_base *var;
1777+ List_iterator_fast<set_var_base> it(*var_list);
1778+
1779+ thd->stmt_da->can_overwrite_status= true;
1780+
1781+ while ((var = it++))
1782+ {
1783+ if (!var->check(thd)) //check variable update if valid
1784+ error |= var->update(thd); //Returns 0, -1 or 1
1785+ else
1786+ err= true;
1787+ }
1788+ /*
1789+ We encountered some sort of error, but no message was sent.
1790+ Send something semi-generic here since we don't know which
1791+ assignment in the list caused the error. Do not enter
1792+ if error is already existing as will cause crash since error
1793+ is from statement after FOR loop.
1794+ */
1795+ if (err && !thd->is_error())
1796+ my_error(ER_WRONG_ARGUMENTS, MYF(0), "SET");
1797+ thd->stmt_da->can_overwrite_status= false;
1798+ var_list->empty(); // do some cleanup
1799+ return err;
1800+}
1801
1802=== modified file 'Percona-Server/sql/set_var.h'
1803--- Percona-Server/sql/set_var.h 2012-04-19 16:51:34 +0000
1804+++ Percona-Server/sql/set_var.h 2012-04-26 09:29:20 +0000
1805@@ -321,5 +321,10 @@
1806 int sys_var_add_options(DYNAMIC_ARRAY *long_options, int parse_flags);
1807 void sys_var_end(void);
1808
1809+bool set_stmt_get_reset_vars(THD *thd,
1810+ set_var * var,
1811+ List<set_var_base> *lex_var_list);
1812+bool set_stmt_reset_vars(THD *thd, List<set_var_base> *var_list);
1813+
1814 #endif
1815
1816
1817=== modified file 'Percona-Server/sql/sql_lex.cc'
1818--- Percona-Server/sql/sql_lex.cc 2012-04-19 16:51:34 +0000
1819+++ Percona-Server/sql/sql_lex.cc 2012-04-26 09:29:20 +0000
1820@@ -372,6 +372,7 @@
1821 lex->value_list.empty();
1822 lex->update_list.empty();
1823 lex->set_var_list.empty();
1824+ lex->stmt_set_list.empty();
1825 lex->param_list.empty();
1826 lex->view_list.empty();
1827 lex->prepared_stmt_params.empty();
1828
1829=== modified file 'Percona-Server/sql/sql_lex.h'
1830--- Percona-Server/sql/sql_lex.h 2012-04-19 16:51:34 +0000
1831+++ Percona-Server/sql/sql_lex.h 2012-04-26 09:29:20 +0000
1832@@ -39,6 +39,7 @@
1833 class partition_info;
1834 class Event_parse_data;
1835 class set_var_base;
1836+class set_var;
1837 class sys_var;
1838 class Item_func_match;
1839 class Alter_drop;
1840@@ -2279,6 +2280,10 @@
1841 List<Item> *insert_list,field_list,value_list,update_list;
1842 List<List_item> many_values;
1843 List<set_var_base> var_list;
1844+ /*
1845+ Variables list for SET STATEMENT expression.
1846+ */
1847+ List<set_var> stmt_set_list;
1848 List<Item_func_set_user_var> set_var_list; // in-query assignment list
1849 List<Item_param> param_list;
1850 List<LEX_STRING> view_list; // view list (list of field names in view)
1851
1852=== modified file 'Percona-Server/sql/sql_parse.cc'
1853--- Percona-Server/sql/sql_parse.cc 2012-04-19 16:51:34 +0000
1854+++ Percona-Server/sql/sql_parse.cc 2012-04-26 09:29:20 +0000
1855@@ -1989,6 +1989,8 @@
1856 int
1857 mysql_execute_command(THD *thd)
1858 {
1859+ List<set_var_base> stmt_reset_list;
1860+ Item* stmt_free_list= NULL;
1861 int res= FALSE;
1862 int up_result= 0;
1863 LEX *lex= thd->lex;
1864@@ -2205,6 +2207,60 @@
1865 DEBUG_SYNC(thd,"before_execute_sql_command");
1866 #endif
1867
1868+ if (!lex->stmt_set_list.is_empty())
1869+ {
1870+ List<set_var_base> lex_var_list;
1871+ List_iterator_fast<set_var> it(lex->stmt_set_list);
1872+ set_var *var;
1873+
1874+ while ((var= it++))
1875+ {
1876+ if (set_stmt_get_reset_vars(thd, var, &stmt_reset_list))
1877+ {
1878+ my_error(ER_WRONG_ARGUMENTS, MYF(0), "SET");
1879+ goto error;
1880+ }
1881+ lex_var_list.push_back(var);
1882+ }
1883+
1884+ if (!(res= sql_set_variables(thd, &lex_var_list)))
1885+ {
1886+ /*
1887+ If the previous command was a SET ONE_SHOT, we don't want to forget
1888+ about the ONE_SHOT property of that SET. So we use a |= instead of = .
1889+ */
1890+ thd->one_shot_set |= lex->one_shot_set;
1891+ }
1892+ else
1893+ {
1894+ /*
1895+ We encountered some sort of error, but no message was sent.
1896+ Send something semi-generic here since we don't know which
1897+ assignment in the list caused the error.
1898+ */
1899+ if (!thd->is_error())
1900+ my_error(ER_WRONG_ARGUMENTS, MYF(0), "SET");
1901+ goto error;
1902+ }
1903+ /*
1904+ In set_stmt_reset_vars function the instances of class Item are created.
1905+ This instances are put into thd->free_list to be freed after query
1906+ execution. But when PREPARE or EXECUTE commands are used the
1907+ thd->free_list is cleaned up inside of Prepared_statement::prepare and
1908+ Prepared_statement::execute respectively. So the instances of
1909+ class Item that are used fot restoring per query variables values
1910+ can be freed before restoring operation. To avoid it in case of
1911+ PREPARE or EXECUTE commands processing the thd->free_list
1912+ is preserved in local variable to be restored after commands processing.
1913+ */
1914+ if(lex->sql_command == SQLCOM_EXECUTE || lex->sql_command == SQLCOM_PREPARE)
1915+ {
1916+ stmt_free_list = thd->free_list;
1917+ thd->free_list = 0;
1918+ }
1919+ lex_var_list.empty();
1920+ }
1921+
1922 switch (lex->sql_command) {
1923
1924 case SQLCOM_SHOW_EVENTS:
1925@@ -4581,6 +4637,22 @@
1926 my_ok(thd);
1927 break;
1928 }
1929+
1930+ if (!stmt_reset_list.is_empty()) /* reset variables for SET STATEMENT */
1931+ {
1932+ /* re-attach items to free_list for clean out later */
1933+ if(lex->sql_command == SQLCOM_EXECUTE || lex->sql_command == SQLCOM_PREPARE)
1934+ thd->free_list= stmt_free_list;
1935+
1936+ if (set_stmt_reset_vars(thd, &stmt_reset_list))
1937+ {
1938+ thd_proc_info(thd, "query end");
1939+ res= TRUE;
1940+ goto finish;
1941+ }
1942+ }
1943+
1944+
1945 thd_proc_info(thd, "query end");
1946
1947 /*
1948@@ -4599,6 +4671,18 @@
1949 goto finish;
1950
1951 error:
1952+
1953+ if (!stmt_reset_list.is_empty())
1954+ {
1955+
1956+ /*
1957+ re-attach items to free_list for clean out later
1958+ */
1959+ if (lex->sql_command == SQLCOM_EXECUTE || lex->sql_command == SQLCOM_PREPARE)
1960+ thd->free_list= stmt_free_list;
1961+ set_stmt_reset_vars(thd, &stmt_reset_list);
1962+ }
1963+
1964 res= TRUE;
1965
1966 finish:
1967
1968=== modified file 'Percona-Server/sql/sql_prepare.cc'
1969--- Percona-Server/sql/sql_prepare.cc 2012-04-19 16:51:34 +0000
1970+++ Percona-Server/sql/sql_prepare.cc 2012-04-26 09:29:20 +0000
1971@@ -3717,9 +3717,13 @@
1972 /*
1973 If the free_list is not empty, we'll wrongly free some externally
1974 allocated items when cleaning up after validation of the prepared
1975- statement.
1976+ statement. The exception is the case of SET STATEMENT using. When
1977+ it is used the free_list is preserved in local variable in
1978+ mysq_execute_command to avoid free the data for restoring values of
1979+ system variables.
1980 */
1981- DBUG_ASSERT(thd->free_list == NULL);
1982+ if(thd->lex->stmt_set_list.is_empty())
1983+ DBUG_ASSERT(thd->free_list == NULL);
1984
1985 /*
1986 Install the metadata observer. If some metadata version is
1987
1988=== modified file 'Percona-Server/sql/sql_yacc.yy'
1989--- Percona-Server/sql/sql_yacc.yy 2012-04-19 16:51:34 +0000
1990+++ Percona-Server/sql/sql_yacc.yy 2012-04-26 09:29:20 +0000
1991@@ -781,10 +781,10 @@
1992
1993 %pure_parser /* We have threads */
1994 /*
1995- Currently there are 168 shift/reduce conflicts.
1996+ Currently there are 169 shift/reduce conflicts.
1997 We should not introduce new conflicts any more.
1998 */
1999-%expect 168
2000+%expect 169
2001
2002 /*
2003 Comments for TOKENS.
2004@@ -1294,6 +1294,7 @@
2005 %token STARTING
2006 %token STARTS_SYM
2007 %token START_SYM /* SQL-2003-R */
2008+%token STATEMENT_SYM
2009 %token STATUS_SYM
2010 %token NOLOCK_SYM /* SHOW SLAVE STATUS NOLOCK */
2011 %token STDDEV_SAMP_SYM /* SQL-2003-N */
2012@@ -12780,6 +12781,7 @@
2013 | SQL_NO_CACHE_SYM {}
2014 | SQL_THREAD {}
2015 | STARTS_SYM {}
2016+ | STATEMENT_SYM {}
2017 | STATUS_SYM {}
2018 | STORAGE_SYM {}
2019 | STRING_SYM {}
2020@@ -12835,7 +12837,8 @@
2021 /* Option functions */
2022
2023 set:
2024- SET opt_option
2025+ SET statement_set {}
2026+ | SET opt_option
2027 {
2028 LEX *lex=Lex;
2029 lex->sql_command= SQLCOM_SET_OPTION;
2030@@ -12854,6 +12857,37 @@
2031 | OPTION {}
2032 ;
2033
2034+statement_set:
2035+ STATEMENT_SYM
2036+ {
2037+ LEX *lex= Lex;
2038+ mysql_init_select(lex);
2039+ }
2040+ simple_option_type_list FOR_SYM statement {}
2041+ ;
2042+
2043+simple_option_type_value:
2044+ internal_variable_name equal set_expr_or_default
2045+ {
2046+ if(!$1.var)
2047+ {
2048+ my_parse_error(ER(ER_SYNTAX_ERROR));
2049+ MYSQL_YYABORT;
2050+ }
2051+ LEX *lex= Lex;
2052+ set_var *var= new set_var(OPT_SESSION, $1.var, &$1.base_name, $3);
2053+
2054+ if (var == NULL)
2055+ MYSQL_YYABORT;
2056+ lex->stmt_set_list.push_back(var);
2057+ }
2058+ ;
2059+
2060+simple_option_type_list:
2061+ simple_option_type_value
2062+ | simple_option_type_list ',' simple_option_type_value
2063+ ;
2064+
2065 option_value_list:
2066 option_type_value
2067 | option_value_list ',' option_type_value

Subscribers

People subscribed via source and target branches