# # Testing of table locking # --disable_warnings drop table if exists t1,t2; --enable_warnings CREATE TABLE t1 ( `id` int(11) NOT NULL default '0', `id2` int(11) NOT NULL default '0', `id3` int(11) NOT NULL default '0', `dummy1` char(30) default NULL, PRIMARY KEY (`id`,`id2`), KEY `index_id3` (`id3`)) ENGINE=MyISAM; insert into t1 (id,id2) values (1,1),(1,2),(1,3); LOCK TABLE t1 WRITE; select dummy1,count(distinct id) from t1 group by dummy1; update t1 set id=-1 where id=1; LOCK TABLE t1 READ; --error 1099 update t1 set id=1 where id=1; --error 1100 create table t2 SELECT * from t1; create temporary table t2 SELECT * from t1; drop table if exists t2; unlock tables; create table t2 SELECT * from t1; LOCK TABLE t1 WRITE,t2 write; insert into t2 SELECT * from t1; update t1 set id=1 where id=-1; drop table t1,t2; # # Check bug with INSERT ... SELECT with lock tables # CREATE TABLE t1 ( index1 smallint(6) default NULL, nr smallint(6) default NULL, KEY index1(index1) ) ENGINE=MyISAM; CREATE TABLE t2 ( nr smallint(6) default NULL, name varchar(20) default NULL ) ENGINE=MyISAM; INSERT INTO t2 VALUES (1,'item1'); INSERT INTO t2 VALUES (2,'item2'); # problem begins here! lock tables t1 write, t2 read; insert into t1 select 1,nr from t2 where name='item1'; insert into t1 select 2,nr from t2 where name='item2'; unlock tables; check table t1; # Check error message lock tables t1 write; check table t2; --error 1100 insert into t1 select index1,nr from t1; unlock tables; lock tables t1 write, t1 as t1_alias read; insert into t1 select index1,nr from t1 as t1_alias; drop table t1,t2; # # BUG#5390 - problems with merge tables # Supplement test for the after-fix optimization # Check that a dropped table is correctly removed from a lock. create table t1 (c1 int); create table t2 (c1 int); create table t3 (c1 int); lock tables t1 write, t2 write, t3 write; # This removes one table after the other from the lock. drop table t2, t3, t1; # # Check that a lock merge works. create table t1 (c1 int); create table t2 (c1 int); create table t3 (c1 int); lock tables t1 write, t2 write, t3 write, t1 as t4 read; alter table t2 add column c2 int; drop table t1, t2, t3; # Bug7241 - Invalid response when DELETE .. USING and LOCK TABLES used. # create table t1 ( a int(11) not null auto_increment, primary key(a)); create table t2 ( a int(11) not null auto_increment, primary key(a)); lock tables t1 write, t2 read; delete from t1 using t1,t2 where t1.a=t2.a; delete t1 from t1,t2 where t1.a=t2.a; --error 1099 delete from t2 using t1,t2 where t1.a=t2.a; --error 1099 delete t2 from t1,t2 where t1.a=t2.a; drop table t1,t2; --echo End of 4.1 tests. # # Bug#18884 "lock table + global read lock = crash" # The bug is not repeatable, just add the test case. # --disable_warnings drop table if exists t1; --enable_warnings create table t1 (a int); lock table t1 write; --error ER_LOCK_OR_ACTIVE_TRANSACTION flush tables with read lock; unlock tables; drop table t1; # # Test LOCK TABLE on system tables. See bug#9953: CONVERT_TZ requires # mysql.time_zone_name to be locked. # --disable_warnings DROP TABLE IF EXISTS t1; --enable_warnings CREATE TABLE t1 (i INT); LOCK TABLES mysql.time_zone READ, mysql.proc READ, t1 READ; UNLOCK TABLES; LOCK TABLES mysql.time_zone READ, mysql.proc READ, t1 WRITE; UNLOCK TABLES; LOCK TABLES mysql.time_zone READ, mysql.proc READ; UNLOCK TABLES; LOCK TABLES mysql.time_zone WRITE, mysql.proc WRITE; UNLOCK TABLES; # If at least one system table is locked for WRITE, then all other # tables should be system tables locked also for WRITE. --error ER_WRONG_LOCK_OF_SYSTEM_TABLE LOCK TABLES mysql.time_zone READ, mysql.proc WRITE, t1 READ; --error ER_WRONG_LOCK_OF_SYSTEM_TABLE LOCK TABLES mysql.time_zone WRITE, mysql.proc WRITE, t1 READ; --error ER_WRONG_LOCK_OF_SYSTEM_TABLE LOCK TABLES mysql.time_zone WRITE, mysql.proc WRITE, t1 WRITE; --error ER_WRONG_LOCK_OF_SYSTEM_TABLE LOCK TABLES mysql.time_zone READ, mysql.proc WRITE; DROP TABLE t1; --echo --echo Bug#5719 impossible to lock VIEW --echo --echo Just covering existing behaviour with tests. --echo Consistency has not been found here. --echo --disable_warnings drop view if exists v_bug5719; drop table if exists t1, t2, t3; --enable_warnings create table t1 (a int); create temporary table t2 (a int); create table t3 (a int); create view v_bug5719 as select 1; lock table v_bug5719 write; --error ER_TABLE_NOT_LOCKED select * from t1; --echo --echo Allowed to select from a temporary talbe under LOCK TABLES --echo select * from t2; --error ER_TABLE_NOT_LOCKED select * from t3; select * from v_bug5719; drop view v_bug5719; --echo --echo sic: did not left LOCK TABLES mode automatically --echo --error ER_TABLE_NOT_LOCKED select * from t1; unlock tables; create view v_bug5719 as select * from t1; lock tables v_bug5719 write; select * from v_bug5719; --echo --echo Allowed to use an underlying table under LOCK TABLES <view> --echo select * from t1; --echo --echo Allowed to select from a temporary table under LOCK TABLES --echo select * from t2; --error ER_TABLE_NOT_LOCKED select * from t3; drop table t1; --echo --echo sic: left LOCK TABLES mode --echo select * from t3; --error ER_VIEW_INVALID select * from v_bug5719; unlock tables; drop view v_bug5719; --echo --echo When limitation to use temporary tables in views is removed, please --echo add a test that shows what happens under LOCK TABLES when a view --echo references a temporary table, is locked, and the underlying table --echo is dropped. --echo --error ER_VIEW_SELECT_TMPTABLE create view v_bug5719 as select * from t2; --echo --echo Cleanup. --echo drop table t2, t3; --echo # --echo # Bug#39843 DELETE requires write access to table in subquery in where clause --echo # --disable_warnings DROP TABLE IF EXISTS t1,t2; --enable_warnings CREATE TABLE t1 ( table1_rowid SMALLINT NOT NULL ); CREATE TABLE t2 ( table2_rowid SMALLINT NOT NULL ); INSERT INTO t1 VALUES (1); INSERT INTO t2 VALUES (1); LOCK TABLES t1 WRITE, t2 READ; --echo # Sub-select should not try to aquire a write lock. DELETE FROM t1 WHERE EXISTS ( SELECT 'x' FROM t2 WHERE t1.table1_rowid = t2.table2_rowid ) ; --echo # While implementing the patch we didn't break old behavior; --echo # The following sub-select should still requires a write lock: --error ER_TABLE_NOT_LOCKED_FOR_WRITE SELECT * FROM t1 WHERE 1 IN (SELECT * FROM t2 FOR UPDATE); UNLOCK TABLES; DROP TABLE t1,t2; --echo End of 5.1 tests.