Create 'main' test directory and move 't' and 'r' there

1 files changed, 675 insertions, 0 deletions

diff --git a/mysql-test/main/commit.test b/mysql-test/main/commit.test
new file mode 100644
index 00000000000..762397dfa23
--- /dev/null
+++ b/mysql-test/main/commit.test
@@ -0,0 +1,675 @@
+--source include/have_innodb.inc
+
+connect (con1,localhost,root,,);
+
+--echo #
+--echo # Bug#20837 Apparent change of isolation level 
+--echo #           during transaction
+--echo #
+--echo # Bug#53343 completion_type=1, COMMIT/ROLLBACK 
+--echo #           AND CHAIN don't preserve the isolation 
+--echo #           level
+#
+# A set of test cases that verifies operation of
+# transaction isolation level and chaining is 
+# provided
+
+# init
+connection default;
+
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+CREATE TABLE t1 (s1 INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1),(2);
+COMMIT;
+
+#
+# Verify that SET TRANS ISO LEVEL is not allowed 
+# inside a transaction
+#
+START TRANSACTION;
+--error ER_CANT_CHANGE_TX_CHARACTERISTICS
+SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+COMMIT;
+
+#
+# Verify consistent output from 
+# SELECT @@tx_isolation  (Bug#20837)
+#
+# The transaction will be in READ UNCOMMITTED mode,
+# but SELECT @@tx_isolation should report the session
+# value, which is REPEATABLE READ
+#
+SET @@autocommit=0;
+COMMIT;
+SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+START TRANSACTION;
+SELECT @@tx_isolation;
+--echo Should be REPEATABLE READ
+SELECT * FROM t1;
+SELECT @@tx_isolation;
+--echo Should be REPEATABLE READ
+INSERT INTO t1 VALUES (-1);
+SELECT @@tx_isolation;
+--echo Should be REPEATABLE READ
+COMMIT;
+
+#
+# Verify that a change in the session variable
+# does not affect the currently started
+# transaction
+#
+START TRANSACTION;
+SELECT * FROM t1;
+SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+
+connection con1;
+START TRANSACTION;
+INSERT INTO t1 VALUES (1000);
+COMMIT;
+
+connection default;
+--echo We should not be able to read the '1000'
+SELECT * FROM t1;
+COMMIT;
+
+--echo Now, the '1000' should appear.
+START TRANSACTION;
+SELECT * FROM t1;
+COMMIT;
+
+# restore the session value
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+
+#
+# A set of test cases for verification that
+# isolation level during chaining works. MySQL
+# has three variants of chaining, i.e
+# COMMIT AND CHAIN, ROLLBACK AND CHAIN, and
+# the use of @completion_type
+#
+
+#
+# Verify isolation level with COMMIT AND CHAIN
+#
+# COMMIT AND CHAIN causes a new transaction to
+# begin as soon as the current ends, and the new
+# transaction will have the same tran. iso. level
+# as the first.
+#
+connection default;
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
+START TRANSACTION;
+
+connection con1;
+START TRANSACTION;
+INSERT INTO t1 VALUES (1001);
+COMMIT;
+
+connection default;
+SELECT COUNT(*) FROM t1 WHERE s1 = 1001;
+--echo Should be 1
+COMMIT AND CHAIN;
+
+connection con1;
+INSERT INTO t1 VALUES (1002);
+COMMIT;
+
+connection default;
+SELECT COUNT(*) FROM t1 WHERE s1 = 1002;
+--echo Should be 1
+COMMIT;
+SELECT * FROM t1;
+DELETE FROM t1 WHERE s1 >= 1000;
+COMMIT;
+
+#
+# Verify isolation level with ROLLBACK AND CHAIN
+#
+connection default;
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
+START TRANSACTION;
+
+connection con1;
+START TRANSACTION;
+INSERT INTO t1 VALUES (1001);
+COMMIT;
+
+connection default;
+SELECT COUNT(*) FROM t1 WHERE s1 = 1001;
+--echo Should be 1
+ROLLBACK AND CHAIN;
+
+connection con1;
+INSERT INTO t1 VALUES (1002);
+COMMIT;
+
+connection default;
+SELECT COUNT(*) FROM t1 WHERE s1 = 1002;
+--echo Should be 1
+COMMIT;
+SELECT * FROM t1;
+DELETE FROM t1 WHERE s1 >= 1000;
+COMMIT;
+
+#
+# Verify isolation level with @completion_type=1.
+# (A @@completion_type value of 1 is equivalentl to 
+# explicitly adding "AND CHAIN" to COMMIT or ROLLBACK)
+#
+
+#
+# Verify that COMMIT AND NO CHAIN  overrides the value
+# of @@completion_type
+#
+SET @@completion_type=1;
+
+connection default;
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
+START TRANSACTION;
+
+connection con1;
+START TRANSACTION;
+INSERT INTO t1 VALUES (1001);
+COMMIT;
+
+connection default;
+SELECT * FROM t1 WHERE s1 >= 1000;
+--echo Should see 1001
+COMMIT AND NO CHAIN;
+--echo default transaction is now in REPEATABLE READ
+
+connection con1;
+INSERT INTO t1 VALUES (1002);
+COMMIT;
+
+connection default;
+SELECT * FROM t1 WHERE s1 >= 1000;
+--echo Should see 1001 and 1002
+
+connection con1;
+INSERT INTO t1 VALUES (1003);
+COMMIT;
+
+connection default;
+SELECT * FROM t1 WHERE s1 >= 1000;
+--echo Should see 1001 and 1002, but NOT 1003
+COMMIT;
+
+SELECT * FROM t1;
+DELETE FROM t1 WHERE s1 >= 1000;
+COMMIT AND NO CHAIN;
+SET @@completion_type=0;
+COMMIT;
+
+#
+# Verify that ROLLBACK AND NO CHAIN  overrides the value
+# of @@completion_type
+#
+connection default;
+SET @@completion_type=1;
+COMMIT AND NO CHAIN;
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
+START TRANSACTION;
+
+connection con1;
+START TRANSACTION;
+INSERT INTO t1 VALUES (1001);
+COMMIT;
+
+connection default;
+SELECT * FROM t1 WHERE s1 >= 1000;
+--echo Should see 1001
+ROLLBACK AND NO CHAIN;
+--echo default transaction is now in REPEATABLE READ
+
+connection con1;
+INSERT INTO t1 VALUES (1002);
+COMMIT;
+
+connection default;
+SELECT * FROM t1 WHERE s1 >= 1000;
+--echo Should see 1001 and 1002
+
+connection con1;
+INSERT INTO t1 VALUES (1003);
+COMMIT;
+
+connection default;
+SELECT * FROM t1 WHERE s1 >= 1000;
+--echo Should see 1001 and 1002, but NOT 1003
+
+COMMIT;
+SELECT * FROM t1;
+DELETE FROM t1 WHERE s1 >= 1000;
+COMMIT AND NO CHAIN;
+SET @@completion_type=0;
+COMMIT;
+
+#
+# Verify that in the sequence:
+#  SET TRANSACTION ISOLATION LEVEL
+#  SET SESSION ISOLATION LEVEL
+#
+# SET SESSION ISOLATION LEVEL has precedence over 
+# SET TRANSACTION.  (Note that this is _not_ 
+# in accordance with ISO 9075.)
+#
+connection default;
+
+SET TRANSACTION ISOLATION LEVEL	READ COMMITTED;
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+START TRANSACTION;
+SELECT * FROM t1;
+
+connection con1;
+INSERT INTO t1 VALUES (1000);
+COMMIT;
+
+connection default;
+SELECT * FROM t1;
+--echo Should get same result as above (i.e should not read '1000')
+COMMIT;
+
+DELETE FROM t1 WHERE s1 >= 1000;
+COMMIT;
+
+
+#
+# Verify that a transaction ended with an
+# implicit commit (i.e a DDL statement), the
+# @@completetion_type setting is ignored, and
+# the next transaction's isolation level is
+# the session level.
+#
+SET @@completion_type=1;
+COMMIT AND NO CHAIN;
+SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
+START TRANSACTION;
+TRUNCATE TABLE t1;
+INSERT INTO t1 VALUES (1000);
+SELECT * FROM t1;
+--echo Should read '1000'
+
+connection con1;
+INSERT INTO t1 VALUES (1001);
+COMMIT;
+
+connection default;
+SELECT * FROM t1;
+--echo 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;
+
+
+#
+# Cleanup
+#
+SET @@autocommit=1;
+COMMIT;
+
+disconnect con1;
+
+DROP TABLE t1;
+
+--echo #
+--echo # End of test cases for Bug#20837
+--echo #
+
+
+--echo #
+--echo # WL#5968 Implement START TRANSACTION READ (WRITE|ONLY);
+--echo #
+
+--echo #
+--echo # Test 1: Check supported syntax
+
+START TRANSACTION;
+COMMIT;
+
+START TRANSACTION READ ONLY;
+COMMIT;
+
+START TRANSACTION READ WRITE;
+COMMIT;
+
+--error ER_PARSE_ERROR
+START TRANSACTION READ ONLY, READ WRITE;
+
+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;
+
+--error ER_PARSE_ERROR
+START TRANSACTION READ ONLY, WITH CONSISTENT SNAPSHOT, READ WRITE;
+
+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;
+--error ER_PARSE_ERROR
+SET TRANSACTION READ ONLY, READ WRITE;
+COMMIT;
+
+--echo #
+--echo # Test 2: Check setting of variable.
+
+SET SESSION TRANSACTION READ WRITE;
+SELECT @@tx_read_only;
+
+SET SESSION TRANSACTION READ ONLY;
+SELECT @@tx_read_only;
+
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE;
+SELECT @@tx_read_only;
+
+SET SESSION TRANSACTION READ ONLY, ISOLATION LEVEL REPEATABLE READ;
+SELECT @@tx_read_only;
+
+START TRANSACTION;
+--echo # Not allowed inside a transaction
+--error ER_CANT_CHANGE_TX_CHARACTERISTICS
+SET TRANSACTION READ ONLY;
+--echo # But these are allowed.
+SET SESSION TRANSACTION READ ONLY;
+SET GLOBAL TRANSACTION READ ONLY;
+COMMIT;
+
+# Reset to defaults
+SET SESSION TRANSACTION READ WRITE;
+SET GLOBAL TRANSACTION READ WRITE;
+
+--echo #
+--echo # 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;
+
+--echo # 1: DDL should be blocked, also on temporary tables.
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+CREATE TABLE t3(a INT);
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+ALTER TABLE t1 COMMENT "Test";
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+DROP TABLE t1;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+CREATE TEMPORARY TABLE temp_t3(a INT);
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+ALTER TABLE temp_t2 COMMENT "Test";
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+DROP TEMPORARY TABLE temp_t2;
+
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+CREATE FUNCTION f1() RETURNS INT RETURN 1;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+DROP FUNCTION f1;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+CREATE PROCEDURE p1() BEGIN END;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+DROP PROCEDURE p1;
+
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+CREATE VIEW v1 AS SELECT 1;
+SET SESSION TRANSACTION READ WRITE;
+CREATE VIEW v1 AS SELECT 1;
+SET SESSION TRANSACTION READ ONLY;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+DROP VIEW v1;
+SET SESSION TRANSACTION READ WRITE;
+DROP VIEW v1;
+SET SESSION TRANSACTION READ ONLY;
+
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+RENAME TABLE t1 TO t2;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+RENAME TABLE temp_t2 TO temp_t3;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+TRUNCATE TABLE t1;
+
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+CREATE DATABASE db1;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+DROP DATABASE db1;
+SET SESSION TRANSACTION READ WRITE;
+
+--echo # 2: DML should be blocked on non-temporary tables.
+START TRANSACTION READ ONLY;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+INSERT INTO t1 VALUES (1), (2);
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+UPDATE t1 SET a= 3;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+DELETE FROM t1;
+
+--echo # 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;
+
+--echo # 4: Queries should not be blocked.
+SELECT * FROM t1;
+SELECT * FROM temp_t2;
+
+HANDLER t1 OPEN;
+HANDLER t1 READ FIRST;
+HANDLER t1 CLOSE;
+
+HANDLER temp_t2 OPEN;
+HANDLER temp_t2 READ FIRST;
+HANDLER temp_t2 CLOSE;
+
+--echo # 5: Prepared statements
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+PREPARE stmt FROM "DELETE FROM t1";
+
+PREPARE stmt FROM "DELETE FROM temp_t2";
+EXECUTE stmt;
+DEALLOCATE PREPARE stmt;
+COMMIT;
+
+--echo # 6: Stored routines
+
+delimiter |;
+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|
+delimiter ;|
+
+CREATE PROCEDURE p1() DELETE FROM t1;
+CREATE PROCEDURE p2() DELETE FROM temp_t2;
+
+START TRANSACTION READ ONLY;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+SELECT f1();
+SELECT f2();
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+CALL p1();
+CALL p2();
+COMMIT;
+
+DROP FUNCTION f1;
+DROP FUNCTION f2;
+DROP PROCEDURE p1;
+DROP PROCEDURE p2;
+
+--echo # 7: Views
+CREATE VIEW v1 AS SELECT a FROM t1;
+# Not supported for temporary tables.
+
+START TRANSACTION READ ONLY;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+INSERT INTO v1 VALUES (1), (2);
+SELECT * FROM v1;
+COMMIT;
+
+DROP VIEW v1;
+
+--echo # 8: LOCK TABLE
+SET SESSION TRANSACTION READ ONLY;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+LOCK TABLE t1 WRITE;
+
+LOCK TABLE t1 READ;
+UNLOCK TABLES;
+
+# Not supported for temporary tables.
+
+SET SESSION TRANSACTION READ WRITE;
+DROP TABLE temp_t2, t1;
+
+--echo #
+--echo # Test 4: SET TRANSACTION, CHAINing transactions
+
+CREATE TABLE t1(a INT);
+
+SET SESSION TRANSACTION READ ONLY;
+START TRANSACTION;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+DELETE FROM t1;
+COMMIT;
+START TRANSACTION READ WRITE;
+DELETE FROM t1;
+COMMIT;
+
+SET SESSION TRANSACTION READ WRITE;
+SET TRANSACTION READ ONLY;
+START TRANSACTION;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+DELETE FROM t1;
+COMMIT;
+START TRANSACTION READ WRITE;
+DELETE FROM t1;
+COMMIT;
+
+START TRANSACTION READ ONLY;
+SELECT * FROM t1;
+COMMIT AND CHAIN;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+DELETE FROM t1;
+COMMIT;
+
+START TRANSACTION READ ONLY;
+SELECT * FROM t1;
+ROLLBACK AND CHAIN;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+DELETE FROM t1;
+COMMIT;
+
+DROP TABLE t1;
+
+--echo #
+--echo # Test 5: Test that reserved keyword ONLY is still allowed as
+--echo #         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;
+DROP TABLE t1;
+
+DELIMITER |;
+CREATE PROCEDURE p1()
+BEGIN
+  DECLARE only INT DEFAULT 1;
+END|
+DELIMITER ;|
+
+CALL p1();
+
+DROP PROCEDURE p1;
+
+--echo #
+--echo # Test 6: Check that XA transactions obey default access mode.
+
+CREATE TABLE t1(a INT);
+
+SET TRANSACTION READ ONLY;
+XA START 'test1';
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+INSERT INTO t1 VALUES (1);
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+UPDATE t1 SET a=2;
+--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION
+DELETE FROM t1;
+XA END 'test1';
+XA PREPARE 'test1';
+XA COMMIT 'test1';
+
+DROP TABLE t1;
+
+--echo #
+--echo # Test 7: SET TRANSACTION inside stored routines
+
+CREATE PROCEDURE p1() SET SESSION TRANSACTION READ ONLY;
+CALL p1();
+SELECT @@tx_read_only;
+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;
+SET SESSION TRANSACTION READ WRITE, ISOLATION LEVEL REPEATABLE READ;
+DROP PROCEDURE p1;
+
+delimiter |;
+CREATE FUNCTION f1() RETURNS INT
+BEGIN
+  SET SESSION TRANSACTION READ ONLY;
+  RETURN 1;
+END|
+delimiter ;|
+
+SELECT f1();
+SELECT @@tx_read_only;
+SET SESSION TRANSACTION READ WRITE;
+DROP FUNCTION f1;
+
+delimiter |;
+CREATE FUNCTION f1() RETURNS INT
+BEGIN
+  SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE, READ ONLY;
+  RETURN 1;
+END|
+delimiter ;|
+
+SELECT f1();
+SELECT @@tx_read_only;
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ, READ WRITE;
+DROP FUNCTION f1;
+
+--echo #
+--echo # Test 8: SET TRANSACTION and auto-commit
+
+SELECT @@autocommit;
+CREATE TABLE t1(a INT) engine=InnoDB;
+
+SET TRANSACTION READ ONLY;
+SELECT * FROM t1;
+--echo # This statement should work, since last statement committed.
+INSERT INTO t1 VALUES (1);
+
+DROP TABLE t1;