Merge lp:~vlad-lesin/percona-server/5.6-per_query_variables_settings into lp:percona-server/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 :
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 :
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 :
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
=== added file 'Percona-Server/mysql-test/r/percona_statement_set.result'
--- Percona-Server/mysql-test/r/percona_statement_set.result 1970-01-01 00:00:00 +0000
+++ Percona-Server/mysql-test/r/percona_statement_set.result 2013-09-27 17:50:05 +0000
@@ -0,0 +1,862 @@
1'# SET STATEMENT ..... FOR .... TEST'
2DROP TABLE IF EXISTS t1;
3DROP FUNCTION IF EXISTS myProc;
4DROP PROCEDURE IF EXISTS p1;
5DROP PROCEDURE IF EXISTS p2;
6DROP PROCEDURE IF EXISTS p3;
7DROP PROCEDURE IF EXISTS p4;
8DROP PROCEDURE IF EXISTS p5;
9DROP TABLE IF EXISTS STATEMENT;
10'# Setup database'
11CREATE TABLE t1 (v1 INT, v2 INT);
12INSERT INTO t1 VALUES (1,2);
13INSERT INTO t1 VALUES (3,4);
14''
15'#------------------ STATEMENT Test 1 -----------------------#'
16'# Initialize variables to known setting'
17SET SESSION sort_buffer_size=100000;
18''
19'# Pre-STATEMENT variable value'
20SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
21Variable_name Value
22sort_buffer_size 100000
23SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t1;
24v1 v2
251 2
263 4
27''
28'# Post-STATEMENT variable value'
29SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
30Variable_name Value
31sort_buffer_size 100000
32''
33'#------------------ STATEMENT Test 2 -----------------------#'
34'# Initialize variables to known setting'
35SET SESSION binlog_format=mixed;
36SET SESSION sort_buffer_size=100000;
37'# Pre-STATEMENT variable value'
38SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
39Variable_name Value
40sort_buffer_size 100000
41SHOW SESSION VARIABLES LIKE 'binlog_format';
42Variable_name Value
43binlog_format MIXED
44SET STATEMENT sort_buffer_size=150000, binlog_format=row
45FOR SELECT * FROM t1;
46v1 v2
471 2
483 4
49'# Post-STATEMENT variable value'
50SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
51Variable_name Value
52sort_buffer_size 100000
53SHOW SESSION VARIABLES LIKE 'binlog_format';
54Variable_name Value
55binlog_format MIXED
56''
57'#------------------ STATEMENT Test 3 -----------------------#'
58'# set initial variable value, make prepared statement
59SET SESSION binlog_format=row;
60PREPARE stmt1 FROM 'SET STATEMENT binlog_format=row FOR SELECT * FROM t1';
61''
62'# Change variable setting'
63SET SESSION binlog_format=mixed;
64''
65'# Pre-STATEMENT variable value'
66''
67SHOW SESSION VARIABLES LIKE 'binlog_format';
68Variable_name Value
69binlog_format MIXED
70''
71EXECUTE stmt1;
72v1 v2
731 2
743 4
75''
76'# Post-STATEMENT variable value'
77SHOW SESSION VARIABLES LIKE 'binlog_format';
78Variable_name Value
79binlog_format MIXED
80''
81DEALLOCATE PREPARE stmt1;
82'#------------------ STATEMENT Test 4 -----------------------#'
83'# set initial variable value, make prepared statement
84SET SESSION myisam_sort_buffer_size=500000, myisam_repair_threads=1;
85''
86'# Pre-STATEMENT variable value'
87SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
88Variable_name Value
89myisam_sort_buffer_size 500000
90SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
91Variable_name Value
92myisam_repair_threads 1
93''
94SET STATEMENT myisam_sort_buffer_size=800000,
95myisam_repair_threads=2 FOR OPTIMIZE TABLE t1;
96Table Op Msg_type Msg_text
97test.t1 optimize status OK
98''
99'# Post-STATEMENT variable value'
100SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
101Variable_name Value
102myisam_sort_buffer_size 500000
103SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
104Variable_name Value
105myisam_repair_threads 1
106''
107'#------------------ STATEMENT Test 5 -----------------------#'
108'# Initialize variables to known setting'
109SET SESSION sort_buffer_size=100000;
110''
111'# Pre-STATEMENT variable value'
112SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
113Variable_name Value
114sort_buffer_size 100000
115''
116SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t2;
117ERROR 42S02: Table 'test.t2' doesn't exist
118''
119'# Post-STATEMENT variable value'
120SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
121Variable_name Value
122sort_buffer_size 100000
123''
124'#------------------ STATEMENT Test 6 -----------------------#'
125'# Initialize variables to known setting'
126SET SESSION keep_files_on_create=ON;
127''
128'# Pre-STATEMENT variable value'
129SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
130Variable_name Value
131keep_files_on_create ON
132''
133SET STATEMENT keep_files_on_create=OFF FOR SELECT * FROM t1;
134v1 v2
1351 2
1363 4
137''
138'# Post-STATEMENT variable value'
139SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
140Variable_name Value
141keep_files_on_create ON
142''
143'#------------------ STATEMENT Test 7 -----------------------#'
144'# Initialize variables to known setting'
145SET SESSION max_join_size=2222220000000;
146''
147'# Pre-STATEMENT variable value'
148SHOW SESSION VARIABLES LIKE 'max_join_size';
149Variable_name Value
150max_join_size 2222220000000
151''
152SET STATEMENT max_join_size=1000000000000 FOR SELECT * FROM t1;
153v1 v2
1541 2
1553 4
156''
157'# Post-STATEMENT variable value'
158SHOW SESSION VARIABLES LIKE 'max_join_size';
159Variable_name Value
160max_join_size 2222220000000
161''
162'#------------------Test 8-----------------------#'
163'# Initialize test variables'
164SET SESSION myisam_sort_buffer_size=500000,
165myisam_repair_threads=1,
166sort_buffer_size = 200000,
167max_join_size=2222220000000,
168keep_files_on_create=ON;
169''
170'# LONG '
171SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
172Variable_name Value
173sort_buffer_size 200000
174SET STATEMENT sort_buffer_size = 100000
175FOR SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
176Variable_name Value
177sort_buffer_size 100000
178SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
179Variable_name Value
180sort_buffer_size 200000
181''
182'# MY_BOOL '
183SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
184Variable_name Value
185keep_files_on_create ON
186SET STATEMENT keep_files_on_create=OFF
187FOR SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
188Variable_name Value
189keep_files_on_create OFF
190SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
191Variable_name Value
192keep_files_on_create ON
193''
194'# INT/LONG '
195SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
196Variable_name Value
197myisam_repair_threads 1
198SET STATEMENT myisam_repair_threads=2
199FOR SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
200Variable_name Value
201myisam_repair_threads 2
202SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
203Variable_name Value
204myisam_repair_threads 1
205''
206'# ULONGLONG '
207SHOW SESSION VARIABLES LIKE 'max_join_size';
208Variable_name Value
209max_join_size 2222220000000
210SET STATEMENT max_join_size=2000000000000
211FOR SHOW SESSION VARIABLES LIKE 'max_join_size';
212Variable_name Value
213max_join_size 2000000000000
214SHOW SESSION VARIABLES LIKE 'max_join_size';
215Variable_name Value
216max_join_size 2222220000000
217''
218'#------------------Test 9-----------------------#'
219'# set initial variable values
220SET SESSION myisam_sort_buffer_size=500000,
221myisam_repair_threads=1,
222sort_buffer_size=100000,
223binlog_format=mixed,
224keep_files_on_create=ON,
225max_join_size=2222220000000;
226''
227''
228'# Pre-STATEMENT variable value
229SELECT @@myisam_sort_buffer_size,
230@@myisam_repair_threads,
231@@sort_buffer_size,
232@@binlog_format,
233@@keep_files_on_create,
234@@max_join_size;
235@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
236500000 1 100000 MIXED 1 2222220000000
237''
238''
239CREATE FUNCTION myProc (cost DECIMAL(10,2))
240RETURNS DECIMAL(10,2)
241SQL SECURITY DEFINER
242tax: BEGIN
243DECLARE order_tax DECIMAL(10,2);
244SET order_tax = cost * .05;
245RETURN order_tax;
246END|
247''
248'# During Execution values
249SET STATEMENT myisam_sort_buffer_size=400000,
250myisam_repair_threads=2,
251sort_buffer_size=200000,
252binlog_format=row,
253keep_files_on_create=OFF,
254max_join_size=4444440000000 FOR
255SELECT myProc(123.45);
256myProc(123.45)
2576.17
258''
259'# Post-STATEMENT No 1 variable value Pre-STATEMENT for No 2'
260SELECT @@myisam_sort_buffer_size,
261@@myisam_repair_threads,
262@@sort_buffer_size,
263@@binlog_format,
264@@keep_files_on_create,
265@@max_join_size;
266@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
267500000 1 100000 MIXED 1 2222220000000
268''
269SET STATEMENT myisam_sort_buffer_size=400000,
270myisam_repair_threads=2,
271sort_buffer_size=200000,
272binlog_format=row,
273keep_files_on_create=OFF,
274max_join_size=4444440000000 FOR
275DROP FUNCTION myProc;
276''
277'# Post-STATEMENT No 2 variable value
278SELECT @@myisam_sort_buffer_size,
279@@myisam_repair_threads,
280@@sort_buffer_size,
281@@binlog_format,
282@@keep_files_on_create,
283@@max_join_size;
284@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
285500000 1 100000 MIXED 1 2222220000000
286''
287'#------------------Test 10-----------------------#'
288'# set initial variable values
289SET SESSION myisam_sort_buffer_size=500000,
290myisam_repair_threads=1,
291sort_buffer_size=100000,
292binlog_format=mixed,
293keep_files_on_create=ON,
294max_join_size=2222220000000;
295''
296'# Pre-STATEMENT variable value
297SELECT @@myisam_sort_buffer_size,
298@@myisam_repair_threads,
299@@sort_buffer_size,
300@@binlog_format,
301@@keep_files_on_create,
302@@max_join_size;
303@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
304500000 1 100000 MIXED 1 2222220000000
305''
306''
307SET STATEMENT myisam_sort_buffer_size=400000,
308myisam_repair_threads=2,
309sort_buffer_size=200000,
310binlog_format=row,
311keep_files_on_create=OFF,
312max_join_size=4444440000000 FOR
313PREPARE stmt2
314FROM 'SELECT * FROM t1';
315''
316'Test No 1 Post Value & Test 2 Pre values'
317SELECT @@myisam_sort_buffer_size,
318@@myisam_repair_threads,
319@@sort_buffer_size,
320@@binlog_format,
321@@keep_files_on_create,
322@@max_join_size;
323@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
324500000 1 100000 MIXED 1 2222220000000
325''
326''
327SET STATEMENT myisam_sort_buffer_size=400000,
328myisam_repair_threads=2,
329sort_buffer_size=200000,
330binlog_format=row,
331keep_files_on_create=OFF,
332max_join_size=4444440000000 FOR
333EXECUTE stmt2;
334v1 v2
3351 2
3363 4
337''
338'# Post-STATEMENT No 2
339SELECT @@myisam_sort_buffer_size,
340@@myisam_repair_threads,
341@@sort_buffer_size,
342@@binlog_format,
343@@keep_files_on_create,
344@@max_join_size;
345@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
346500000 1 100000 MIXED 1 2222220000000
347''
348DEALLOCATE PREPARE stmt2;
349''
350'#------------------Test 11-----------------------#'
351'# set initial variable values
352SET SESSION myisam_sort_buffer_size=500000,
353myisam_repair_threads=1,
354sort_buffer_size=100000,
355binlog_format=mixed,
356keep_files_on_create=ON,
357max_join_size=2222220000000;
358''
359''
360'# Pre-STATEMENT variable value
361SELECT @@myisam_sort_buffer_size,
362@@myisam_repair_threads,
363@@sort_buffer_size,
364@@binlog_format,
365@@keep_files_on_create,
366@@max_join_size;
367@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
368500000 1 100000 MIXED 1 2222220000000
369''
370''
371SET STATEMENT myisam_sort_buffer_size=400000,
372myisam_repair_threads=2,
373sort_buffer_size=200000,
374keep_files_on_create=OFF,
375max_join_size=4444440000000 FOR
376PREPARE stmt1 FROM
377'SET STATEMENT binlog_format=row FOR SELECT * FROM t1';
378''
379'Test No 1 Post Value & Test 2 Pre values'
380SELECT @@myisam_sort_buffer_size,
381@@myisam_repair_threads,
382@@sort_buffer_size,
383@@binlog_format,
384@@keep_files_on_create,
385@@max_join_size;
386@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
387500000 1 100000 MIXED 1 2222220000000
388''
389''
390SET STATEMENT myisam_sort_buffer_size=400000,
391myisam_repair_threads=2,
392sort_buffer_size=200000,
393keep_files_on_create=OFF,
394max_join_size=4444440000000 FOR
395EXECUTE stmt1;
396v1 v2
3971 2
3983 4
399''
400'# Post-STATEMENT No 2
401SELECT @@myisam_sort_buffer_size,
402@@myisam_repair_threads,
403@@sort_buffer_size,
404@@binlog_format,
405@@keep_files_on_create,
406@@max_join_size;
407@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
408500000 1 100000 MIXED 1 2222220000000
409''
410''
411'#------------------Test 12-----------------------#'
412'# set initial variable values
413SET SESSION myisam_sort_buffer_size=500000,
414myisam_repair_threads=1,
415sort_buffer_size=100000,
416binlog_format=mixed,
417keep_files_on_create=ON,
418max_join_size=2222220000000;
419''
420''
421'# Pre-STATEMENT variable value
422SELECT @@myisam_sort_buffer_size,
423@@myisam_repair_threads,
424@@sort_buffer_size,
425@@binlog_format,
426@@keep_files_on_create,
427@@max_join_size;
428@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
429500000 1 100000 MIXED 1 2222220000000
430''
431''
432SET STATEMENT myisam_sort_buffer_size=400000,
433myisam_repair_threads=2,
434sort_buffer_size=200000,
435binlog_format=row,
436keep_files_on_create=OFF,
437max_join_size=4444440000000 FOR
438CREATE PROCEDURE p1() BEGIN
439SELECT @@myisam_sort_buffer_size,
440@@myisam_repair_threads,
441@@sort_buffer_size,
442@@binlog_format,
443@@keep_files_on_create,
444@@max_join_size;
445END|
446''
447'Test No 1 Post Value & Test 2 Pre values'
448SELECT @@myisam_sort_buffer_size,
449@@myisam_repair_threads,
450@@sort_buffer_size,
451@@binlog_format,
452@@keep_files_on_create,
453@@max_join_size;
454@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
455500000 1 100000 MIXED 1 2222220000000
456''
457''
458SET STATEMENT myisam_sort_buffer_size=400000,
459myisam_repair_threads=2,
460sort_buffer_size=200000,
461binlog_format=row,
462keep_files_on_create=OFF,
463max_join_size=4444440000000 FOR
464CALL p1();
465@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
466400000 2 200000 ROW 0 4444440000000
467''
468'# Post-STATEMENT No 2
469SELECT @@myisam_sort_buffer_size,
470@@myisam_repair_threads,
471@@sort_buffer_size,
472@@binlog_format,
473@@keep_files_on_create,
474@@max_join_size;
475@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
476500000 1 100000 MIXED 1 2222220000000
477''
478''
479'#------------------Test 13-----------------------#'
480'# set initial variable values
481SET SESSION myisam_sort_buffer_size=500000,
482myisam_repair_threads=1,
483sort_buffer_size=100000,
484binlog_format=mixed,
485keep_files_on_create=ON,
486max_join_size=2222220000000;
487''
488''
489CREATE PROCEDURE p2() BEGIN
490SET STATEMENT myisam_sort_buffer_size=400000,
491myisam_repair_threads=3,
492sort_buffer_size=300000,
493binlog_format=mixed,
494keep_files_on_create=OFF,
495max_join_size=3333330000000 FOR
496CALL p1();
497END|
498''
499'# Pre-STATEMENT variable value
500SELECT @@myisam_sort_buffer_size,
501@@myisam_repair_threads,
502@@sort_buffer_size,
503@@binlog_format,
504@@keep_files_on_create,
505@@max_join_size;
506@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
507500000 1 100000 MIXED 1 2222220000000
508''
509''
510SET STATEMENT myisam_sort_buffer_size=400000,
511myisam_repair_threads=2,
512sort_buffer_size=200000,
513binlog_format=row,
514keep_files_on_create=OFF,
515max_join_size=4444440000000 FOR
516CALL p2();
517@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
518400000 3 300000 MIXED 0 3333330000000
519''
520'# Post-STATEMENT
521SELECT @@myisam_sort_buffer_size,
522@@myisam_repair_threads,
523@@sort_buffer_size,
524@@binlog_format,
525@@keep_files_on_create,
526@@max_join_size;
527@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
528500000 1 100000 MIXED 1 2222220000000
529''
530''
531'#------------------Test 14-----------------------#'
532'# set initial variable values
533SET SESSION myisam_sort_buffer_size=500000,
534myisam_repair_threads=1,
535sort_buffer_size=100000,
536binlog_format=mixed,
537keep_files_on_create=ON,
538max_join_size=2222220000000;
539''
540''
541CREATE PROCEDURE p3() BEGIN
542SELECT @@myisam_sort_buffer_size,
543@@myisam_repair_threads,
544@@sort_buffer_size,
545@@binlog_format,
546@@keep_files_on_create,
547@@max_join_size;
548SET STATEMENT myisam_sort_buffer_size=320000,
549myisam_repair_threads=2,
550sort_buffer_size=220022,
551binlog_format=row,
552keep_files_on_create=ON,
553max_join_size=2222220000000 FOR
554CALL p2();
555END|
556''
557'# Pre-STATEMENT variable value
558SELECT @@myisam_sort_buffer_size,
559@@myisam_repair_threads,
560@@sort_buffer_size,
561@@binlog_format,
562@@keep_files_on_create,
563@@max_join_size;
564@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
565500000 1 100000 MIXED 1 2222220000000
566''
567''
568SET STATEMENT myisam_sort_buffer_size=400000,
569myisam_repair_threads=2,
570sort_buffer_size=200000,
571binlog_format=row,
572keep_files_on_create=OFF,
573max_join_size=4444440000000 FOR
574CALL p3();
575@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
576400000 2 200000 ROW 0 4444440000000
577@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
578400000 3 300000 MIXED 0 3333330000000
579''
580'# Post-STATEMENT
581SELECT @@myisam_sort_buffer_size,
582@@myisam_repair_threads,
583@@sort_buffer_size,
584@@binlog_format,
585@@keep_files_on_create,
586@@max_join_size;
587@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
588500000 1 100000 MIXED 1 2222220000000
589''
590''
591''
592''
593'#------------------Test 15-----------------------#'
594'# set initial variable values
595SET SESSION myisam_sort_buffer_size=500000,
596myisam_repair_threads=1,
597sort_buffer_size=100000,
598binlog_format=mixed,
599keep_files_on_create=ON,
600max_join_size=2222220000000;
601''
602''
603CREATE PROCEDURE p4() BEGIN
604SELECT @@myisam_sort_buffer_size,
605@@myisam_repair_threads,
606@@sort_buffer_size,
607@@binlog_format,
608@@keep_files_on_create,
609@@max_join_size;
610SET STATEMENT myisam_sort_buffer_size=320000,
611myisam_repair_threads=2,
612sort_buffer_size=220022,
613binlog_format=row,
614keep_files_on_create=ON,
615max_join_size=2222220000000 FOR
616SELECT @@myisam_sort_buffer_size,
617@@myisam_repair_threads,
618@@sort_buffer_size,
619@@binlog_format,
620@@keep_files_on_create,
621@@max_join_size;
622SET STATEMENT myisam_sort_buffer_size=320000,
623myisam_repair_threads=2,
624sort_buffer_size=220022,
625binlog_format=row,
626keep_files_on_create=ON,
627max_join_size=2222220000000 FOR
628SELECT @@myisam_sort_buffer_size,
629@@myisam_repair_threads,
630@@sort_buffer_size,
631@@binlog_format,
632@@keep_files_on_create,
633@@max_join_size;
634SET STATEMENT myisam_sort_buffer_size=320000,
635myisam_repair_threads=2,
636sort_buffer_size=220022,
637binlog_format=row,
638keep_files_on_create=ON,
639max_join_size=2222220000000 FOR
640SELECT @@myisam_sort_buffer_size,
641@@myisam_repair_threads,
642@@sort_buffer_size,
643@@binlog_format,
644@@keep_files_on_create,
645@@max_join_size;
646END|
647''
648'# Pre-STATEMENT variable value
649SELECT @@myisam_sort_buffer_size,
650@@myisam_repair_threads,
651@@sort_buffer_size,
652@@binlog_format,
653@@keep_files_on_create,
654@@max_join_size;
655@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
656500000 1 100000 MIXED 1 2222220000000
657''
658''
659SET STATEMENT myisam_sort_buffer_size=400000,
660myisam_repair_threads=2,
661sort_buffer_size=200000,
662binlog_format=row,
663keep_files_on_create=OFF,
664max_join_size=4444440000000 FOR
665CALL p4();
666@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
667400000 2 200000 ROW 0 4444440000000
668@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
669320000 2 220022 ROW 1 2222220000000
670@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
671320000 2 220022 ROW 1 2222220000000
672@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
673320000 2 220022 ROW 1 2222220000000
674''
675'# Post-STATEMENT
676SELECT @@myisam_sort_buffer_size,
677@@myisam_repair_threads,
678@@sort_buffer_size,
679@@binlog_format,
680@@keep_files_on_create,
681@@max_join_size;
682@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
683500000 1 100000 MIXED 1 2222220000000
684''
685''
686'#------------------Test 16-----------------------#'
687''
688'# Pre-STATEMENT variable value
689SELECT @@sql_mode;
690@@sql_mode
691NO_ENGINE_SUBSTITUTION
692''
693''
694SET STATEMENT sql_mode='ansi' FOR SELECT * FROM t1;
695v1 v2
6961 2
6973 4
698''
699'# Post-STATEMENT
700SELECT @@sql_mode;
701@@sql_mode
702NO_ENGINE_SUBSTITUTION
703''
704''
705'#------------------Test 17-----------------------#'
706'# set initial variable values
707SET SESSION myisam_sort_buffer_size=500000,
708myisam_repair_threads=1,
709sort_buffer_size=100000,
710binlog_format=mixed,
711keep_files_on_create=ON,
712max_join_size=2222220000000;
713''
714'# Pre-STATEMENT variable value
715SELECT @@myisam_sort_buffer_size,
716@@myisam_repair_threads,
717@@sort_buffer_size,
718@@binlog_format,
719@@keep_files_on_create,
720@@max_join_size;
721@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
722500000 1 100000 MIXED 1 2222220000000
723''
724''
725SET STATEMENT myisam_sort_buffer_size=320000,
726myisam_repair_threads=2,
727sort_buffer_size=220022,
728binlog_format=row,
729keep_files_on_create=ON,
730max_join_size=2222220000000
731FOR SET SESSION
732myisam_sort_buffer_size=260000,
733myisam_repair_threads=3,
734sort_buffer_size=230013,
735binlog_format=row,
736keep_files_on_create=ON,
737max_join_size=2323230000000;
738''
739'# Post-STATEMENT
740SELECT @@myisam_sort_buffer_size,
741@@myisam_repair_threads,
742@@sort_buffer_size,
743@@binlog_format,
744@@keep_files_on_create,
745@@max_join_size;
746@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
747500000 1 100000 MIXED 1 2222220000000
748''
749''
750'#------------------Test 18-----------------------#'
751'# set initial variable values
752SET SESSION myisam_sort_buffer_size=500000,
753myisam_repair_threads=1,
754sort_buffer_size=100000,
755binlog_format=mixed,
756keep_files_on_create=ON,
757max_join_size=2222220000000;
758''
759'# Pre-STATEMENT variable value
760SELECT @@myisam_sort_buffer_size,
761@@myisam_repair_threads,
762@@sort_buffer_size,
763@@binlog_format,
764@@keep_files_on_create,
765@@max_join_size;
766@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
767500000 1 100000 MIXED 1 2222220000000
768''
769''
770CREATE PROCEDURE p5() BEGIN
771SELECT @@myisam_sort_buffer_size,
772@@myisam_repair_threads,
773@@sort_buffer_size,
774@@binlog_format,
775@@keep_files_on_create,
776@@max_join_size;
777SET SESSION
778myisam_sort_buffer_size=260000,
779myisam_repair_threads=3,
780sort_buffer_size=230013,
781binlog_format=row,
782keep_files_on_create=ON,
783max_join_size=2323230000000;
784SELECT @@myisam_sort_buffer_size,
785@@myisam_repair_threads,
786@@sort_buffer_size,
787@@binlog_format,
788@@keep_files_on_create,
789@@max_join_size;
790END|
791''
792''
793SET STATEMENT myisam_sort_buffer_size=400000,
794myisam_repair_threads=2,
795sort_buffer_size=200000,
796binlog_format=row,
797keep_files_on_create=OFF,
798max_join_size=4444440000000 FOR
799CALL p5();
800@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
801400000 2 200000 ROW 0 4444440000000
802@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
803260000 3 230013 ROW 1 2323230000000
804''
805'# Post-STATEMENT
806SELECT @@myisam_sort_buffer_size,
807@@myisam_repair_threads,
808@@sort_buffer_size,
809@@binlog_format,
810@@keep_files_on_create,
811@@max_join_size;
812@@myisam_sort_buffer_size @@myisam_repair_threads @@sort_buffer_size @@binlog_format @@keep_files_on_create @@max_join_size
813500000 1 100000 MIXED 1 2222220000000
814''
815''
816'#------------------Test 19-----------------------#'
817SET STATEMENT max_error_count=100 FOR;
818ERROR 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
819SET STATEMENT max_error_count=100 INSERT t1 VALUES (1,2);
820ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INSERT t1 VALUES (1,2)' at line 1
821SET STATEMENT FOR INSERT INTO t1 VALUES (1,2);
822ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOR INSERT INTO t1 VALUES (1,2)' at line 1
823SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
824ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOR INSERT INTO t1 VALUES (1,2)' at line 1
825SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
826ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'FOR INSERT INTO t1 VALUES (1,2)' at line 1
827SET STATEMENT GLOBAL max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
828ERROR HY000: Unknown system variable 'GLOBAL'
829SET STATEMENT @@global.max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
830ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '@@global.max_error_count=100 FOR INSERT INTO t1 VALUES (1,2)' at line 1
831''
832''
833'#------------------Test 20-----------------------#'
834SET STATEMENT connect_timeout=100 FOR INSERT INTO t1 VALUES (1,2);
835ERROR HY000: Variable 'connect_timeout' is a GLOBAL variable and should be set with SET GLOBAL
836''
837''
838'#------------------Test 21-----------------------#'
839SELECT @@myisam_sort_buffer_size, @@sort_buffer_size;
840@@myisam_sort_buffer_size @@sort_buffer_size
841500000 100000
842SET STATEMENT myisam_sort_buffer_size = 700000, sort_buffer_size = 3000000
843FOR SET STATEMENT myisam_sort_buffer_size=200000
844FOR SELECT @@myisam_sort_buffer_size, @@sort_buffer_size;
845@@myisam_sort_buffer_size @@sort_buffer_size
846200000 3000000
847SELECT @@myisam_sort_buffer_size, @@sort_buffer_size;
848@@myisam_sort_buffer_size @@sort_buffer_size
849500000 100000
850''
851''
852'#------------------Test 22-----------------------#'
853CREATE TABLE STATEMENT(a INT);
854DROP TABLE STATEMENT;
855''
856'# Cleanup'
857DROP TABLE t1;
858DROP PROCEDURE p1;
859DROP PROCEDURE p2;
860DROP PROCEDURE p3;
861DROP PROCEDURE p4;
862DROP PROCEDURE p5;
0863
=== added file 'Percona-Server/mysql-test/suite/rpl/include/percona_rpl_set_statement_variable_test.inc'
--- Percona-Server/mysql-test/suite/rpl/include/percona_rpl_set_statement_variable_test.inc 1970-01-01 00:00:00 +0000
+++ Percona-Server/mysql-test/suite/rpl/include/percona_rpl_set_statement_variable_test.inc 2013-09-27 17:50:05 +0000
@@ -0,0 +1,31 @@
1#Check if the variable is replicated correctly with "SET STATEMENT"
2# Usage:
3# $rpl_ssvt_var_name - the name of tested variable;
4# $rpl_ssvt_var_value - the value to set;
5# $rpl_ssvt_table - the table name to insert values.
6
7--connection master
8--echo [connection master]
9eval SELECT @@$rpl_ssvt_var_name;
10
11--connection slave
12--echo [connection slave]
13eval SELECT @@$rpl_ssvt_var_name;
14
15--connection master
16--echo [connection master]
17--disable_result_log
18eval SET STATEMENT $rpl_ssvt_var_name=$rpl_ssvt_var_value FOR
19 INSERT INTO $rpl_ssvt_table VALUES(@@$rpl_ssvt_var_name);
20--enable_result_log
21eval SELECT @@$rpl_ssvt_var_name;
22
23--sync_slave_with_master
24--echo [connection slave]
25eval SELECT * FROM $rpl_ssvt_table;
26eval SELECT @@$rpl_ssvt_var_name;
27
28--connection master
29--echo [connection master]
30eval DELETE FROM $rpl_ssvt_table;
31
032
=== added file 'Percona-Server/mysql-test/suite/rpl/r/percona_rpl_stm_per_query_variables_settings.result'
--- Percona-Server/mysql-test/suite/rpl/r/percona_rpl_stm_per_query_variables_settings.result 1970-01-01 00:00:00 +0000
+++ Percona-Server/mysql-test/suite/rpl/r/percona_rpl_stm_per_query_variables_settings.result 2013-09-27 17:50:05 +0000
@@ -0,0 +1,143 @@
1include/master-slave.inc
2Warnings:
3Note #### Sending passwords in plain text without SSL/TLS is extremely insecure.
4Note #### 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.
5[connection master]
6DROP TABLE IF EXISTS t1;
7DROP TABLE IF EXISTS t2;
8call mtr.add_suppression("Unsafe statement written to the binary log*");
9CREATE TABLE t1 (a bigint unsigned not null);
10CREATE TABLE t2 (a char(255) not null);
11
12There are the following types of variables:
131) variables that are NOT replicated correctly when using STATEMENT mode;
14
15[connection master]
16SELECT @@max_join_size;
17@@max_join_size
1818446744073709551615
19[connection slave]
20SELECT @@max_join_size;
21@@max_join_size
2218446744073709551615
23[connection master]
24SET STATEMENT max_join_size=2 FOR
25INSERT INTO t1 VALUES(@@max_join_size);
26SELECT @@max_join_size;
27@@max_join_size
2818446744073709551615
29[connection slave]
30SELECT * FROM t1;
31a
3218446744073709551615
33SELECT @@max_join_size;
34@@max_join_size
3518446744073709551615
36[connection master]
37DELETE FROM t1;
38
392) variables thar ARE replicated correctly
40They must be replicated correctly with "SET STATEMENT" too.
41
42[connection master]
43SELECT @@auto_increment_increment;
44@@auto_increment_increment
451
46[connection slave]
47SELECT @@auto_increment_increment;
48@@auto_increment_increment
491
50[connection master]
51SET STATEMENT auto_increment_increment=10 FOR
52INSERT INTO t1 VALUES(@@auto_increment_increment);
53SELECT @@auto_increment_increment;
54@@auto_increment_increment
551
56[connection slave]
57SELECT * FROM t1;
58a
5910
60SELECT @@auto_increment_increment;
61@@auto_increment_increment
621
63[connection master]
64DELETE FROM t1;
65
663) sql_mode which is replicated correctly exept NO_DIR_IN_CREATE value;
67
68[connection master]
69SELECT @@sql_mode;
70@@sql_mode
71NO_ENGINE_SUBSTITUTION
72[connection slave]
73SELECT @@sql_mode;
74@@sql_mode
75NO_ENGINE_SUBSTITUTION
76[connection master]
77SET STATEMENT sql_mode='ERROR_FOR_DIVISION_BY_ZERO' FOR
78INSERT INTO t2 VALUES(@@sql_mode);
79SELECT @@sql_mode;
80@@sql_mode
81NO_ENGINE_SUBSTITUTION
82[connection slave]
83SELECT * FROM t2;
84a
85ERROR_FOR_DIVISION_BY_ZERO
86SELECT @@sql_mode;
87@@sql_mode
88NO_ENGINE_SUBSTITUTION
89[connection master]
90DELETE FROM t2;
91[connection master]
92SELECT @@sql_mode;
93@@sql_mode
94NO_ENGINE_SUBSTITUTION
95[connection slave]
96SELECT @@sql_mode;
97@@sql_mode
98NO_ENGINE_SUBSTITUTION
99[connection master]
100SET STATEMENT sql_mode='NO_DIR_IN_CREATE' FOR
101INSERT INTO t2 VALUES(@@sql_mode);
102SELECT @@sql_mode;
103@@sql_mode
104NO_ENGINE_SUBSTITUTION
105[connection slave]
106SELECT * FROM t2;
107a
108
109SELECT @@sql_mode;
110@@sql_mode
111NO_ENGINE_SUBSTITUTION
112[connection master]
113DELETE FROM t2;
114
1154) variables that are not replicated at all:
116default_storage_engine, storage_engine, max_heap_table_size
117
118[connection master]
119SELECT @@max_heap_table_size;
120@@max_heap_table_size
1211048576
122[connection slave]
123SELECT @@max_heap_table_size;
124@@max_heap_table_size
1251048576
126[connection master]
127SET STATEMENT max_heap_table_size=16384 FOR
128INSERT INTO t1 VALUES(@@max_heap_table_size);
129SELECT @@max_heap_table_size;
130@@max_heap_table_size
1311048576
132[connection slave]
133SELECT * FROM t1;
134a
1351048576
136SELECT @@max_heap_table_size;
137@@max_heap_table_size
1381048576
139[connection master]
140DELETE FROM t1;
141DROP TABLE t1;
142DROP TABLE t2;
143include/stop_slave.inc
0144
=== added file 'Percona-Server/mysql-test/suite/rpl/t/percona_rpl_stm_per_query_variables_settings.test'
--- Percona-Server/mysql-test/suite/rpl/t/percona_rpl_stm_per_query_variables_settings.test 1970-01-01 00:00:00 +0000
+++ Percona-Server/mysql-test/suite/rpl/t/percona_rpl_stm_per_query_variables_settings.test 2013-09-27 17:50:05 +0000
@@ -0,0 +1,57 @@
1--source include/master-slave.inc
2--source include/have_binlog_format_statement.inc
3
4--disable_warnings
5DROP TABLE IF EXISTS t1;
6DROP TABLE IF EXISTS t2;
7--enable_warnings
8
9call mtr.add_suppression("Unsafe statement written to the binary log*");
10CREATE TABLE t1 (a bigint unsigned not null);
11CREATE TABLE t2 (a char(255) not null);
12
13--echo
14--echo There are the following types of variables:
15--echo 1) variables that are NOT replicated correctly when using STATEMENT mode;
16--echo
17
18--let $rpl_ssvt_var_name=max_join_size
19--let $rpl_ssvt_var_value=2
20--let $rpl_ssvt_table=t1
21--source suite/rpl/include/percona_rpl_set_statement_variable_test.inc
22
23--echo
24--echo 2) variables thar ARE replicated correctly
25--echo They must be replicated correctly with "SET STATEMENT" too.
26--echo
27--let $rpl_ssvt_var_name=auto_increment_increment
28--let $rpl_ssvt_var_value=10
29--let $rpl_ssvt_table=t1
30--source suite/rpl/include/percona_rpl_set_statement_variable_test.inc
31
32--echo
33--echo 3) sql_mode which is replicated correctly exept NO_DIR_IN_CREATE value;
34--echo
35--let $rpl_ssvt_var_name=sql_mode
36--let $rpl_ssvt_var_value='ERROR_FOR_DIVISION_BY_ZERO'
37--let $rpl_ssvt_table=t2
38--source suite/rpl/include/percona_rpl_set_statement_variable_test.inc
39--let $rpl_ssvt_var_name=sql_mode
40--let $rpl_ssvt_var_value='NO_DIR_IN_CREATE'
41--let $rpl_ssvt_table=t2
42--source suite/rpl/include/percona_rpl_set_statement_variable_test.inc
43
44--echo
45--echo 4) variables that are not replicated at all:
46--echo default_storage_engine, storage_engine, max_heap_table_size
47--echo
48--let $rpl_ssvt_var_name=max_heap_table_size
49--let $rpl_ssvt_var_value=16384
50--let $rpl_ssvt_table=t1
51--source suite/rpl/include/percona_rpl_set_statement_variable_test.inc
52
53connection master;
54DROP TABLE t1;
55DROP TABLE t2;
56sync_slave_with_master;
57source include/stop_slave.inc;
058
=== added file 'Percona-Server/mysql-test/t/percona_statement_set.test'
--- Percona-Server/mysql-test/t/percona_statement_set.test 1970-01-01 00:00:00 +0000
+++ Percona-Server/mysql-test/t/percona_statement_set.test 2013-09-27 17:50:05 +0000
@@ -0,0 +1,830 @@
1--echo '# SET STATEMENT ..... FOR .... TEST'
2############################ STATEMENT_SET #############################
3# #
4# Testing working functionality of SET STATEMENT #
5# #
6# #
7# There is important documentation within #
8# #
9# #
10# Author: Joe Lukas #
11# Creation: #
12# 2009-08-02 Implement this test as part of #
13# WL#681 Per query variable settings #
14# #
15########################################################################
16
17--disable_warnings
18DROP TABLE IF EXISTS t1;
19DROP FUNCTION IF EXISTS myProc;
20DROP PROCEDURE IF EXISTS p1;
21DROP PROCEDURE IF EXISTS p2;
22DROP PROCEDURE IF EXISTS p3;
23DROP PROCEDURE IF EXISTS p4;
24DROP PROCEDURE IF EXISTS p5;
25DROP TABLE IF EXISTS STATEMENT;
26--enable_warnings
27####################################################################
28#Set up current database
29####################################################################
30--echo '# Setup database'
31CREATE TABLE t1 (v1 INT, v2 INT);
32INSERT INTO t1 VALUES (1,2);
33INSERT INTO t1 VALUES (3,4);
34--echo ''
35--echo '#------------------ STATEMENT Test 1 -----------------------#'
36####################################################################
37# Checks with variable value type ulong #
38####################################################################
39--echo '# Initialize variables to known setting'
40SET SESSION sort_buffer_size=100000;
41--echo ''
42--echo '# Pre-STATEMENT variable value'
43SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
44SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t1;
45--echo ''
46--echo '# Post-STATEMENT variable value'
47SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
48--echo ''
49--echo '#------------------ STATEMENT Test 2 -----------------------#'
50####################################################################
51# Checks for multiple set values inside STATEMENT ... FOR #
52####################################################################
53--echo '# Initialize variables to known setting'
54SET SESSION binlog_format=mixed;
55SET SESSION sort_buffer_size=100000;
56--echo '# Pre-STATEMENT variable value'
57SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
58SHOW SESSION VARIABLES LIKE 'binlog_format';
59SET STATEMENT sort_buffer_size=150000, binlog_format=row
60 FOR SELECT * FROM t1;
61--echo '# Post-STATEMENT variable value'
62SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
63SHOW SESSION VARIABLES LIKE 'binlog_format';
64
65--echo ''
66--echo '#------------------ STATEMENT Test 3 -----------------------#'
67####################################################################
68# Check current variable value is stored in using stored #
69# statements. #
70####################################################################
71--echo '# set initial variable value, make prepared statement
72SET SESSION binlog_format=row;
73PREPARE stmt1 FROM 'SET STATEMENT binlog_format=row FOR SELECT * FROM t1';
74--echo ''
75--echo '# Change variable setting'
76SET SESSION binlog_format=mixed;
77--echo ''
78--echo '# Pre-STATEMENT variable value'
79--echo ''
80SHOW SESSION VARIABLES LIKE 'binlog_format';
81--echo ''
82EXECUTE stmt1;
83--echo ''
84--echo '# Post-STATEMENT variable value'
85SHOW SESSION VARIABLES LIKE 'binlog_format';
86
87--echo ''
88DEALLOCATE PREPARE stmt1;
89--echo '#------------------ STATEMENT Test 4 -----------------------#'
90####################################################################
91# Check works with OPTIMIZE TABLE command #
92# Checks works with a variable value of type INT #
93# Checks works with variable type ULONGLONG #
94####################################################################
95--echo '# set initial variable value, make prepared statement
96SET SESSION myisam_sort_buffer_size=500000, myisam_repair_threads=1;
97--echo ''
98--echo '# Pre-STATEMENT variable value'
99SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
100SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
101--echo ''
102SET STATEMENT myisam_sort_buffer_size=800000,
103 myisam_repair_threads=2 FOR OPTIMIZE TABLE t1;
104--echo ''
105--echo '# Post-STATEMENT variable value'
106SHOW SESSION VARIABLES LIKE 'myisam_sort_buffer_size';
107SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
108
109--echo ''
110--echo '#------------------ STATEMENT Test 5 -----------------------#'
111####################################################################
112# Checks if variable reset after error in statement after FOR #
113####################################################################
114--echo '# Initialize variables to known setting'
115SET SESSION sort_buffer_size=100000;
116--echo ''
117--echo '# Pre-STATEMENT variable value'
118SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
119--echo ''
120--error ER_NO_SUCH_TABLE
121SET STATEMENT sort_buffer_size=150000 FOR SELECT * FROM t2;
122--echo ''
123--echo '# Post-STATEMENT variable value'
124SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
125
126--echo ''
127--echo '#------------------ STATEMENT Test 6 -----------------------#'
128####################################################################
129# Checks works with variable type MY_BOOL #
130####################################################################
131--echo '# Initialize variables to known setting'
132SET SESSION keep_files_on_create=ON;
133--echo ''
134--echo '# Pre-STATEMENT variable value'
135SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
136--echo ''
137SET STATEMENT keep_files_on_create=OFF FOR SELECT * FROM t1;
138--echo ''
139--echo '# Post-STATEMENT variable value'
140SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
141
142--echo ''
143--echo '#------------------ STATEMENT Test 7 -----------------------#'
144####################################################################
145# Checks works with variable type HA_ROWS #
146####################################################################
147--echo '# Initialize variables to known setting'
148SET SESSION max_join_size=2222220000000;
149--echo ''
150--echo '# Pre-STATEMENT variable value'
151SHOW SESSION VARIABLES LIKE 'max_join_size';
152--echo ''
153SET STATEMENT max_join_size=1000000000000 FOR SELECT * FROM t1;
154--echo ''
155--echo '# Post-STATEMENT variable value'
156SHOW SESSION VARIABLES LIKE 'max_join_size';
157
158--echo ''
159--echo '#------------------Test 8-----------------------#'
160####################################################################
161# Ensure variable of each type is set to proper value during #
162# statement after FOR execution #
163####################################################################
164--echo '# Initialize test variables'
165SET SESSION myisam_sort_buffer_size=500000,
166 myisam_repair_threads=1,
167 sort_buffer_size = 200000,
168 max_join_size=2222220000000,
169 keep_files_on_create=ON;
170
171--echo ''
172--echo '# LONG '
173SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
174SET STATEMENT sort_buffer_size = 100000
175 FOR SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
176SHOW SESSION VARIABLES LIKE 'sort_buffer_size';
177--echo ''
178--echo '# MY_BOOL '
179SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
180SET STATEMENT keep_files_on_create=OFF
181 FOR SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
182SHOW SESSION VARIABLES LIKE 'keep_files_on_create';
183
184--echo ''
185--echo '# INT/LONG '
186SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
187SET STATEMENT myisam_repair_threads=2
188 FOR SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
189SHOW SESSION VARIABLES LIKE 'myisam_repair_threads';
190--echo ''
191--echo '# ULONGLONG '
192SHOW SESSION VARIABLES LIKE 'max_join_size';
193SET STATEMENT max_join_size=2000000000000
194 FOR SHOW SESSION VARIABLES LIKE 'max_join_size';
195SHOW SESSION VARIABLES LIKE 'max_join_size';
196
197--echo ''
198--echo '#------------------Test 9-----------------------#'
199####################################################################
200# No 1 - Check works with CREATE ... BEGIN ... END command #
201# Display variables during execution #
202# No 2 - Test with DROP command #
203####################################################################
204--echo '# set initial variable values
205SET SESSION myisam_sort_buffer_size=500000,
206 myisam_repair_threads=1,
207 sort_buffer_size=100000,
208 binlog_format=mixed,
209 keep_files_on_create=ON,
210 max_join_size=2222220000000;
211--echo ''
212--echo ''
213--echo '# Pre-STATEMENT variable value
214SELECT @@myisam_sort_buffer_size,
215 @@myisam_repair_threads,
216 @@sort_buffer_size,
217 @@binlog_format,
218 @@keep_files_on_create,
219 @@max_join_size;
220--echo ''
221--echo ''
222DELIMITER |;
223CREATE FUNCTION myProc (cost DECIMAL(10,2))
224 RETURNS DECIMAL(10,2)
225
226 SQL SECURITY DEFINER
227
228 tax: BEGIN
229 DECLARE order_tax DECIMAL(10,2);
230 SET order_tax = cost * .05;
231 RETURN order_tax;
232 END|
233DELIMITER ;|
234--echo ''
235--echo '# During Execution values
236SET STATEMENT myisam_sort_buffer_size=400000,
237 myisam_repair_threads=2,
238 sort_buffer_size=200000,
239 binlog_format=row,
240 keep_files_on_create=OFF,
241 max_join_size=4444440000000 FOR
242 SELECT myProc(123.45);
243--echo ''
244--echo '# Post-STATEMENT No 1 variable value Pre-STATEMENT for No 2'
245SELECT @@myisam_sort_buffer_size,
246 @@myisam_repair_threads,
247 @@sort_buffer_size,
248 @@binlog_format,
249 @@keep_files_on_create,
250 @@max_join_size;
251--echo ''
252SET STATEMENT myisam_sort_buffer_size=400000,
253 myisam_repair_threads=2,
254 sort_buffer_size=200000,
255 binlog_format=row,
256 keep_files_on_create=OFF,
257 max_join_size=4444440000000 FOR
258 DROP FUNCTION myProc;
259--echo ''
260--echo '# Post-STATEMENT No 2 variable value
261SELECT @@myisam_sort_buffer_size,
262 @@myisam_repair_threads,
263 @@sort_buffer_size,
264 @@binlog_format,
265 @@keep_files_on_create,
266 @@max_join_size;
267
268--echo ''
269--echo '#------------------Test 10-----------------------#'
270####################################################################
271# No 1 - Check with PREPARE statement #
272# with STATEMENT inside with same variable as outside #
273# No 2 - Check with EXECUTE statement #
274####################################################################
275--echo '# set initial variable values
276SET SESSION myisam_sort_buffer_size=500000,
277 myisam_repair_threads=1,
278 sort_buffer_size=100000,
279 binlog_format=mixed,
280 keep_files_on_create=ON,
281 max_join_size=2222220000000;
282--echo ''
283--echo '# Pre-STATEMENT variable value
284SELECT @@myisam_sort_buffer_size,
285 @@myisam_repair_threads,
286 @@sort_buffer_size,
287 @@binlog_format,
288 @@keep_files_on_create,
289 @@max_join_size;
290--echo ''
291--echo ''
292SET STATEMENT myisam_sort_buffer_size=400000,
293 myisam_repair_threads=2,
294 sort_buffer_size=200000,
295 binlog_format=row,
296 keep_files_on_create=OFF,
297 max_join_size=4444440000000 FOR
298 PREPARE stmt2
299 FROM 'SELECT * FROM t1';
300--echo ''
301--echo 'Test No 1 Post Value & Test 2 Pre values'
302SELECT @@myisam_sort_buffer_size,
303 @@myisam_repair_threads,
304 @@sort_buffer_size,
305 @@binlog_format,
306 @@keep_files_on_create,
307 @@max_join_size;
308--echo ''
309--echo ''
310SET STATEMENT myisam_sort_buffer_size=400000,
311 myisam_repair_threads=2,
312 sort_buffer_size=200000,
313 binlog_format=row,
314 keep_files_on_create=OFF,
315 max_join_size=4444440000000 FOR
316 EXECUTE stmt2;
317--echo ''
318--echo '# Post-STATEMENT No 2
319SELECT @@myisam_sort_buffer_size,
320 @@myisam_repair_threads,
321 @@sort_buffer_size,
322 @@binlog_format,
323 @@keep_files_on_create,
324 @@max_join_size;
325--echo ''
326DEALLOCATE PREPARE stmt2;
327--echo ''
328--echo '#------------------Test 11-----------------------#'
329####################################################################
330# No 1 - Check with PREPARE statement #
331# check with different variable on inside PREPARE #
332# No 2 - Check with EXECUTE statement #
333####################################################################
334--echo '# set initial variable values
335SET SESSION myisam_sort_buffer_size=500000,
336 myisam_repair_threads=1,
337 sort_buffer_size=100000,
338 binlog_format=mixed,
339 keep_files_on_create=ON,
340 max_join_size=2222220000000;
341--echo ''
342--echo ''
343--echo '# Pre-STATEMENT variable value
344SELECT @@myisam_sort_buffer_size,
345 @@myisam_repair_threads,
346 @@sort_buffer_size,
347 @@binlog_format,
348 @@keep_files_on_create,
349 @@max_join_size;
350--echo ''
351--echo ''
352SET STATEMENT myisam_sort_buffer_size=400000,
353 myisam_repair_threads=2,
354 sort_buffer_size=200000,
355 keep_files_on_create=OFF,
356 max_join_size=4444440000000 FOR
357 PREPARE stmt1 FROM
358 'SET STATEMENT binlog_format=row FOR SELECT * FROM t1';
359--echo ''
360--echo 'Test No 1 Post Value & Test 2 Pre values'
361SELECT @@myisam_sort_buffer_size,
362 @@myisam_repair_threads,
363 @@sort_buffer_size,
364 @@binlog_format,
365 @@keep_files_on_create,
366 @@max_join_size;
367--echo ''
368--echo ''
369SET STATEMENT myisam_sort_buffer_size=400000,
370 myisam_repair_threads=2,
371 sort_buffer_size=200000,
372 keep_files_on_create=OFF,
373 max_join_size=4444440000000 FOR
374 EXECUTE stmt1;
375--echo ''
376--echo '# Post-STATEMENT No 2
377SELECT @@myisam_sort_buffer_size,
378 @@myisam_repair_threads,
379 @@sort_buffer_size,
380 @@binlog_format,
381 @@keep_files_on_create,
382 @@max_join_size;
383--echo ''
384--echo ''
385--echo '#------------------Test 12-----------------------#'
386####################################################################
387# No 1 - Check with PROCEDURE (show variables in procedure) #
388# No 2 - Check with CALL statement show variables in PROCEDURE #
389####################################################################
390--echo '# set initial variable values
391SET SESSION myisam_sort_buffer_size=500000,
392 myisam_repair_threads=1,
393 sort_buffer_size=100000,
394 binlog_format=mixed,
395 keep_files_on_create=ON,
396 max_join_size=2222220000000;
397--echo ''
398--echo ''
399--echo '# Pre-STATEMENT variable value
400SELECT @@myisam_sort_buffer_size,
401 @@myisam_repair_threads,
402 @@sort_buffer_size,
403 @@binlog_format,
404 @@keep_files_on_create,
405 @@max_join_size;
406--echo ''
407--echo ''
408DELIMITER |;
409SET STATEMENT myisam_sort_buffer_size=400000,
410 myisam_repair_threads=2,
411 sort_buffer_size=200000,
412 binlog_format=row,
413 keep_files_on_create=OFF,
414 max_join_size=4444440000000 FOR
415 CREATE PROCEDURE p1() BEGIN
416 SELECT @@myisam_sort_buffer_size,
417 @@myisam_repair_threads,
418 @@sort_buffer_size,
419 @@binlog_format,
420 @@keep_files_on_create,
421 @@max_join_size;
422 END|
423DELIMITER ;|
424--echo ''
425--echo 'Test No 1 Post Value & Test 2 Pre values'
426SELECT @@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--echo ''
433--echo ''
434SET STATEMENT myisam_sort_buffer_size=400000,
435 myisam_repair_threads=2,
436 sort_buffer_size=200000,
437 binlog_format=row,
438 keep_files_on_create=OFF,
439 max_join_size=4444440000000 FOR
440 CALL p1();
441--echo ''
442--echo '# Post-STATEMENT No 2
443SELECT @@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--echo ''
450--echo ''
451
452--echo '#------------------Test 13-----------------------#'
453####################################################################
454# Check PROCEDURE containing SET STATEMENT FOR #
455# p1() from test 12 will be used to display variables #
456####################################################################
457--echo '# set initial variable values
458SET SESSION myisam_sort_buffer_size=500000,
459 myisam_repair_threads=1,
460 sort_buffer_size=100000,
461 binlog_format=mixed,
462 keep_files_on_create=ON,
463 max_join_size=2222220000000;
464--echo ''
465--echo ''
466DELIMITER |;
467CREATE PROCEDURE p2() BEGIN
468 SET STATEMENT myisam_sort_buffer_size=400000,
469 myisam_repair_threads=3,
470 sort_buffer_size=300000,
471 binlog_format=mixed,
472 keep_files_on_create=OFF,
473 max_join_size=3333330000000 FOR
474 CALL p1();
475 END|
476DELIMITER ;|
477--echo ''
478--echo '# Pre-STATEMENT variable value
479SELECT @@myisam_sort_buffer_size,
480 @@myisam_repair_threads,
481 @@sort_buffer_size,
482 @@binlog_format,
483 @@keep_files_on_create,
484 @@max_join_size;
485--echo ''
486--echo ''
487SET STATEMENT myisam_sort_buffer_size=400000,
488 myisam_repair_threads=2,
489 sort_buffer_size=200000,
490 binlog_format=row,
491 keep_files_on_create=OFF,
492 max_join_size=4444440000000 FOR
493 CALL p2();
494--echo ''
495--echo '# Post-STATEMENT
496SELECT @@myisam_sort_buffer_size,
497 @@myisam_repair_threads,
498 @@sort_buffer_size,
499 @@binlog_format,
500 @@keep_files_on_create,
501 @@max_join_size;
502--echo ''
503--echo ''
504--echo '#------------------Test 14-----------------------#'
505####################################################################
506# Check PROCEDURE containing compound SET STATEMENT FOR #
507# p2() will be used as compounding statement from test 13 #
508####################################################################
509--echo '# set initial variable values
510SET SESSION myisam_sort_buffer_size=500000,
511 myisam_repair_threads=1,
512 sort_buffer_size=100000,
513 binlog_format=mixed,
514 keep_files_on_create=ON,
515 max_join_size=2222220000000;
516--echo ''
517--echo ''
518DELIMITER |;
519CREATE PROCEDURE p3() BEGIN
520 SELECT @@myisam_sort_buffer_size,
521 @@myisam_repair_threads,
522 @@sort_buffer_size,
523 @@binlog_format,
524 @@keep_files_on_create,
525 @@max_join_size;
526 SET STATEMENT myisam_sort_buffer_size=320000,
527 myisam_repair_threads=2,
528 sort_buffer_size=220022,
529 binlog_format=row,
530 keep_files_on_create=ON,
531 max_join_size=2222220000000 FOR
532 CALL p2();
533 END|
534DELIMITER ;|
535--echo ''
536--echo '# Pre-STATEMENT variable value
537SELECT @@myisam_sort_buffer_size,
538 @@myisam_repair_threads,
539 @@sort_buffer_size,
540 @@binlog_format,
541 @@keep_files_on_create,
542 @@max_join_size;
543--echo ''
544--echo ''
545SET STATEMENT myisam_sort_buffer_size=400000,
546 myisam_repair_threads=2,
547 sort_buffer_size=200000,
548 binlog_format=row,
549 keep_files_on_create=OFF,
550 max_join_size=4444440000000 FOR
551 CALL p3();
552--echo ''
553--echo '# Post-STATEMENT
554SELECT @@myisam_sort_buffer_size,
555 @@myisam_repair_threads,
556 @@sort_buffer_size,
557 @@binlog_format,
558 @@keep_files_on_create,
559 @@max_join_size;
560--echo ''
561--echo ''
562
563 --echo ''
564--echo ''
565--echo '#------------------Test 15-----------------------#'
566####################################################################
567# Check PROCEDURE containing compound SET STATEMENT FOR #
568# call multiple SET STATEMENT .. FOR showing SELECT #
569####################################################################
570--echo '# set initial variable values
571SET SESSION myisam_sort_buffer_size=500000,
572 myisam_repair_threads=1,
573 sort_buffer_size=100000,
574 binlog_format=mixed,
575 keep_files_on_create=ON,
576 max_join_size=2222220000000;
577--echo ''
578--echo ''
579DELIMITER |;
580CREATE PROCEDURE p4() BEGIN
581 SELECT @@myisam_sort_buffer_size,
582 @@myisam_repair_threads,
583 @@sort_buffer_size,
584 @@binlog_format,
585 @@keep_files_on_create,
586 @@max_join_size;
587 SET STATEMENT myisam_sort_buffer_size=320000,
588 myisam_repair_threads=2,
589 sort_buffer_size=220022,
590 binlog_format=row,
591 keep_files_on_create=ON,
592 max_join_size=2222220000000 FOR
593 SELECT @@myisam_sort_buffer_size,
594 @@myisam_repair_threads,
595 @@sort_buffer_size,
596 @@binlog_format,
597 @@keep_files_on_create,
598 @@max_join_size;
599 SET STATEMENT myisam_sort_buffer_size=320000,
600 myisam_repair_threads=2,
601 sort_buffer_size=220022,
602 binlog_format=row,
603 keep_files_on_create=ON,
604 max_join_size=2222220000000 FOR
605 SELECT @@myisam_sort_buffer_size,
606 @@myisam_repair_threads,
607 @@sort_buffer_size,
608 @@binlog_format,
609 @@keep_files_on_create,
610 @@max_join_size;
611 SET STATEMENT myisam_sort_buffer_size=320000,
612 myisam_repair_threads=2,
613 sort_buffer_size=220022,
614 binlog_format=row,
615 keep_files_on_create=ON,
616 max_join_size=2222220000000 FOR
617 SELECT @@myisam_sort_buffer_size,
618 @@myisam_repair_threads,
619 @@sort_buffer_size,
620 @@binlog_format,
621 @@keep_files_on_create,
622 @@max_join_size;
623 END|
624DELIMITER ;|
625--echo ''
626--echo '# Pre-STATEMENT variable value
627SELECT @@myisam_sort_buffer_size,
628 @@myisam_repair_threads,
629 @@sort_buffer_size,
630 @@binlog_format,
631 @@keep_files_on_create,
632 @@max_join_size;
633--echo ''
634--echo ''
635SET STATEMENT myisam_sort_buffer_size=400000,
636 myisam_repair_threads=2,
637 sort_buffer_size=200000,
638 binlog_format=row,
639 keep_files_on_create=OFF,
640 max_join_size=4444440000000 FOR
641 CALL p4();
642--echo ''
643--echo '# Post-STATEMENT
644SELECT @@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
651--echo ''
652--echo ''
653--echo '#------------------Test 16-----------------------#'
654####################################################################
655# Test Effect on parsing #
656####################################################################
657--echo ''
658--echo '# Pre-STATEMENT variable value
659SELECT @@sql_mode;
660--echo ''
661--echo ''
662SET STATEMENT sql_mode='ansi' FOR SELECT * FROM t1;
663
664--echo ''
665--echo '# Post-STATEMENT
666SELECT @@sql_mode;
667--echo ''
668--echo ''
669--echo '#------------------Test 17-----------------------#'
670####################################################################
671# Test effect of SET STATEMENT FOR with SET SESSION modifying #
672# the same variables as the SET STATEMENT #
673####################################################################
674--echo '# set initial variable values
675SET SESSION myisam_sort_buffer_size=500000,
676 myisam_repair_threads=1,
677 sort_buffer_size=100000,
678 binlog_format=mixed,
679 keep_files_on_create=ON,
680 max_join_size=2222220000000;
681--echo ''
682--echo '# Pre-STATEMENT variable value
683SELECT @@myisam_sort_buffer_size,
684 @@myisam_repair_threads,
685 @@sort_buffer_size,
686 @@binlog_format,
687 @@keep_files_on_create,
688 @@max_join_size;
689--echo ''
690--echo ''
691SET STATEMENT myisam_sort_buffer_size=320000,
692 myisam_repair_threads=2,
693 sort_buffer_size=220022,
694 binlog_format=row,
695 keep_files_on_create=ON,
696 max_join_size=2222220000000
697 FOR SET SESSION
698 myisam_sort_buffer_size=260000,
699 myisam_repair_threads=3,
700 sort_buffer_size=230013,
701 binlog_format=row,
702 keep_files_on_create=ON,
703 max_join_size=2323230000000;
704
705--echo ''
706--echo '# Post-STATEMENT
707SELECT @@myisam_sort_buffer_size,
708 @@myisam_repair_threads,
709 @@sort_buffer_size,
710 @@binlog_format,
711 @@keep_files_on_create,
712 @@max_join_size;
713
714--echo ''
715--echo ''
716--echo '#------------------Test 18-----------------------#'
717####################################################################
718# Test effect of SET SESSION inside a stored procedure with #
719# with a SET STATEMENT on outside variables #
720####################################################################
721--echo '# set initial variable values
722SET SESSION myisam_sort_buffer_size=500000,
723 myisam_repair_threads=1,
724 sort_buffer_size=100000,
725 binlog_format=mixed,
726 keep_files_on_create=ON,
727 max_join_size=2222220000000;
728--echo ''
729--echo '# Pre-STATEMENT variable value
730SELECT @@myisam_sort_buffer_size,
731 @@myisam_repair_threads,
732 @@sort_buffer_size,
733 @@binlog_format,
734 @@keep_files_on_create,
735 @@max_join_size;
736--echo ''
737--echo ''
738DELIMITER |;
739CREATE PROCEDURE p5() BEGIN
740 SELECT @@myisam_sort_buffer_size,
741 @@myisam_repair_threads,
742 @@sort_buffer_size,
743 @@binlog_format,
744 @@keep_files_on_create,
745 @@max_join_size;
746 SET SESSION
747 myisam_sort_buffer_size=260000,
748 myisam_repair_threads=3,
749 sort_buffer_size=230013,
750 binlog_format=row,
751 keep_files_on_create=ON,
752 max_join_size=2323230000000;
753 SELECT @@myisam_sort_buffer_size,
754 @@myisam_repair_threads,
755 @@sort_buffer_size,
756 @@binlog_format,
757 @@keep_files_on_create,
758 @@max_join_size;
759 END|
760DELIMITER ;|
761--echo ''
762--echo ''
763SET STATEMENT myisam_sort_buffer_size=400000,
764 myisam_repair_threads=2,
765 sort_buffer_size=200000,
766 binlog_format=row,
767 keep_files_on_create=OFF,
768 max_join_size=4444440000000 FOR
769 CALL p5();
770
771--echo ''
772--echo '# Post-STATEMENT
773SELECT @@myisam_sort_buffer_size,
774 @@myisam_repair_threads,
775 @@sort_buffer_size,
776 @@binlog_format,
777 @@keep_files_on_create,
778 @@max_join_size;
779
780--echo ''
781--echo ''
782--echo '#------------------Test 19-----------------------#'
783#Test for bad syntax
784--error ER_PARSE_ERROR
785SET STATEMENT max_error_count=100 FOR;
786--error ER_PARSE_ERROR
787SET STATEMENT max_error_count=100 INSERT t1 VALUES (1,2);
788--error ER_PARSE_ERROR
789SET STATEMENT FOR INSERT INTO t1 VALUES (1,2);
790--error ER_PARSE_ERROR
791SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
792--error ER_PARSE_ERROR
793SET max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
794--error ER_UNKNOWN_SYSTEM_VARIABLE
795SET STATEMENT GLOBAL max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
796--error ER_PARSE_ERROR
797SET STATEMENT @@global.max_error_count=100 FOR INSERT INTO t1 VALUES (1,2);
798
799--echo ''
800--echo ''
801--echo '#------------------Test 20-----------------------#'
802#Test for global-only variables
803--error ER_GLOBAL_VARIABLE
804SET STATEMENT connect_timeout=100 FOR INSERT INTO t1 VALUES (1,2);
805
806--echo ''
807--echo ''
808--echo '#------------------Test 21-----------------------#'
809#Test for recursion
810SELECT @@myisam_sort_buffer_size, @@sort_buffer_size;
811SET STATEMENT myisam_sort_buffer_size = 700000, sort_buffer_size = 3000000
812 FOR SET STATEMENT myisam_sort_buffer_size=200000
813 FOR SELECT @@myisam_sort_buffer_size, @@sort_buffer_size;
814SELECT @@myisam_sort_buffer_size, @@sort_buffer_size;
815
816--echo ''
817--echo ''
818--echo '#------------------Test 22-----------------------#'
819#Test for STATEMENT keyword
820CREATE TABLE STATEMENT(a INT);
821DROP TABLE STATEMENT;
822
823--echo ''
824--echo '# Cleanup'
825DROP TABLE t1;
826DROP PROCEDURE p1;
827DROP PROCEDURE p2;
828DROP PROCEDURE p3;
829DROP PROCEDURE p4;
830DROP PROCEDURE p5;
0831
=== modified file 'Percona-Server/sql/lex.h'
--- Percona-Server/sql/lex.h 2013-05-10 13:29:38 +0000
+++ Percona-Server/sql/lex.h 2013-09-27 17:50:05 +0000
@@ -558,6 +558,7 @@
558 { "START", SYM(START_SYM)},558 { "START", SYM(START_SYM)},
559 { "STARTING", SYM(STARTING)},559 { "STARTING", SYM(STARTING)},
560 { "STARTS", SYM(STARTS_SYM)},560 { "STARTS", SYM(STARTS_SYM)},
561 { "STATEMENT", SYM(STATEMENT_SYM)},
561 { "STATS_AUTO_RECALC",SYM(STATS_AUTO_RECALC_SYM)},562 { "STATS_AUTO_RECALC",SYM(STATS_AUTO_RECALC_SYM)},
562 { "STATS_PERSISTENT", SYM(STATS_PERSISTENT_SYM)},563 { "STATS_PERSISTENT", SYM(STATS_PERSISTENT_SYM)},
563 { "STATS_SAMPLE_PAGES",SYM(STATS_SAMPLE_PAGES_SYM)},564 { "STATS_SAMPLE_PAGES",SYM(STATS_SAMPLE_PAGES_SYM)},
564565
=== modified file 'Percona-Server/sql/set_var.h'
--- Percona-Server/sql/set_var.h 2013-09-09 15:24:45 +0000
+++ Percona-Server/sql/set_var.h 2013-09-27 17:50:05 +0000
@@ -114,6 +114,9 @@
114 */114 */
115 bool set_default(THD *thd, set_var *var);115 bool set_default(THD *thd, set_var *var);
116 bool update(THD *thd, set_var *var);116 bool update(THD *thd, set_var *var);
117 void stmt_update(THD *thd) {
118 on_update && on_update(this, thd, OPT_SESSION);
119 }
117120
118 SHOW_TYPE show_type() { return show_val_type; }121 SHOW_TYPE show_type() { return show_val_type; }
119 int scope() const { return flags & SCOPE_MASK; }122 int scope() const { return flags & SCOPE_MASK; }
120123
=== modified file 'Percona-Server/sql/sql_lex.cc'
--- Percona-Server/sql/sql_lex.cc 2013-08-14 03:57:21 +0000
+++ Percona-Server/sql/sql_lex.cc 2013-09-27 17:50:05 +0000
@@ -487,6 +487,7 @@
487 lex->is_change_password= false;487 lex->is_change_password= false;
488 lex->is_set_password_sql= false;488 lex->is_set_password_sql= false;
489 lex->mark_broken(false);489 lex->mark_broken(false);
490 lex->set_statement= false;
490 DBUG_VOID_RETURN;491 DBUG_VOID_RETURN;
491}492}
492493
493494
=== modified file 'Percona-Server/sql/sql_lex.h'
--- Percona-Server/sql/sql_lex.h 2013-08-14 03:57:21 +0000
+++ Percona-Server/sql/sql_lex.h 2013-09-27 17:50:05 +0000
@@ -2322,7 +2322,8 @@
2322 required a local context, the parser pops the top-most context.2322 required a local context, the parser pops the top-most context.
2323 */2323 */
2324 List<Name_resolution_context> context_stack;2324 List<Name_resolution_context> context_stack;
23252325 /* true if SET STATEMENT ... FOR ... statement is use, false otherwise */
2326 bool set_statement;
2326 /**2327 /**
2327 Argument values for PROCEDURE ANALYSE(); is NULL for other queries2328 Argument values for PROCEDURE ANALYSE(); is NULL for other queries
2328 */2329 */
23292330
=== modified file 'Percona-Server/sql/sql_parse.cc'
--- Percona-Server/sql/sql_parse.cc 2013-09-21 18:26:20 +0000
+++ Percona-Server/sql/sql_parse.cc 2013-09-27 17:50:05 +0000
@@ -2500,6 +2500,8 @@
2500 /* have table map for update for multi-update statement (BUG#37051) */2500 /* have table map for update for multi-update statement (BUG#37051) */
2501 bool have_table_map_for_update= FALSE;2501 bool have_table_map_for_update= FALSE;
2502#endif2502#endif
2503 struct system_variables *per_query_variables_backup;
2504
2503 DBUG_ENTER("mysql_execute_command");2505 DBUG_ENTER("mysql_execute_command");
2504 DBUG_ASSERT(!lex->describe || is_explainable_query(lex->sql_command));2506 DBUG_ASSERT(!lex->describe || is_explainable_query(lex->sql_command));
25052507
@@ -2792,6 +2794,22 @@
2792 goto error;2794 goto error;
2793 }2795 }
27942796
2797 if (lex->set_statement && !lex->var_list.is_empty()) {
2798 per_query_variables_backup= copy_system_variables(&thd->variables,
2799 thd->m_enable_plugins);
2800 if ((res= sql_set_variables(thd, &lex->var_list)))
2801 {
2802 /*
2803 We encountered some sort of error, but no message was sent.
2804 Send something semi-generic here since we don't know which
2805 assignment in the list caused the error.
2806 */
2807 if (!thd->is_error())
2808 my_error(ER_WRONG_ARGUMENTS, MYF(0), "SET");
2809 goto error;
2810 }
2811 }
2812
2795 switch (lex->sql_command) {2813 switch (lex->sql_command) {
27962814
2797 case SQLCOM_SHOW_STATUS:2815 case SQLCOM_SHOW_STATUS:
@@ -2854,6 +2872,12 @@
2854 }2872 }
2855 case SQLCOM_EXECUTE:2873 case SQLCOM_EXECUTE:
2856 {2874 {
2875 /*
2876 Deallocate free_list here to avoid assertion failure later in
2877 Prepared_statement::execute_loop
2878 */
2879 free_items(thd->free_list);
2880 thd->free_list= NULL;
2857 mysql_sql_stmt_execute(thd);2881 mysql_sql_stmt_execute(thd);
2858 break;2882 break;
2859 }2883 }
@@ -5287,6 +5311,29 @@
5287 if (reset_timer)5311 if (reset_timer)
5288 reset_statement_timer(thd);5312 reset_statement_timer(thd);
52895313
5314 if (lex->set_statement && !lex->var_list.is_empty()) {
5315 List_iterator_fast<set_var_base> it(thd->lex->var_list);
5316 set_var *var;
5317
5318 free_system_variables(&thd->variables, thd->m_enable_plugins);
5319 thd->variables= *per_query_variables_backup;
5320 my_free(per_query_variables_backup);
5321 /*
5322 When variables are restored after "SET STATEMENT ... FOR ..." statement
5323 execution an update callback must be invoked for the system variables
5324 to save special logic if it is. set_var_base class does not contain
5325 refference to variable as it is just an interface class. But only
5326 system variables are allowed to be used in "SET STATEMENT ... FOR ..."
5327 statement, so cast from set_var_base* to set_var* can be used here.
5328 */
5329 while ((var=(set_var *)it++))
5330 {
5331 var->var->stmt_update(thd);
5332 }
5333
5334 thd->lex->set_statement= false;
5335 }
5336
5290 if (! thd->in_sub_stmt)5337 if (! thd->in_sub_stmt)
5291 {5338 {
5292 /* report error issued during command execution */5339 /* report error issued during command execution */
52935340
=== modified file 'Percona-Server/sql/sql_plugin.cc'
--- Percona-Server/sql/sql_plugin.cc 2013-08-06 15:16:34 +0000
+++ Percona-Server/sql/sql_plugin.cc 2013-09-27 17:50:05 +0000
@@ -3917,3 +3917,69 @@
3917 DBUG_ENTER("unlock_plugin_data");3917 DBUG_ENTER("unlock_plugin_data");
3918 DBUG_RETURN(mysql_mutex_unlock(&LOCK_plugin));3918 DBUG_RETURN(mysql_mutex_unlock(&LOCK_plugin));
3919}3919}
3920
3921/**
3922 Create deep copy of system_variables instance.
3923*/
3924struct system_variables *
3925copy_system_variables(const struct system_variables *src,
3926 bool enable_plugins)
3927{
3928 struct system_variables *dst;
3929
3930 DBUG_ASSERT(src);
3931
3932 dst= (struct system_variables *)
3933 my_malloc(sizeof(struct system_variables), MYF(MY_WME | MY_FAE));
3934 *dst = *src;
3935
3936 if (dst->dynamic_variables_ptr)
3937 {
3938 dst->dynamic_variables_ptr=
3939 (char *)my_malloc(dst->dynamic_variables_size, MYF(MY_WME | MY_FAE));
3940 memcpy(dst->dynamic_variables_ptr,
3941 src->dynamic_variables_ptr,
3942 src->dynamic_variables_size);
3943 }
3944
3945 dst->dynamic_variables_allocs= 0;
3946
3947 for (LIST *i= src->dynamic_variables_allocs; i; i= i->next)
3948 {
3949 const char *src_value= (const char *)(i + 1);
3950 size_t src_length= strlen(src_value) + 1;
3951 LIST *dst_el= (LIST *) my_malloc(sizeof(LIST) + src_length, MYF(MY_WME | MY_FAE));
3952 memcpy(dst_el + 1, src_value, src_length);
3953 dst->dynamic_variables_allocs= list_add(dst->dynamic_variables_allocs,
3954 dst_el);
3955 }
3956
3957 if (enable_plugins)
3958 {
3959 mysql_mutex_lock(&LOCK_plugin);
3960 dst->table_plugin=
3961 my_intern_plugin_lock(NULL, src->table_plugin);
3962 dst->temp_table_plugin=
3963 my_intern_plugin_lock(NULL, src->temp_table_plugin);
3964 mysql_mutex_unlock(&LOCK_plugin);
3965 }
3966
3967 return dst;
3968}
3969
3970void free_system_variables(struct system_variables *v, bool enable_plugins)
3971{
3972 DBUG_ASSERT(v);
3973
3974 if (enable_plugins)
3975 {
3976 mysql_mutex_lock(&LOCK_plugin);
3977 intern_plugin_unlock(NULL, v->table_plugin);
3978 intern_plugin_unlock(NULL, v->temp_table_plugin);
3979 mysql_mutex_unlock(&LOCK_plugin);
3980 }
3981
3982 plugin_var_memalloc_free(v);
3983
3984 my_free(v->dynamic_variables_ptr);
3985}
39203986
=== modified file 'Percona-Server/sql/sql_plugin.h'
--- Percona-Server/sql/sql_plugin.h 2013-02-27 15:41:20 +0000
+++ Percona-Server/sql/sql_plugin.h 2013-09-27 17:50:05 +0000
@@ -173,4 +173,13 @@
173struct st_plugin_int *plugin_find_by_type(LEX_STRING *plugin, int type);173struct st_plugin_int *plugin_find_by_type(LEX_STRING *plugin, int type);
174int lock_plugin_data();174int lock_plugin_data();
175int unlock_plugin_data();175int unlock_plugin_data();
176
177/**
178 Create deep copy of system_variables instance.
179*/
180extern
181struct system_variables *
182copy_system_variables(const struct system_variables *src,
183 bool enable_plugins);
184extern void free_system_variables(struct system_variables *v, bool enable_plugins);
176#endif185#endif
177186
=== modified file 'Percona-Server/sql/sql_yacc.yy'
--- Percona-Server/sql/sql_yacc.yy 2013-08-14 03:57:21 +0000
+++ Percona-Server/sql/sql_yacc.yy 2013-09-27 17:50:05 +0000
@@ -1029,7 +1029,7 @@
1029 Currently there are 161 shift/reduce conflicts.1029 Currently there are 161 shift/reduce conflicts.
1030 We should not introduce new conflicts any more.1030 We should not introduce new conflicts any more.
1031*/1031*/
1032%expect 1611032%expect 162
10331033
1034/*1034/*
1035 Comments for TOKENS.1035 Comments for TOKENS.
@@ -1563,6 +1563,7 @@
1563%token STARTING1563%token STARTING
1564%token STARTS_SYM1564%token STARTS_SYM
1565%token START_SYM /* SQL-2003-R */1565%token START_SYM /* SQL-2003-R */
1566%token STATEMENT_SYM
1566%token STATS_AUTO_RECALC_SYM1567%token STATS_AUTO_RECALC_SYM
1567%token STATS_PERSISTENT_SYM1568%token STATS_PERSISTENT_SYM
1568%token STATS_SAMPLE_PAGES_SYM1569%token STATS_SAMPLE_PAGES_SYM
@@ -14470,6 +14471,7 @@
14470 | STATS_AUTO_RECALC_SYM {}14471 | STATS_AUTO_RECALC_SYM {}
14471 | STATS_PERSISTENT_SYM {}14472 | STATS_PERSISTENT_SYM {}
14472 | STATS_SAMPLE_PAGES_SYM {}14473 | STATS_SAMPLE_PAGES_SYM {}
14474 | STATEMENT_SYM {}
14473 | STATUS_SYM {}14475 | STATUS_SYM {}
14474 | STORAGE_SYM {}14476 | STORAGE_SYM {}
14475 | STRING_SYM {}14477 | STRING_SYM {}
@@ -14546,8 +14548,36 @@
14546 }14548 }
14547 start_option_value_list14549 start_option_value_list
14548 {}14550 {}
14551 | SET STATEMENT_SYM
14552 {
14553 LEX *lex= Lex;
14554 mysql_init_select(lex);
14555 lex->sql_command= SQLCOM_SET_OPTION;
14556 /* Don't clear var_list in the case of recursive statement */
14557 if (!lex->set_statement)
14558 lex->var_list.empty();
14559 lex->one_shot_set= 0;
14560 lex->autocommit= 0;
14561 lex->set_statement= true;
14562 sp_head *sp= lex->sphead;
14563 if (sp && !sp->is_invoked())
14564 {
14565 sp->m_parser_data.set_current_stmt_start_ptr(YY_TOKEN_START);
14566 sp->m_parser_data.set_option_start_ptr(YY_TOKEN_END);
14567 }
14568 }
14569 set_stmt_option_value_following_option_type_list FOR_SYM statement
14570 {}
14549 ;14571 ;
1455014572
14573set_stmt_option_value_following_option_type_list:
14574 /*
14575 Only system variables can be used here. If this condition is changed
14576 please check careful code under lex->option_type == OPT_STATEMENT
14577 condition on wrong type casts.
14578 */
14579 option_value_following_option_type
14580 | set_stmt_option_value_following_option_type_list ',' option_value_following_option_type
1455114581
14552// Start of option value list14582// Start of option value list
14553start_option_value_list:14583start_option_value_list:
@@ -14652,23 +14682,31 @@
14652 {14682 {
14653 THD *thd= YYTHD;14683 THD *thd= YYTHD;
14654 LEX *lex= Lex;14684 LEX *lex= Lex;
1465514685 /*
14656 if ($1.var && $1.var != trg_new_row_fake_var)14686 Ignore SET STATEMENT variables list on slaves because system
14657 {14687 variables are not replicated except certain variables set the
14658 /* It is a system variable. */14688 values of whose are written to binlog event header and nothing
14659 if (set_system_variable(thd, &$1, lex->option_type, $3))14689 additional is required to set them.
14690 */
14691 if (!thd->slave_thread || !lex->set_statement)
14692 {
14693 if ($1.var && $1.var != trg_new_row_fake_var)
14694 {
14695 /* It is a system variable. */
14696 if (set_system_variable(thd, &$1, lex->option_type, $3))
14697 MYSQL_YYABORT;
14698 }
14699 else
14700 {
14701 /*
14702 Not in trigger assigning value to new row,
14703 and option_type preceeding local variable is illegal.
14704 */
14705 my_parse_error(ER(ER_SYNTAX_ERROR));
14660 MYSQL_YYABORT;14706 MYSQL_YYABORT;
14661 }14707 }
14662 else14708 }
14663 {14709 }
14664 /*
14665 Not in trigger assigning value to new row,
14666 and option_type preceeding local variable is illegal.
14667 */
14668 my_parse_error(ER(ER_SYNTAX_ERROR));
14669 MYSQL_YYABORT;
14670 }
14671 }
14672 ;14710 ;
1467314711
14674// Option values without preceeding option_type.14712// Option values without preceeding option_type.

Subscribers

People subscribed via source and target branches