diff options
author | Sergey Vojtovich <svoj@mariadb.org> | 2017-12-22 14:03:25 +0400 |
---|---|---|
committer | Sergey Vojtovich <svoj@mariadb.org> | 2017-12-22 14:03:25 +0400 |
commit | 4b8cd4536a2c6376284288cd4617369a5e48236a (patch) | |
tree | b823207f11b78961a967e3ca1984c25c8987bc1a | |
parent | 24efee9100f4666ac61e1ac317e3d0fd12a5265c (diff) | |
download | mariadb-git-4b8cd4536a2c6376284288cd4617369a5e48236a.tar.gz |
MDEV-13626 Merge InnoDB test cases from MySQL 5.7
Coverage for temporary tables modifications in read-only transactions.
Introduced in 5.7 by 325cdf426
-rw-r--r-- | mysql-test/suite/innodb/r/innodb.result | 80 | ||||
-rw-r--r-- | mysql-test/suite/innodb/t/innodb.test | 70 |
2 files changed, 150 insertions, 0 deletions
diff --git a/mysql-test/suite/innodb/r/innodb.result b/mysql-test/suite/innodb/r/innodb.result index cded1f98fb3..159043a7170 100644 --- a/mysql-test/suite/innodb/r/innodb.result +++ b/mysql-test/suite/innodb/r/innodb.result @@ -3143,3 +3143,83 @@ show status like "handler_read_key"; Variable_name Value Handler_read_key 0 drop table t1; +CREATE TABLE t1 (c1 INT) ENGINE=InnoDB; +CREATE TEMPORARY TABLE t2 (c1 INT) ENGINE=InnoDB; +START TRANSACTION READ ONLY; +INSERT INTO t2 VALUES(0); +INSERT INTO t1 VALUES(0); +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +ROLLBACK; +SELECT * FROM t1; +c1 +SELECT * FROM t2; +c1 +START TRANSACTION READ ONLY; +INSERT INTO t1 VALUES(0); +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +INSERT INTO t2 VALUES(1); +COMMIT; +SET TRANSACTION READ ONLY; +START TRANSACTION; +INSERT INTO t2 VALUES(3); +INSERT INTO t1 VALUES(0); +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +COMMIT; +SELECT * FROM t1; +c1 +SELECT * FROM t2; +c1 +1 +3 +DROP TABLE t2; +CREATE TEMPORARY TABLE t2 ( +c1 INT AUTO_INCREMENT PRIMARY KEY, +c2 INT, INDEX idx(c2)) ENGINE=InnoDB; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TEMPORARY TABLE `t2` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + `c2` int(11) DEFAULT NULL, + PRIMARY KEY (`c1`), + KEY `idx` (`c2`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +START TRANSACTION READ ONLY; +INSERT INTO t2 VALUES(NULL,1),(NULL,2),(NULL,3); +INSERT INTO t1 VALUES(0); +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +ROLLBACK; +SELECT * FROM t1; +c1 +SELECT * FROM t2; +c1 c2 +START TRANSACTION READ ONLY; +INSERT INTO t1 VALUES(0); +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +INSERT INTO t2 VALUES(NULL,1),(NULL,2),(NULL,3); +COMMIT; +SET TRANSACTION READ ONLY; +START TRANSACTION; +INSERT INTO t2 VALUES(NULL,1),(NULL,2),(NULL,3); +INSERT INTO t1 VALUES(0); +ERROR 25006: Cannot execute statement in a READ ONLY transaction. +COMMIT; +SHOW CREATE TABLE t2; +Table Create Table +t2 CREATE TEMPORARY TABLE `t2` ( + `c1` int(11) NOT NULL AUTO_INCREMENT, + `c2` int(11) DEFAULT NULL, + PRIMARY KEY (`c1`), + KEY `idx` (`c2`) +) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=latin1 +SELECT * FROM t1; +c1 +SELECT * FROM t2; +c1 c2 +4 1 +7 1 +5 2 +8 2 +6 3 +9 3 +DROP TABLE t1; +DROP TABLE t2; diff --git a/mysql-test/suite/innodb/t/innodb.test b/mysql-test/suite/innodb/t/innodb.test index b519c48b23d..c3b6ed7d0a7 100644 --- a/mysql-test/suite/innodb/t/innodb.test +++ b/mysql-test/suite/innodb/t/innodb.test @@ -2528,6 +2528,76 @@ select f1 from t1; show status like "handler_read_key"; drop table t1; +# +# Test handling of writes to TEMPORARY tables for read-only transactions +# +CREATE TABLE t1 (c1 INT) ENGINE=InnoDB; +CREATE TEMPORARY TABLE t2 (c1 INT) ENGINE=InnoDB; + +# Check that the rollback works +START TRANSACTION READ ONLY; +INSERT INTO t2 VALUES(0); +--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION +INSERT INTO t1 VALUES(0); +ROLLBACK; + +SELECT * FROM t1; +SELECT * FROM t2; + +START TRANSACTION READ ONLY; +--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION +INSERT INTO t1 VALUES(0); +INSERT INTO t2 VALUES(1); +COMMIT; + +SET TRANSACTION READ ONLY; +START TRANSACTION; +INSERT INTO t2 VALUES(3); +--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION +INSERT INTO t1 VALUES(0); +COMMIT; + +SELECT * FROM t1; +SELECT * FROM t2; + +DROP TABLE t2; + +# This time with some indexes +CREATE TEMPORARY TABLE t2 ( + c1 INT AUTO_INCREMENT PRIMARY KEY, + c2 INT, INDEX idx(c2)) ENGINE=InnoDB; + +SHOW CREATE TABLE t2; + +# Check that the rollback works +START TRANSACTION READ ONLY; +INSERT INTO t2 VALUES(NULL,1),(NULL,2),(NULL,3); +--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION +INSERT INTO t1 VALUES(0); +ROLLBACK; + +SELECT * FROM t1; +SELECT * FROM t2; + +START TRANSACTION READ ONLY; +--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION +INSERT INTO t1 VALUES(0); +INSERT INTO t2 VALUES(NULL,1),(NULL,2),(NULL,3); +COMMIT; + +SET TRANSACTION READ ONLY; +START TRANSACTION; +INSERT INTO t2 VALUES(NULL,1),(NULL,2),(NULL,3); +--error ER_CANT_EXECUTE_IN_READ_ONLY_TRANSACTION +INSERT INTO t1 VALUES(0); +COMMIT; + +SHOW CREATE TABLE t2; +SELECT * FROM t1; +SELECT * FROM t2; + +DROP TABLE t1; +DROP TABLE t2; ####################################################################### # # # Please, DO NOT TOUCH this file as well as the innodb.result file. # |