2 # Testing of table locking
6 drop table if exists t1,t2;
8 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;
9 insert into t1 (id,id2) values (1,1),(1,2),(1,3);
11 select dummy1,count(distinct id) from t1 group by dummy1;
12 update t1 set id=-1 where id=1;
15 update t1 set id=1 where id=1;
17 create table t2 SELECT * from t1;
18 create temporary table t2 SELECT * from t1;
19 drop table if exists t2;
21 create table t2 SELECT * from t1;
22 LOCK TABLE t1 WRITE,t2 write;
23 insert into t2 SELECT * from t1;
24 update t1 set id=1 where id=-1;
29 # Check bug with INSERT ... SELECT with lock tables
33 index1 smallint(6) default NULL,
34 nr smallint(6) default NULL,
39 nr smallint(6) default NULL,
40 name varchar(20) default NULL
43 INSERT INTO t2 VALUES (1,'item1');
44 INSERT INTO t2 VALUES (2,'item2');
46 # problem begins here!
47 lock tables t1 write, t2 read;
48 insert into t1 select 1,nr from t2 where name='item1';
49 insert into t1 select 2,nr from t2 where name='item2';
57 insert into t1 select index1,nr from t1;
59 lock tables t1 write, t1 as t1_alias read;
60 insert into t1 select index1,nr from t1 as t1_alias;
64 # BUG#5390 - problems with merge tables
65 # Supplement test for the after-fix optimization
66 # Check that a dropped table is correctly removed from a lock.
67 create table t1 (c1 int);
68 create table t2 (c1 int);
69 create table t3 (c1 int);
70 lock tables t1 write, t2 write, t3 write;
71 # This removes one table after the other from the lock.
72 drop table t2, t3, t1;
74 # Check that a lock merge works.
75 create table t1 (c1 int);
76 create table t2 (c1 int);
77 create table t3 (c1 int);
78 lock tables t1 write, t2 write, t3 write, t1 as t4 read;
79 alter table t2 add column c2 int;
80 drop table t1, t2, t3;
82 # Bug7241 - Invalid response when DELETE .. USING and LOCK TABLES used.
84 create table t1 ( a int(11) not null auto_increment, primary key(a));
85 create table t2 ( a int(11) not null auto_increment, primary key(a));
86 lock tables t1 write, t2 read;
87 delete from t1 using t1,t2 where t1.a=t2.a;
88 delete t1 from t1,t2 where t1.a=t2.a;
90 delete from t2 using t1,t2 where t1.a=t2.a;
92 delete t2 from t1,t2 where t1.a=t2.a;
95 --echo End of 4.1 tests.
99 # Bug#18884 "lock table + global read lock = crash"
100 # The bug is not repeatable, just add the test case.
103 drop table if exists t1;
105 create table t1 (a int);
107 --error ER_LOCK_OR_ACTIVE_TRANSACTION
108 flush tables with read lock;
114 # Test LOCK TABLE on system tables. See bug#9953: CONVERT_TZ requires
115 # mysql.time_zone_name to be locked.
118 DROP TABLE IF EXISTS t1;
121 CREATE TABLE t1 (i INT);
123 LOCK TABLES mysql.time_zone READ, mysql.proc READ, t1 READ;
126 LOCK TABLES mysql.time_zone READ, mysql.proc READ, t1 WRITE;
129 LOCK TABLES mysql.time_zone READ, mysql.proc READ;
132 LOCK TABLES mysql.time_zone WRITE, mysql.proc WRITE;
135 # If at least one system table is locked for WRITE, then all other
136 # tables should be system tables locked also for WRITE.
137 --error ER_WRONG_LOCK_OF_SYSTEM_TABLE
138 LOCK TABLES mysql.time_zone READ, mysql.proc WRITE, t1 READ;
140 --error ER_WRONG_LOCK_OF_SYSTEM_TABLE
141 LOCK TABLES mysql.time_zone WRITE, mysql.proc WRITE, t1 READ;
143 --error ER_WRONG_LOCK_OF_SYSTEM_TABLE
144 LOCK TABLES mysql.time_zone WRITE, mysql.proc WRITE, t1 WRITE;
146 --error ER_WRONG_LOCK_OF_SYSTEM_TABLE
147 LOCK TABLES mysql.time_zone READ, mysql.proc WRITE;
152 --echo Bug#5719 impossible to lock VIEW
154 --echo Just covering existing behaviour with tests.
155 --echo Consistency has not been found here.
158 drop view if exists v_bug5719;
159 drop table if exists t1, t2, t3;
161 create table t1 (a int);
162 create temporary table t2 (a int);
163 create table t3 (a int);
164 create view v_bug5719 as select 1;
165 lock table v_bug5719 write;
166 --error ER_TABLE_NOT_LOCKED
169 --echo Allowed to select from a temporary talbe under LOCK TABLES
172 --error ER_TABLE_NOT_LOCKED
174 select * from v_bug5719;
177 --echo sic: did not left LOCK TABLES mode automatically
179 --error ER_TABLE_NOT_LOCKED
182 create view v_bug5719 as select * from t1;
183 lock tables v_bug5719 write;
184 select * from v_bug5719;
186 --echo Allowed to use an underlying table under LOCK TABLES <view>
190 --echo Allowed to select from a temporary table under LOCK TABLES
193 --error ER_TABLE_NOT_LOCKED
197 --echo sic: left LOCK TABLES mode
200 --error ER_VIEW_INVALID
201 select * from v_bug5719;
205 --echo When limitation to use temporary tables in views is removed, please
206 --echo add a test that shows what happens under LOCK TABLES when a view
207 --echo references a temporary table, is locked, and the underlying table
210 --error ER_VIEW_SELECT_TMPTABLE
211 create view v_bug5719 as select * from t2;
218 --echo # Bug#39843 DELETE requires write access to table in subquery in where clause
221 DROP TABLE IF EXISTS t1,t2;
224 table1_rowid SMALLINT NOT NULL
227 table2_rowid SMALLINT NOT NULL
229 INSERT INTO t1 VALUES (1);
230 INSERT INTO t2 VALUES (1);
231 LOCK TABLES t1 WRITE, t2 READ;
232 --echo # Sub-select should not try to aquire a write lock.
238 WHERE t1.table1_rowid = t2.table2_rowid
240 --echo # While implementing the patch we didn't break old behavior;
241 --echo # The following sub-select should still requires a write lock:
242 --error ER_TABLE_NOT_LOCKED_FOR_WRITE
243 SELECT * FROM t1 WHERE 1 IN (SELECT * FROM t2 FOR UPDATE);
247 --echo End of 5.1 tests.