From 3f619478f796eddbba6e39502fe941b285dd97b1 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Sat, 4 May 2024 20:00:34 +0200 Subject: Adding upstream version 1:10.11.6. Signed-off-by: Daniel Baumann --- mysql-test/main/read_only.test | 364 +++++++++++++++++++++++++++++++++++++++++ 1 file changed, 364 insertions(+) create mode 100644 mysql-test/main/read_only.test (limited to 'mysql-test/main/read_only.test') diff --git a/mysql-test/main/read_only.test b/mysql-test/main/read_only.test new file mode 100644 index 00000000..107a67c3 --- /dev/null +++ b/mysql-test/main/read_only.test @@ -0,0 +1,364 @@ +# Test of the READ_ONLY global variable: +# check that it blocks updates unless they are only on temporary tables. + +# should work with embedded server after mysqltest is fixed +--source include/not_embedded.inc +set @start_read_only= @@global.read_only; + +# Save the initial number of concurrent sessions +--source include/count_sessions.inc + +--disable_warnings +DROP TABLE IF EXISTS t1,t2,t3; +--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; +grant ALL on test.* to test@localhost; + +connect (con1,localhost,test,,test); + +connection default; + +set global read_only=0; + +connection con1; + +create table t1 (a int); +create trigger trg1 before insert on t1 for each row set @a:=1; + +insert into t1 values(1); + +create table t2 select * from t1; + +connection default; + +set global read_only=1; + +# We check that SUPER can: + +create table t3 (a int); +drop table t3; + +connection con1; + +select @@global.read_only; + +--error ER_OPTION_PREVENTS_STATEMENT +create table t3 (a int); + +--error ER_OPTION_PREVENTS_STATEMENT +insert into t1 values(1); + +--error ER_OPTION_PREVENTS_STATEMENT +drop trigger trg1; + +# if a statement, after parse stage, looks like it will update a +# non-temp table, it will be rejected, even if at execution it would +# have turned out that 0 rows would be updated +--error ER_OPTION_PREVENTS_STATEMENT +update t1 set a=1 where 1=0; + +# multi-update is special (see sql_parse.cc) so we test it +--error ER_OPTION_PREVENTS_STATEMENT +update t1,t2 set t1.a=t2.a+1 where t1.a=t2.a; + +# check multi-delete to be sure +--error ER_OPTION_PREVENTS_STATEMENT +delete t1,t2 from t1,t2 where t1.a=t2.a; + +# With temp tables updates should be accepted: + +create temporary table t3 (a int); + +create temporary table t4 (a int) select * from t3; + +insert into t3 values(1); + +insert into t4 select * from t3; + +--error ER_OPTION_PREVENTS_STATEMENT +create table t3 (a int); + +# a non-temp table updated: +--error ER_OPTION_PREVENTS_STATEMENT +update t1,t3 set t1.a=t3.a+1 where t1.a=t3.a; + +# no non-temp table updated (just swapped): +update t1,t3 set t3.a=t1.a+1 where t1.a=t3.a; + +update t4,t3 set t4.a=t3.a+1 where t4.a=t3.a; + +--error ER_OPTION_PREVENTS_STATEMENT +delete t1 from t1,t3 where t1.a=t3.a; + +delete t3 from t1,t3 where t1.a=t3.a; + +delete t4 from t3,t4 where t4.a=t3.a; + +# and even homonymous ones + +create temporary table t1 (a int); + +insert into t1 values(1); + +update t1,t3 set t1.a=t3.a+1 where t1.a=t3.a; + +delete t1 from t1,t3 where t1.a=t3.a; + +drop table t1; + +--error ER_OPTION_PREVENTS_STATEMENT +insert into t1 values(1); + +# +# MDEV-14056 DROP TEMPORARY TABLE IF EXISTS causes error 1290 with read_only option +# +drop temporary table if exists t1; + +# +# Bug#11733 COMMITs should not happen if read-only is set +# + +# LOCK TABLE ... WRITE / READ_ONLY +# - is an error in the same connection +# - is ok in a different connection + +connection default; +set global read_only=0; +lock table t1 write; + +connection con1; +lock table t2 write; + +connection default; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +set global read_only=1; +unlock tables ; +# The following call blocks until con1 releases the write lock. +# Blocking is expected. +--echo send set global read_only=1; +send set global read_only=1; + +connection con1; +select @@global.read_only; +unlock tables ; +let $wait_condition= SELECT @@global.read_only= 1; +--source include/wait_condition.inc +select @@global.read_only; + +connection default; +--echo reap; +reap; + +# LOCK TABLE ... READ / READ_ONLY +# - is an error in the same connection +# - is ok in a different connection + +connection default; +set global read_only=0; +lock table t1 read; + +connection con1; +lock table t2 read; + +connection default; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +set global read_only=1; +unlock tables ; + +# after unlock tables in current connection +# the next command must be executed successfully +set global read_only=1; +select @@global.read_only; + +connection con1; +select @@global.read_only; +unlock tables ; + +connection default; + +# pending transaction / READ_ONLY +# - is an error in the same connection +# - is ok in a different connection + +connection default; +set global read_only=0; +BEGIN; + +connection con1; +BEGIN; + +connection default; +--error ER_LOCK_OR_ACTIVE_TRANSACTION +set global read_only=1; +ROLLBACK; + +set global read_only=1; + +connection con1; +select @@global.read_only; +ROLLBACK; + +# Verify that FLUSH TABLES WITH READ LOCK do not block READ_ONLY +# - in the same SUPER connection +# - in another SUPER connection + +connection default; +set global read_only=0; +flush tables with read lock; +set global read_only=1; +unlock tables; + +connect (root2,localhost,root,,test); + +connection default; +set global read_only=0; +flush tables with read lock; + +connection root2; +set global read_only=1; + +connection default; +select @@global.read_only; +unlock tables; +disconnect root2; + +# Bug#22077 DROP TEMPORARY TABLE fails with wrong error if read_only is set +# +# check if DROP TEMPORARY on a non-existing temporary table returns the right +# error + +--error ER_BAD_TABLE_ERROR +drop temporary table ttt; + +# check if DROP TEMPORARY TABLE IF EXISTS produces a warning with read_only set +drop temporary table if exists ttt; + +# +# Cleanup +# +connection default; +set global read_only=0; +disconnect con1; +drop table t1,t2; +drop user test@localhost; + +--echo # +--echo # Bug#27440 read_only allows create and drop database +--echo # +set global read_only= 1; +--disable_warnings +drop database if exists mysqltest_db1; +drop database if exists mysqltest_db2; +--enable_warnings + +delete from mysql.user where User like 'mysqltest_%'; +delete from mysql.db where User like 'mysqltest_%'; +delete from mysql.tables_priv where User like 'mysqltest_%'; +delete from mysql.columns_priv where User like 'mysqltest_%'; +flush privileges; + +create user `mysqltest_u1`@`%`; +grant all on mysqltest_db2.* to `mysqltest_u1`@`%`; +create database mysqltest_db1; +grant all on mysqltest_db1.* to `mysqltest_u1`@`%`; +grant select on test.* to `mysqltest_u1`@`%`; +flush privileges; +connect (con_bug27440,127.0.0.1,mysqltest_u1,,test,$MASTER_MYPORT,); +connection con_bug27440; +--error ER_OPTION_PREVENTS_STATEMENT +create database mysqltest_db2; +show databases like '%mysqltest_db2%'; +--error ER_OPTION_PREVENTS_STATEMENT +drop database mysqltest_db1; +disconnect con_bug27440; +connection default; +delete from mysql.user where User like 'mysqltest_%'; +delete from mysql.db where User like 'mysqltest_%'; +delete from mysql.tables_priv where User like 'mysqltest_%'; +delete from mysql.columns_priv where User like 'mysqltest_%'; +flush privileges; +drop database mysqltest_db1; +set global read_only= @start_read_only; + +--echo # +--echo # MDEV-16987 - ALTER DATABASE possible in read-only mode +--echo # +CREATE USER user1@localhost; +GRANT ALTER ON test1.* TO user1@localhost; +CREATE DATABASE test1; +SET GLOBAL read_only=1; +change_user user1; +--error ER_OPTION_PREVENTS_STATEMENT +ALTER DATABASE test1 CHARACTER SET utf8; +change_user root; +SET GLOBAL read_only=0; +DROP DATABASE test1; +DROP USER user1@localhost; +USE test; + +--echo # End of 5.5 tests + +--echo # +--echo # WL#5968 Implement START TRANSACTION READ (WRITE|ONLY); +--echo # + +--echo # +--echo # Test interaction with read_only system variable. + +CREATE TABLE t1(a INT); +INSERT INTO t1 VALUES (1), (2); + +CREATE USER user1; +GRANT ALL on test.* to user1; +connect (con1, localhost, user1); +connection default; + +SET GLOBAL read_only= 1; + +--echo # All allowed with super privilege +START TRANSACTION; +COMMIT; + +START TRANSACTION READ ONLY; +COMMIT; + +START TRANSACTION READ WRITE; +COMMIT; + +--echo # We allow implicit RW transaction without super privilege +--echo # for compatibility reasons +connection con1; +START TRANSACTION; +--echo # Check that table updates are still disallowed. +--error ER_OPTION_PREVENTS_STATEMENT +INSERT INTO t1 VALUES (3); +--error ER_OPTION_PREVENTS_STATEMENT +UPDATE t1 SET a= 1; +--error ER_OPTION_PREVENTS_STATEMENT +DELETE FROM t1; +COMMIT; + +START TRANSACTION READ ONLY; +COMMIT; + +--echo # Explicit RW trans is not allowed without super privilege +--error ER_OPTION_PREVENTS_STATEMENT +START TRANSACTION READ WRITE; +COMMIT; +disconnect con1; +--source include/wait_until_disconnected.inc +connection default; +DROP USER user1; + +SET GLOBAL read_only= 0; +DROP TABLE t1; + +# Wait till all disconnects are completed +--source include/wait_until_count_sessions.inc + +--echo # End of 10.0 tests -- cgit v1.2.3