MySQL Bugs: #47627: SET @@{global.session}.local_variable in stored routine causes crash

Bug #47627	SET @@{global.session}.local_variable in stored routine causes crash
Submitted:	24 Sep 2009 17:14	Modified:	12 Mar 2010 17:28
Reporter:	Paul DuBois	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0 and up	OS:	Any
Assigned to:	Davi Arnaut	CPU Architecture:	Any

Description:
It is syntactically legal to apply @@session. and @@global. prefixes to local routine variables in SET statements within stored routines. The prefix is supposed to be ignored:

http://dev.mysql.com/doc/refman/5.1/en/set-statement.html:

"The SET statement in stored programs is implemented as part of the pre-existing SET syntax. This allows an extended syntax of SET a=x, b=y, ... where different variable types (locally declared variables, global and session server variables, user-defined variables) can be mixed. This also allows combinations of local variables and some options that make sense only for system variables; in that case, the options are recognized but ignored."

However, although the statement is accepted when the routine is defined, the server crashes when the routine is executed.

How to repeat:
Test script:

DROP PROCEDURE IF EXISTS myproc;
delimiter //
CREATE PROCEDURE myproc()
BEGIN
  DECLARE v_i INT DEFAULT 0;
  SET @@session.v_i = 10;
END;
//
delimiter ;
SHOW CREATE PROCEDURE myproc\G
CALL myproc();

Result:

--------------
SHOW CREATE PROCEDURE myproc
--------------

*************************** 1. row ***************************
       Procedure: myproc
        sql_mode: 
Create Procedure: CREATE DEFINER=`paul`@`localhost` PROCEDURE `myproc`()
BEGIN
  DECLARE v_i INT DEFAULT 0;
  SET @@session.v_i = 10;
END
--------------
CALL myproc()
--------------

ERROR 2013 (HY000) at line 11: Lost connection to MySQL server during query

Similar results occur with @@global. instead of @@session.

Similar results also occur with @@ (no global. or session.).

Thank you for the bug report. Verified just as described with recent 5.4.3 from bzr on Mac OS X. Stack trace is:

stack_bottom = 0xb0986f64 thread_stack 0x30000
0   mysqld                              0x005acd0e my_print_stacktrace + 45
1   mysqld                              0x000fd216 handle_segfault + 888
2   libSystem.B.dylib                   0x940472bb _sigtramp + 43
3   ???                                 0xffffffff 0x0 + 4294967295
4   mysqld                              0x0012a42a _Z17sql_set_variablesP3THDP4ListI12set_var_baseE + 130
5   mysqld                              0x0011a9a7 _Z21mysql_execute_commandP3THD + 20601
6   mysqld                              0x002f842c _ZN13sp_instr_stmt9exec_coreEP3THDPj + 140
7   mysqld                              0x002f809c _ZN13sp_lex_keeper23reset_lex_and_exec_coreEP3THDPjbP8sp_instr + 516
8   mysqld                              0x002fb712 _ZN13sp_instr_stmt7executeEP3THDPj + 472
9   mysqld                              0x002f4162 _ZN7sp_head7executeEP3THD + 1354
10  mysqld                              0x002f5e53 _ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE + 1957
11  mysqld                              0x0011ddfe _Z21mysql_execute_commandP3THD + 34000
12  mysqld                              0x001208a1 _Z11mysql_parseP3THDPKcjPS2_ + 729
13  mysqld                              0x00121726 _Z16dispatch_command19enum_server_commandP3THDPcj + 3244
14  mysqld                              0x00122b60 _Z10do_commandP3THD + 666
15  mysqld                              0x0010cfb4 handle_one_connection + 438
16  libSystem.B.dylib                   0x9400c095 _pthread_start + 321
17  libSystem.B.dylib                   0x9400bf52 thread_start + 34
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x109a228 = SET @@session.v_i = 10

Picture of a thousand words (now with better graphics)

Attachment: bug47627.png (image/png, text), 118.19 KiB.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/87194

3162 Kristofer Pettersson	2009-10-16
      Bug#47627 SET @@{global.session}.local_variable in stored routine causes crash
      
      Adding @@session and @@global prefixes to a
      declared variable in a stored procedure the server
      would lead to a crash.
      
      The reason was that during the parsing of the
      syntactic rule 'option_value' an uninitialized
      set_var object was pushed to the parameter stack
      of the SET statement. The parent rule
      'option_type_value'  interpreted the existence of
      variables on the parameter stack as an assignment
      and wrapped it in a sp_instr_set object.
      
      As the procedure later was executed an attempt
      was made to run the method 'check()' on an
      uninitialized member object (NULL value) belonging
      to the previously created but uninitialized object.
     @ mysql-test/r/sp.result
        Test for bug 47627
     @ mysql-test/t/sp.test
        Test for bug 47627
     @ sql/sql_yacc.yy
        * Assign the option_type at once since it is needed by the next parsing rule (internal_variable_name)
        * Rearranged the if statement to reduce negations and gain more clarity of code.
        * Added check for option_type to better detect if current variable is a SP local variable or a system variable.

If current patch is approved it would lead to a slight change in documentation as the prefix is won't be allowed.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/87252

3162 Kristofer Pettersson	2009-10-19
      Bug#47627 SET @@{global.session}.local_variable in stored routine causes crash
      
      Adding @@session and @@global prefixes to a
      declared variable in a stored procedure the server
      would lead to a crash.
      
      The reason was that during the parsing of the
      syntactic rule 'option_value' an uninitialized
      set_var object was pushed to the parameter stack
      of the SET statement. The parent rule
      'option_type_value'  interpreted the existence of
      variables on the parameter stack as an assignment
      and wrapped it in a sp_instr_set object.
      
      As the procedure later was executed an attempt
      was made to run the method 'check()' on an
      uninitialized member object (NULL value) belonging
      to the previously created but uninitialized object.
     @ sql/sql_yacc.yy
        * Assign the option_type at once since it is needed by the next
          parsing rule (internal_variable_name)
        * Rearranged the if statement to reduce negations and gain more
          clarity of code.
        * Added check for option_type to better detect if current
          variable is a SP local variable or a system variable.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/87967

3193 Georgi Kodinov	2009-10-23
      Revert the fix for bug #47627 as it's causing the regression tests in pb2 to
      fail.

marc_alff: I think we need more tests.
thek: no we don't. What could possibly go wrong?

:-)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/88251

3162 Kristofer Pettersson	2009-10-27
      Bug#47627 SET @@{global.session}.local_variable in stored routine causes crash
      
      Adding @@session and @@global prefixes to a
      declared variable in a stored procedure the server
      would lead to a crash.
      
      The reason was that during the parsing of the
      syntactic rule 'option_value' an uninitialized
      set_var object was pushed to the parameter stack
      of the SET statement. The parent rule
      'option_type_value' interpreted the existence of
      variables on the parameter stack as an assignment
      and wrapped it in a sp_instr_set object.
      
      As the procedure later was executed an attempt
      was made to run the method 'check()' on an
      uninitialized member object (NULL value) belonging
      to the previously created but uninitialized object.
      
      In addition this patch reveals and fixes a bug
      hidden by a mistake in the variables-test file.
      The bug caused a variable assigned in a sequence of
      assignments to inherit the option_type of a
      previous scope prefix.
      Example: SET GLOBAL v1= l1, v2= l2;
      Where the scope of the v2 assignment would become
      GLOBAL instead of the default SESSION.
     @ mysql-test/r/sp.result
        * Test case for bug 47627
     @ mysql-test/r/variables.result
        * Fixed test case: GLOBAL scope intended for
          net_write_timeout and net_read_timeout
          assignments.
          Before this patch the scope was inherited
          by the previous assignment.
     @ mysql-test/t/sp.test
        * Test case for bug 47627
     @ mysql-test/t/variables.test
        * Fixed test case: GLOBAL scope intended for
          net_write_timeout and net_read_timeout
          assignments.
          Before this patch the scope was inherited
          by the previous assignment.
     @ sql/sql_yacc.yy
        * Assign the option_type at once since it is needed by the next
          parsing rule (internal_variable_name)
        * Rearranged the if statement to reduce negations and gain more
          clarity of code.
        * Added check for option_type to better detect if current
          variable is a SP local variable or a system variable.
        * In the sys_option_value-rule: Removed if-statement
          preventing option_value-subrule to be affective for
          the OPT_DEFAULT value.

Pushed into 5.1.41 (revid:joro@sun.com-20091104092152-qz96bzlf2o1japwc) (version source revid:kristofer.pettersson@sun.com-20091103162305-08l4gkeuif2ozsoj) (merge vers: 5.1.41) (pib:13)

Given the history involved with this fix and the risk for regression in this area,
the last patch is rejected.

Changing the status to verified.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/89565

2930 Alexander Nozdrin	2009-11-06 [merge]
      Null-merge patch for Bug#47627
      (revision-id: kristofer.pettersson@sun.com-20091019074333-g2ces0lo4c2ejar7),
      because it broke the tree and was reverted later.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/89798

3162 Kristofer Pettersson	2009-11-09
      Bug#47627 SET @@{global.session}.local_variable in stored routine causes crash
      Bug#48626 Crash or lost connection using SET for declared variables with @@
      
      This patch fixes two bugs:
      
      1. Adding @@session and @@global prefixes to a
      declared variable in a stored procedure the server
      would lead to a crash.
      
      2. Just adding '@@' prefix to a declared variable in
      a stored procedure would crash the server when
      the SP is called.
            
      In the first case the reason was that during the
      parsing of the syntactic rule 'option_value' an
      uninitialized set_var object was pushed to the
      parameter stack of the SET statement. The parent
      rule 'option_type_value' interpreted the existence
      of variables on the parameter stack as an assignment
      and wrapped it in a sp_instr_set object.
            
      As the procedure later was executed an attempt
      was made to run the method 'check()' on an
      uninitialized member object (NULL value) belonging
      to the previously created but uninitialized object.
      
      For a similar but slightly different reason the SP
      caused a crash in the second case:
      The opt_var_type rule was oblivious of the preceding
      '@'-tokens which resulted in the wrong scope
      (OPT_DEFAULT) being assigned to the variable.
      The correct scope for double '@'-tokens is 
      OPT_SESSION unless it is overridden by a explicit
      prefix.
      
      In addition this patch reveals and fixes a bug
      hidden by a mistake in the variables-test file.
      The bug caused a variable assigned in a sequence of
      assignments to inherit the option_type of a
      previous scope prefix.
      Example: SET GLOBAL v1= l1, v2= l2;
      Where the scope of the v2 assignment would become
      GLOBAL instead of the default SESSION.
     @ mysql-test/r/sp.result
        * Added test case for bugs 47827 and 48626
     @ mysql-test/r/variables.result
        * Adjusted faulty test case revealed by bugs 47827 and 48626
     @ mysql-test/t/sp.test
        * Added test case for bugs 47827 and 48626
     @ mysql-test/t/variables.test
        * Adjusted faulty test case revealed by bugs 47827 and 48626
     @ sql/sql_yacc.yy
        * Assign the option_type at once since it is needed by the next
          parsing rule (internal_variable_name)
        * Rearranged the if statement to reduce negations and gain more
          clarity of code.
        * Added check for option_type to better detect if current
          variable is a SP local variable or a system variable.
        * In the sys_option_value-rule: Removed if-statement
          preventing option_value-subrule to be affective for
          the OPT_DEFAULT value.
        * Adjusted the opt_var_type rule to now overwrite 
          a preceding option_value assignment per default.
        * Set default option_value for double '@' tokens
          to OPT_SESSION instead of OPT_DEFAULT.

Again the mighty documentation has laid down the requirements for this fix and a more careful investigations gives us these rules to follow:

"To indicate explicitly that a variable is a session variable, precede its name by SESSION, @@session., or @@. "

"If no modifier is present, SET changes the session variable."

"When you refer to a system variable in an expression as @@var_name (that is, when you do not specify @@global. or @@session.), MySQL returns the session value if it exists and the global value otherwise. (This differs from SET @@var_name = value, which always refers to the session value.)" 

"If you set several system variables, the most recent GLOBAL or SESSION modifier in the statement is used for following variables that have no modifier specified."

"MySQL returns the session value if it exists and the global value otherwise." 

For a later release it is however possible to change these behaviours to something more consistent. I propose that
* Variable scope is interpreted the same way regardless if the statement is SET or SELECT.
* A qualified variable name is always used. This means that everyone using '@@bla' to reference the GLOBAL variable 'bla' will have to write '@@global.bla' instead. No modifier will be rejected (used to default to SESSION scope). 
* Just using '@@' is synonym for '@@session.'
* @@local. should be deprecated because it is at best confusing.

feed back is welcome.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091110093407-rw5g8dys2baqkt67) (version source revid:alik@sun.com-20091109102746-27k6d2wf06kmt9ct) (merge vers: 6.0.14-alpha) (pib:13)

Pushed into 5.5.0-beta (revid:alik@sun.com-20091109115615-nuohp02h8mdrz8m2) (version source revid:alik@sun.com-20091105092041-sp6eyod7sdlfuj3b) (merge vers: 5.5.0-beta) (pib:13)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/90080

3162 Kristofer Pettersson	2009-11-11
      Bug#47627 SET @@{global.session}.local_variable in stored routine causes crash
      Bug#48626 Crash or lost connection using SET for declared variables with @@
      
      Adding @@, @@session or @@global prefixes to a
      declared variable in a stored procedure the server
      would lead to a crash.
            
      The reason was that during the parsing of the
      syntactic rule 'option_value' an uninitialized
      set_var object was pushed to the parameter stack
      of the SET statement. The parent rule
      'option_type_value' interpreted the existence of
      variables on the parameter stack as an assignment
      and wrapped it in a sp_instr_set object.
           
      As the procedure later was executed an attempt
      was made to run the method 'check()' on an
      uninitialized member object (NULL value) belonging
      to the previously created but uninitialized object.
      
      This patch prevent the crash by checking that the
      expected variable pointer is valid or exit with
      an error message.
     @ mysql-test/r/sp.result
        * Test case for 47627
     @ mysql-test/t/sp.test
        * Test case for 47627
     @ sql/sql_yacc.yy
        * Inserted extra check for a valid system variable pointer

Pushed into 5.5.0-beta (revid:alik@sun.com-20091110093229-0bh5hix780cyeicl) (version source revid:alik@sun.com-20091106103041-0hlgvnqbimla513i) (merge vers: 5.5.0-beta) (pib:13)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/90225

3191 Kristofer Pettersson	2009-11-12
      Bug#47627 SET @@{global.session}.local_variable in stored routine causes crash
      
      An assignment of a system variable sharing the same
      base name as a declared stored procedure variable
      in the same context would lead to a crash.
      
      The reason was that during the parsing of the
      syntactic rule 'option_value' an uninitialized
      set_var object was pushed to the parameter stack
      of the SET statement. The parent rule
      'option_type_value' interpreted the existence of
      variables on the parameter stack as an assignment
      and wrapped it in a sp_instr_set object.
                 
      As the procedure later was executed an attempt
      was made to run the method 'check()' on an
      uninitialized member object (NULL value) belonging
      to the previously created but uninitialized object.
      
      This patch refactors the 'internal_variable_name'
      rule and copy the semantic analysis part to the
      depending parent rules: 'option_value' and
      'sys_option_value'. This makes it possible
      to account for any prefixes affecting the
      interpretation of the internal_variable_name.
     @ sql/sql_yacc.yy
        * Test case for 47627

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/90293

3200 Davi Arnaut	2009-11-12
      Bug#47627 SET @@{global.session}.local_variable in stored routine causes crash
      
      This patch borrows ideas, text and code from Kristofer
      Pettersson's patch.
      
      An assignment of a system variable sharing the same base
      name as a declared stored procedure variable in the same
      context could lead to a crash.
      
      The reason was that during the parsing of the syntactic
      rule 'option_value' an uninitialized set_var object was
      pushed to the parameter stack of the SET statement. The
      parent rule 'option_type_value' interpreted the existence
      of variables on the parameter stack as an assignment and
      wrapped it in a sp_instr_set object.
      
      As the procedure later was executed an attempt was made
      to run the method 'check()' on an uninitialized member
      object (NULL value) belonging to the previously created
      but uninitialized object.
      
      This patch refactors the 'internal_variable_name' rule and
      copies the semantic analysis part to the depending parent
      rule: 'option_value'. This makes it possible to account
      for any prefixes affecting the interpretation of the
      internal_variable_name.
     @ mysql-test/r/sp.result
        Add test case result.
     @ mysql-test/t/sp.test
        Add test case for bug.
     @ sql/sql_yacc.yy
        - Reduce churn in rule sys_option_value by moving to
          specialized functions.
        - Comment the the lookup in the rule internel_variable_name
          is a best effort operation.
        - Lookup for a system variable in the option_value if one was
          not found (the variable could have been shadowed)

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/90294

3200 Davi Arnaut	2009-11-12
      Bug#47627 SET @@{global.session}.local_variable in stored routine causes crash
      
      This patch borrows ideas, text and code from Kristofer
      Pettersson's patch.
      
      An assignment of a system variable sharing the same base
      name as a declared stored procedure variable in the same
      context could lead to a crash.
      
      The reason was that during the parsing of the syntactic
      rule 'option_value' an uninitialized set_var object was
      pushed to the parameter stack of the SET statement. The
      parent rule 'option_type_value' interpreted the existence
      of variables on the parameter stack as an assignment and
      wrapped it in a sp_instr_set object.
      
      As the procedure later was executed an attempt was made
      to run the method 'check()' on an uninitialized member
      object (NULL value) belonging to the previously created
      but uninitialized object.
      
      This patch refactors the 'internal_variable_name' rule and
      copies the semantic analysis part to the depending parent
      rule: 'option_value'. This makes it possible to account
      for any prefixes affecting the interpretation of the
      internal_variable_name.
     @ mysql-test/r/sp.result
        Add test case result.
     @ mysql-test/t/sp.test
        Add test case for bug.
     @ sql/sql_yacc.yy
        - Reduce churn in rule sys_option_value by moving to
          specialized functions.
        - Comment the the lookup in the rule internel_variable_name
          is a best effort operation.
        - Lookup for a system variable in the option_value if one was
          not found (the variable could have been shadowed)

Queued to 5.1-bugteam

Pushed into 5.1.42 (revid:joro@sun.com-20091202080033-mndu4sxwx19lz2zs) (version source revid:davi.arnaut@sun.com-20091113010326-ei0gdymqayqh3gqx) (merge vers: 5.1.41) (pib:13)

Noted in 5.1.42 changelog.

Assignment of a system variable sharing the same base name as a
declared stored program variable in the same context could lead to a
crash.  

Setting NDI pending push into 5.6.x+.

Pushed into 6.0.14-alpha (revid:alik@sun.com-20091216083311-xorsasf5kopjxshf) (version source revid:alik@sun.com-20091214191830-wznm8245ku8xo702) (merge vers: 6.0.14-alpha) (pib:14)

Pushed into 5.5.0-beta (revid:alik@sun.com-20091216082430-s0gtzibcgkv4pqul) (version source revid:alexey.kopytov@sun.com-20091126114659-f3imubfuye9fn7qp) (merge vers: 5.5.0-beta) (pib:14)

Pushed into mysql-next-mr (revid:alik@sun.com-20091216083231-rp8ecpnvkkbhtb27) (version source revid:alik@sun.com-20091212203859-fx4rx5uab47wwuzd) (merge vers: 5.6.0-beta) (pib:14)

Noted in 5.5.1, 6.0.14 changelogs.

Pushed into 5.1.41-ndb-7.1.0 (revid:jonas@mysql.com-20091218102229-64tk47xonu3dv6r6) (version source revid:jonas@mysql.com-20091218095730-26gwjidfsdw45dto) (merge vers: 5.1.41-ndb-7.1.0) (pib:15)

Pushed into 5.1.41-ndb-6.2.19 (revid:jonas@mysql.com-20091218100224-vtzr0fahhsuhjsmt) (version source revid:jonas@mysql.com-20091217101452-qwzyaig50w74xmye) (merge vers: 5.1.41-ndb-6.2.19) (pib:15)

Pushed into 5.1.41-ndb-6.3.31 (revid:jonas@mysql.com-20091218100616-75d9tek96o6ob6k0) (version source revid:jonas@mysql.com-20091217154335-290no45qdins5bwo) (merge vers: 5.1.41-ndb-6.3.31) (pib:15)

Pushed into 5.1.41-ndb-7.0.11 (revid:jonas@mysql.com-20091218101303-ga32mrnr15jsa606) (version source revid:jonas@mysql.com-20091218064304-ezreonykd9f4kelk) (merge vers: 5.1.41-ndb-7.0.11) (pib:15)

Pushed into 5.1.44-ndb-7.0.14 (revid:jonas@mysql.com-20100312135944-t0z8s1da2orvl66x) (version source revid:jonas@mysql.com-20100312115609-woou0te4a6s4ae9y) (merge vers: 5.1.44-ndb-7.0.14) (pib:16)

Pushed into 5.1.44-ndb-6.2.19 (revid:jonas@mysql.com-20100312134846-tuqhd9w3tv4xgl3d) (version source revid:jonas@mysql.com-20100312060623-mx6407w2vx76h3by) (merge vers: 5.1.44-ndb-6.2.19) (pib:16)

Pushed into 5.1.44-ndb-6.3.33 (revid:jonas@mysql.com-20100312135724-xcw8vw2lu3mijrhn) (version source revid:jonas@mysql.com-20100312103652-snkltsd197l7q2yg) (merge vers: 5.1.44-ndb-6.3.33) (pib:16)