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.

10 files changed, 163 insertions, 30 deletions

diff --git a/mysql-test/r/innodb-semi-consistent.result b/mysql-test/r/innodb-semi-consistent.result
index ca0e362ef80..989cb818cec 100644
--- a/mysql-test/r/innodb-semi-consistent.result
+++ b/mysql-test/r/innodb-semi-consistent.result
@@ -13,6 +13,7 @@ set autocommit=0;
 update t1 set a=10 where a=5;
 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
 commit;
+commit;
 set session transaction isolation level read committed;
 update t1 set a=10 where a=5;
 select * from t1 where a=2 for update;
diff --git a/mysql-test/r/parser.result b/mysql-test/r/parser.result
index 467bb7c5cb8..0cfde6dd2a0 100644
--- a/mysql-test/r/parser.result
+++ b/mysql-test/r/parser.result
@@ -618,3 +618,48 @@ DROP TABLE t1, t2, t3;
 #
 # End of 5.1 tests
 #
+# Bug#46527 "COMMIT AND CHAIN RELEASE does not make sense"
+#
+COMMIT AND CHAIN RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RELEASE' at line 1
+COMMIT AND NO CHAIN RELEASE;
+COMMIT RELEASE;
+COMMIT CHAIN RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAIN RELEASE' at line 1
+COMMIT NO CHAIN RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAIN RELEASE' at line 1
+COMMIT AND NO RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RELEASE' at line 1
+COMMIT AND RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RELEASE' at line 1
+COMMIT NO RELEASE;
+COMMIT CHAIN NO RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAIN NO RELEASE' at line 1
+COMMIT NO CHAIN NO RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAIN NO RELEASE' at line 1
+COMMIT AND RELEASE CHAIN;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RELEASE CHAIN' at line 1
+COMMIT AND NO CHAIN NO RELEASE;
+ROLLBACK AND CHAIN RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RELEASE' at line 1
+ROLLBACK AND NO CHAIN RELEASE;
+ROLLBACK RELEASE;
+ROLLBACK CHAIN RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAIN RELEASE' at line 1
+ROLLBACK NO CHAIN RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAIN RELEASE' at line 1
+ROLLBACK AND NO RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RELEASE' at line 1
+ROLLBACK AND RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RELEASE' at line 1
+ROLLBACK NO RELEASE;
+ROLLBACK CHAIN NO RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAIN NO RELEASE' at line 1
+ROLLBACK NO CHAIN NO RELEASE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CHAIN NO RELEASE' at line 1
+ROLLBACK AND RELEASE CHAIN;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RELEASE CHAIN' at line 1
+ROLLBACK AND NO CHAIN NO RELEASE;
+#
+# End of 5.5 tests
+#
diff --git a/mysql-test/r/partition_innodb_semi_consistent.result b/mysql-test/r/partition_innodb_semi_consistent.result
index 48a1bb3d258..b3253ff33ea 100644
--- a/mysql-test/r/partition_innodb_semi_consistent.result
+++ b/mysql-test/r/partition_innodb_semi_consistent.result
@@ -18,6 +18,7 @@ set autocommit=0;
 update t1 set a=10 where a=5;
 ERROR HY000: Lock wait timeout exceeded; try restarting transaction
 commit;
+commit;
 set session transaction isolation level read committed;
 update t1 set a=10 where a=5;
 select * from t1 where a=2 for update;
diff --git a/mysql-test/r/ps.result b/mysql-test/r/ps.result
index 0b2002b1350..a1d85539484 100644
--- a/mysql-test/r/ps.result
+++ b/mysql-test/r/ps.result
@@ -695,11 +695,11 @@ REPEATABLE-READ
 set transaction isolation level read committed;
 execute stmt;
 @@tx_isolation
-READ-COMMITTED
+REPEATABLE-READ
 set transaction isolation level serializable;
 execute stmt;
 @@tx_isolation
-SERIALIZABLE
+REPEATABLE-READ
 set @@tx_isolation=default;
 execute stmt;
 @@tx_isolation
diff --git a/mysql-test/suite/binlog/r/binlog_innodb.result b/mysql-test/suite/binlog/r/binlog_innodb.result
index 65182405136..44c9614950d 100644
--- a/mysql-test/suite/binlog/r/binlog_innodb.result
+++ b/mysql-test/suite/binlog/r/binlog_innodb.result
@@ -2,65 +2,65 @@ SET BINLOG_FORMAT=MIXED;
 RESET MASTER;
 CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=INNODB;
 INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+BEGIN;
 UPDATE t1 SET b = 2*a WHERE a > 1;
 COMMIT;
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+BEGIN;
 UPDATE t1 SET b = a * a WHERE a > 3;
 COMMIT;
 SET BINLOG_FORMAT=STATEMENT;
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+BEGIN;
 UPDATE t1 SET b = 1*a WHERE a > 1;
 ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
 COMMIT;
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+BEGIN;
 UPDATE t1 SET b = 2*a WHERE a > 2;
 ERROR HY000: Cannot execute statement: impossible to write to binary log since BINLOG_FORMAT = STATEMENT and at least one table uses a storage engine limited to row-based logging. InnoDB is limited to row-logging when transaction isolation level is READ COMMITTED or READ UNCOMMITTED.
 COMMIT;
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+BEGIN;
 UPDATE t1 SET b = 3*a WHERE a > 3;
 COMMIT;
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+BEGIN;
 UPDATE t1 SET b = 4*a WHERE a > 4;
 COMMIT;
 SET BINLOG_FORMAT=MIXED;
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+BEGIN;
 UPDATE t1 SET b = 1*a WHERE a > 1;
 COMMIT;
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+BEGIN;
 UPDATE t1 SET b = 2*a WHERE a > 2;
 COMMIT;
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+BEGIN;
 UPDATE t1 SET b = 3*a WHERE a > 3;
 COMMIT;
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+BEGIN;
 UPDATE t1 SET b = 4*a WHERE a > 4;
 COMMIT;
 SET BINLOG_FORMAT=ROW;
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+BEGIN;
 UPDATE t1 SET b = 1*a WHERE a > 1;
 COMMIT;
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+BEGIN;
 UPDATE t1 SET b = 2*a WHERE a > 2;
 COMMIT;
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+BEGIN;
 UPDATE t1 SET b = 3*a WHERE a > 3;
 COMMIT;
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+BEGIN;
 UPDATE t1 SET b = 4*a WHERE a > 4;
 COMMIT;
 show binlog events from <binlog_start>;
diff --git a/mysql-test/suite/binlog/t/binlog_innodb.test b/mysql-test/suite/binlog/t/binlog_innodb.test
index c33a31a30f2..13edf262026 100644
--- a/mysql-test/suite/binlog/t/binlog_innodb.test
+++ b/mysql-test/suite/binlog/t/binlog_innodb.test
@@ -8,14 +8,14 @@ RESET MASTER;
 CREATE TABLE t1 (a INT PRIMARY KEY, b INT) ENGINE=INNODB;
 INSERT INTO t1 VALUES (1,1),(2,2),(3,3),(4,4),(5,5),(6,6);
 
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+BEGIN;
 # Should be logged as statement
 UPDATE t1 SET b = 2*a WHERE a > 1;
 COMMIT;
 
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+BEGIN;
 # Should be logged as rows
 UPDATE t1 SET b = a * a WHERE a > 3;
 COMMIT;
@@ -25,69 +25,69 @@ COMMIT;
 
 SET BINLOG_FORMAT=STATEMENT;
 
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+BEGIN;
 error ER_BINLOG_STMT_MODE_AND_ROW_ENGINE;
 UPDATE t1 SET b = 1*a WHERE a > 1;
 COMMIT;
 
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+BEGIN;
 error ER_BINLOG_STMT_MODE_AND_ROW_ENGINE;
 UPDATE t1 SET b = 2*a WHERE a > 2;
 COMMIT;
 
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+BEGIN;
 UPDATE t1 SET b = 3*a WHERE a > 3;
 COMMIT;
 
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+BEGIN;
 UPDATE t1 SET b = 4*a WHERE a > 4;
 COMMIT;
 
 SET BINLOG_FORMAT=MIXED;
 
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+BEGIN;
 UPDATE t1 SET b = 1*a WHERE a > 1;
 COMMIT;
 
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+BEGIN;
 UPDATE t1 SET b = 2*a WHERE a > 2;
 COMMIT;
 
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+BEGIN;
 UPDATE t1 SET b = 3*a WHERE a > 3;
 COMMIT;
 
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+BEGIN;
 UPDATE t1 SET b = 4*a WHERE a > 4;
 COMMIT;
 
 SET BINLOG_FORMAT=ROW;
 
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+BEGIN;
 UPDATE t1 SET b = 1*a WHERE a > 1;
 COMMIT;
 
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+BEGIN;
 UPDATE t1 SET b = 2*a WHERE a > 2;
 COMMIT;
 
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+BEGIN;
 UPDATE t1 SET b = 3*a WHERE a > 3;
 COMMIT;
 
-BEGIN;
 SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+BEGIN;
 UPDATE t1 SET b = 4*a WHERE a > 4;
 COMMIT;
 
diff --git a/mysql-test/t/innodb-semi-consistent.test b/mysql-test/t/innodb-semi-consistent.test
index 61ad7815ca9..28bf532ff1f 100644
--- a/mysql-test/t/innodb-semi-consistent.test
+++ b/mysql-test/t/innodb-semi-consistent.test
@@ -23,6 +23,7 @@ set session transaction isolation level repeatable read;
 set autocommit=0;
 -- error ER_LOCK_WAIT_TIMEOUT
 update t1 set a=10 where a=5;
+commit;
 connection a;
 commit;
 connection b;
diff --git a/mysql-test/t/parser.test b/mysql-test/t/parser.test
index e6c9c8b423f..43d3abb0649 100644
--- a/mysql-test/t/parser.test
+++ b/mysql-test/t/parser.test
@@ -732,3 +732,79 @@ DROP TABLE t1, t2, t3;
 --echo #
 --echo # End of 5.1 tests
 --echo #
+
+--echo # Bug#46527 "COMMIT AND CHAIN RELEASE does not make sense"
+--echo #
+--error ER_PARSE_ERROR
+COMMIT AND CHAIN RELEASE;
+
+COMMIT AND NO CHAIN RELEASE;
+disconnect default;
+connect(default, localhost, root,,);
+
+COMMIT RELEASE;
+disconnect default;
+connect(default, localhost, root,,);
+
+--error ER_PARSE_ERROR
+COMMIT CHAIN RELEASE;
+
+--error ER_PARSE_ERROR
+COMMIT NO CHAIN RELEASE;
+
+--error ER_PARSE_ERROR
+COMMIT AND NO RELEASE;
+--error ER_PARSE_ERROR
+COMMIT AND RELEASE;
+
+COMMIT NO RELEASE;
+--error ER_PARSE_ERROR
+COMMIT CHAIN NO RELEASE;
+--error ER_PARSE_ERROR
+COMMIT NO CHAIN NO RELEASE;
+
+--error ER_PARSE_ERROR
+COMMIT AND RELEASE CHAIN;
+
+COMMIT AND NO CHAIN NO RELEASE;
+
+--error ER_PARSE_ERROR
+ROLLBACK AND CHAIN RELEASE;
+
+ROLLBACK AND NO CHAIN RELEASE;
+disconnect default;
+connect(default, localhost, root,,);
+
+ROLLBACK RELEASE;
+disconnect default;
+connect(default, localhost, root,,);
+
+--error ER_PARSE_ERROR
+ROLLBACK CHAIN RELEASE;
+
+--error ER_PARSE_ERROR
+ROLLBACK NO CHAIN RELEASE;
+disconnect default;
+connect(default, localhost, root,,);
+
+--error ER_PARSE_ERROR
+ROLLBACK AND NO RELEASE;
+
+--error ER_PARSE_ERROR
+ROLLBACK AND RELEASE;
+
+ROLLBACK NO RELEASE;
+
+--error ER_PARSE_ERROR
+ROLLBACK CHAIN NO RELEASE;
+
+--error ER_PARSE_ERROR
+ROLLBACK NO CHAIN NO RELEASE;
+--error ER_PARSE_ERROR
+ROLLBACK AND RELEASE CHAIN;
+
+ROLLBACK AND NO CHAIN NO RELEASE;
+
+--echo #
+--echo # End of 5.5 tests
+--echo #
diff --git a/mysql-test/t/partition_innodb_semi_consistent.test b/mysql-test/t/partition_innodb_semi_consistent.test
index 294521a45d5..f6cca8481d1 100644
--- a/mysql-test/t/partition_innodb_semi_consistent.test
+++ b/mysql-test/t/partition_innodb_semi_consistent.test
@@ -31,6 +31,7 @@ set session transaction isolation level repeatable read;
 set autocommit=0;
 -- error ER_LOCK_WAIT_TIMEOUT
 update t1 set a=10 where a=5;
+commit;
 connection a;
 #DELETE FROM t1 WHERE a=5;
 commit;
diff --git a/mysql-test/t/sp_trans.test b/mysql-test/t/sp_trans.test
index c4915bdb87a..a75bb52d4c2 100644
--- a/mysql-test/t/sp_trans.test
+++ b/mysql-test/t/sp_trans.test
@@ -636,7 +636,15 @@ UPDATE t1_aux SET f2 = 2 WHERE f1 = f1_two_inserts()|
 DROP TABLE t1_aux, t1_not_null|
 DROP FUNCTION f1_two_inserts|
 
+--echo #
+--echo # Bug#53346 completion_type has strange effect in a stored
+--echo # procedure/prepared statement
+--echo #
+
 
+--echo #
+--echo # End of 5.5 tests
+--echo # 
 #
 # BUG#NNNN: New bug synopsis
 #