MySQL Bugs: #53346: completion_type has strange effect in a stored procedure/prepared statement

Bug #53346	completion_type has strange effect in a stored procedure/prepared statement
Submitted:	1 May 2010 12:10	Modified:	29 Jul 2010 19:00
Reporter:	Konstantin Osipov (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.0+	OS:	Any
Assigned to:	Magne Mæhre	CPU Architecture:	Any

Description:
The value of completion_type session variable is taken at parse
time, and thus at the time when a stored procedure is loaded
into the cache or prepared statement is created.

This leads to strange effects when this variable is changed
between invocations of a stored procedure or prepared statement:
the variable change has no effect on a subsequent invocation.

In the test case I use completion_type=2 (release) just for the purpose
of demonstration. Not working properly with completion_type=1 (chain) might have a different user impact, but is harder to demonstrate.

How to repeat:
mysql> create procedure p1() commit;
Query OK, 0 rows affected (0.00 sec)

mysql> set @@completion_type=2;
Query OK, 0 rows affected (0.00 sec)

mysql> call p1();
ERROR 1317 (70100): Query execution was interrupted
mysql> select * from t1;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    2
Current database: test

+-------+
| a     |
+-------+
| value | 
+-------+
1 row in set (0.01 sec)

mysql> call p1();
Query OK, 0 rows affected (0.00 sec)

mysql> set @@completion_type=2;
Query OK, 0 rows affected (0.00 sec)

mysql> call p1();
Query OK, 0 rows affected (0.00 sec)

Suggested fix:
Move assignment of completion type to a parse tree node outside the parser.
Parsers parse!

Thank you for the bug report.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.0.91-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create procedure p1() commit;
Query OK, 0 rows affected (0.02 sec)

mysql> set @@completion_type=2;
Query OK, 0 rows affected (0.00 sec)

mysql> call p1();
ERROR 1317 (70100): Query execution was interrupted
mysql> select * from t1;                                                                                                                                           
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id:    3
Current database: test

+-----+
| id  |
+-----+
|  75 | 
|  76 | 
|  77 | 
|  78 | 
|  79 | 
| 101 | 
| 102 | 
| 103 | 
| 104 | 
| 105 | 
| 106 | 
| 107 | 
+-----+
12 rows in set (0.01 sec)

mysql> call p1();
Query OK, 0 rows affected (0.00 sec)

mysql> set @@completion_type=2;
Query OK, 0 rows affected (0.00 sec)

mysql> call p1();
Query OK, 0 rows affected (0.00 sec)

mysql>

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/107774

3015 Konstantin Osipov	2010-05-07
      Draft patch that fixes and a sketches test cases for:
      Bug#20837 Apparent change of isolation level during transaction,
      Bug#46527 COMMIT AND CHAIN RELEASE does not make sense,
      Bug#53343 completion_type=1, COMMIT/ROLLBACK AND CHAIN don't 
      preserve the isolation level
      Bug#53346 completion_type has strange effect in a stored 
      procedure/prepared statement
      
      Make thd->tx_isolation mean strictly "current transaction 
      isolation level"
      Make thd->variables.tx_isolation mean "current session isolation
      level".
      The current transaction isolation level is now established
      at transaction start. If there was a SET TRANSACTION
      ISOLATION LEVEL statement, the value is taken from it.
      Otherwise, the session value is used.
      A change in a session value, made while a transaction is active,
      whereas still allowed, no longer has any effect on the
      current transaction isolation level. This is an incompatible
      change.
      A change in a session isolation level, made while there is
      no active transaction, overrides SET TRANSACTION statement,
      if there was any.
      Changed the impelmentation to not look at @@session.completion_type
      in the parser, and thus fixed Bug#53346.
      Changed the parser to not allow AND NO CHAIN RELEASE,
      and thus fixed Bug#46527.
      Changed the transaction API to take the current transaction
      isolation level into account:
      - BEGIN/COMMIT now do preserve the current transaction
      isolation level if chaining is on.
      - implicit commit, XA COMMIT or XA ROLLBACK or autocommit don't.

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/110406

3046 Magne Mahre	2010-06-07 [merge]
      Bug#20837 Apparent change of isolation level during transaction
      Bug#46527 COMMIT AND CHAIN RELEASE does not make sense
      Bug#53343 completion_type=1, COMMIT/ROLLBACK AND CHAIN don't 
                preserve the isolation level
      Bug#53346 completion_type has strange effect in a stored 
                procedure/prepared statement
      
      Added test cases to verify the expected behaviour of :
       SET SESSION TRANSACTION ISOLATION LEVEL, 
       SET TRANSACTION ISOLATION LEVEL,
       @@completion_type,
       COMMIT AND CHAIN,
       ROLLBACK AND CHAIN
       ..and some combinations of the above

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/110534

3047 Magne Mahre	2010-06-08 [merge]
      Bug#20837 Apparent change of isolation level during transaction
      Bug#46527 COMMIT AND CHAIN RELEASE does not make sense
      Bug#53343 completion_type=1, COMMIT/ROLLBACK AND CHAIN don't 
                preserve the isolation level
      Bug#53346 completion_type has strange effect in a stored 
                procedure/prepared statement
      
      Added test cases to verify the expected behaviour of :
       SET SESSION TRANSACTION ISOLATION LEVEL, 
       SET TRANSACTION ISOLATION LEVEL,
       @@completion_type,
       COMMIT AND CHAIN,
       ROLLBACK AND CHAIN
       ..and some combinations of the above

Pushed into 5.5.5-m3 (revid:alik@sun.com-20100622130139-u05awgya93zvbsop) (version source revid:marko.makela@oracle.com-20100603095032-v5ptkkzt1bhz0m1d) (merge vers: 5.1.48) (pib:16)

Pushed into mysql-next-mr (revid:alik@sun.com-20100622130623-r7yhm89fz9n5t9nb) (version source revid:alik@sun.com-20100622130528-187gd949sa9b6pa6) (pib:16)

Noted in 5.5.5 changelog.

If the completion_type session variable was changed after a stored
procedure or prepared statement had been cached, the change had no
effect on subsequent executions of the procedure or statement.