# 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:

set @orig_sql_mode= @@sql_mode;
set sql_mode= (select replace(@@sql_mode,'NO_AUTO_CREATE_USER',''));
grant CREATE, SELECT, DROP on *.* to test@localhost;

--echo connect (con1,localhost,test,,test);
connect (con1,localhost,test,,test);

--echo connection default;
connection default;

set global read_only=0;

--echo connection con1;
connection con1;

create table t1 (a int);

insert into t1 values(1);

create table t2 select * from t1;

--echo connection default;
connection default;

set global read_only=1;

# We check that SUPER can:

create table t3 (a int);
drop table t3;

--echo connection con1;
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);

# 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;

# 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);

#
# 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

--echo connection default;
connection default;
set global read_only=0;
lock table t1 write;

--echo connection con1;
connection con1;
lock table t2 write;

--echo connection default;
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;

--echo connection con1;
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;

--echo connection default;
connection default;
--echo reap;
reap;

# LOCK TABLE ... READ / READ_ONLY
# - is an error in the same connection
# - is ok in a different connection

--echo connection default;
connection default;
set global read_only=0;
lock table t1 read;

--echo connection con1;
connection con1;
lock table t2 read;

--echo connection default;
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;

--echo connection con1;
connection con1;
select @@global.read_only;
unlock tables ;

--echo connection default;
connection default;

# pending transaction / READ_ONLY
# - is an error in the same connection
# - is ok in a different connection

--echo connection default;
connection default;
set global read_only=0;
BEGIN;

--echo connection con1;
connection con1;
BEGIN;

--echo connection default;
connection default;
--error ER_LOCK_OR_ACTIVE_TRANSACTION
set global read_only=1;
ROLLBACK;

set global read_only=1;

--echo connection con1;
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

--echo connection default;
connection default;
set global read_only=0;
flush tables with read lock;
set global read_only=1;
unlock tables;

--echo connect (root2,localhost,root,,test);
connect (root2,localhost,root,,test);

--echo connection default;
connection default;
set global read_only=0;
flush tables with read lock;

--echo connection root2;
connection root2;
set global read_only=1;

--echo connection default;
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
#
--echo connection default;
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;

grant all on mysqltest_db2.* to `mysqltest_u1`@`%`;
create database mysqltest_db1;
grant all on mysqltest_db1.* to `mysqltest_u1`@`%`;
flush privileges;
--echo connect (con_bug27440,127.0.0.1,mysqltest_u1,,test,MASTER_MYPORT,);
connect (con_bug27440,127.0.0.1,mysqltest_u1,,test,$MASTER_MYPORT,);
--echo connection con_bug27440;
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;
--echo disconnect con_bug27440;
disconnect con_bug27440;
--echo connection default;
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 # WL#5968 Implement START TRANSACTION READ (WRITE|ONLY);
--echo #

--echo #
--echo # Test interaction with read_only system variable.

--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings

CREATE TABLE t1(a INT);
INSERT INTO t1 VALUES (1), (2);

CREATE USER 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;

set sql_mode= @orig_sql_mode;


--echo #
--echo # BUG#28438114: SET READ_ONLY=1 SOMETIMES DOESN'T BLOCK CONCURRENT DDL.
--echo #

--echo # Prepare the sql file for testing DDLS.

SET @save_read_only= @@global.read_only;

CREATE USER test@localhost;
GRANT CREATE, DROP, INSERT, CREATE TABLESPACE, ALTER ROUTINE, RELOAD ON *.* TO test@localhost;

--echo connect (con1,localhost,test,,test);
connect (con1,localhost,test,,test);

--echo connect (con2,localhost,root,,test);
connect (con2,localhost,root,,test);

--echo # Test CREATE DATABASE statement.
let $query= CREATE DATABASE new;
--source include/read_only_bug28438114.inc
--echo # Test ALTER DATABASE statement
--let query= ALTER DATABASE test CHARACTER SET utf8;
--source include/read_only_bug28438114.inc
--echo # Test DROP DATABASE statement.
CREATE DATABASE new;
let $query= DROP DATABASE new;
--source include/read_only_bug28438114.inc
DROP DATABASE new;

--echo # Test CREATE TABLE statement.
let $query= CREATE TABLE t1(fld1 INT);
--source include/read_only_bug28438114.inc
--echo # Test ALTER TABLE statement.
CREATE TABLE t1(fld1 INT);
let $query= ALTER TABLE t1 ADD fld2 INT;
--source include/read_only_bug28438114.inc
--echo # Test DROP TABLE statement.
let $query= DROP TABLE t1;
--source include/read_only_bug28438114.inc
--echo # Test CREATE INDEX statement.
let $query= CREATE INDEX idx1 ON t1 (fld1) USING BTREE;
--source include/read_only_bug28438114.inc
--echo # Test DROP INDEX statement.
CREATE INDEX idx1 ON t1 (fld1) USING BTREE;
let $query= DROP INDEX idx1 ON t1;
--source include/read_only_bug28438114.inc
DROP INDEX idx1 ON t1;

--echo # Test CREATE TRIGGER statement.
let $query= CREATE TRIGGER trg1 BEFORE INSERT ON t1
FOR EACH ROW BEGIN END;
--source include/read_only_bug28438114.inc
--echo # Test DROP TRIGGER statement.
CREATE TRIGGER trg1 BEFORE INSERT ON t1
FOR EACH ROW BEGIN END;
let $query= DROP TRIGGER trg1;
--source include/read_only_bug28438114.inc
DROP TRIGGER trg1;

--echo # Test TRUNCATE TABLE statement.
let $query= TRUNCATE TABLE t1;
--source include/read_only_bug28438114.inc

--echo # Test CREATE VIEW statement.
let $query= CREATE VIEW v1 AS SELECT * FROM t1;
--source include/read_only_bug28438114.inc
--echo # Test ALTER VIEW statement.
CREATE VIEW v1 AS SELECT * FROM t1;
let $query= ALTER VIEW v1 AS SELECT 1;
--source include/read_only_bug28438114.inc
--echo # Test DROP VIEW statement.
let $query= DROP VIEW v1;
--source include/read_only_bug28438114.inc
DROP VIEW v1;
DROP TABLE t1;

--echo # Test CREATE TABLESPACE statement
let $query= CREATE TABLESPACE ts1
            ADD DATAFILE 'ts1.ibd' ENGINE=INNODB;
--source include/read_only_bug28438114.inc
CREATE TABLESPACE ts1 ADD DATAFILE 'ts1.ibd' ENGINE=INNODB;
--echo # Test DROP TABLESPACE statement
let $query= DROP TABLESPACE ts1;
--source include/read_only_bug28438114.inc
DROP TABLESPACE ts1;

--echo # Test CREATE FUNCTION statement.
let $query= CREATE FUNCTION f1() RETURNS INT RETURN 5;
--source include/read_only_bug28438114.inc
--echo # Test ALTER FUNCTION statement.
CREATE FUNCTION f1() RETURNS INT RETURN 5;
let $query= ALTER FUNCTION f1 COMMENT 'test';
--source include/read_only_bug28438114.inc
--echo # Test DROP FUNCTION statement.
let $query= DROP FUNCTION f1;
--source include/read_only_bug28438114.inc
DROP FUNCTION f1;

--echo # Test CREATE PROCEDURE statement
let $query= CREATE PROCEDURE p1() select 1;
--source include/read_only_bug28438114.inc
--echo # Test ALTER PROCEDURE statement.
CREATE PROCEDURE p1() select 1;
let $query= ALTER PROCEDURE p1 comment 'test';
--source include/read_only_bug28438114.inc
--echo # Test DROP PROCEDURE statement.
let $query= DROP PROCEDURE p1;
--source include/read_only_bug28438114.inc
DROP PROCEDURE p1;

--echo # Test CREATE EVENT statement
let $query= CREATE EVENT event1 ON SCHEDULE
EVERY 10 HOUR DO SELECT 1;
--source include/read_only_bug28438114.inc
--echo # Test ALTER EVENT statement
CREATE EVENT event1 ON SCHEDULE EVERY 10 HOUR DO SELECT 1;
let $query= ALTER EVENT event1 DISABLE;
--source include/read_only_bug28438114.inc
--echo # Test DROP EVENT statement.
let $query= DROP EVENT event1;
--source include/read_only_bug28438114.inc
DROP EVENT event1;

--echo # Test case added for coverage.
CREATE TABLE t1(fld1 INT);
connection con1;
--echo # FTWRL and FLUSH TABLE..FOR EXPORT is not blocked
--echo # in read_only/super_read_only mode.
FLUSH TABLES t1 WITH READ LOCK;
UNLOCK TABLES;
FLUSH TABLES t1 FOR EXPORT;
UNLOCK TABLES;

--echo # Clean up.
connection default;
DROP TABLE t1;
DROP USER test@localhost;
SET GLOBAL read_only= @save_read_only;
--disconnect con1
--disconnect con2

# Wait till all disconnects are completed
--source include/wait_until_count_sessions.inc