SQL syntax extended with START TRANSACTION READ ONLY|READ WRITE

and SET TRANSACTION READ ONLT|READ WRITE
statements.


per-file comments:
  mysql-test/include/check-warnings.test
        READ ONLY transaction flag cleaned.
  mysql-test/r/commit.result
        result updated
  mysql-test/r/read_only.result
        result updated
  mysql-test/t/commit.test
        tests added.
  mysql-test/t/read_only.test
        tests added
  sql/lex.h
        ONLY symbol added.
  sql/sql_base.cc
        DBUG_RETURN added.
  sql/sql_parse.cc
        implementations added.
  sql/sql_yacc.yy
        SQL syntax extended.
  storage/perfschema/gen_pfs_lex_token
        changes forced by lex.h
  storage/perfschema/pfs_lex_token.h
        changes forced by lex.h

1 files changed, 305 insertions, 1 deletions

diff --git a/mysql-test/r/commit.result b/mysql-test/r/commit.result
index 00e39c29768..a62d2940d85 100644
--- a/mysql-test/r/commit.result
+++ b/mysql-test/r/commit.result
@@ -264,9 +264,313 @@ Should only read the '1000' as this transaction is now in REP READ
 COMMIT AND NO CHAIN;
 SET @@completion_type=0;
 COMMIT AND NO CHAIN;
-SET @autocommit=1;
+SET @@autocommit=1;
 COMMIT;
 DROP TABLE t1;
 #
 # End of test cases for Bug#20837
 #
+#
+# WL#5968 Implement START TRANSACTION READ (WRITE|ONLY);
+#
+#
+# Test 1: Check supported syntax
+START TRANSACTION;
+COMMIT;
+START TRANSACTION READ ONLY;
+COMMIT;
+START TRANSACTION READ WRITE;
+COMMIT;
+START TRANSACTION READ ONLY, READ WRITE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
+START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT;
+COMMIT;
+START TRANSACTION READ WRITE, WITH CONSISTENT SNAPSHOT;
+COMMIT;
+START TRANSACTION WITH CONSISTENT SNAPSHOT, READ ONLY;
+COMMIT;
+START TRANSACTION WITH CONSISTENT SNAPSHOT, READ WRITE;
+COMMIT;
+START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT, READ WRITE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '' at line 1
+SET TRANSACTION READ ONLY;
+SET TRANSACTION READ WRITE;
+SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY;
+SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ WRITE;
+SET TRANSACTION READ ONLY, ISOLATION LEVEL READ COMMITTED;
+SET TRANSACTION READ WRITE, ISOLATION LEVEL READ COMMITTED;
+SET TRANSACTION READ ONLY, READ WRITE;
+ERROR 42000: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'READ WRITE' at line 1
+COMMIT;
+#
+# Test 2: Check setting of variable.
+SET SESSION TRANSACTION READ WRITE;
+SELECT @@tx_read_only;
+@@tx_read_only
+0
+SET SESSION TRANSACTION READ ONLY;
+SELECT @@tx_read_only;
+@@tx_read_only
+1
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE;
+SELECT @@tx_read_only;
+@@tx_read_only
+0
+SET SESSION TRANSACTION READ ONLY, ISOLATION LEVEL REPEATABLE READ;
+SELECT @@tx_read_only;
+@@tx_read_only
+1
+START TRANSACTION;
+# Not allowed inside a transaction
+SET TRANSACTION READ ONLY;
+ERROR 25001: Transaction characteristics can't be changed while a transaction is in progress
+# But these are allowed.
+SET SESSION TRANSACTION READ ONLY;
+SET GLOBAL TRANSACTION READ ONLY;
+COMMIT;
+SET SESSION TRANSACTION READ WRITE;
+SET GLOBAL TRANSACTION READ WRITE;
+#
+# Test 3: Test that write operations are properly blocked.
+CREATE TABLE t1(a INT);
+CREATE TEMPORARY TABLE temp_t2(a INT);
+SET SESSION TRANSACTION READ ONLY;
+# 1: DDL should be blocked, also on temporary tables.
+CREATE TABLE t3(a INT);
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+ALTER TABLE t1 COMMENT "Test";
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+DROP TABLE t1;
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+CREATE TEMPORARY TABLE temp_t3(a INT);
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+ALTER TABLE temp_t2 COMMENT "Test";
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+DROP TEMPORARY TABLE temp_t2;
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+CREATE FUNCTION f1() RETURNS INT RETURN 1;
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+DROP FUNCTION f1;
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+CREATE PROCEDURE p1() BEGIN END;
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+DROP PROCEDURE p1;
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+CREATE VIEW v1 AS SELECT 1;
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+SET SESSION TRANSACTION READ WRITE;
+CREATE VIEW v1 AS SELECT 1;
+SET SESSION TRANSACTION READ ONLY;
+DROP VIEW v1;
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+SET SESSION TRANSACTION READ WRITE;
+DROP VIEW v1;
+SET SESSION TRANSACTION READ ONLY;
+RENAME TABLE t1 TO t2;
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+RENAME TABLE temp_t2 TO temp_t3;
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+TRUNCATE TABLE t1;
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+CREATE DATABASE db1;
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+DROP DATABASE db1;
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+SET SESSION TRANSACTION READ WRITE;
+# 2: DML should be blocked on non-temporary tables.
+START TRANSACTION READ ONLY;
+INSERT INTO t1 VALUES (1), (2);
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+UPDATE t1 SET a= 3;
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+DELETE FROM t1;
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+# 3: DML should be allowed on temporary tables.
+INSERT INTO temp_t2 VALUES (1), (2);
+UPDATE temp_t2 SET a= 3;
+DELETE FROM temp_t2;
+# 4: Queries should not be blocked.
+SELECT * FROM t1;
+a
+SELECT * FROM temp_t2;
+a
+HANDLER t1 OPEN;
+HANDLER t1 READ FIRST;
+a
+HANDLER t1 CLOSE;
+HANDLER temp_t2 OPEN;
+HANDLER temp_t2 READ FIRST;
+a
+HANDLER temp_t2 CLOSE;
+# 5: Prepared statements
+PREPARE stmt FROM "DELETE FROM t1";
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+PREPARE stmt FROM "DELETE FROM temp_t2";
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+COMMIT;
+# 6: Stored routines
+CREATE FUNCTION f1() RETURNS INT
+BEGIN
+DELETE FROM t1;
+RETURN 1;
+END|
+CREATE FUNCTION f2() RETURNS INT
+BEGIN
+DELETE FROM temp_t2;
+RETURN 1;
+END|
+CREATE PROCEDURE p1() DELETE FROM t1;
+CREATE PROCEDURE p2() DELETE FROM temp_t2;
+START TRANSACTION READ ONLY;
+SELECT f1();
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+SELECT f2();
+f2()
+1
+CALL p1();
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+CALL p2();
+COMMIT;
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+# 7: Views
+CREATE VIEW v1 AS SELECT a FROM t1;
+START TRANSACTION READ ONLY;
+INSERT INTO v1 VALUES (1), (2);
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+SELECT * FROM v1;
+a
+COMMIT;
+DROP VIEW v1;
+# 8: LOCK TABLE
+SET SESSION TRANSACTION READ ONLY;
+LOCK TABLE t1 WRITE;
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+LOCK TABLE t1 READ;
+UNLOCK TABLES;
+SET SESSION TRANSACTION READ WRITE;
+DROP TABLE temp_t2, t1;
+#
+# Test 4: SET TRANSACTION, CHAINing transactions
+CREATE TABLE t1(a INT);
+SET SESSION TRANSACTION READ ONLY;
+START TRANSACTION;
+DELETE FROM t1;
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+COMMIT;
+START TRANSACTION READ WRITE;
+DELETE FROM t1;
+COMMIT;
+SET SESSION TRANSACTION READ WRITE;
+SET TRANSACTION READ ONLY;
+START TRANSACTION;
+DELETE FROM t1;
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+COMMIT;
+START TRANSACTION READ WRITE;
+DELETE FROM t1;
+COMMIT;
+START TRANSACTION READ ONLY;
+SELECT * FROM t1;
+a
+COMMIT AND CHAIN;
+DELETE FROM t1;
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+COMMIT;
+START TRANSACTION READ ONLY;
+SELECT * FROM t1;
+a
+ROLLBACK AND CHAIN;
+DELETE FROM t1;
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+COMMIT;
+DROP TABLE t1;
+#
+# Test 5: Test that reserved keyword ONLY is still allowed as
+#         identifier - both directly and in SPs.
+SET @only= 1;
+CREATE TABLE t1 (only INT);
+INSERT INTO t1 (only) values (1);
+SELECT only FROM t1 WHERE only = 1;
+only
+1
+DROP TABLE t1;
+CREATE PROCEDURE p1()
+BEGIN
+DECLARE only INT DEFAULT 1;
+END|
+CALL p1();
+DROP PROCEDURE p1;
+#
+# Test 6: Check that XA transactions obey default access mode.
+CREATE TABLE t1(a INT);
+SET TRANSACTION READ ONLY;
+XA START 'test1';
+INSERT INTO t1 VALUES (1);
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+UPDATE t1 SET a=2;
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+DELETE FROM t1;
+ERROR 25006: Cannot execute statement in a READ ONLY transaction.
+XA END 'test1';
+XA PREPARE 'test1';
+XA COMMIT 'test1';
+DROP TABLE t1;
+#
+# Test 7: SET TRANSACTION inside stored routines
+CREATE PROCEDURE p1() SET SESSION TRANSACTION READ ONLY;
+CALL p1();
+SELECT @@tx_read_only;
+@@tx_read_only
+1
+SET SESSION TRANSACTION READ WRITE;
+DROP PROCEDURE p1;
+CREATE PROCEDURE p1() SET SESSION TRANSACTION READ ONLY,
+ISOLATION LEVEL SERIALIZABLE;
+CALL p1();
+SELECT @@tx_read_only;
+@@tx_read_only
+1
+SET SESSION TRANSACTION READ WRITE, ISOLATION LEVEL REPEATABLE READ;
+DROP PROCEDURE p1;
+CREATE FUNCTION f1() RETURNS INT
+BEGIN
+SET SESSION TRANSACTION READ ONLY;
+RETURN 1;
+END|
+SELECT f1();
+f1()
+1
+SELECT @@tx_read_only;
+@@tx_read_only
+1
+SET SESSION TRANSACTION READ WRITE;
+DROP FUNCTION f1;
+CREATE FUNCTION f1() RETURNS INT
+BEGIN
+SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY;
+RETURN 1;
+END|
+SELECT f1();
+f1()
+1
+SELECT @@tx_read_only;
+@@tx_read_only
+1
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE;
+DROP FUNCTION f1;
+#
+# Test 8: SET TRANSACTION and auto-commit
+SELECT @@autocommit;
+@@autocommit
+1
+CREATE TABLE t1(a INT) engine=InnoDB;
+SET TRANSACTION READ ONLY;
+SELECT * FROM t1;
+a
+# This statement should work, since last statement committed.
+INSERT INTO t1 VALUES (1);
+DROP TABLE t1;