diff options
Diffstat (limited to 'mysql-test/main/read_only_innodb.test')
| -rw-r--r-- | mysql-test/main/read_only_innodb.test | 251 |
1 files changed, 251 insertions, 0 deletions
diff --git a/mysql-test/main/read_only_innodb.test b/mysql-test/main/read_only_innodb.test new file mode 100644 index 00000000000..9ba3ccaca07 --- /dev/null +++ b/mysql-test/main/read_only_innodb.test @@ -0,0 +1,251 @@ +# should work with embedded server after mysqltest is fixed +-- source include/not_embedded.inc +-- source include/have_innodb.inc + +# +# BUG#11733: COMMITs should not happen if read-only is set +# + +--disable_warnings +DROP TABLE IF EXISTS table_11733 ; +--enable_warnings + +# READ_ONLY does nothing to SUPER users +# so we use a non-SUPER one: + +create user test@localhost; +grant CREATE, SELECT, DROP on *.* to test@localhost; + +connect (con1,localhost,test,,test); + +connection default; +set global read_only=0; + +# Any transactional engine will do +create table table_11733 (a int) engine=InnoDb; + +connection con1; +BEGIN; +insert into table_11733 values(11733); + +connection default; +set global read_only=1; + +connection con1; +select @@global.read_only; +select * from table_11733 ; +--error ER_OPTION_PREVENTS_STATEMENT +COMMIT; + +connection default; +set global read_only=0; +drop table table_11733 ; +drop user test@localhost; + +disconnect con1; + +# +# Bug #35732: read-only blocks SELECT statements in InnoDB +# +# Test 1: read only mode +create user test@localhost; +GRANT CREATE, SELECT, DROP ON *.* TO test@localhost; +connect(con1, localhost, test, , test); + +connection default; +CREATE TABLE t1(a INT) ENGINE=INNODB; +INSERT INTO t1 VALUES (0), (1); +SET GLOBAL read_only=1; + +connection con1; +SELECT * FROM t1; +BEGIN; +SELECT * FROM t1; +COMMIT; + +connection default; +SET GLOBAL read_only=0; + +# +# Test 2: global read lock +# +FLUSH TABLES WITH READ LOCK; + +connection con1; +SELECT * FROM t1; +BEGIN; +SELECT * FROM t1; +COMMIT; + +# +# Tests that LOCK TABLE doesn't block the SET READ_ONLY=1 for the InnoDB tables +# + +connection default; +UNLOCK TABLES; + +connection con1; +lock table t1 read; + +connection default; +set global read_only=1; + +connection con1; +unlock tables; + +connection default; +SET GLOBAL read_only=0; + +UNLOCK TABLES; +DROP TABLE t1; +DROP USER test@localhost; + +disconnect con1; + +--echo echo End of 5.1 tests + +--echo # +--echo # Bug#33669: Transactional temporary tables do not work under --read-only +--echo # + +--disable_warnings +DROP DATABASE IF EXISTS db1; +--enable_warnings + +--echo # Setup user and tables +CREATE USER bug33669@localhost; +CREATE DATABASE db1; +CREATE TABLE db1.t1 (a INT) ENGINE=INNODB; +CREATE TABLE db1.t2 (a INT) ENGINE=INNODB; +INSERT INTO db1.t1 VALUES (1); +INSERT INTO db1.t2 VALUES (2); +GRANT CREATE TEMPORARY TABLES, DROP, INSERT, DELETE, UPDATE, + SELECT, LOCK TABLES ON db1.* TO bug33669@localhost; +SET GLOBAL READ_ONLY = ON; +connect(con1,localhost,bug33669,,db1); + +--echo +--echo # Create, insert and drop temporary table: +--echo +CREATE TEMPORARY TABLE temp (a INT) ENGINE=INNODB; +INSERT INTO temp VALUES (1); +DROP TABLE temp; + +--echo +--echo # Lock base tables and use temporary table: +--echo +CREATE TEMPORARY TABLE temp (a INT) ENGINE=INNODB; +LOCK TABLES t1 READ, t2 READ; +SELECT * FROM t1; +INSERT INTO temp values (1); +SELECT * FROM t2; +UNLOCK TABLES; +DROP TABLE temp; + +--echo +--echo # Transaction +--echo +BEGIN; +SELECT * FROM t1; +CREATE TEMPORARY TABLE temp (a INT) ENGINE=INNODB; +--error ER_OPTION_PREVENTS_STATEMENT +INSERT INTO t1 VALUES (1); +INSERT INTO temp VALUES (1); +SELECT * FROM t2; +ROLLBACK; +SELECT * FROM temp; +DROP TABLE temp; + +--echo +--echo # Lock base table as READ and temporary table as WRITE: +--echo +CREATE TEMPORARY TABLE temp (a INT) ENGINE=INNODB; +LOCK TABLES t1 READ, temp WRITE; +SELECT * FROM t1; +SELECT * FROM temp; +--error ER_OPTION_PREVENTS_STATEMENT +INSERT INTO t1 VALUES (1); +INSERT INTO temp VALUES (1); +DROP TABLE temp; +UNLOCK TABLES; + +--echo +--echo # Lock temporary table that shadows a base table: +--echo +CREATE TEMPORARY TABLE t1 (a INT) ENGINE=INNODB; +LOCK TABLES t1 WRITE; +DROP TABLE t1; +--error ER_TABLE_NOT_LOCKED +SELECT * FROM t1; + +--echo +--echo # INSERT SELECT from base table into temporary table: +--echo + +CREATE TEMPORARY TABLE temp1 (a INT) ENGINE=INNODB; +CREATE TEMPORARY TABLE temp2 LIKE temp1; +BEGIN; +INSERT INTO temp1 VALUES (10); +INSERT INTO temp2 VALUES (10); +INSERT INTO temp1 SELECT * FROM t1; +INSERT INTO temp2 SELECT * FROM t2; +SELECT * FROM temp1 ORDER BY a; +SELECT * FROM temp2 ORDER BY a; +ROLLBACK; +SELECT * FROM temp1,temp2; +LOCK TABLES t1 READ, t2 READ; +INSERT INTO temp1 VALUES (10); +INSERT INTO temp2 VALUES (10); +INSERT INTO temp1 SELECT * FROM t1; +INSERT INTO temp2 SELECT * FROM t2; +SELECT * FROM temp1 ORDER BY a; +SELECT * FROM temp2 ORDER BY a; +UNLOCK TABLES; +DELETE temp1, temp2 FROM temp1, temp2; +INSERT INTO temp1 VALUES (10); +INSERT INTO temp2 VALUES (10); +INSERT INTO temp1 SELECT * FROM t1; +INSERT INTO temp2 SELECT * FROM t2; +SELECT * FROM temp1 ORDER BY a; +SELECT * FROM temp2 ORDER BY a; +DROP TABLE temp1, temp2; + +--echo +--echo # INSERT and INSERT SELECT that uses subqueries: +CREATE TEMPORARY TABLE temp1 (a INT) ENGINE=INNODB; +CREATE TEMPORARY TABLE temp2 LIKE temp1; +INSERT INTO temp1 (a) VALUES ((SELECT MAX(a) FROM t1)); +LOCK TABLES t2 READ; +INSERT INTO temp2 (a) VALUES ((SELECT MAX(a) FROM t2)); +UNLOCK TABLES; +LOCK TABLES t1 READ, t2 READ; +INSERT INTO temp1 SELECT * FROM t1 WHERE a < (SELECT MAX(a) FROM t2); +INSERT INTO temp2 SELECT * FROM t2 WHERE a > (SELECT MAX(a) FROM t1); +UNLOCK TABLES; +INSERT INTO temp1 SELECT * FROM t1 WHERE a < (SELECT MAX(a) FROM t2); +INSERT INTO temp2 SELECT * FROM t2 WHERE a > (SELECT MAX(a) FROM t1); +SELECT * FROM temp1 ORDER BY a; +SELECT * FROM temp2 ORDER BY a; +DROP TABLE temp1, temp2; + +--echo +--echo # Multiple table update: +--echo + +CREATE TEMPORARY TABLE temp1 (a INT) ENGINE=INNODB; +CREATE TEMPORARY TABLE temp2 LIKE temp1; +INSERT INTO temp1 VALUES (1),(2); +INSERT INTO temp2 VALUES (3),(4); +UPDATE temp1,temp2 SET temp1.a = 5, temp2.a = 10; +SELECT * FROM temp1, temp2; +DROP TABLE temp1, temp2; + +--echo +--echo # Disconnect and cleanup +--echo +disconnect con1; +connection default; +SET GLOBAL READ_ONLY = OFF; +DROP USER bug33669@localhost; +DROP DATABASE db1; + |