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
=== added file 'Percona-Server/mysql-test/r/percona_statement_set.result'
--- Percona-Server/mysql-test/r/percona_statement_set.result 1970-01-01 00:00:00 +0000
+++ Percona-Server/mysql-test/r/percona_statement_set.result 2012-04-26 09:29:20 +0000
@@ -0,0 +1,815 @@
1'# SET STATEMENT ..... FOR .... TEST'
2'# Setup database'
3CREATE TABLE t1 (v1 INT, v2 INT);
4INSERT INTO t1 VALUES (1,2);
5INSERT INTO t1 VALUES (3,4);
6''
7'#------------------ STATEMENT Test 1 -----------------------#'
8'# Initialize variables to known setting'
9SET SESSION sort_buffer_size=100000;
10''
11'# Pre-STATEMENT variable value'
12SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
13Variable_name Value
14sort_buffer_size 100000
15SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t1;
16v1 v2
171 2
183 4
19''
20'# Post-STATEMENT variable value'
21SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
22Variable_name Value
23sort_buffer_size 100000
24''
25'#------------------ STATEMENT Test 2 -----------------------#'
26'# Initialize variables to known setting'
27SET SESSION binlog_format=mixed;
28SET SESSION sort_buffer_size=100000;
29'# Pre-STATEMENT variable value'
30SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
31Variable_name Value
32sort_buffer_size 100000
33SHOW SESSION VARIABLES LIKE 'binlog_format';
34Variable_name Value
35binlog_format MIXED
36SET STATEMENT sort_buffer_size=150000, binlog_format=row
37FOR SELECT * FROM t1;
38v1 v2
391 2
403 4
41'# Post-STATEMENT variable value'
42SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
43Variable_name Value
44sort_buffer_size 100000
45SHOW SESSION VARIABLES LIKE 'binlog_format';
46Variable_name Value
47binlog_format MIXED
48''
49'#------------------ STATEMENT Test 3 -----------------------#'
50'# set initial variable value, make prepared statement
51SET SESSION binlog_format=row;
52PREPARE stmt1 FROM 'SET STATEMENT binlog_format=row FOR SELECT * FROM t1';
53''
54'# Change variable setting'
55SET SESSION binlog_format=mixed;
56''
57'# Pre-STATEMENT variable value'
58''
59SHOW SESSION VARIABLES LIKE 'binlog_format';
60Variable_name Value
61binlog_format MIXED
62''
63EXECUTE stmt1;
64v1 v2
651 2
663 4
67''
68'# Post-STATEMENT variable value'
69SHOW SESSION VARIABLES LIKE 'binlog_format';
70Variable_name Value
71binlog_format MIXED
72''
73DEALLOCATE PREPARE stmt1;
74'#------------------ STATEMENT Test 4 -----------------------#'
75'# set initial variable value, make prepared statement
76SET SESSION myisam_sort_buffer_size=500000, myisam_repair_threads=1;
77''
78'# Pre-STATEMENT variable value'
79SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
80Variable_name Value
81myisam_sort_buffer_size 500000
82SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
83Variable_name Value
84myisam_repair_threads 1
85''
86SET STATEMENT myisam_sort_buffer_size=800000,
87myisam_repair_threads=2 FOR OPTIMIZE TABLE t1;
88Table Op Msg_type Msg_text
89test.t1 optimize status OK
90''
91'# Post-STATEMENT variable value'
92SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
93Variable_name Value
94myisam_sort_buffer_size 500000
95SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
96Variable_name Value
97myisam_repair_threads 1
98''
99'#------------------ STATEMENT Test 5 -----------------------#'
100'# Initialize variables to known setting'
101SET SESSION sort_buffer_size=100000;
102''
103'# Pre-STATEMENT variable value'
104SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
105Variable_name Value
106sort_buffer_size 100000
107''
108SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t2;
109ERROR 42S02: Table 'test.t2' doesn't exist
110''
111'# Post-STATEMENT variable value'
112SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
113Variable_name Value
114sort_buffer_size 100000
115''
116'#------------------ STATEMENT Test 6 -----------------------#'
117'# Initialize variables to known setting'
118SET SESSION keep_files_on_create=ON;
119''
120'# Pre-STATEMENT variable value'
121SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
122Variable_name Value
123keep_files_on_create ON
124''
125SET STATEMENT keep_files_on_create=OFF FOR SELECT * FROM t1;
126v1 v2
1271 2
1283 4
129''
130'# Post-STATEMENT variable value'
131SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
132Variable_name Value
133keep_files_on_create ON
134''
135'#------------------ STATEMENT Test 7 -----------------------#'
136'# Initialize variables to known setting'
137SET SESSION max_join_size=2222220000000;
138''
139'# Pre-STATEMENT variable value'
140SHOW SESSION VARIABLES LIKE 'max_join_size';
141Variable_name Value
142max_join_size 2222220000000
143''
144SET STATEMENT max_join_size=1000000000000 FOR SELECT * FROM t1;
145v1 v2
1461 2
1473 4
148''
149'# Post-STATEMENT variable value'
150SHOW SESSION VARIABLES LIKE 'max_join_size';
151Variable_name Value
152max_join_size 2222220000000
153''
154'#------------------Test 8-----------------------#'
155'# Initialize test variables'
156SET SESSION myisam_sort_buffer_size=500000,
157myisam_repair_threads=1,
158sort_buffer_size = 200000,
159max_join_size=2222220000000,
160keep_files_on_create=ON;
161''
162'# LONG '
163SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
164Variable_name Value
165sort_buffer_size 200000
166SET STATEMENT sort_buffer_size = 100000
167FOR SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
168Variable_name Value
169sort_buffer_size 100000
170SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
171Variable_name Value
172sort_buffer_size 200000
173''
174'# MY_BOOL '
175SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
176Variable_name Value
177keep_files_on_create ON
178SET STATEMENT keep_files_on_create=OFF
179FOR SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
180Variable_name Value
181keep_files_on_create OFF
182SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
183Variable_name Value
184keep_files_on_create ON
185''
186'# INT/LONG '
187SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
188Variable_name Value
189myisam_repair_threads 1
190SET STATEMENT myisam_repair_threads=2
191FOR SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
192Variable_name Value
193myisam_repair_threads 2
194SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
195Variable_name Value
196myisam_repair_threads 1
197''
198'# ULONGLONG '
199SHOW SESSION VARIABLES LIKE 'max_join_size';
200Variable_name Value
201max_join_size 2222220000000
202SET STATEMENT max_join_size=2000000000000
203FOR SHOW SESSION VARIABLES LIKE 'max_join_size';
204Variable_name Value
205max_join_size 2000000000000
206SHOW SESSION VARIABLES LIKE 'max_join_size';
207Variable_name Value
208max_join_size 2222220000000
209''
210'#------------------Test 9-----------------------#'
211'# set initial variable values
212SET SESSION myisam_sort_buffer_size=500000,
213myisam_repair_threads=1,
214sort_buffer_size=100000,
215binlog_format=mixed,
216keep_files_on_create=ON,
217max_join_size=2222220000000;
218''
219''
220'# Pre-STATEMENT variable value
221SELECT @@myisam_sort_buffer_size,
222@@myisam_repair_threads,
223@@sort_buffer_size,
224@@binlog_format,
225@@keep_files_on_create,
226@@max_join_size;
227@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
228500000 1 100000 MIXED 1 2222220000000
229''
230''
231CREATE FUNCTION myProc (cost DECIMAL(10,2))
232RETURNS DECIMAL(10,2)
233SQL SECURITY DEFINER
234tax: BEGIN
235DECLARE order_tax DECIMAL(10,2);
236SET order_tax = cost * .05;
237RETURN order_tax;
238END|
239''
240'# During Execution values
241SET STATEMENT myisam_sort_buffer_size=400000,
242myisam_repair_threads=2,
243sort_buffer_size=200000,
244binlog_format=row,
245keep_files_on_create=OFF,
246max_join_size=4444440000000 FOR
247SELECT myProc(123.45);
248myProc(123.45)
2496.17
250Warnings:
251Note 1265 Data truncated for column 'order_tax' at row 1
252''
253'# Post-STATEMENT No 1 variable value Pre-STATEMENT for No 2'
254SELECT @@myisam_sort_buffer_size,
255@@myisam_repair_threads,
256@@sort_buffer_size,
257@@binlog_format,
258@@keep_files_on_create,
259@@max_join_size;
260@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
261500000 1 100000 MIXED 1 2222220000000
262''
263SET STATEMENT myisam_sort_buffer_size=400000,
264myisam_repair_threads=2,
265sort_buffer_size=200000,
266binlog_format=row,
267keep_files_on_create=OFF,
268max_join_size=4444440000000 FOR
269DROP FUNCTION myProc;
270''
271'# Post-STATEMENT No 2 variable value
272SELECT @@myisam_sort_buffer_size,
273@@myisam_repair_threads,
274@@sort_buffer_size,
275@@binlog_format,
276@@keep_files_on_create,
277@@max_join_size;
278@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
279500000 1 100000 MIXED 1 2222220000000
280''
281'#------------------Test 10-----------------------#'
282'# set initial variable values
283SET SESSION myisam_sort_buffer_size=500000,
284myisam_repair_threads=1,
285sort_buffer_size=100000,
286binlog_format=mixed,
287keep_files_on_create=ON,
288max_join_size=2222220000000;
289''
290'# Pre-STATEMENT variable value
291SELECT @@myisam_sort_buffer_size,
292@@myisam_repair_threads,
293@@sort_buffer_size,
294@@binlog_format,
295@@keep_files_on_create,
296@@max_join_size;
297@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
298500000 1 100000 MIXED 1 2222220000000
299''
300''
301SET STATEMENT myisam_sort_buffer_size=400000,
302myisam_repair_threads=2,
303sort_buffer_size=200000,
304binlog_format=row,
305keep_files_on_create=OFF,
306max_join_size=4444440000000 FOR
307PREPARE stmt2
308FROM 'SELECT * FROM t1';
309''
310'Test No 1 Post Value & Test 2 Pre values'
311SELECT @@myisam_sort_buffer_size,
312@@myisam_repair_threads,
313@@sort_buffer_size,
314@@binlog_format,
315@@keep_files_on_create,
316@@max_join_size;
317@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
318500000 1 100000 MIXED 1 2222220000000
319''
320''
321SET STATEMENT myisam_sort_buffer_size=400000,
322myisam_repair_threads=2,
323sort_buffer_size=200000,
324binlog_format=row,
325keep_files_on_create=OFF,
326max_join_size=4444440000000 FOR
327EXECUTE stmt2;
328v1 v2
3291 2
3303 4
331''
332'# Post-STATEMENT No 2
333SELECT @@myisam_sort_buffer_size,
334@@myisam_repair_threads,
335@@sort_buffer_size,
336@@binlog_format,
337@@keep_files_on_create,
338@@max_join_size;
339@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
340500000 1 100000 MIXED 1 2222220000000
341''
342DEALLOCATE PREPARE stmt2;
343''
344'#------------------Test 11-----------------------#'
345'# set initial variable values
346SET SESSION myisam_sort_buffer_size=500000,
347myisam_repair_threads=1,
348sort_buffer_size=100000,
349binlog_format=mixed,
350keep_files_on_create=ON,
351max_join_size=2222220000000;
352''
353''
354'# Pre-STATEMENT variable value
355SELECT @@myisam_sort_buffer_size,
356@@myisam_repair_threads,
357@@sort_buffer_size,
358@@binlog_format,
359@@keep_files_on_create,
360@@max_join_size;
361@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
362500000 1 100000 MIXED 1 2222220000000
363''
364''
365SET STATEMENT myisam_sort_buffer_size=400000,
366myisam_repair_threads=2,
367sort_buffer_size=200000,
368keep_files_on_create=OFF,
369max_join_size=4444440000000 FOR
370PREPARE stmt1 FROM
371'SET STATEMENT binlog_format=row FOR SELECT * FROM t1';
372''
373'Test No 1 Post Value & Test 2 Pre values'
374SELECT @@myisam_sort_buffer_size,
375@@myisam_repair_threads,
376@@sort_buffer_size,
377@@binlog_format,
378@@keep_files_on_create,
379@@max_join_size;
380@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
381500000 1 100000 MIXED 1 2222220000000
382''
383''
384SET STATEMENT myisam_sort_buffer_size=400000,
385myisam_repair_threads=2,
386sort_buffer_size=200000,
387keep_files_on_create=OFF,
388max_join_size=4444440000000 FOR
389EXECUTE stmt1;
390v1 v2
3911 2
3923 4
393''
394'# Post-STATEMENT No 2
395SELECT @@myisam_sort_buffer_size,
396@@myisam_repair_threads,
397@@sort_buffer_size,
398@@binlog_format,
399@@keep_files_on_create,
400@@max_join_size;
401@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
402500000 1 100000 MIXED 1 2222220000000
403''
404''
405'#------------------Test 12-----------------------#'
406'# set initial variable values
407SET SESSION myisam_sort_buffer_size=500000,
408myisam_repair_threads=1,
409sort_buffer_size=100000,
410binlog_format=mixed,
411keep_files_on_create=ON,
412max_join_size=2222220000000;
413''
414''
415'# Pre-STATEMENT variable value
416SELECT @@myisam_sort_buffer_size,
417@@myisam_repair_threads,
418@@sort_buffer_size,
419@@binlog_format,
420@@keep_files_on_create,
421@@max_join_size;
422@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
423500000 1 100000 MIXED 1 2222220000000
424''
425''
426SET STATEMENT myisam_sort_buffer_size=400000,
427myisam_repair_threads=2,
428sort_buffer_size=200000,
429binlog_format=row,
430keep_files_on_create=OFF,
431max_join_size=4444440000000 FOR
432CREATE PROCEDURE p1() BEGIN
433SELECT @@myisam_sort_buffer_size,
434@@myisam_repair_threads,
435@@sort_buffer_size,
436@@binlog_format,
437@@keep_files_on_create,
438@@max_join_size;
439END|
440''
441'Test No 1 Post Value & Test 2 Pre values'
442SELECT @@myisam_sort_buffer_size,
443@@myisam_repair_threads,
444@@sort_buffer_size,
445@@binlog_format,
446@@keep_files_on_create,
447@@max_join_size;
448@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
449500000 1 100000 MIXED 1 2222220000000
450''
451''
452SET STATEMENT myisam_sort_buffer_size=400000,
453myisam_repair_threads=2,
454sort_buffer_size=200000,
455binlog_format=row,
456keep_files_on_create=OFF,
457max_join_size=4444440000000 FOR
458CALL p1();
459@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
460400000 2 200000 ROW 0 4444440000000
461''
462'# Post-STATEMENT No 2
463SELECT @@myisam_sort_buffer_size,
464@@myisam_repair_threads,
465@@sort_buffer_size,
466@@binlog_format,
467@@keep_files_on_create,
468@@max_join_size;
469@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
470500000 1 100000 MIXED 1 2222220000000
471''
472''
473'#------------------Test 13-----------------------#'
474'# set initial variable values
475SET SESSION myisam_sort_buffer_size=500000,
476myisam_repair_threads=1,
477sort_buffer_size=100000,
478binlog_format=mixed,
479keep_files_on_create=ON,
480max_join_size=2222220000000;
481''
482''
483CREATE PROCEDURE p2() BEGIN
484SET STATEMENT myisam_sort_buffer_size=400000,
485myisam_repair_threads=3,
486sort_buffer_size=300000,
487binlog_format=mixed,
488keep_files_on_create=OFF,
489max_join_size=3333330000000 FOR
490CALL p1();
491END|
492''
493'# Pre-STATEMENT variable value
494SELECT @@myisam_sort_buffer_size,
495@@myisam_repair_threads,
496@@sort_buffer_size,
497@@binlog_format,
498@@keep_files_on_create,
499@@max_join_size;
500@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
501500000 1 100000 MIXED 1 2222220000000
502''
503''
504SET STATEMENT myisam_sort_buffer_size=400000,
505myisam_repair_threads=2,
506sort_buffer_size=200000,
507binlog_format=row,
508keep_files_on_create=OFF,
509max_join_size=4444440000000 FOR
510CALL p2();
511@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
512400000 3 300000 MIXED 0 3333330000000
513''
514'# Post-STATEMENT
515SELECT @@myisam_sort_buffer_size,
516@@myisam_repair_threads,
517@@sort_buffer_size,
518@@binlog_format,
519@@keep_files_on_create,
520@@max_join_size;
521@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
522500000 1 100000 MIXED 1 2222220000000
523''
524''
525'#------------------Test 14-----------------------#'
526'# set initial variable values
527SET SESSION myisam_sort_buffer_size=500000,
528myisam_repair_threads=1,
529sort_buffer_size=100000,
530binlog_format=mixed,
531keep_files_on_create=ON,
532max_join_size=2222220000000;
533''
534''
535CREATE PROCEDURE p3() BEGIN
536SELECT @@myisam_sort_buffer_size,
537@@myisam_repair_threads,
538@@sort_buffer_size,
539@@binlog_format,
540@@keep_files_on_create,
541@@max_join_size;
542SET STATEMENT myisam_sort_buffer_size=320000,
543myisam_repair_threads=2,
544sort_buffer_size=220022,
545binlog_format=row,
546keep_files_on_create=ON,
547max_join_size=2222220000000 FOR
548CALL p2();
549END|
550''
551'# Pre-STATEMENT variable value
552SELECT @@myisam_sort_buffer_size,
553@@myisam_repair_threads,
554@@sort_buffer_size,
555@@binlog_format,
556@@keep_files_on_create,
557@@max_join_size;
558@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
559500000 1 100000 MIXED 1 2222220000000
560''
561''
562SET STATEMENT myisam_sort_buffer_size=400000,
563myisam_repair_threads=2,
564sort_buffer_size=200000,
565binlog_format=row,
566keep_files_on_create=OFF,
567max_join_size=4444440000000 FOR
568CALL p3();
569@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
570400000 2 200000 ROW 0 4444440000000
571@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
572400000 3 300000 MIXED 0 3333330000000
573''
574'# Post-STATEMENT
575SELECT @@myisam_sort_buffer_size,
576@@myisam_repair_threads,
577@@sort_buffer_size,
578@@binlog_format,
579@@keep_files_on_create,
580@@max_join_size;
581@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
582500000 1 100000 MIXED 1 2222220000000
583''
584''
585''
586''
587'#------------------Test 15-----------------------#'
588'# set initial variable values
589SET SESSION myisam_sort_buffer_size=500000,
590myisam_repair_threads=1,
591sort_buffer_size=100000,
592binlog_format=mixed,
593keep_files_on_create=ON,
594max_join_size=2222220000000;
595''
596''
597CREATE PROCEDURE p4() BEGIN
598SELECT @@myisam_sort_buffer_size,
599@@myisam_repair_threads,
600@@sort_buffer_size,
601@@binlog_format,
602@@keep_files_on_create,
603@@max_join_size;
604SET STATEMENT myisam_sort_buffer_size=320000,
605myisam_repair_threads=2,
606sort_buffer_size=220022,
607binlog_format=row,
608keep_files_on_create=ON,
609max_join_size=2222220000000 FOR
610SELECT @@myisam_sort_buffer_size,
611@@myisam_repair_threads,
612@@sort_buffer_size,
613@@binlog_format,
614@@keep_files_on_create,
615@@max_join_size;
616SET STATEMENT myisam_sort_buffer_size=320000,
617myisam_repair_threads=2,
618sort_buffer_size=220022,
619binlog_format=row,
620keep_files_on_create=ON,
621max_join_size=2222220000000 FOR
622SELECT @@myisam_sort_buffer_size,
623@@myisam_repair_threads,
624@@sort_buffer_size,
625@@binlog_format,
626@@keep_files_on_create,
627@@max_join_size;
628SET STATEMENT myisam_sort_buffer_size=320000,
629myisam_repair_threads=2,
630sort_buffer_size=220022,
631binlog_format=row,
632keep_files_on_create=ON,
633max_join_size=2222220000000 FOR
634SELECT @@myisam_sort_buffer_size,
635@@myisam_repair_threads,
636@@sort_buffer_size,
637@@binlog_format,
638@@keep_files_on_create,
639@@max_join_size;
640END|
641''
642'# Pre-STATEMENT variable value
643SELECT @@myisam_sort_buffer_size,
644@@myisam_repair_threads,
645@@sort_buffer_size,
646@@binlog_format,
647@@keep_files_on_create,
648@@max_join_size;
649@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
650500000 1 100000 MIXED 1 2222220000000
651''
652''
653SET STATEMENT myisam_sort_buffer_size=400000,
654myisam_repair_threads=2,
655sort_buffer_size=200000,
656binlog_format=row,
657keep_files_on_create=OFF,
658max_join_size=4444440000000 FOR
659CALL p4();
660@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
661400000 2 200000 ROW 0 4444440000000
662@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
663320000 2 220022 ROW 1 2222220000000
664@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
665320000 2 220022 ROW 1 2222220000000
666@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
667320000 2 220022 ROW 1 2222220000000
668''
669'# Post-STATEMENT
670SELECT @@myisam_sort_buffer_size,
671@@myisam_repair_threads,
672@@sort_buffer_size,
673@@binlog_format,
674@@keep_files_on_create,
675@@max_join_size;
676@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
677500000 1 100000 MIXED 1 2222220000000
678''
679''
680'#------------------Test 16-----------------------#'
681''
682'# Pre-STATEMENT variable value
683SELECT @@sql_mode;
684@@sql_mode
685
686''
687''
688SET STATEMENT sql_mode='ansi' FOR SELECT * FROM t1;
689v1 v2
6901 2
6913 4
692''
693'# Post-STATEMENT
694SELECT @@sql_mode;
695@@sql_mode
696
697''
698''
699'#------------------Test 17-----------------------#'
700'# set initial variable values
701SET SESSION myisam_sort_buffer_size=500000,
702myisam_repair_threads=1,
703sort_buffer_size=100000,
704binlog_format=mixed,
705keep_files_on_create=ON,
706max_join_size=2222220000000;
707''
708'# Pre-STATEMENT variable value
709SELECT @@myisam_sort_buffer_size,
710@@myisam_repair_threads,
711@@sort_buffer_size,
712@@binlog_format,
713@@keep_files_on_create,
714@@max_join_size;
715@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
716500000 1 100000 MIXED 1 2222220000000
717''
718''
719SET STATEMENT myisam_sort_buffer_size=320000,
720myisam_repair_threads=2,
721sort_buffer_size=220022,
722binlog_format=row,
723keep_files_on_create=ON,
724max_join_size=2222220000000
725FOR SET SESSION
726myisam_sort_buffer_size=260000,
727myisam_repair_threads=3,
728sort_buffer_size=230013,
729binlog_format=row,
730keep_files_on_create=ON,
731max_join_size=2323230000000;
732''
733'# Post-STATEMENT
734SELECT @@myisam_sort_buffer_size,
735@@myisam_repair_threads,
736@@sort_buffer_size,
737@@binlog_format,
738@@keep_files_on_create,
739@@max_join_size;
740@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
741500000 1 100000 MIXED 1 2222220000000
742''
743''
744'#------------------Test 18-----------------------#'
745'# set initial variable values
746SET SESSION myisam_sort_buffer_size=500000,
747myisam_repair_threads=1,
748sort_buffer_size=100000,
749binlog_format=mixed,
750keep_files_on_create=ON,
751max_join_size=2222220000000;
752''
753'# Pre-STATEMENT variable value
754SELECT @@myisam_sort_buffer_size,
755@@myisam_repair_threads,
756@@sort_buffer_size,
757@@binlog_format,
758@@keep_files_on_create,
759@@max_join_size;
760@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
761500000 1 100000 MIXED 1 2222220000000
762''
763''
764CREATE PROCEDURE p5() BEGIN
765SELECT @@myisam_sort_buffer_size,
766@@myisam_repair_threads,
767@@sort_buffer_size,
768@@binlog_format,
769@@keep_files_on_create,
770@@max_join_size;
771SET SESSION
772myisam_sort_buffer_size=260000,
773myisam_repair_threads=3,
774sort_buffer_size=230013,
775binlog_format=row,
776keep_files_on_create=ON,
777max_join_size=2323230000000;
778SELECT @@myisam_sort_buffer_size,
779@@myisam_repair_threads,
780@@sort_buffer_size,
781@@binlog_format,
782@@keep_files_on_create,
783@@max_join_size;
784END|
785''
786''
787SET STATEMENT myisam_sort_buffer_size=400000,
788myisam_repair_threads=2,
789sort_buffer_size=200000,
790binlog_format=row,
791keep_files_on_create=OFF,
792max_join_size=4444440000000 FOR
793CALL p5();
794@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
795400000 2 200000 ROW 0 4444440000000
796@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
797260000 3 230013 ROW 1 2323230000000
798''
799'# Post-STATEMENT
800SELECT @@myisam_sort_buffer_size,
801@@myisam_repair_threads,
802@@sort_buffer_size,
803@@binlog_format,
804@@keep_files_on_create,
805@@max_join_size;
806@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
807500000 1 100000 MIXED 1 2222220000000
808''
809'# Cleanup'
810DROP TABLE t1;
811DROP PROCEDURE p1;
812DROP PROCEDURE p2;
813DROP PROCEDURE p3;
814DROP PROCEDURE p4;
815DROP PROCEDURE p5;
0816
=== added file 'Percona-Server/mysql-test/t/percona_statement_set.test'
--- Percona-Server/mysql-test/t/percona_statement_set.test 1970-01-01 00:00:00 +0000
+++ Percona-Server/mysql-test/t/percona_statement_set.test 2012-04-26 09:29:20 +0000
@@ -0,0 +1,778 @@
1--echo '# SET STATEMENT ..... FOR .... TEST'
2############################ STATEMENT_SET #############################
3# #
4# Testing working functionality of SET STATEMENT #
5# #
6# #
7# There is important documentation within #
8# #
9# #
10# Author: Joe Lukas #
11# Creation: #
12# 2009-08-02 Implement this test as part of #
13# WL#681 Per query variable settings #
14# #
15########################################################################
16
17
18####################################################################
19# Set up current database #
20####################################################################
21--echo '# Setup database'
22CREATE TABLE t1 (v1 INT, v2 INT);
23INSERT INTO t1 VALUES (1,2);
24INSERT INTO t1 VALUES (3,4);
25--echo ''
26--echo '#------------------ STATEMENT Test 1 -----------------------#'
27####################################################################
28# Checks with variable value type ulong #
29####################################################################
30--echo '# Initialize variables to known setting'
31SET SESSION sort_buffer_size=100000;
32--echo ''
33--echo '# Pre-STATEMENT variable value'
34SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
35SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t1;
36--echo ''
37--echo '# Post-STATEMENT variable value'
38SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
39--echo ''
40--echo '#------------------ STATEMENT Test 2 -----------------------#'
41####################################################################
42# Checks for multiple set values inside STATEMENT ... FOR #
43####################################################################
44--echo '# Initialize variables to known setting'
45SET SESSION binlog_format=mixed;
46SET SESSION sort_buffer_size=100000;
47--echo '# Pre-STATEMENT variable value'
48SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
49SHOW SESSION VARIABLES LIKE 'binlog_format';
50SET STATEMENT sort_buffer_size=150000, binlog_format=row
51 FOR SELECT * FROM t1;
52--echo '# Post-STATEMENT variable value'
53SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
54SHOW SESSION VARIABLES LIKE 'binlog_format';
55
56--echo ''
57--echo '#------------------ STATEMENT Test 3 -----------------------#'
58####################################################################
59# Check current variable value is stored in using stored #
60# statements. #
61####################################################################
62--echo '# set initial variable value, make prepared statement
63SET SESSION binlog_format=row;
64PREPARE stmt1 FROM 'SET STATEMENT binlog_format=row FOR SELECT * FROM t1';
65--echo ''
66--echo '# Change variable setting'
67SET SESSION binlog_format=mixed;
68--echo ''
69--echo '# Pre-STATEMENT variable value'
70--echo ''
71SHOW SESSION VARIABLES LIKE 'binlog_format';
72--echo ''
73EXECUTE stmt1;
74--echo ''
75--echo '# Post-STATEMENT variable value'
76SHOW SESSION VARIABLES LIKE 'binlog_format';
77
78--echo ''
79DEALLOCATE PREPARE stmt1;
80--echo '#------------------ STATEMENT Test 4 -----------------------#'
81####################################################################
82# Check works with OPTIMIZE TABLE command #
83# Checks works with a variable value of type INT #
84# Checks works with variable type ULONGLONG #
85####################################################################
86--echo '# set initial variable value, make prepared statement
87SET SESSION myisam_sort_buffer_size=500000, myisam_repair_threads=1;
88--echo ''
89--echo '# Pre-STATEMENT variable value'
90SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
91SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
92--echo ''
93SET STATEMENT myisam_sort_buffer_size=800000,
94 myisam_repair_threads=2 FOR OPTIMIZE TABLE t1;
95--echo ''
96--echo '# Post-STATEMENT variable value'
97SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
98SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
99
100--echo ''
101--echo '#------------------ STATEMENT Test 5 -----------------------#'
102####################################################################
103# Checks if variable reset after error in statement after FOR #
104####################################################################
105--echo '# Initialize variables to known setting'
106SET SESSION sort_buffer_size=100000;
107--echo ''
108--echo '# Pre-STATEMENT variable value'
109SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
110--echo ''
111--error ER_NO_SUCH_TABLE
112SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t2;
113--echo ''
114--echo '# Post-STATEMENT variable value'
115SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
116
117--echo ''
118--echo '#------------------ STATEMENT Test 6 -----------------------#'
119####################################################################
120# Checks works with variable type MY_BOOL #
121####################################################################
122--echo '# Initialize variables to known setting'
123SET SESSION keep_files_on_create=ON;
124--echo ''
125--echo '# Pre-STATEMENT variable value'
126SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
127--echo ''
128SET STATEMENT keep_files_on_create=OFF FOR SELECT * FROM t1;
129--echo ''
130--echo '# Post-STATEMENT variable value'
131SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
132
133--echo ''
134--echo '#------------------ STATEMENT Test 7 -----------------------#'
135####################################################################
136# Checks works with variable type HA_ROWS #
137####################################################################
138--echo '# Initialize variables to known setting'
139SET SESSION max_join_size=2222220000000;
140--echo ''
141--echo '# Pre-STATEMENT variable value'
142SHOW SESSION VARIABLES LIKE 'max_join_size';
143--echo ''
144SET STATEMENT max_join_size=1000000000000 FOR SELECT * FROM t1;
145--echo ''
146--echo '# Post-STATEMENT variable value'
147SHOW SESSION VARIABLES LIKE 'max_join_size';
148
149--echo ''
150--echo '#------------------Test 8-----------------------#'
151####################################################################
152# Ensure variable of each type is set to proper value during #
153# statement after FOR execution #
154####################################################################
155--echo '# Initialize test variables'
156SET SESSION myisam_sort_buffer_size=500000,
157 myisam_repair_threads=1,
158 sort_buffer_size = 200000,
159 max_join_size=2222220000000,
160 keep_files_on_create=ON;
161
162--echo ''
163--echo '# LONG '
164SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
165SET STATEMENT sort_buffer_size = 100000
166 FOR SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
167SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
168--echo ''
169--echo '# MY_BOOL '
170SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
171SET STATEMENT keep_files_on_create=OFF
172 FOR SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
173SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
174
175--echo ''
176--echo '# INT/LONG '
177SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
178SET STATEMENT myisam_repair_threads=2
179 FOR SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
180SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
181--echo ''
182--echo '# ULONGLONG '
183SHOW SESSION VARIABLES LIKE 'max_join_size';
184SET STATEMENT max_join_size=2000000000000
185 FOR SHOW SESSION VARIABLES LIKE 'max_join_size';
186SHOW SESSION VARIABLES LIKE 'max_join_size';
187
188--echo ''
189--echo '#------------------Test 9-----------------------#'
190####################################################################
191# No 1 - Check works with CREATE ... BEGIN ... END command #
192# Display variables during execution #
193# No 2 - Test with DROP command #
194####################################################################
195--echo '# set initial variable values
196SET SESSION myisam_sort_buffer_size=500000,
197 myisam_repair_threads=1,
198 sort_buffer_size=100000,
199 binlog_format=mixed,
200 keep_files_on_create=ON,
201 max_join_size=2222220000000;
202--echo ''
203--echo ''
204--echo '# Pre-STATEMENT variable value
205SELECT @@myisam_sort_buffer_size,
206 @@myisam_repair_threads,
207 @@sort_buffer_size,
208 @@binlog_format,
209 @@keep_files_on_create,
210 @@max_join_size;
211--echo ''
212--echo ''
213DELIMITER |;
214CREATE FUNCTION myProc (cost DECIMAL(10,2))
215 RETURNS DECIMAL(10,2)
216
217 SQL SECURITY DEFINER
218
219 tax: BEGIN
220 DECLARE order_tax DECIMAL(10,2);
221 SET order_tax = cost * .05;
222 RETURN order_tax;
223 END|
224DELIMITER ;|
225--echo ''
226--echo '# During Execution values
227SET STATEMENT myisam_sort_buffer_size=400000,
228 myisam_repair_threads=2,
229 sort_buffer_size=200000,
230 binlog_format=row,
231 keep_files_on_create=OFF,
232 max_join_size=4444440000000 FOR
233 SELECT myProc(123.45);
234--echo ''
235--echo '# Post-STATEMENT No 1 variable value Pre-STATEMENT for No 2'
236SELECT @@myisam_sort_buffer_size,
237 @@myisam_repair_threads,
238 @@sort_buffer_size,
239 @@binlog_format,
240 @@keep_files_on_create,
241 @@max_join_size;
242--echo ''
243SET STATEMENT myisam_sort_buffer_size=400000,
244 myisam_repair_threads=2,
245 sort_buffer_size=200000,
246 binlog_format=row,
247 keep_files_on_create=OFF,
248 max_join_size=4444440000000 FOR
249 DROP FUNCTION myProc;
250--echo ''
251--echo '# Post-STATEMENT No 2 variable value
252SELECT @@myisam_sort_buffer_size,
253 @@myisam_repair_threads,
254 @@sort_buffer_size,
255 @@binlog_format,
256 @@keep_files_on_create,
257 @@max_join_size;
258
259--echo ''
260--echo '#------------------Test 10-----------------------#'
261####################################################################
262# No 1 - Check with PREPARE statement #
263# with STATEMENT inside with same variable as outside #
264# No 2 - Check with EXECUTE statement #
265####################################################################
266--echo '# set initial variable values
267SET SESSION myisam_sort_buffer_size=500000,
268 myisam_repair_threads=1,
269 sort_buffer_size=100000,
270 binlog_format=mixed,
271 keep_files_on_create=ON,
272 max_join_size=2222220000000;
273--echo ''
274--echo '# Pre-STATEMENT variable value
275SELECT @@myisam_sort_buffer_size,
276 @@myisam_repair_threads,
277 @@sort_buffer_size,
278 @@binlog_format,
279 @@keep_files_on_create,
280 @@max_join_size;
281--echo ''
282--echo ''
283SET STATEMENT myisam_sort_buffer_size=400000,
284 myisam_repair_threads=2,
285 sort_buffer_size=200000,
286 binlog_format=row,
287 keep_files_on_create=OFF,
288 max_join_size=4444440000000 FOR
289 PREPARE stmt2
290 FROM 'SELECT * FROM t1';
291--echo ''
292--echo 'Test No 1 Post Value & Test 2 Pre values'
293SELECT @@myisam_sort_buffer_size,
294 @@myisam_repair_threads,
295 @@sort_buffer_size,
296 @@binlog_format,
297 @@keep_files_on_create,
298 @@max_join_size;
299--echo ''
300--echo ''
301SET STATEMENT myisam_sort_buffer_size=400000,
302 myisam_repair_threads=2,
303 sort_buffer_size=200000,
304 binlog_format=row,
305 keep_files_on_create=OFF,
306 max_join_size=4444440000000 FOR
307 EXECUTE stmt2;
308--echo ''
309--echo '# Post-STATEMENT No 2
310SELECT @@myisam_sort_buffer_size,
311 @@myisam_repair_threads,
312 @@sort_buffer_size,
313 @@binlog_format,
314 @@keep_files_on_create,
315 @@max_join_size;
316--echo ''
317DEALLOCATE PREPARE stmt2;
318--echo ''
319--echo '#------------------Test 11-----------------------#'
320####################################################################
321# No 1 - Check with PREPARE statement #
322# check with different variable on inside PREPARE #
323# No 2 - Check with EXECUTE statement #
324####################################################################
325--echo '# set initial variable values
326SET SESSION myisam_sort_buffer_size=500000,
327 myisam_repair_threads=1,
328 sort_buffer_size=100000,
329 binlog_format=mixed,
330 keep_files_on_create=ON,
331 max_join_size=2222220000000;
332--echo ''
333--echo ''
334--echo '# Pre-STATEMENT variable value
335SELECT @@myisam_sort_buffer_size,
336 @@myisam_repair_threads,
337 @@sort_buffer_size,
338 @@binlog_format,
339 @@keep_files_on_create,
340 @@max_join_size;
341--echo ''
342--echo ''
343SET STATEMENT myisam_sort_buffer_size=400000,
344 myisam_repair_threads=2,
345 sort_buffer_size=200000,
346 keep_files_on_create=OFF,
347 max_join_size=4444440000000 FOR
348 PREPARE stmt1 FROM
349 'SET STATEMENT binlog_format=row FOR SELECT * FROM t1';
350--echo ''
351--echo 'Test No 1 Post Value & Test 2 Pre values'
352SELECT @@myisam_sort_buffer_size,
353 @@myisam_repair_threads,
354 @@sort_buffer_size,
355 @@binlog_format,
356 @@keep_files_on_create,
357 @@max_join_size;
358--echo ''
359--echo ''
360SET STATEMENT myisam_sort_buffer_size=400000,
361 myisam_repair_threads=2,
362 sort_buffer_size=200000,
363 keep_files_on_create=OFF,
364 max_join_size=4444440000000 FOR
365 EXECUTE stmt1;
366--echo ''
367--echo '# Post-STATEMENT No 2
368SELECT @@myisam_sort_buffer_size,
369 @@myisam_repair_threads,
370 @@sort_buffer_size,
371 @@binlog_format,
372 @@keep_files_on_create,
373 @@max_join_size;
374--echo ''
375--echo ''
376--echo '#------------------Test 12-----------------------#'
377####################################################################
378# No 1 - Check with PROCEDURE (show variables in procedure) #
379# No 2 - Check with CALL statement show variables in PROCEDURE #
380####################################################################
381--echo '# set initial variable values
382SET SESSION myisam_sort_buffer_size=500000,
383 myisam_repair_threads=1,
384 sort_buffer_size=100000,
385 binlog_format=mixed,
386 keep_files_on_create=ON,
387 max_join_size=2222220000000;
388--echo ''
389--echo ''
390--echo '# Pre-STATEMENT variable value
391SELECT @@myisam_sort_buffer_size,
392 @@myisam_repair_threads,
393 @@sort_buffer_size,
394 @@binlog_format,
395 @@keep_files_on_create,
396 @@max_join_size;
397--echo ''
398--echo ''
399DELIMITER |;
400SET STATEMENT myisam_sort_buffer_size=400000,
401 myisam_repair_threads=2,
402 sort_buffer_size=200000,
403 binlog_format=row,
404 keep_files_on_create=OFF,
405 max_join_size=4444440000000 FOR
406 CREATE PROCEDURE p1() BEGIN
407 SELECT @@myisam_sort_buffer_size,
408 @@myisam_repair_threads,
409 @@sort_buffer_size,
410 @@binlog_format,
411 @@keep_files_on_create,
412 @@max_join_size;
413 END|
414DELIMITER ;|
415--echo ''
416--echo 'Test No 1 Post Value & Test 2 Pre values'
417SELECT @@myisam_sort_buffer_size,
418 @@myisam_repair_threads,
419 @@sort_buffer_size,
420 @@binlog_format,
421 @@keep_files_on_create,
422 @@max_join_size;
423--echo ''
424--echo ''
425SET STATEMENT myisam_sort_buffer_size=400000,
426 myisam_repair_threads=2,
427 sort_buffer_size=200000,
428 binlog_format=row,
429 keep_files_on_create=OFF,
430 max_join_size=4444440000000 FOR
431 CALL p1();
432--echo ''
433--echo '# Post-STATEMENT No 2
434SELECT @@myisam_sort_buffer_size,
435 @@myisam_repair_threads,
436 @@sort_buffer_size,
437 @@binlog_format,
438 @@keep_files_on_create,
439 @@max_join_size;
440--echo ''
441--echo ''
442
443--echo '#------------------Test 13-----------------------#'
444####################################################################
445# Check PROCEDURE containing SET STATEMENT FOR #
446# p1() from test 12 will be used to display variables #
447####################################################################
448--echo '# set initial variable values
449SET SESSION myisam_sort_buffer_size=500000,
450 myisam_repair_threads=1,
451 sort_buffer_size=100000,
452 binlog_format=mixed,
453 keep_files_on_create=ON,
454 max_join_size=2222220000000;
455--echo ''
456--echo ''
457DELIMITER |;
458CREATE PROCEDURE p2() BEGIN
459 SET STATEMENT myisam_sort_buffer_size=400000,
460 myisam_repair_threads=3,
461 sort_buffer_size=300000,
462 binlog_format=mixed,
463 keep_files_on_create=OFF,
464 max_join_size=3333330000000 FOR
465 CALL p1();
466 END|
467DELIMITER ;|
468--echo ''
469--echo '# Pre-STATEMENT variable value
470SELECT @@myisam_sort_buffer_size,
471 @@myisam_repair_threads,
472 @@sort_buffer_size,
473 @@binlog_format,
474 @@keep_files_on_create,
475 @@max_join_size;
476--echo ''
477--echo ''
478SET STATEMENT myisam_sort_buffer_size=400000,
479 myisam_repair_threads=2,
480 sort_buffer_size=200000,
481 binlog_format=row,
482 keep_files_on_create=OFF,
483 max_join_size=4444440000000 FOR
484 CALL p2();
485--echo ''
486--echo '# Post-STATEMENT
487SELECT @@myisam_sort_buffer_size,
488 @@myisam_repair_threads,
489 @@sort_buffer_size,
490 @@binlog_format,
491 @@keep_files_on_create,
492 @@max_join_size;
493--echo ''
494--echo ''
495--echo '#------------------Test 14-----------------------#'
496####################################################################
497# Check PROCEDURE containing compound SET STATEMENT FOR #
498# p2() will be used as compounding statement from test 13 #
499####################################################################
500--echo '# set initial variable values
501SET SESSION myisam_sort_buffer_size=500000,
502 myisam_repair_threads=1,
503 sort_buffer_size=100000,
504 binlog_format=mixed,
505 keep_files_on_create=ON,
506 max_join_size=2222220000000;
507--echo ''
508--echo ''
509DELIMITER |;
510CREATE PROCEDURE p3() BEGIN
511 SELECT @@myisam_sort_buffer_size,
512 @@myisam_repair_threads,
513 @@sort_buffer_size,
514 @@binlog_format,
515 @@keep_files_on_create,
516 @@max_join_size;
517 SET STATEMENT myisam_sort_buffer_size=320000,
518 myisam_repair_threads=2,
519 sort_buffer_size=220022,
520 binlog_format=row,
521 keep_files_on_create=ON,
522 max_join_size=2222220000000 FOR
523 CALL p2();
524 END|
525DELIMITER ;|
526--echo ''
527--echo '# Pre-STATEMENT variable value
528SELECT @@myisam_sort_buffer_size,
529 @@myisam_repair_threads,
530 @@sort_buffer_size,
531 @@binlog_format,
532 @@keep_files_on_create,
533 @@max_join_size;
534--echo ''
535--echo ''
536SET STATEMENT myisam_sort_buffer_size=400000,
537 myisam_repair_threads=2,
538 sort_buffer_size=200000,
539 binlog_format=row,
540 keep_files_on_create=OFF,
541 max_join_size=4444440000000 FOR
542 CALL p3();
543--echo ''
544--echo '# Post-STATEMENT
545SELECT @@myisam_sort_buffer_size,
546 @@myisam_repair_threads,
547 @@sort_buffer_size,
548 @@binlog_format,
549 @@keep_files_on_create,
550 @@max_join_size;
551--echo ''
552--echo ''
553
554 --echo ''
555--echo ''
556--echo '#------------------Test 15-----------------------#'
557####################################################################
558# Check PROCEDURE containing compound SET STATEMENT FOR #
559# call multiple SET STATEMENT .. FOR showing SELECT #
560####################################################################
561--echo '# set initial variable values
562SET SESSION myisam_sort_buffer_size=500000,
563 myisam_repair_threads=1,
564 sort_buffer_size=100000,
565 binlog_format=mixed,
566 keep_files_on_create=ON,
567 max_join_size=2222220000000;
568--echo ''
569--echo ''
570DELIMITER |;
571CREATE PROCEDURE p4() BEGIN
572 SELECT @@myisam_sort_buffer_size,
573 @@myisam_repair_threads,
574 @@sort_buffer_size,
575 @@binlog_format,
576 @@keep_files_on_create,
577 @@max_join_size;
578 SET STATEMENT myisam_sort_buffer_size=320000,
579 myisam_repair_threads=2,
580 sort_buffer_size=220022,
581 binlog_format=row,
582 keep_files_on_create=ON,
583 max_join_size=2222220000000 FOR
584 SELECT @@myisam_sort_buffer_size,
585 @@myisam_repair_threads,
586 @@sort_buffer_size,
587 @@binlog_format,
588 @@keep_files_on_create,
589 @@max_join_size;
590 SET STATEMENT myisam_sort_buffer_size=320000,
591 myisam_repair_threads=2,
592 sort_buffer_size=220022,
593 binlog_format=row,
594 keep_files_on_create=ON,
595 max_join_size=2222220000000 FOR
596 SELECT @@myisam_sort_buffer_size,
597 @@myisam_repair_threads,
598 @@sort_buffer_size,
599 @@binlog_format,
600 @@keep_files_on_create,
601 @@max_join_size;
602 SET STATEMENT myisam_sort_buffer_size=320000,
603 myisam_repair_threads=2,
604 sort_buffer_size=220022,
605 binlog_format=row,
606 keep_files_on_create=ON,
607 max_join_size=2222220000000 FOR
608 SELECT @@myisam_sort_buffer_size,
609 @@myisam_repair_threads,
610 @@sort_buffer_size,
611 @@binlog_format,
612 @@keep_files_on_create,
613 @@max_join_size;
614 END|
615DELIMITER ;|
616--echo ''
617--echo '# Pre-STATEMENT variable value
618SELECT @@myisam_sort_buffer_size,
619 @@myisam_repair_threads,
620 @@sort_buffer_size,
621 @@binlog_format,
622 @@keep_files_on_create,
623 @@max_join_size;
624--echo ''
625--echo ''
626SET STATEMENT myisam_sort_buffer_size=400000,
627 myisam_repair_threads=2,
628 sort_buffer_size=200000,
629 binlog_format=row,
630 keep_files_on_create=OFF,
631 max_join_size=4444440000000 FOR
632 CALL p4();
633--echo ''
634--echo '# Post-STATEMENT
635SELECT @@myisam_sort_buffer_size,
636 @@myisam_repair_threads,
637 @@sort_buffer_size,
638 @@binlog_format,
639 @@keep_files_on_create,
640 @@max_join_size;
641
642--echo ''
643--echo ''
644--echo '#------------------Test 16-----------------------#'
645####################################################################
646# Test Effect on parsing #
647####################################################################
648--echo ''
649--echo '# Pre-STATEMENT variable value
650SELECT @@sql_mode;
651--echo ''
652--echo ''
653SET STATEMENT sql_mode='ansi' FOR SELECT * FROM t1;
654
655--echo ''
656--echo '# Post-STATEMENT
657SELECT @@sql_mode;
658--echo ''
659--echo ''
660--echo '#------------------Test 17-----------------------#'
661####################################################################
662# Test effect of SET STATEMENT FOR with SET SESSION modifying #
663# the same variables as the SET STATEMENT #
664####################################################################
665--echo '# set initial variable values
666SET SESSION myisam_sort_buffer_size=500000,
667 myisam_repair_threads=1,
668 sort_buffer_size=100000,
669 binlog_format=mixed,
670 keep_files_on_create=ON,
671 max_join_size=2222220000000;
672--echo ''
673--echo '# Pre-STATEMENT variable value
674SELECT @@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--echo ''
681--echo ''
682SET STATEMENT myisam_sort_buffer_size=320000,
683 myisam_repair_threads=2,
684 sort_buffer_size=220022,
685 binlog_format=row,
686 keep_files_on_create=ON,
687 max_join_size=2222220000000
688 FOR SET SESSION
689 myisam_sort_buffer_size=260000,
690 myisam_repair_threads=3,
691 sort_buffer_size=230013,
692 binlog_format=row,
693 keep_files_on_create=ON,
694 max_join_size=2323230000000;
695
696--echo ''
697--echo '# Post-STATEMENT
698SELECT @@myisam_sort_buffer_size,
699 @@myisam_repair_threads,
700 @@sort_buffer_size,
701 @@binlog_format,
702 @@keep_files_on_create,
703 @@max_join_size;
704
705--echo ''
706--echo ''
707--echo '#------------------Test 18-----------------------#'
708####################################################################
709# Test effect of SET SESSION inside a stored procedure with #
710# with a SET STATEMENT on outside variables #
711####################################################################
712--echo '# set initial variable values
713SET SESSION myisam_sort_buffer_size=500000,
714 myisam_repair_threads=1,
715 sort_buffer_size=100000,
716 binlog_format=mixed,
717 keep_files_on_create=ON,
718 max_join_size=2222220000000;
719--echo ''
720--echo '# Pre-STATEMENT variable value
721SELECT @@myisam_sort_buffer_size,
722 @@myisam_repair_threads,
723 @@sort_buffer_size,
724 @@binlog_format,
725 @@keep_files_on_create,
726 @@max_join_size;
727--echo ''
728--echo ''
729DELIMITER |;
730CREATE PROCEDURE p5() BEGIN
731 SELECT @@myisam_sort_buffer_size,
732 @@myisam_repair_threads,
733 @@sort_buffer_size,
734 @@binlog_format,
735 @@keep_files_on_create,
736 @@max_join_size;
737 SET SESSION
738 myisam_sort_buffer_size=260000,
739 myisam_repair_threads=3,
740 sort_buffer_size=230013,
741 binlog_format=row,
742 keep_files_on_create=ON,
743 max_join_size=2323230000000;
744 SELECT @@myisam_sort_buffer_size,
745 @@myisam_repair_threads,
746 @@sort_buffer_size,
747 @@binlog_format,
748 @@keep_files_on_create,
749 @@max_join_size;
750 END|
751DELIMITER ;|
752--echo ''
753--echo ''
754SET STATEMENT myisam_sort_buffer_size=400000,
755 myisam_repair_threads=2,
756 sort_buffer_size=200000,
757 binlog_format=row,
758 keep_files_on_create=OFF,
759 max_join_size=4444440000000 FOR
760 CALL p5();
761
762--echo ''
763--echo '# Post-STATEMENT
764SELECT @@myisam_sort_buffer_size,
765 @@myisam_repair_threads,
766 @@sort_buffer_size,
767 @@binlog_format,
768 @@keep_files_on_create,
769 @@max_join_size;
770
771--echo ''
772--echo '# Cleanup'
773DROP TABLE t1;
774DROP PROCEDURE p1;
775DROP PROCEDURE p2;
776DROP PROCEDURE p3;
777DROP PROCEDURE p4;
778DROP PROCEDURE p5;
0779
=== modified file 'Percona-Server/sql/lex.h'
--- Percona-Server/sql/lex.h 2012-04-18 23:26:01 +0000
+++ Percona-Server/sql/lex.h 2012-04-26 09:29:20 +0000
@@ -534,6 +534,7 @@
534 { "START", SYM(START_SYM)},534 { "START", SYM(START_SYM)},
535 { "STARTING", SYM(STARTING)},535 { "STARTING", SYM(STARTING)},
536 { "STARTS", SYM(STARTS_SYM)},536 { "STARTS", SYM(STARTS_SYM)},
537 { "STATEMENT", SYM(STATEMENT_SYM)},
537 { "STATUS", SYM(STATUS_SYM)},538 { "STATUS", SYM(STATUS_SYM)},
538 { "STOP", SYM(STOP_SYM)},539 { "STOP", SYM(STOP_SYM)},
539 { "STORAGE", SYM(STORAGE_SYM)},540 { "STORAGE", SYM(STORAGE_SYM)},
540541
=== modified file 'Percona-Server/sql/set_var.cc'
--- Percona-Server/sql/set_var.cc 2011-07-03 23:48:19 +0000
+++ Percona-Server/sql/set_var.cc 2012-04-26 09:29:20 +0000
@@ -797,3 +797,181 @@
797 return 0;797 return 0;
798}798}
799799
800
801/**
802 set_stmt_get_reset_vars function will retrieve the current value of
803 a SESSION variable and create a new set_var_base class item containing
804 the value and necessary update & check functions to be used to reset the
805 variable to a previous setting.
806
807 @param var set_var containing value of variable that will be change
808 too and holds name, name length, SESSION variable type, and
809 value_ptr() function to retrieve current value.
810
811 @param lex_var_list list of set_var_bases that are created to hold the
812 previous values and the update & check functions to be
813 used to return the variable to a previous setting
814
815 @return true if error occurs false if successful
816
817*/
818
819bool set_stmt_get_reset_vars(
820 THD *thd,
821 set_var * var,
822 List<set_var_base> *lex_var_list)
823{
824 bool res= false;
825 set_var *old_var= NULL;
826 LEX_STRING base;
827 char str[var->var->name.length];
828
829 strcpy(str, var->var->name.str);
830 base.str= str;
831 base.length= var->var->name.length;
832 /*
833 When per query variables are set, the "set_var" structure is created and
834 pushed into array. During "mysql_execute_command" this array is used for
835 preserving old variables values ans setting new values. But the main
836 trouble here is to get the old variable value. The matter is we can
837 operate only with "sys_var" structure which describes system variable.
838 This structure contains "value_ptr" method which uses the offset to count
839 the address of the variable. And the only field in "sys_var" which shows
840 the length of a variable is "const SHOW_TYPE show_val_type;". The current
841 implementation uses the "switch" for the "const SHOW_TYPE show_val_type"
842 field and considers only "int" and "char" types. I don't like this
843 approach because we have to add the processing of the new type into
844 several places. But it seems there is no another way except using "switch"
845 for all possible values of "SHOW_TYPE" enumeration.
846 */
847 switch (var->var->show_type())
848 {
849 case SHOW_INT:
850 {
851 uint value;
852 Item_int *item;
853 /*
854 We don't need to lock LOCK_global_system_variables here,
855 because it's impossible to have global-only variable in
856 var parameter due to syntax rules. So
857 SELECT STATEMENT GLOBAL variable_name FOR ...
858 or
859 SELECT STATEMENT @@global.variable_name FOR ...
860 is a wrong syntax.
861 */
862 value= *(uint*) var->var->value_ptr(thd, OPT_SESSION, &base);
863 item= new Item_uint((ulonglong) value);
864 old_var = new set_var(OPT_SESSION, var->var, &base, item);
865 break;
866 }
867 case SHOW_LONG:
868 {
869 ulong value;
870 Item_int *item;
871
872 value= *(ulong*) var->var->value_ptr(thd, OPT_SESSION, &base);
873 item= new Item_uint((ulonglong) value);
874 old_var= new set_var(OPT_SESSION, var->var, &base, item);
875 break;
876 }
877 case SHOW_LONGLONG:
878 {
879 longlong value;
880 Item_int *item;
881
882 value= *(longlong*) var->var->value_ptr(thd, OPT_SESSION, &base);
883 item= new Item_int(value);
884 old_var= new set_var(OPT_SESSION, var->var, &base, item);
885 break;
886 }
887 case SHOW_HA_ROWS:
888 {
889 ha_rows value;
890 Item_int *item;
891
892 value= *(ha_rows*) var->var->value_ptr(thd, OPT_SESSION, &base);
893 item= new Item_int((longlong) value);
894 old_var= new set_var(OPT_SESSION, var->var, &base, item);
895 break;
896 }
897 case SHOW_MY_BOOL:
898 {
899 Item_int *item;
900 item= new Item_int((int32) *(my_bool*) var->var->value_ptr(thd,
901 OPT_SESSION, &base), 1);
902 old_var= new set_var(OPT_SESSION, var->var, &base, item);
903 break;
904 }
905 case SHOW_CHAR:
906 {
907 Item *item;
908 char *str= (char*) var->var->value_ptr(thd, OPT_SESSION, &base);
909 if (str)
910 item= new Item_string(str, strlen(str), system_charset_info,
911 DERIVATION_SYSCONST);
912 else
913 {
914 item= new Item_null();
915 item->collation.set(system_charset_info, DERIVATION_SYSCONST);
916 }
917 old_var= new set_var(OPT_SESSION, var->var, &base, item);
918 break;
919 }
920 default:
921 my_error(ER_NOT_SUPPORTED_YET, MYF(0), base.str);
922 }
923
924 if (old_var == NULL)
925 res= true;
926 else
927 lex_var_list->push_back(old_var);
928
929 return res;
930}
931
932
933/**
934 set_stmt_reset_vars function will check and update SESSION variables to the
935 previous setting for SET STATEMENT ... FOR. Please use sql_set_variables
936 for setting variables as this version is designed for SET STATEMENT and
937 removes checks and procedures that should be followed in normal SET
938 operations. This function will also do partial sets of any passing comment
939 instead of all or nothing
940
941 @param var_list list of set_var_bases that contain previous SESSION
942 variables that where previously changed temporarily and now
943 need to be set back using the check and update functions.
944
945 @return true if error occurs false if successful
946
947*/
948
949bool set_stmt_reset_vars(THD *thd, List<set_var_base> *var_list)
950{
951 int error;
952 bool err= false;
953 set_var_base *var;
954 List_iterator_fast<set_var_base> it(*var_list);
955
956 thd->stmt_da->can_overwrite_status= true;
957
958 while ((var = it++))
959 {
960 if (!var->check(thd)) //check variable update if valid
961 error |= var->update(thd); //Returns 0, -1 or 1
962 else
963 err= true;
964 }
965 /*
966 We encountered some sort of error, but no message was sent.
967 Send something semi-generic here since we don't know which
968 assignment in the list caused the error. Do not enter
969 if error is already existing as will cause crash since error
970 is from statement after FOR loop.
971 */
972 if (err && !thd->is_error())
973 my_error(ER_WRONG_ARGUMENTS, MYF(0), "SET");
974 thd->stmt_da->can_overwrite_status= false;
975 var_list->empty(); // do some cleanup
976 return err;
977}
800978
=== modified file 'Percona-Server/sql/set_var.h'
--- Percona-Server/sql/set_var.h 2012-04-19 16:51:34 +0000
+++ Percona-Server/sql/set_var.h 2012-04-26 09:29:20 +0000
@@ -321,5 +321,10 @@
321int sys_var_add_options(DYNAMIC_ARRAY *long_options, int parse_flags);321int sys_var_add_options(DYNAMIC_ARRAY *long_options, int parse_flags);
322void sys_var_end(void);322void sys_var_end(void);
323323
324bool set_stmt_get_reset_vars(THD *thd,
325 set_var * var,
326 List<set_var_base> *lex_var_list);
327bool set_stmt_reset_vars(THD *thd, List<set_var_base> *var_list);
328
324#endif329#endif
325330
326331
=== modified file 'Percona-Server/sql/sql_lex.cc'
--- Percona-Server/sql/sql_lex.cc 2012-04-19 16:51:34 +0000
+++ Percona-Server/sql/sql_lex.cc 2012-04-26 09:29:20 +0000
@@ -372,6 +372,7 @@
372 lex->value_list.empty();372 lex->value_list.empty();
373 lex->update_list.empty();373 lex->update_list.empty();
374 lex->set_var_list.empty();374 lex->set_var_list.empty();
375 lex->stmt_set_list.empty();
375 lex->param_list.empty();376 lex->param_list.empty();
376 lex->view_list.empty();377 lex->view_list.empty();
377 lex->prepared_stmt_params.empty();378 lex->prepared_stmt_params.empty();
378379
=== modified file 'Percona-Server/sql/sql_lex.h'
--- Percona-Server/sql/sql_lex.h 2012-04-19 16:51:34 +0000
+++ Percona-Server/sql/sql_lex.h 2012-04-26 09:29:20 +0000
@@ -39,6 +39,7 @@
39class partition_info;39class partition_info;
40class Event_parse_data;40class Event_parse_data;
41class set_var_base;41class set_var_base;
42class set_var;
42class sys_var;43class sys_var;
43class Item_func_match;44class Item_func_match;
44class Alter_drop;45class Alter_drop;
@@ -2279,6 +2280,10 @@
2279 List<Item> *insert_list,field_list,value_list,update_list;2280 List<Item> *insert_list,field_list,value_list,update_list;
2280 List<List_item> many_values;2281 List<List_item> many_values;
2281 List<set_var_base> var_list;2282 List<set_var_base> var_list;
2283 /*
2284 Variables list for SET STATEMENT expression.
2285 */
2286 List<set_var> stmt_set_list;
2282 List<Item_func_set_user_var> set_var_list; // in-query assignment list2287 List<Item_func_set_user_var> set_var_list; // in-query assignment list
2283 List<Item_param> param_list;2288 List<Item_param> param_list;
2284 List<LEX_STRING> view_list; // view list (list of field names in view)2289 List<LEX_STRING> view_list; // view list (list of field names in view)
22852290
=== modified file 'Percona-Server/sql/sql_parse.cc'
--- Percona-Server/sql/sql_parse.cc 2012-04-19 16:51:34 +0000
+++ Percona-Server/sql/sql_parse.cc 2012-04-26 09:29:20 +0000
@@ -1989,6 +1989,8 @@
1989int1989int
1990mysql_execute_command(THD *thd)1990mysql_execute_command(THD *thd)
1991{1991{
1992 List<set_var_base> stmt_reset_list;
1993 Item* stmt_free_list= NULL;
1992 int res= FALSE;1994 int res= FALSE;
1993 int up_result= 0;1995 int up_result= 0;
1994 LEX *lex= thd->lex;1996 LEX *lex= thd->lex;
@@ -2205,6 +2207,60 @@
2205 DEBUG_SYNC(thd,"before_execute_sql_command");2207 DEBUG_SYNC(thd,"before_execute_sql_command");
2206#endif2208#endif
22072209
2210 if (!lex->stmt_set_list.is_empty())
2211 {
2212 List<set_var_base> lex_var_list;
2213 List_iterator_fast<set_var> it(lex->stmt_set_list);
2214 set_var *var;
2215
2216 while ((var= it++))
2217 {
2218 if (set_stmt_get_reset_vars(thd, var, &stmt_reset_list))
2219 {
2220 my_error(ER_WRONG_ARGUMENTS, MYF(0), "SET");
2221 goto error;
2222 }
2223 lex_var_list.push_back(var);
2224 }
2225
2226 if (!(res= sql_set_variables(thd, &lex_var_list)))
2227 {
2228 /*
2229 If the previous command was a SET ONE_SHOT, we don't want to forget
2230 about the ONE_SHOT property of that SET. So we use a |= instead of = .
2231 */
2232 thd->one_shot_set |= lex->one_shot_set;
2233 }
2234 else
2235 {
2236 /*
2237 We encountered some sort of error, but no message was sent.
2238 Send something semi-generic here since we don't know which
2239 assignment in the list caused the error.
2240 */
2241 if (!thd->is_error())
2242 my_error(ER_WRONG_ARGUMENTS, MYF(0), "SET");
2243 goto error;
2244 }
2245 /*
2246 In set_stmt_reset_vars function the instances of class Item are created.
2247 This instances are put into thd->free_list to be freed after query
2248 execution. But when PREPARE or EXECUTE commands are used the
2249 thd->free_list is cleaned up inside of Prepared_statement::prepare and
2250 Prepared_statement::execute respectively. So the instances of
2251 class Item that are used fot restoring per query variables values
2252 can be freed before restoring operation. To avoid it in case of
2253 PREPARE or EXECUTE commands processing the thd->free_list
2254 is preserved in local variable to be restored after commands processing.
2255 */
2256 if(lex->sql_command == SQLCOM_EXECUTE || lex->sql_command == SQLCOM_PREPARE)
2257 {
2258 stmt_free_list = thd->free_list;
2259 thd->free_list = 0;
2260 }
2261 lex_var_list.empty();
2262 }
2263
2208 switch (lex->sql_command) {2264 switch (lex->sql_command) {
22092265
2210 case SQLCOM_SHOW_EVENTS:2266 case SQLCOM_SHOW_EVENTS:
@@ -4581,6 +4637,22 @@
4581 my_ok(thd);4637 my_ok(thd);
4582 break;4638 break;
4583 }4639 }
4640
4641 if (!stmt_reset_list.is_empty()) /* reset variables for SET STATEMENT */
4642 {
4643 /* re-attach items to free_list for clean out later */
4644 if(lex->sql_command == SQLCOM_EXECUTE || lex->sql_command == SQLCOM_PREPARE)
4645 thd->free_list= stmt_free_list;
4646
4647 if (set_stmt_reset_vars(thd, &stmt_reset_list))
4648 {
4649 thd_proc_info(thd, "query end");
4650 res= TRUE;
4651 goto finish;
4652 }
4653 }
4654
4655
4584 thd_proc_info(thd, "query end");4656 thd_proc_info(thd, "query end");
45854657
4586 /*4658 /*
@@ -4599,6 +4671,18 @@
4599 goto finish;4671 goto finish;
46004672
4601error:4673error:
4674
4675 if (!stmt_reset_list.is_empty())
4676 {
4677
4678 /*
4679 re-attach items to free_list for clean out later
4680 */
4681 if (lex->sql_command == SQLCOM_EXECUTE || lex->sql_command == SQLCOM_PREPARE)
4682 thd->free_list= stmt_free_list;
4683 set_stmt_reset_vars(thd, &stmt_reset_list);
4684 }
4685
4602 res= TRUE;4686 res= TRUE;
46034687
4604finish:4688finish:
46054689
=== modified file 'Percona-Server/sql/sql_prepare.cc'
--- Percona-Server/sql/sql_prepare.cc 2012-04-19 16:51:34 +0000
+++ Percona-Server/sql/sql_prepare.cc 2012-04-26 09:29:20 +0000
@@ -3717,9 +3717,13 @@
3717 /*3717 /*
3718 If the free_list is not empty, we'll wrongly free some externally3718 If the free_list is not empty, we'll wrongly free some externally
3719 allocated items when cleaning up after validation of the prepared3719 allocated items when cleaning up after validation of the prepared
3720 statement.3720 statement. The exception is the case of SET STATEMENT using. When
3721 it is used the free_list is preserved in local variable in
3722 mysq_execute_command to avoid free the data for restoring values of
3723 system variables.
3721 */3724 */
3722 DBUG_ASSERT(thd->free_list == NULL);3725 if(thd->lex->stmt_set_list.is_empty())
3726 DBUG_ASSERT(thd->free_list == NULL);
37233727
3724 /*3728 /*
3725 Install the metadata observer. If some metadata version is3729 Install the metadata observer. If some metadata version is
37263730
=== modified file 'Percona-Server/sql/sql_yacc.yy'
--- Percona-Server/sql/sql_yacc.yy 2012-04-19 16:51:34 +0000
+++ Percona-Server/sql/sql_yacc.yy 2012-04-26 09:29:20 +0000
@@ -781,10 +781,10 @@
781781
782%pure_parser /* We have threads */782%pure_parser /* We have threads */
783/*783/*
784 Currently there are 168 shift/reduce conflicts.784 Currently there are 169 shift/reduce conflicts.
785 We should not introduce new conflicts any more.785 We should not introduce new conflicts any more.
786*/786*/
787%expect 168787%expect 169
788788
789/*789/*
790 Comments for TOKENS.790 Comments for TOKENS.
@@ -1294,6 +1294,7 @@
1294%token STARTING1294%token STARTING
1295%token STARTS_SYM1295%token STARTS_SYM
1296%token START_SYM /* SQL-2003-R */1296%token START_SYM /* SQL-2003-R */
1297%token STATEMENT_SYM
1297%token STATUS_SYM1298%token STATUS_SYM
1298%token NOLOCK_SYM /* SHOW SLAVE STATUS NOLOCK */1299%token NOLOCK_SYM /* SHOW SLAVE STATUS NOLOCK */
1299%token STDDEV_SAMP_SYM /* SQL-2003-N */1300%token STDDEV_SAMP_SYM /* SQL-2003-N */
@@ -12780,6 +12781,7 @@
12780 | SQL_NO_CACHE_SYM {}12781 | SQL_NO_CACHE_SYM {}
12781 | SQL_THREAD {}12782 | SQL_THREAD {}
12782 | STARTS_SYM {}12783 | STARTS_SYM {}
12784 | STATEMENT_SYM {}
12783 | STATUS_SYM {}12785 | STATUS_SYM {}
12784 | STORAGE_SYM {}12786 | STORAGE_SYM {}
12785 | STRING_SYM {}12787 | STRING_SYM {}
@@ -12835,7 +12837,8 @@
12835/* Option functions */12837/* Option functions */
1283612838
12837set:12839set:
12838 SET opt_option12840 SET statement_set {}
12841 | SET opt_option
12839 {12842 {
12840 LEX *lex=Lex;12843 LEX *lex=Lex;
12841 lex->sql_command= SQLCOM_SET_OPTION;12844 lex->sql_command= SQLCOM_SET_OPTION;
@@ -12854,6 +12857,37 @@
12854 | OPTION {}12857 | OPTION {}
12855 ;12858 ;
1285612859
12860statement_set:
12861 STATEMENT_SYM
12862 {
12863 LEX *lex= Lex;
12864 mysql_init_select(lex);
12865 }
12866 simple_option_type_list FOR_SYM statement {}
12867 ;
12868
12869simple_option_type_value:
12870 internal_variable_name equal set_expr_or_default
12871 {
12872 if(!$1.var)
12873 {
12874 my_parse_error(ER(ER_SYNTAX_ERROR));
12875 MYSQL_YYABORT;
12876 }
12877 LEX *lex= Lex;
12878 set_var *var= new set_var(OPT_SESSION, $1.var, &$1.base_name, $3);
12879
12880 if (var == NULL)
12881 MYSQL_YYABORT;
12882 lex->stmt_set_list.push_back(var);
12883 }
12884 ;
12885
12886simple_option_type_list:
12887 simple_option_type_value
12888 | simple_option_type_list ',' simple_option_type_value
12889 ;
12890
12857option_value_list:12891option_value_list:
12858 option_type_value12892 option_type_value
12859 | option_value_list ',' option_type_value12893 | option_value_list ',' option_type_value

Subscribers

People subscribed via source and target branches