~drizzle-trunk/drizzle/development

# Test of the READ_ONLY global variable:

# check that it blocks updates unless they are only on temporary tables.

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

grant CREATE, SELECT, DROP on *.* to test@localhost;

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

connection default;

set global read_only=0;

connection con1;

create table t1 (a int);

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 1290

create table t3 (a int);

--error 1290

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 1290

update t1 set a=1 where 1=0;

# multi-update is special (see sql_parse.cc) so we test it

--error 1290

update t1,t2 set t1.a=t2.a+1 where t1.a=t2.a;

# check multi-delete to be sure

--error 1290

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 1290

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 1290

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 1290

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

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.

send set global read_only=1;

connection con1;

--sleep 1

select @@global.read_only;

unlock tables ;

--sleep 1

select @@global.read_only;

connection default;

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 ;

# The following call blocks until con1 releases the read lock.

# Blocking is a limitation, and could be improved.

send set global read_only=1;

connection con1;

--sleep 1

select @@global.read_only;

unlock tables ;

--sleep 1

select @@global.read_only;

connection default;

reap;

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

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

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;

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=0;