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

1 files changed, 578 insertions, 0 deletions

diff --git a/mysql-test/main/commit.result b/mysql-test/main/commit.result
new file mode 100644
index 00000000000..f2e012d4782
--- /dev/null
+++ b/mysql-test/main/commit.result
@@ -0,0 +1,578 @@
+connect  con1,localhost,root,,;
+#
+# Bug#20837 Apparent change of isolation level 
+#           during transaction
+#
+# Bug#53343 completion_type=1, COMMIT/ROLLBACK 
+#           AND CHAIN don't preserve the isolation 
+#           level
+connection default;
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+CREATE TABLE t1 (s1 INT) ENGINE=InnoDB;
+INSERT INTO t1 VALUES (1),(2);
+COMMIT;
+START TRANSACTION;
+SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
+ERROR 25001: Transaction characteristics can't be changed while a transaction is in progress
+COMMIT;
+SET @@autocommit=0;
+COMMIT;
+SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
+START TRANSACTION;
+SELECT @@tx_isolation;
+@@tx_isolation
+REPEATABLE-READ
+Should be REPEATABLE READ
+SELECT * FROM t1;
+s1
+1
+2
+SELECT @@tx_isolation;
+@@tx_isolation
+REPEATABLE-READ
+Should be REPEATABLE READ
+INSERT INTO t1 VALUES (-1);
+SELECT @@tx_isolation;
+@@tx_isolation
+REPEATABLE-READ
+Should be REPEATABLE READ
+COMMIT;
+START TRANSACTION;
+SELECT * FROM t1;
+s1
+1
+2
+-1
+SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
+connection con1;
+START TRANSACTION;
+INSERT INTO t1 VALUES (1000);
+COMMIT;
+connection default;
+We should not be able to read the '1000'
+SELECT * FROM t1;
+s1
+1
+2
+-1
+COMMIT;
+Now, the '1000' should appear.
+START TRANSACTION;
+SELECT * FROM t1;
+s1
+1
+2
+-1
+1000
+COMMIT;
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+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;
+COUNT(*)
+1
+Should be 1
+COMMIT AND CHAIN;
+connection con1;
+INSERT INTO t1 VALUES (1002);
+COMMIT;
+connection default;
+SELECT COUNT(*) FROM t1 WHERE s1 = 1002;
+COUNT(*)
+1
+Should be 1
+COMMIT;
+SELECT * FROM t1;
+s1
+1
+2
+-1
+1000
+1001
+1002
+DELETE FROM t1 WHERE s1 >= 1000;
+COMMIT;
+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;
+COUNT(*)
+1
+Should be 1
+ROLLBACK AND CHAIN;
+connection con1;
+INSERT INTO t1 VALUES (1002);
+COMMIT;
+connection default;
+SELECT COUNT(*) FROM t1 WHERE s1 = 1002;
+COUNT(*)
+1
+Should be 1
+COMMIT;
+SELECT * FROM t1;
+s1
+1
+2
+-1
+1001
+1002
+DELETE FROM t1 WHERE s1 >= 1000;
+COMMIT;
+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;
+s1
+1001
+Should see 1001
+COMMIT AND NO CHAIN;
+default transaction is now in REPEATABLE READ
+connection con1;
+INSERT INTO t1 VALUES (1002);
+COMMIT;
+connection default;
+SELECT * FROM t1 WHERE s1 >= 1000;
+s1
+1001
+1002
+Should see 1001 and 1002
+connection con1;
+INSERT INTO t1 VALUES (1003);
+COMMIT;
+connection default;
+SELECT * FROM t1 WHERE s1 >= 1000;
+s1
+1001
+1002
+Should see 1001 and 1002, but NOT 1003
+COMMIT;
+SELECT * FROM t1;
+s1
+1
+2
+-1
+1001
+1002
+1003
+DELETE FROM t1 WHERE s1 >= 1000;
+COMMIT AND NO CHAIN;
+SET @@completion_type=0;
+COMMIT;
+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;
+s1
+1001
+Should see 1001
+ROLLBACK AND NO CHAIN;
+default transaction is now in REPEATABLE READ
+connection con1;
+INSERT INTO t1 VALUES (1002);
+COMMIT;
+connection default;
+SELECT * FROM t1 WHERE s1 >= 1000;
+s1
+1001
+1002
+Should see 1001 and 1002
+connection con1;
+INSERT INTO t1 VALUES (1003);
+COMMIT;
+connection default;
+SELECT * FROM t1 WHERE s1 >= 1000;
+s1
+1001
+1002
+Should see 1001 and 1002, but NOT 1003
+COMMIT;
+SELECT * FROM t1;
+s1
+1
+2
+-1
+1001
+1002
+1003
+DELETE FROM t1 WHERE s1 >= 1000;
+COMMIT AND NO CHAIN;
+SET @@completion_type=0;
+COMMIT;
+connection default;
+SET TRANSACTION ISOLATION LEVEL	READ COMMITTED;
+SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
+START TRANSACTION;
+SELECT * FROM t1;
+s1
+1
+2
+-1
+connection con1;
+INSERT INTO t1 VALUES (1000);
+COMMIT;
+connection default;
+SELECT * FROM t1;
+s1
+1
+2
+-1
+Should get same result as above (i.e should not read '1000')
+COMMIT;
+DELETE FROM t1 WHERE s1 >= 1000;
+COMMIT;
+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;
+s1
+1000
+Should read '1000'
+connection con1;
+INSERT INTO t1 VALUES (1001);
+COMMIT;
+connection default;
+SELECT * FROM t1;
+s1
+1000
+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;
+COMMIT;
+disconnect con1;
+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;