mirror of
https://github.com/MariaDB/server.git
synced 2025-08-14 22:41:34 +02:00
e3d9369774
let's always disconnect a user connection before dropping the said user. MariaDB is traditionally very tolerant to active connections of the dropped user, which isn't the case for most other databases. Let's avoid unintentionally spreading incompatible behavior and disconnect before drop. Except in cases when the test specifically tests such a behavior.
456 lines
10 KiB
Text
456 lines
10 KiB
Text
# 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;
|
|
|
|
# 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_timeout=10;
|
|
let $wait_condition= SELECT @@global.read_only="on";
|
|
--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;
|
|
connection default;
|
|
DROP USER user1;
|
|
|
|
SET GLOBAL read_only= 0;
|
|
DROP TABLE t1;
|
|
|
|
--echo # End of 10.0 tests
|
|
|
|
--echo #
|
|
--echo # MDEV-36425 Extend read_only to also block share locks and super user
|
|
--echo #
|
|
|
|
|
|
CREATE USER user1;
|
|
GRANT ALL on test.* to user1;
|
|
connect (con1, localhost, user1);
|
|
connection default;
|
|
|
|
CREATE TABLE t1 (a int primary key auto_increment);
|
|
insert into t1 values (1),(2);
|
|
show variables like "read_only";
|
|
|
|
SET GLOBAL read_only=1;
|
|
show variables like "read_only";
|
|
--echo # admin
|
|
insert into t1 values ();
|
|
lock tables t1 read;
|
|
unlock tables;
|
|
lock tables t1 write;
|
|
unlock tables;
|
|
begin;
|
|
select count(*) from t1 LOCK IN SHARE MODE;
|
|
select count(*) from t1,(select a from t1 LOCK IN SHARE MODE) as t2;
|
|
commit;
|
|
|
|
--echo # user
|
|
connection con1;
|
|
--error ER_OPTION_PREVENTS_STATEMENT
|
|
insert into t1 values ();
|
|
lock tables t1 read;
|
|
unlock tables;
|
|
--error ER_OPTION_PREVENTS_STATEMENT
|
|
lock tables t1 write;
|
|
unlock tables;
|
|
begin;
|
|
select count(*) from t1 LOCK IN SHARE MODE;
|
|
select count(*) from t1,(select a from t1 LOCK IN SHARE MODE) as t2;
|
|
commit;
|
|
|
|
connection default;
|
|
SET GLOBAL read_only=2;
|
|
show variables like "read_only";
|
|
|
|
--echo # admin
|
|
insert into t1 values ();
|
|
lock tables t1 read;
|
|
unlock tables;
|
|
lock tables t1 write;
|
|
unlock tables;
|
|
begin;
|
|
select count(*) from t1 LOCK IN SHARE MODE;
|
|
select count(*) from t1,(select a from t1 LOCK IN SHARE MODE) as t2;
|
|
commit;
|
|
|
|
--echo #user
|
|
connection con1;
|
|
--error ER_OPTION_PREVENTS_STATEMENT
|
|
insert into t1 values ();
|
|
--error ER_OPTION_PREVENTS_STATEMENT
|
|
lock tables t1 read;
|
|
unlock tables;
|
|
--error ER_OPTION_PREVENTS_STATEMENT
|
|
lock tables t1 write;
|
|
unlock tables;
|
|
begin;
|
|
--error ER_OPTION_PREVENTS_STATEMENT
|
|
select count(*) from t1 LOCK IN SHARE MODE;
|
|
--error ER_OPTION_PREVENTS_STATEMENT
|
|
select count(*) from t1,(select a from t1 LOCK IN SHARE MODE) as t2;
|
|
commit;
|
|
|
|
connection default;
|
|
SET GLOBAL read_only=3;
|
|
show variables like "read_only";
|
|
|
|
--echo # admin
|
|
--error ER_OPTION_PREVENTS_STATEMENT
|
|
insert into t1 values ();
|
|
--error ER_OPTION_PREVENTS_STATEMENT
|
|
lock tables t1 read;
|
|
unlock tables;
|
|
--error ER_OPTION_PREVENTS_STATEMENT
|
|
lock tables t1 write;
|
|
unlock tables;
|
|
begin;
|
|
--error ER_OPTION_PREVENTS_STATEMENT
|
|
select count(*) from t1 LOCK IN SHARE MODE;
|
|
--error ER_OPTION_PREVENTS_STATEMENT
|
|
select count(*) from t1,(select a from t1 LOCK IN SHARE MODE) as t2;
|
|
commit;
|
|
|
|
disconnect con1;
|
|
SET GLOBAL read_only=0;
|
|
select count(*) from t1;
|
|
drop table t1;
|
|
drop user user1;
|
|
|
|
--echo # End of 11.8 tests
|