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

Merge lp:~vlad-lesin/percona-server/5.6-per_query_variables_settings into lp:percona-server/5.6

  1. 5.6-per_query_variables_settings
  2. Merge into 5.6
Proposed by Vlad Lesin
Status: Merged
Approved by: Alexey Kopytov
Approved revision: no longer in the source branch.
Merged at revision: 477
Proposed branch: lp:~vlad-lesin/percona-server/5.6-per_query_variables_settings
Merge into: lp:percona-server/5.6
Diff against target: 2284 lines (+2107/-18)
13 files modified
Percona-Server/mysql-test/r/percona_statement_set.result (+862/-0)
Percona-Server/mysql-test/suite/rpl/include/percona_rpl_set_statement_variable_test.inc (+31/-0)
Percona-Server/mysql-test/suite/rpl/r/percona_rpl_stm_per_query_variables_settings.result (+143/-0)
Percona-Server/mysql-test/suite/rpl/t/percona_rpl_stm_per_query_variables_settings.test (+57/-0)
Percona-Server/mysql-test/t/percona_statement_set.test (+830/-0)
Percona-Server/sql/lex.h (+1/-0)
Percona-Server/sql/set_var.h (+3/-0)
Percona-Server/sql/sql_lex.cc (+1/-0)
Percona-Server/sql/sql_lex.h (+2/-1)
Percona-Server/sql/sql_parse.cc (+47/-0)
Percona-Server/sql/sql_plugin.cc (+66/-0)
Percona-Server/sql/sql_plugin.h (+9/-0)
Percona-Server/sql/sql_yacc.yy (+55/-17)
To merge this branch: bzr merge lp:~vlad-lesin/percona-server/5.6-per_query_variables_settings
Reviewer Review Type Date Requested Status
Alexey Kopytov (community) Approve
Laurynas Biveinis (community) Needs Fixing
Review via email: mp+144082@code.launchpad.net
To post a comment you must log in.
Revision history for this message
Laurynas Biveinis (laurynas-biveinis) wrote :

   - How does it replicate? Would a test be needed for that?
     https://dev.mysql.com/doc/refman/5.6/en/replication-features-variables.html
   - Please add tests for invalid syntax
   - Please add tests for global-only variable use attempts
   - Since it is supported only for variables with certain show type,
     please test that variables with unsupported show types are
     rejected correctly.
   - set_stmt_reset_vars shouldn't assume that the update back to the
     original value might possibly fail. I.e. it can fail, but that
     should be a fatal assertion in a debug build. Thus instead of
     if (!var->check(thd))
       error|= var->update(thd);
     I'd do something like
     DBUG_ASSERT(var->check(thd));
     error|= var->update(thd);
     DBUG_ASSERT(!error);
   - Please add CREATE TABLE STATEMENT(a INT) test to a testcase
   - Lines 2063--2067: one_shot is removed as of 5.6.1.
   - Lines 2093--2094: can this free list swap cause that something
     non related to the per-statement vars might get freed late as
     well? If that's not the case, then a
     DBUG_ASSERT(!thd->free_list) before the set_stmt_get_reset_vars
     loop call would document this. But if it is the case, then care
     must be taken to remove only per-statement var Item instances
     from the thd->free_list.
   - Lines 2106--2112 and 2127--2135: please factor out to a common
     function.
   - Should lines 2240--2244 be sp_create_assignment_lex() call
     instead?
   - Spurious line 2260.
   Minor comments:
   - Please use the disable_warnings; DROP TABLE IF EXISTS;
     enable_warnings; idiom in the testcase.
   - The test depends on t1 being MyISAM. Please either spell it out
     by ENGINE=MyISAM, either look into future-proofing the test by
     using an InnoDB table instead (that would require adjusting Test
     4 accordingly).
   - Testcase comments: s/check/check that/g where appropriate.
   - set_stmt_get_reset_vars header comment: s/will retrieve/retrieves
   - body comment: s/ans/and
   - Diff line 2022: s/Variables list/Variable list
   - Diff line 2086: s/fot/for

review: Needs Fixing
Revision history for this message
Vlad Lesin (vlad-lesin) wrote :
Download full text (5.2 KiB)

> - How does it replicate? Would a test be needed for that?
> https://dev.mysql.com/doc/refman/5.6/en/replication-features-variables.html

There are the following types of variables:
1) variables that are NOT replicated correctly when using STATEMENT mode;
2) variables thar ARE replicated correctly when using STATEMENT mode;
3) sql_mode which is replicated correctly exept NO_DIR_IN_CREATE value;
4) variables that are not replicated at all:
 default_storage_engine, storage_engine, max_heap_table_size;

1)
But as we can set variables in a statements itself and that statements are written to binlog in STATEMENT mode and old values of variables are restored just after statement execution it works correctly.

2)
That variables are saved in Query_log_event on master for each event and restored on slave in Query_log_event::do_apply_event(). "Per query variables settings" code saves old values of variables that are listed in "SET STATEMENT ... FOR ..." statement before query execution and restores them after that. This code works in mysql_execute_command(). There is the following call stack on slave: Query_log_event::do_apply_event()->mysql_parse()->mysql_execute_command(). As variables are restored from mysqlbinlog event by slave thread in Query_log_event::do_apply_event() before "per query variables settings" code saves their old values the old values are lost. But in master the variables are restored to the previous values just after query execution and the next mysqlbinlog event will set them to the previous values on slave. As "SET STATEMENT ... FOR ..." changes only session values I think such behaviour is acceptable.

3)
The NO_DIR_IN_CREATE mode is reset just before event applying on slave in Query_log_event::do_apply_event(). But after that mysql_parse() is invoked and the new value of sql_mode is parsed from statement including NO_DIR_IN_CREATE. For this case we filter out sql_mode variable on slaves during query parsing.

4)
The same is for 3.

> - Please add tests for invalid syntax
Done.

> - Please add tests for global-only variable use attempts
Done

> - Since it is supported only for variables with certain show type,
> please test that variables with unsupported show types are
> rejected correctly.
Done.

> - set_stmt_reset_vars shouldn't assume that the update back to the
> original value might possibly fail. I.e. it can fail, but that
> should be a fatal assertion in a debug build. Thus instead of
> if (!var->check(thd))
> error|= var->update(thd);
> I'd do something like
> DBUG_ASSERT(var->check(thd));
> error|= var->update(thd);
> DBUG_ASSERT(!error);
Done.

> - Please add CREATE TABLE STATEMENT(a INT) test to a testcase
Done.

> - Lines 2063--2067: one_shot is removed as of 5.6.1.
But thd->one_shot and lex->one_shot are still in the code. And it's resonable to take them into account while they are still there.

> - Lines 2093--2094: can this free list swap cause that something
> non related to the per-statement vars might get freed late as
> well? If that's not the case, then a
> DBUG_ASSERT(!thd->free_list) before the set_stmt_get_reset_vars
> loop call woul...

Read more...

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

Rebased to new code.

http://jenkins.percona.com/view/PS%205.6/job/percona-server-5.6-param/210/

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

It looks like tests have not been committed after rebasing.

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

  - the following assertion

+ if (lex->sql_command == SQLCOM_EXECUTE || lex->sql_command == SQLCOM_PREPARE)
+ {
+ DBUG_ASSERT(thd->free_list == NULL);
+ thd->free_list= stmt_free_list;
+ }

     would fail in the following case:

     SET global_only_var=..., normal_var=... FOR EXECUTE stmt;

     because if an error occurs in one of the set_stmt_get_reset_vars,
     we bail out before saving and resetting thd->free_list.

  - the following code will not be resetting variables to their previous
    values in release builds (i.e. var->update() will not be called at
    all):

+ while ((var = it++))
+ {
+ DBUG_ASSERT(!var->check(thd));
+ DBUG_ASSERT(!var->update(thd));
+ }

  - variable length array in set_stmt_get_reset_vars() is not portable.
    You actually don't even need another buffer for the var name, and
    another LEX_STRING variable. what's wrong with using a pointer to
    var->var->name directly?

  - switch() in set_stmt_get_reset_vars() handles only a subset of the
    options. What about SHOW_BOOL, SHOW_DOUBLE, SHOW_SIGNED_LONG, etc.?

  - please remove all changes related to ONE_SHOT

  - the following code should be wrapped into #ifndef DBUG_OFF:

+ if(thd->lex->stmt_set_list.is_empty())
+ DBUG_ASSERT(thd->free_list == NULL);

  - in general, I would implement it all differently. Just replace
    thd->variables with its deep copy before statement execution, then
    destroy it after statement execution and assign the original pointer
    to thd->variables. This way you don't have to mess with option
    types, Item instances, thd->free_list, etc. I guess the patch would
    be 50% smaller and more robust against future changes.

  - replication. No system variables are replicated except a small
    subset which is listed in the manual. But variables from that subset
    are written to the binlog event header. Which means a slave should
    ignore _all_ statement variables and we don't need
    filter_replicated_variables().

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

> - the following assertion
>
> + if (lex->sql_command == SQLCOM_EXECUTE || lex->sql_command ==
> SQLCOM_PREPARE)
> + {
> + DBUG_ASSERT(thd->free_list == NULL);
> + thd->free_list= stmt_free_list;
> + }
>
> would fail in the following case:
>
> SET global_only_var=..., normal_var=... FOR EXECUTE stmt;
>
> because if an error occurs in one of the set_stmt_get_reset_vars,
> we bail out before saving and resetting thd->free_list.
>
> - the following code will not be resetting variables to their previous
> values in release builds (i.e. var->update() will not be called at
> all):
>
> + while ((var = it++))
> + {
> + DBUG_ASSERT(!var->check(thd));
> + DBUG_ASSERT(!var->update(thd));
> + }
>
> - variable length array in set_stmt_get_reset_vars() is not portable.
> You actually don't even need another buffer for the var name, and
> another LEX_STRING variable. what's wrong with using a pointer to
> var->var->name directly?
>
> - switch() in set_stmt_get_reset_vars() handles only a subset of the
> options. What about SHOW_BOOL, SHOW_DOUBLE, SHOW_SIGNED_LONG, etc.?
>
The above comments do not make sense because the feature is implemented in another way now.

> - please remove all changes related to ONE_SHOT
>
Done.

> - the following code should be wrapped into #ifndef DBUG_OFF:
>
> + if(thd->lex->stmt_set_list.is_empty())
> + DBUG_ASSERT(thd->free_list == NULL);
>
Done.

> - in general, I would implement it all differently. Just replace
> thd->variables with its deep copy before statement execution, then
> destroy it after statement execution and assign the original pointer
> to thd->variables. This way you don't have to mess with option
> types, Item instances, thd->free_list, etc. I guess the patch would
> be 50% smaller and more robust against future changes.
Done.

> - replication. No system variables are replicated except a small
> subset which is listed in the manual. But variables from that subset
> are written to the binlog event header. Which means a slave should
> ignore _all_ statement variables and we don't need
> filter_replicated_variables().
Done.

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

http://jenkins.percona.com/view/PS%205.6/job/percona-server-5.6-param/268/

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

Debug builds fail.

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

> Debug builds fail.

Fixed.
http://jenkins.percona.com/view/PS%205.6/job/percona-server-5.6-param/271

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

  - the comment for LEX::set_statement says:
    “The number of recursive SET STATEMENT ... FOR ... statements“

    but the variable has the ‘bool’ type?

  - but it got me wondering what happens with recursive SET STATEMENT
    statements, and indeed they don’t work as one would expect,
    i.e. only the innermost SET STATEMENT has an effect, and the outer
    ones seem to be ignored. This doesn’t look like a serious limitation
    and I’m fine with documenting it, but are there are real reasons for
    this behavior? It looks like making in work correctly is a matter of
    not calling lex->var_list.empty() if it already has some variables?

  - the patch introduces a new Bison warning:

“...sql/sql_yacc.yy:14551.11-14567.76: warning: type clash on default
action: <NONE> != <>”

  because it doesn’t define an action after “set_stmt_option_value_following_option_type_list FOR_SYM statement“

  - do you really need set_var::stmt_update()? It is used in only one
    place in the code. It returns a value, but the only place where it is
    called doesn’t care about its return value. What gives?

  - the following change leads to server allocating 816 bytes on stack
    for all queries, even those not using per-query variables.

---
@@ -2419,6 +2419,8 @@ mysql_execute_command(THD *thd)
   /* have table map for update for multi-update statement (BUG#37051) */
   bool have_table_map_for_update= FALSE;
 #endif
+ struct system_variables per_query_variables_backup;
+
   DBUG_ENTER("mysql_execute_command");
   DBUG_ASSERT(!lex->describe || is_explainable_query(lex->sql_command));
---

   let’s allocate it on heap instead?

  - I guess changes in sql_prepare.cc are no longer required and can be
    reverted?

  - please rename rpl_* files to percona_rpl_*

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

On 09/22/2013 05:06 PM, Alexey Kopytov wrote:
> Review: Needs Fixing
>
> - the comment for LEX::set_statement says:
> “The number of recursive SET STATEMENT ... FOR ... statements“
>
> but the variable has the ‘bool’ type?
 Yes, I forgot to change this comment, initially this variable was ulint.
But bool is enough even for recursive use. Fixed.

> - but it got me wondering what happens with recursive SET STATEMENT
> statements, and indeed they don’t work as one would expect,
> i.e. only the innermost SET STATEMENT has an effect, and the outer
> ones seem to be ignored. This doesn’t look like a serious limitation
> and I’m fine with documenting it, but are there are real reasons for
> this behavior? It looks like making in work correctly is a matter of
> not calling lex->var_list.empty() if it already has some variables?
Fixed, new test case #21 in mysql-test/t/percona_statement_set.test is
added.

But this implementation has disadvantage. In the case of "SET STATEMENT
... FOR SET SESSION ..." query lex->var_list will be cleared when SET
SESSION is parsed, so all variables settings in SET STATEMENT section
will be cleared. The solution could be in using separate list for
statement variables. But in this case all variables which was set in
"SET SESSION" part will be reset to pre-statement state as
thd->variables will be completely restored after statement execution.

> - the patch introduces a new Bison warning:
>
> “...sql/sql_yacc.yy:14551.11-14567.76: warning: type clash on default
> action: <NONE> != <>”
>
> because it doesn’t define an action after “set_stmt_option_value_following_option_type_list FOR_SYM statement“
Fixed.

> - do you really need set_var::stmt_update()? It is used in only one
> place in the code. It returns a value, but the only place where it is
> called doesn’t care about its return value. What gives?
Return type is changed to "void". But this function is still necessary
because sys_var::on_update is protected.

> - the following change leads to server allocating 816 bytes on stack
> for all queries, even those not using per-query variables.
>
> ---
> @@ -2419,6 +2419,8 @@ mysql_execute_command(THD *thd)
> /* have table map for update for multi-update statement (BUG#37051) */
> bool have_table_map_for_update= FALSE;
> #endif
> + struct system_variables per_query_variables_backup;
> +
> DBUG_ENTER("mysql_execute_command");
> DBUG_ASSERT(!lex->describe || is_explainable_query(lex->sql_command));
> ---
>
> let’s allocate it on heap instead?
Done.

> - I guess changes in sql_prepare.cc are no longer required and can be
> reverted?
No, they are still required because objects of "Item" class are
allocated during parsing and thd->free_list contains list of those objects.

> - please rename rpl_* files to percona_rpl_*
Done.

Here is new testing
http://jenkins.percona.com/view/PS%205.6/job/percona-server-5.6-param/291 .

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

Hi Vlad,

On Mon, 23 Sep 2013 10:27:28 +0400, Vlad Lesin wrote:

>> - I guess changes in sql_prepare.cc are no longer required and can be
>> reverted?
> No, they are still required because objects of "Item" class are
> allocated during parsing and thd->free_list contains list of those objects.
>

OK, I see it now. Shouldn't we instead of adjusting the assertion in
sql_prepare.cc just reset thd->free_list after calling
sql_set_variables() in mysql_execute_command()? It was impossible with
the previous implementation, but is possible now, right?

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

Vlad,

Conflicts, please rebase on trunk.

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

> Conflicts, please rebase on trunk.

Rebased.

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

> Hi Vlad,
>
> On Mon, 23 Sep 2013 10:27:28 +0400, Vlad Lesin wrote:
>
> >> - I guess changes in sql_prepare.cc are no longer required and can be
> >> reverted?
> > No, they are still required because objects of "Item" class are
> > allocated during parsing and thd->free_list contains list of those objects.
> >
>
> OK, I see it now. Shouldn't we instead of adjusting the assertion in
> sql_prepare.cc just reset thd->free_list after calling
> sql_set_variables() in mysql_execute_command()? It was impossible with
> the previous implementation, but is possible now, right?

Resetting thd->free_list after each sql_set_variables() call does not work right because thd->free_list can contain "Item" class instances not only from SET STATEMENT parsing. If fixes in sql_prepare.cc are not desirable thd->free_list can be released in SQLCOM_EXECUTE command handler in mysql_execute_command() function. As it can be seen I have done it. But I have some doubts about this because there is no common solution and the issue which is in sql_prepare.cc is fixed in sql_parse.cc, but can be fixed in the code which is the source of the issue. I do not think such approach will add understanding in the code even if it is documented in comments because referring from one part of code to another can lead to the situation when some part of code is changed but depended part is not changed.

The following note was ignored but I consider it is very important and it should be discussed:
---
But this implementation has disadvantage. In the case of "SET STATEMENT
... FOR SET SESSION ..." query lex->var_list will be cleared when SET
SESSION is parsed, so all variables settings in SET STATEMENT section
will be cleared. The solution could be in using separate list for
statement variables. But in this case all variables which was set in
"SET SESSION" part will be reset to pre-statement state as
thd->variables will be completely restored after statement execution.
---

My suggestions:
1) Forbid using "SET" as a statement after "FOR" keyword. But the problem will not go because SET SESSION can be used in SP or prepared query.
2) Use separate list of parsed variables for SET and SET STATEMENT ... FOR ... statements
3) Check if SET STATEMENT ... FOR ... was parsed in SQLCOM_SET_OPTION handler in mysql_execute_command(). If it was then return error.

Here is new code testing results:
http://jenkins.percona.com/view/PS 5.6/job/percona-server-5.6-param/322

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

Hi Vlad,

On Fri, 27 Sep 2013 21:29:46 -0000, Vlad Lesin wrote:>> Hi Vlad,
>>
>> On Mon, 23 Sep 2013 10:27:28 +0400, Vlad Lesin wrote:
>>
>>>> - I guess changes in sql_prepare.cc are no longer required and can be
>>>> reverted?
>>> No, they are still required because objects of "Item" class are
>>> allocated during parsing and thd->free_list contains list of those objects.
>>>
>>
>> OK, I see it now. Shouldn't we instead of adjusting the assertion in
>> sql_prepare.cc just reset thd->free_list after calling
>> sql_set_variables() in mysql_execute_command()? It was impossible with
>> the previous implementation, but is possible now, right?
>
> Resetting thd->free_list after each sql_set_variables() call does not work right because thd->free_list can contain "Item" class instances not only from SET STATEMENT parsing. If fixes in sql_prepare.cc are not desirable thd->free_list can be released in SQLCOM_EXECUTE command handler in mysql_execute_command() function. As it can be seen I have done it. But I have some doubts about this because there is no common solution and the issue which is in sql_prepare.cc is fixed in sql_parse.cc, but can be fixed in the code which is the source of the issue. I do not think such approach will add understanding in the code even if it is documented in comments because referring from one part of code to another can lead to the situation when some part of code is changed but depended part is not changed.
>

Right, but I think the current way is more correct than adjusting the assertion in Prepared_statement::execute_loop(), because this way we keep the invariant that code relies on (no externally allocated objects when executing a prepared statement).

> The following note was ignored but I consider it is very important and it should be discussed:
> ---
> But this implementation has disadvantage. In the case of "SET STATEMENT
> ... FOR SET SESSION ..." query lex->var_list will be cleared when SET
> SESSION is parsed, so all variables settings in SET STATEMENT section
> will be cleared. The solution could be in using separate list for
> statement variables. But in this case all variables which was set in
> "SET SESSION" part will be reset to pre-statement state as
> thd->variables will be completely restored after statement execution.
> ---
>

I don't think SET STATEMENT ... FOR SET SESSSION ... is a "very important" case. I'm fine with documenting it as a known limitation and figuring out a way to fix it, if and when someone gets unhappy about it.

Approved.

review: Approve

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

Subscribers

People subscribed via source and target branches