1 files changed, 600 insertions, 0 deletions
diff --git a/mysql-test/main/lock.test b/mysql-test/main/lock.test
new file mode 100644
index 00000000000..2e164de9b93
--- /dev/null
+++ b/mysql-test/main/lock.test
@@ -0,0 +1,600 @@
+#
+# Testing of table locking
+#
+
+--source include/have_partition.inc
+
+# Save the initial number of concurrent sessions.
+--source include/count_sessions.inc
+
+--disable_warnings
+drop table if exists t1,t2,t3;
+--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;
+--error ER_TABLE_NOT_LOCKED
+drop table t1,t2;
+unlock tables;
+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;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+drop table t1,t2;
+unlock tables;
+drop table t2,t1;
+
+--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;
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+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 or replace 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;
+--echo Dropping of implicitly locked table is disallowed.
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+drop table t1;
+unlock tables;
+--echo Now let us also lock table explicitly and drop it.
+lock tables t1 write, v_bug5719 write;
+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.
+
+--echo #
+--echo # Ensure that FLUSH TABLES doesn't substitute a base locked table
+--echo # with a temporary one.
+--echo #
+
+--disable_warnings
+drop table if exists t1, t2;
+--enable_warnings
+create table t1 (a int);
+create table t2 (a int);
+lock table t1 write, t2 write;
+create temporary table t1 (a int);
+flush table t1;
+drop temporary table t1;
+select * from t1;
+unlock tables;
+drop table t1, t2;
+
+--echo #
+--echo # Ensure that REPAIR .. USE_FRM works under LOCK TABLES.
+--echo #
+
+--disable_warnings
+drop table if exists t1, t2;
+--enable_warnings
+create table t1 (a int);
+create table t2 (a int);
+lock table t1 write, t2 write;
+repair table t1 use_frm;
+repair table t1 use_frm;
+select * from t1;
+select * from t2;
+repair table t2 use_frm;
+repair table t2 use_frm;
+select * from t1;
+unlock tables;
+drop table t1, t2;
+
+--echo #
+--echo # Ensure that mi_copy_status is called for two instances
+--echo # of the same table when it is reopened after a flush.
+--echo # 
+--disable_warnings
+drop table if exists t1;
+drop view if exists v1;
+--enable_warnings
+create table t1 (c1 int);
+create view v1 as select * from t1;
+lock tables t1 write, v1 write;
+flush table t1;
+insert into t1 values (33);
+flush table t1;
+select * from t1;
+unlock tables;
+drop table t1;
+drop view v1;
+
+--echo #
+--echo # WL#4284: Transactional DDL locking
+--echo #
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (a int);
+connect(con1,localhost,root,,);
+set autocommit= 0;
+insert into t1 values (1);
+lock table t1 write;
+--echo # Ensure that metadata locks will be released if there is an open
+--echo # transaction (autocommit=off) in conjunction with lock tables.
+disconnect con1;
+connection default;
+drop table t1;
+
+--echo # Same problem but now for BEGIN
+
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (a int);
+connect(con1,localhost,root,,);
+begin;
+insert into t1 values (1);
+--echo # Ensure that metadata locks held by the transaction are released.
+disconnect con1;
+connection default;
+drop table t1;
+
+
+--echo #
+--echo # Coverage for situations when we try to execute DDL on tables
+--echo # which are locked by LOCK TABLES only implicitly.
+--echo #
+--disable_warnings
+drop tables if exists t1, t2;
+drop view if exists v1;
+drop function if exists f1;
+--enable_warnings
+create table t1 (i int);
+create table t2 (j int);
+--echo #
+--echo # Try to perform DDL on table which is locked through view.
+create view v1 as select * from t2;
+lock tables t1 write, v1 write;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+flush table t2;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+drop table t2;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+alter table t2 add column k int;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+create trigger t2_bi before insert on t2 for each row set @a:=1;
+--echo # Repair produces error as part of its result set.
+repair table t2;
+unlock tables;
+drop view v1;
+--echo #
+--echo # Now, try  DDL on table which is locked through routine.
+delimiter |;
+create function f1 () returns int
+begin
+  insert into t2 values (1);
+  return 0;
+end|
+delimiter ;|
+create view v1 as select f1() from t1;
+lock tables v1 read;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+flush table t2;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+drop table t2;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+alter table t2 add column k int;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+create trigger t2_bi before insert on t2 for each row set @a:=1;
+--echo # Repair produces error as part of its result set.
+repair table t2;
+unlock tables;
+drop view v1;
+drop function f1;
+--echo #
+--echo # Finally, try DDL on table which is locked thanks to trigger.
+create trigger t1_ai after insert on t1 for each row insert into t2 values (1);
+lock tables t1 write;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+flush table t2;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+drop table t2;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+alter table t2 add column k int;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+create trigger t2_bi before insert on t2 for each row set @a:=1;
+--echo # Repair produces error as part of its result set.
+repair table t2;
+unlock tables;
+drop trigger t1_ai;
+drop tables t1, t2;
+
+
+--echo #
+--echo # Bug#45035 " Altering table under LOCK TABLES results in 
+--echo # "Error 1213 Deadlock found..."
+--echo #
+--echo # When reopening tables under LOCK TABLES after ALTER TABLE,
+--echo # 6.0 used to be taking thr_lock locks one by one, and
+--echo # that would lead to a lock conflict. 
+--echo # Check that taking all locks at once works.
+--echo #
+--disable_warnings
+drop table if exists t1;
+--enable_warnings
+create table t1 (i int);
+lock tables t1 write, t1 as a read, t1 as b read;
+alter table t1 add column j int;
+unlock tables;
+drop table t1;
+create temporary table t1 (i int);
+lock tables t1 write, t1 as a read, t1 as b read;
+alter table t1 add column j int;
+unlock tables;
+drop table t1;
+--echo #
+--echo # Separate case for partitioned tables is important
+--echo # because each partition has an own thr_lock object.
+--echo #
+create table t1 (i int) partition by list (i)
+  (partition p0 values in (1),
+   partition p1 values in (2,3),
+   partition p2 values in (4,5));
+lock tables t1 write, t1 as a read, t1 as b read;
+alter table t1 add column j int;
+unlock tables;
+drop table t1;
+
+
+--echo #
+--echo # Bug #43272 HANDLER SQL command does not work under LOCK TABLES
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (a INT);
+LOCK TABLE t1 WRITE;
+
+--echo # HANDLER commands are not allowed in LOCK TABLES mode
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+HANDLER t1 OPEN;
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+HANDLER t1 READ FIRST;
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+HANDLER t1 CLOSE;
+
+UNLOCK TABLES;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Bug#45066 FLUSH TABLES WITH READ LOCK deadlocks against 
+--echo #           LOCK TABLE 
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1(a INT);
+
+LOCK TABLE t1 READ;
+--error ER_TABLE_NOT_LOCKED_FOR_WRITE
+FLUSH TABLES;
+
+LOCK TABLE t1 WRITE;
+FLUSH TABLES;
+
+--echo #
+--echo # If you allow the next combination, you reintroduce bug Bug#45066
+--echo # 
+LOCK TABLE t1 READ;
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+FLUSH TABLES WITH READ LOCK;
+
+LOCK TABLE t1 WRITE;
+--error ER_LOCK_OR_ACTIVE_TRANSACTION
+FLUSH TABLES WITH READ LOCK;
+
+UNLOCK TABLES;
+DROP TABLE t1;
+
+
+--echo #
+--echo # Simplified test for bug #48538 "Assertion in thr_lock() on LOAD DATA
+--echo # CONCURRENT INFILE".
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1;
+--enable_warnings
+
+CREATE TABLE t1 (f1 INT, f2 INT) ENGINE = MEMORY;
+CREATE TRIGGER t1_ai AFTER INSERT ON t1 FOR EACH ROW 
+  UPDATE LOW_PRIORITY t1 SET f2 = 7; 
+
+--echo # Statement below should fail with ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+--echo # error instead of failing on assertion in table-level locking subsystem.
+--error ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
+INSERT INTO t1(f1) VALUES(0);
+
+DROP TABLE t1;
+
+
+--echo #
+--echo # Bug#43685 Lock table affects other non-related tables
+--echo #
+
+--disable_warnings
+DROP TABLE IF EXISTS t1, t2;
+--enable_warnings
+
+connect (con2, localhost, root);
+CREATE TABLE t1 (id INT);
+CREATE TABLE t2 (id INT);
+
+connection default;
+LOCK TABLE t1 WRITE;
+ANALYZE TABLE t1;
+
+connection con2;
+LOCK TABLE t2 WRITE;
+--echo # This used to hang until the first connection
+--echo # unlocked t1.
+FLUSH TABLE t2;
+
+UNLOCK TABLES;
+
+connection default;
+UNLOCK TABLES;
+DROP TABLE t1, t2;
+disconnect con2;
+
+
+--echo #
+--echo # End of 6.0 tests.
+--echo #
+
+# Check that all connections opened by test cases in this file are really
+# gone so execution of other tests won't be affected by their presence.
+--source include/wait_until_count_sessions.inc
+#
+# Test concurrent lock and read locks
+# This gave a warning:
+# Warning at 'read lock with old write lock' for lock: 5:
+# Found lock of type 8 that is write and read locked. Read_no_write_count: 1
+#
+create table t1 (a int) engine=myisam;
+lock tables t1 write concurrent, t1 as t2 read;
+connect (con1,localhost,root,,);
+connection con1;
+lock tables t1 read local;
+unlock tables;
+connection default;
+unlock tables;
+connection con1;
+lock tables t1 read local;
+connection default;
+lock tables t1 write concurrent, t1 as t2 read;
+unlock tables;
+connection con1;
+unlock tables;
+disconnect con1;
+connection default;
+drop table t1;