test-example-projects/c++_projects/280w-mysql-8.0.18/mysql-test/t/lock.test

#
# Testing of table locking
#

# Save the initial number of concurrent sessions.
--source include/count_sessions.inc

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

CREATE TABLE t1 (i INT);

LOCK TABLES mysql.time_zone READ, mysql.time_zone_name READ, t1 READ;
UNLOCK TABLES;

LOCK TABLES mysql.time_zone READ, mysql.time_zone_name READ, t1 WRITE;
UNLOCK TABLES;

LOCK TABLES mysql.time_zone READ, mysql.time_zone_name READ;
UNLOCK TABLES;

LOCK TABLES mysql.time_zone WRITE, mysql.time_zone_name 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.time_zone_name WRITE, t1 READ;

--error ER_WRONG_LOCK_OF_SYSTEM_TABLE
LOCK TABLES mysql.time_zone WRITE, mysql.time_zone_name WRITE, t1 READ;

--error ER_WRONG_LOCK_OF_SYSTEM_TABLE
LOCK TABLES mysql.time_zone WRITE, mysql.time_zone_name WRITE, t1 WRITE;

--error ER_WRONG_LOCK_OF_SYSTEM_TABLE
LOCK TABLES mysql.time_zone READ, mysql.time_zone_name 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 allowed.
drop table t1;
--echo View becomes invalid.
--error ER_VIEW_INVALID
select * from v_bug5719;
unlock tables;
create table t1 (a int);
--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 #
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 #

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

create table t1 (a int);
connect(con1,localhost,root,,);
set autocommit= 0;
insert into t1 values (1);
lock table t1 write;
--echo # Disconnect
--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

create table t1 (a int);
connect(con1,localhost,root,,);
begin;
insert into t1 values (1);
--echo # Disconnect
--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;
flush table t2;
alter table t2 add column k int;
create trigger t2_bi before insert on t2 for each row set @a:=1;
repair table t2;
drop 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 table t2 (j int);
create view v1 as select f1() from t1;
lock tables v1 read;
flush table t2;
alter table t2 add column k int;
create trigger t2_bi before insert on t2 for each row set @a:=1;
repair table t2;
drop 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);
create table t2 (j int);
lock tables t1 write;
alter table t2 add column k int;
create trigger t2_bi before insert on t2 for each row set @a:=1;
repair table t2;
flush table t2;
drop table t2;
unlock tables;
drop trigger t1_ai;
drop tables t1;


--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 #
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);
--echo #
--echo # This is just for test coverage purposes, 
--echo # when this is allowed, remove the --error.
--echo #
--error ER_CANT_REOPEN_TABLE
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 # Moved case for partitioned tables to partition.test


--echo #
--echo # Bug #43272 HANDLER SQL command does not work under LOCK TABLES
--echo #

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 #

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 #

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 #

connect (con2, localhost, root);
CREATE TABLE t1 (id INT);
CREATE TABLE t2 (id INT);

--echo # Connection default
connection default;
LOCK TABLE t1 WRITE;
ANALYZE TABLE t1;

--echo # Connection con2
connection con2;
LOCK TABLE t2 WRITE;
--echo # This used to hang until the first connection
--echo # unlocked t1.
FLUSH TABLE t2;

UNLOCK TABLES;

--echo # Connection default
connection default;
UNLOCK TABLES;
DROP TABLE t1, t2;
disconnect con2;

--echo #
--echo # Bug#13586314 - RUNTIME - HIBISCUS: ISSUE DEPRECATION
--echo # WARNING FOR LOW_PRIORITY MODIFIER
--echo #

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

CREATE TABLE t1 (a INT);
--echo # Below statement should return a deprecation warning
LOCK TABLES t1 LOW_PRIORITY WRITE;
UNLOCK TABLES;
DROP TABLE t1;

--echo # End of Bug#13586314


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

call mtr.add_suppression("Can't open and lock privilege tables: Table 'user' was not locked with LOCK TABLES");
--echo #
--echo # WL#4284: Transactional DDL locking
--echo #
--echo # FLUSH PRIVILEGES should not implicitly unlock locked tables.
--echo #
--disable_warnings
DROP TABLE IF EXISTS t1;
--enable_warnings
CREATE TABLE t1 (c1 INT);
LOCK TABLES t1 READ;
--error ER_TABLE_NOT_LOCKED
FLUSH PRIVILEGES;
UNLOCK TABLES;
FLUSH PRIVILEGES;
DROP TABLE t1;