mirror of
https://github.com/MariaDB/server.git
synced 2025-10-08 16:59:13 +02:00
eeb00ceffd
Follow-up patch with adjustments of test files and updates of result files for tests. Some of tests were rewritten slighlty. Everywhere where common pattern used: ----- CREATE USER userA; --connect con1 ... userA ... <sql statements...> --disconnect con1 DROP USER userA; ----- the DROP USER statement has been eclosed into the directive --disable_warnings --enable_warnings This change is caused by the race conddition between --disconnect and DROP USER since a number of currently running sessions established on behalf the user being dropped is counted by holding the rw_lock THD_list_iterator::lock that is not acquired on execution the DROP USER statement but the lock is taken as the last step on handling disconnection (when the client is already sending the next statement). Therefore, for the cases where the command --disconnect precedes the DROP USER statement we hide the possible warnings about presence of active sessions for the user being deleted to make tests deterministic.
342 lines
11 KiB
Text
342 lines
11 KiB
Text
set @start_read_only= @@global.read_only;
|
|
DROP TABLE IF EXISTS t1,t2,t3;
|
|
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;
|
|
create table t3 (a int);
|
|
drop table t3;
|
|
connection con1;
|
|
select @@global.read_only;
|
|
@@global.read_only
|
|
ON
|
|
create table t3 (a int);
|
|
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
|
|
insert into t1 values(1);
|
|
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
|
|
drop trigger trg1;
|
|
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
|
|
update t1 set a=1 where 1=0;
|
|
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
|
|
update t1,t2 set t1.a=t2.a+1 where t1.a=t2.a;
|
|
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
|
|
delete t1,t2 from t1,t2 where t1.a=t2.a;
|
|
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
|
|
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;
|
|
create table t3 (a int);
|
|
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
|
|
update t1,t3 set t1.a=t3.a+1 where t1.a=t3.a;
|
|
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
|
|
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;
|
|
delete t1 from t1,t3 where t1.a=t3.a;
|
|
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
|
|
delete t3 from t1,t3 where t1.a=t3.a;
|
|
delete t4 from t3,t4 where t4.a=t3.a;
|
|
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;
|
|
insert into t1 values(1);
|
|
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
|
|
drop temporary table if exists t1;
|
|
Warnings:
|
|
Note 1051 Unknown table 'test.t1'
|
|
connection default;
|
|
set global read_only=0;
|
|
lock table t1 write;
|
|
connection con1;
|
|
lock table t2 write;
|
|
connection default;
|
|
set global read_only=1;
|
|
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
|
|
unlock tables ;
|
|
send set global read_only=1;
|
|
set global read_only=1;
|
|
connection con1;
|
|
select @@global.read_only;
|
|
@@global.read_only
|
|
OFF
|
|
unlock tables ;
|
|
select @@global.read_only;
|
|
@@global.read_only
|
|
ON
|
|
connection default;
|
|
reap;
|
|
connection default;
|
|
set global read_only=0;
|
|
lock table t1 read;
|
|
connection con1;
|
|
lock table t2 read;
|
|
connection default;
|
|
set global read_only=1;
|
|
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
|
|
unlock tables ;
|
|
set global read_only=1;
|
|
select @@global.read_only;
|
|
@@global.read_only
|
|
ON
|
|
connection con1;
|
|
select @@global.read_only;
|
|
@@global.read_only
|
|
ON
|
|
unlock tables ;
|
|
connection default;
|
|
connection default;
|
|
set global read_only=0;
|
|
BEGIN;
|
|
connection con1;
|
|
BEGIN;
|
|
connection default;
|
|
set global read_only=1;
|
|
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
|
|
ROLLBACK;
|
|
set global read_only=1;
|
|
connection con1;
|
|
select @@global.read_only;
|
|
@@global.read_only
|
|
ON
|
|
ROLLBACK;
|
|
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;
|
|
@@global.read_only
|
|
ON
|
|
unlock tables;
|
|
disconnect root2;
|
|
drop temporary table ttt;
|
|
ERROR 42S02: Unknown table 'test.ttt'
|
|
drop temporary table if exists ttt;
|
|
Warnings:
|
|
Note 1051 Unknown table 'test.ttt'
|
|
connection default;
|
|
set global read_only=0;
|
|
disconnect con1;
|
|
drop table t1,t2;
|
|
drop user test@localhost;
|
|
#
|
|
# Bug#27440 read_only allows create and drop database
|
|
#
|
|
set global read_only= 1;
|
|
drop database if exists mysqltest_db1;
|
|
drop database if exists mysqltest_db2;
|
|
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;
|
|
create database mysqltest_db2;
|
|
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
|
|
show databases like '%mysqltest_db2%';
|
|
Database (%mysqltest_db2%)
|
|
drop database mysqltest_db1;
|
|
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
|
|
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;
|
|
#
|
|
# MDEV-16987 - ALTER DATABASE possible in read-only mode
|
|
#
|
|
CREATE USER user1@localhost;
|
|
GRANT ALTER ON test1.* TO user1@localhost;
|
|
CREATE DATABASE test1;
|
|
SET GLOBAL read_only=1;
|
|
ALTER DATABASE test1 CHARACTER SET utf8;
|
|
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
|
|
SET GLOBAL read_only=0;
|
|
DROP DATABASE test1;
|
|
DROP USER user1@localhost;
|
|
USE test;
|
|
# End of 5.5 tests
|
|
#
|
|
# WL#5968 Implement START TRANSACTION READ (WRITE|ONLY);
|
|
#
|
|
#
|
|
# 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;
|
|
# All allowed with super privilege
|
|
START TRANSACTION;
|
|
COMMIT;
|
|
START TRANSACTION READ ONLY;
|
|
COMMIT;
|
|
START TRANSACTION READ WRITE;
|
|
COMMIT;
|
|
# We allow implicit RW transaction without super privilege
|
|
# for compatibility reasons
|
|
connection con1;
|
|
START TRANSACTION;
|
|
# Check that table updates are still disallowed.
|
|
INSERT INTO t1 VALUES (3);
|
|
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
|
|
UPDATE t1 SET a= 1;
|
|
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
|
|
DELETE FROM t1;
|
|
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
|
|
COMMIT;
|
|
START TRANSACTION READ ONLY;
|
|
COMMIT;
|
|
# Explicit RW trans is not allowed without super privilege
|
|
START TRANSACTION READ WRITE;
|
|
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
|
|
COMMIT;
|
|
disconnect con1;
|
|
connection default;
|
|
DROP USER user1;
|
|
SET GLOBAL read_only= 0;
|
|
DROP TABLE t1;
|
|
# End of 10.0 tests
|
|
#
|
|
# MDEV-36425 Extend read_only to also block share locks and super user
|
|
#
|
|
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";
|
|
Variable_name Value
|
|
read_only OFF
|
|
SET GLOBAL read_only=1;
|
|
show variables like "read_only";
|
|
Variable_name Value
|
|
read_only ON
|
|
# 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;
|
|
count(*)
|
|
3
|
|
select count(*) from t1,(select a from t1 LOCK IN SHARE MODE) as t2;
|
|
count(*)
|
|
9
|
|
commit;
|
|
# user
|
|
connection con1;
|
|
insert into t1 values ();
|
|
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
|
|
lock tables t1 read;
|
|
unlock tables;
|
|
lock tables t1 write;
|
|
ERROR HY000: The MariaDB server is running with the --read-only=ON option so it cannot execute this statement
|
|
unlock tables;
|
|
begin;
|
|
select count(*) from t1 LOCK IN SHARE MODE;
|
|
count(*)
|
|
3
|
|
select count(*) from t1,(select a from t1 LOCK IN SHARE MODE) as t2;
|
|
count(*)
|
|
9
|
|
commit;
|
|
connection default;
|
|
SET GLOBAL read_only=2;
|
|
show variables like "read_only";
|
|
Variable_name Value
|
|
read_only NO_LOCK
|
|
# 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;
|
|
count(*)
|
|
4
|
|
select count(*) from t1,(select a from t1 LOCK IN SHARE MODE) as t2;
|
|
count(*)
|
|
16
|
|
commit;
|
|
#user
|
|
connection con1;
|
|
insert into t1 values ();
|
|
ERROR HY000: The MariaDB server is running with the --read-only=NO_LOCK option so it cannot execute this statement
|
|
lock tables t1 read;
|
|
ERROR HY000: The MariaDB server is running with the --read-only=NO_LOCK option so it cannot execute this statement
|
|
unlock tables;
|
|
lock tables t1 write;
|
|
ERROR HY000: The MariaDB server is running with the --read-only=NO_LOCK option so it cannot execute this statement
|
|
unlock tables;
|
|
begin;
|
|
select count(*) from t1 LOCK IN SHARE MODE;
|
|
ERROR HY000: The MariaDB server is running with the --read-only=NO_LOCK option so it cannot execute this statement
|
|
select count(*) from t1,(select a from t1 LOCK IN SHARE MODE) as t2;
|
|
ERROR HY000: The MariaDB server is running with the --read-only=NO_LOCK option so it cannot execute this statement
|
|
commit;
|
|
connection default;
|
|
SET GLOBAL read_only=3;
|
|
show variables like "read_only";
|
|
Variable_name Value
|
|
read_only NO_LOCK_NO_ADMIN
|
|
# admin
|
|
insert into t1 values ();
|
|
ERROR HY000: The MariaDB server is running with the --read-only=NO_LOCK_NO_ADMIN option so it cannot execute this statement
|
|
lock tables t1 read;
|
|
ERROR HY000: The MariaDB server is running with the --read-only=NO_LOCK_NO_ADMIN option so it cannot execute this statement
|
|
unlock tables;
|
|
lock tables t1 write;
|
|
ERROR HY000: The MariaDB server is running with the --read-only=NO_LOCK_NO_ADMIN option so it cannot execute this statement
|
|
unlock tables;
|
|
begin;
|
|
select count(*) from t1 LOCK IN SHARE MODE;
|
|
ERROR HY000: The MariaDB server is running with the --read-only=NO_LOCK_NO_ADMIN option so it cannot execute this statement
|
|
select count(*) from t1,(select a from t1 LOCK IN SHARE MODE) as t2;
|
|
ERROR HY000: The MariaDB server is running with the --read-only=NO_LOCK_NO_ADMIN option so it cannot execute this statement
|
|
commit;
|
|
SET GLOBAL read_only=0;
|
|
select count(*) from t1;
|
|
count(*)
|
|
4
|
|
drop table t1;
|
|
drop user user1;
|
|
Warnings:
|
|
Note 4226 Dropped users ['user1'@'%'] have active connections. Use KILL CONNECTION if they should not be used anymore.
|
|
disconnect con1;
|
|
# End of 11.8 tests
|