MySQL Bugs: #53344: The grammar of SET TRANSACTION statement is incorrect

Bug #53344	The grammar of SET TRANSACTION statement is incorrect
Submitted:	1 May 2010 11:57	Modified:	20 Aug 2013 15:54
Reporter:	Konstantin Osipov (OCA)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Parser	Severity:	S3 (Non-critical)
Version:	5.0+	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The MySQL parser believes that SET TRANSACTION statement is part of 
SET grammar, which allows multiple assignments.

As a result, the following gibberish is accepted and executed:

set @a=1, transaction isolation level serializable, transaction isolation level repeatable read, session transaction isolation level read committed, transaction isolation level read committed, @foo=(select max(a) from t1);

How to repeat:
The most disconcerting is use of tables (and, concequently, stored functions, etc) in a statement that sets transaction isolation level. Semantics of the statement now becomes dependent on the order of evaluation. I sense this as a source of trouble for replication.

Thank you for the bug report. Verified with recent 5.1.47 form bzr on Mac OS X:

77-52-4-109:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

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

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql> drop table t;
Query OK, 0 rows affected (0.38 sec)

mysql> create table t(a int);
Query OK, 0 rows affected (0.05 sec)

mysql> set @a=1, transaction isolation level serializable, transaction isolation level
    -> repeatable read, session transaction isolation level read committed, transaction
    -> isolation level read committed, @foo=(select max(a) from t1);
Query OK, 0 rows affected (0.02 sec)

Still works with 5.5, 5.6.10 throws "ERROR 1193 (HY000): Unknown system variable 'transaction'" though so it seems to be fixed there ...

This issue has been fixed at 5.6.5, see http://dev.mysql.com/doc/refman/5.6/en/set-transaction.html for a new syntax.