You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
388 lines
12 KiB
388 lines
12 KiB
create table t1 (c1 int);
|
|
create table t2 (c1 int);
|
|
create table t3 (c1 int);
|
|
lock tables t1 write, t2 write, t3 write;
|
|
drop table t2, t3, t1;
|
|
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;
|
|
create table t1 ( a int(11) not null auto_increment, primary key(a));
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
create table t2 ( a int(11) not null auto_increment, primary key(a));
|
|
Warnings:
|
|
Warning 1681 Integer display width is deprecated and will be removed in a future release.
|
|
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;
|
|
delete from t2 using t1,t2 where t1.a=t2.a;
|
|
ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
|
|
delete t2 from t1,t2 where t1.a=t2.a;
|
|
ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
|
|
drop table t1,t2;
|
|
ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
|
|
unlock tables;
|
|
drop table t2,t1;
|
|
End of 4.1 tests.
|
|
create table t1 (a int);
|
|
lock table t1 write;
|
|
flush tables with read lock;
|
|
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
|
|
unlock tables;
|
|
drop table t1;
|
|
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;
|
|
LOCK TABLES mysql.time_zone READ, mysql.time_zone_name WRITE, t1 READ;
|
|
ERROR HY000: You can't combine write-locking of system tables with other tables or lock types
|
|
LOCK TABLES mysql.time_zone WRITE, mysql.time_zone_name WRITE, t1 READ;
|
|
ERROR HY000: You can't combine write-locking of system tables with other tables or lock types
|
|
LOCK TABLES mysql.time_zone WRITE, mysql.time_zone_name WRITE, t1 WRITE;
|
|
ERROR HY000: You can't combine write-locking of system tables with other tables or lock types
|
|
LOCK TABLES mysql.time_zone READ, mysql.time_zone_name WRITE;
|
|
ERROR HY000: You can't combine write-locking of system tables with other tables or lock types
|
|
DROP TABLE t1;
|
|
|
|
Bug#5719 impossible to lock VIEW
|
|
|
|
Just covering existing behaviour with tests.
|
|
Consistency has not been found here.
|
|
|
|
drop view if exists v_bug5719;
|
|
drop table if exists t1, t2, t3;
|
|
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;
|
|
select * from t1;
|
|
ERROR HY000: Table 't1' was not locked with LOCK TABLES
|
|
|
|
Allowed to select from a temporary talbe under LOCK TABLES
|
|
|
|
select * from t2;
|
|
a
|
|
select * from t3;
|
|
ERROR HY000: Table 't3' was not locked with LOCK TABLES
|
|
select * from v_bug5719;
|
|
1
|
|
1
|
|
drop view v_bug5719;
|
|
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
|
|
|
|
sic: did not left LOCK TABLES mode automatically
|
|
|
|
select * from t1;
|
|
ERROR HY000: Table 't1' was not locked with LOCK TABLES
|
|
unlock tables;
|
|
create or replace view v_bug5719 as select * from t1;
|
|
lock tables v_bug5719 write;
|
|
select * from v_bug5719;
|
|
a
|
|
|
|
Allowed to use an underlying table under LOCK TABLES <view>
|
|
|
|
select * from t1;
|
|
a
|
|
|
|
Allowed to select from a temporary table under LOCK TABLES
|
|
|
|
select * from t2;
|
|
a
|
|
select * from t3;
|
|
ERROR HY000: Table 't3' was not locked with LOCK TABLES
|
|
Dropping of implicitly locked table is allowed.
|
|
drop table t1;
|
|
View becomes invalid.
|
|
select * from v_bug5719;
|
|
ERROR HY000: View 'test.v_bug5719' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
|
|
unlock tables;
|
|
create table t1 (a int);
|
|
Now let us also lock table explicitly and drop it.
|
|
lock tables t1 write, v_bug5719 write;
|
|
drop table t1;
|
|
|
|
sic: left LOCK TABLES mode
|
|
|
|
select * from t3;
|
|
a
|
|
select * from v_bug5719;
|
|
ERROR HY000: View 'test.v_bug5719' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them
|
|
unlock tables;
|
|
drop view v_bug5719;
|
|
|
|
When limitation to use temporary tables in views is removed, please
|
|
add a test that shows what happens under LOCK TABLES when a view
|
|
references a temporary table, is locked, and the underlying table
|
|
is dropped.
|
|
|
|
create view v_bug5719 as select * from t2;
|
|
ERROR HY000: View's SELECT refers to a temporary table 't2'
|
|
|
|
Cleanup.
|
|
|
|
drop table t2, t3;
|
|
#
|
|
# Bug#39843 DELETE requires write access to table in subquery in where clause
|
|
#
|
|
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;
|
|
# 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
|
|
) ;
|
|
# While implementing the patch we didn't break old behavior;
|
|
# The following sub-select should still requires a write lock:
|
|
SELECT * FROM t1 WHERE 1 IN (SELECT * FROM t2 FOR UPDATE);
|
|
ERROR HY000: Table 't2' was locked with a READ lock and can't be updated
|
|
UNLOCK TABLES;
|
|
DROP TABLE t1,t2;
|
|
End of 5.1 tests.
|
|
#
|
|
# Ensure that FLUSH TABLES doesn't substitute a base locked table
|
|
# with a temporary one.
|
|
#
|
|
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;
|
|
a
|
|
unlock tables;
|
|
drop table t1, t2;
|
|
#
|
|
# Ensure that mi_copy_status is called for two instances
|
|
# of the same table when it is reopened after a flush.
|
|
#
|
|
drop table if exists t1;
|
|
drop view if exists v1;
|
|
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;
|
|
c1
|
|
33
|
|
unlock tables;
|
|
drop table t1;
|
|
drop view v1;
|
|
#
|
|
# WL#4284: Transactional DDL locking
|
|
#
|
|
create table t1 (a int);
|
|
set autocommit= 0;
|
|
insert into t1 values (1);
|
|
lock table t1 write;
|
|
# Disconnect
|
|
# Ensure that metadata locks will be released if there is an open
|
|
# transaction (autocommit=off) in conjunction with lock tables.
|
|
drop table t1;
|
|
# Same problem but now for BEGIN
|
|
create table t1 (a int);
|
|
begin;
|
|
insert into t1 values (1);
|
|
# Disconnect
|
|
# Ensure that metadata locks held by the transaction are released.
|
|
drop table t1;
|
|
#
|
|
# Coverage for situations when we try to execute DDL on tables
|
|
# which are locked by LOCK TABLES only implicitly.
|
|
#
|
|
drop tables if exists t1, t2;
|
|
drop view if exists v1;
|
|
drop function if exists f1;
|
|
create table t1 (i int);
|
|
create table t2 (j int);
|
|
#
|
|
# 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;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 repair note The storage engine for the table doesn't support repair
|
|
drop table t2;
|
|
unlock tables;
|
|
drop view v1;
|
|
#
|
|
# Now, try DDL on table which is locked through routine.
|
|
create function f1 () returns int
|
|
begin
|
|
insert into t2 values (1);
|
|
return 0;
|
|
end|
|
|
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;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 repair note The storage engine for the table doesn't support repair
|
|
drop table t2;
|
|
unlock tables;
|
|
drop view v1;
|
|
drop function f1;
|
|
#
|
|
# 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;
|
|
Table Op Msg_type Msg_text
|
|
test.t2 repair note The storage engine for the table doesn't support repair
|
|
flush table t2;
|
|
drop table t2;
|
|
unlock tables;
|
|
drop trigger t1_ai;
|
|
drop tables t1;
|
|
#
|
|
# Bug#45035 " Altering table under LOCK TABLES results in
|
|
# "Error 1213 Deadlock found..."
|
|
#
|
|
# When reopening tables under LOCK TABLES after ALTER TABLE,
|
|
# 6.0 used to be taking thr_lock locks one by one, and
|
|
# that would lead to a lock conflict.
|
|
# Check that taking all locks at once works.
|
|
#
|
|
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);
|
|
#
|
|
# This is just for test coverage purposes,
|
|
# when this is allowed, remove the --error.
|
|
#
|
|
lock tables t1 write, t1 as a read, t1 as b read;
|
|
ERROR HY000: Can't reopen table: 't1'
|
|
alter table t1 add column j int;
|
|
unlock tables;
|
|
drop table t1;
|
|
# Moved case for partitioned tables to partition.test
|
|
#
|
|
# Bug #43272 HANDLER SQL command does not work under LOCK TABLES
|
|
#
|
|
CREATE TABLE t1 (a INT);
|
|
LOCK TABLE t1 WRITE;
|
|
# HANDLER commands are not allowed in LOCK TABLES mode
|
|
HANDLER t1 OPEN;
|
|
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
|
|
HANDLER t1 READ FIRST;
|
|
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
|
|
HANDLER t1 CLOSE;
|
|
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
|
|
UNLOCK TABLES;
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#45066 FLUSH TABLES WITH READ LOCK deadlocks against
|
|
# LOCK TABLE
|
|
#
|
|
CREATE TABLE t1(a INT);
|
|
LOCK TABLE t1 READ;
|
|
FLUSH TABLES;
|
|
ERROR HY000: Table 't1' was locked with a READ lock and can't be updated
|
|
LOCK TABLE t1 WRITE;
|
|
FLUSH TABLES;
|
|
#
|
|
# If you allow the next combination, you reintroduce bug Bug#45066
|
|
#
|
|
LOCK TABLE t1 READ;
|
|
FLUSH TABLES WITH READ LOCK;
|
|
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
|
|
LOCK TABLE t1 WRITE;
|
|
FLUSH TABLES WITH READ LOCK;
|
|
ERROR HY000: Can't execute the given command because you have active locked tables or an active transaction
|
|
UNLOCK TABLES;
|
|
DROP TABLE t1;
|
|
#
|
|
# Simplified test for bug #48538 "Assertion in thr_lock() on LOAD DATA
|
|
# CONCURRENT INFILE".
|
|
#
|
|
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;
|
|
# Statement below should fail with ER_CANT_UPDATE_USED_TABLE_IN_SF_OR_TRG
|
|
# error instead of failing on assertion in table-level locking subsystem.
|
|
INSERT INTO t1(f1) VALUES(0);
|
|
ERROR HY000: Can't update table 't1' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
|
|
DROP TABLE t1;
|
|
#
|
|
# Bug#43685 Lock table affects other non-related tables
|
|
#
|
|
CREATE TABLE t1 (id INT);
|
|
CREATE TABLE t2 (id INT);
|
|
# Connection default
|
|
LOCK TABLE t1 WRITE;
|
|
ANALYZE TABLE t1;
|
|
Table Op Msg_type Msg_text
|
|
test.t1 analyze status OK
|
|
# Connection con2
|
|
LOCK TABLE t2 WRITE;
|
|
# This used to hang until the first connection
|
|
# unlocked t1.
|
|
FLUSH TABLE t2;
|
|
UNLOCK TABLES;
|
|
# Connection default
|
|
UNLOCK TABLES;
|
|
DROP TABLE t1, t2;
|
|
#
|
|
# Bug#13586314 - RUNTIME - HIBISCUS: ISSUE DEPRECATION
|
|
# WARNING FOR LOW_PRIORITY MODIFIER
|
|
#
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1 (a INT);
|
|
# Below statement should return a deprecation warning
|
|
LOCK TABLES t1 LOW_PRIORITY WRITE;
|
|
Warnings:
|
|
Warning 1287 'LOW_PRIORITY WRITE' is deprecated and will be removed in a future release. Please use WRITE instead
|
|
UNLOCK TABLES;
|
|
DROP TABLE t1;
|
|
# End of Bug#13586314
|
|
#
|
|
# End of 6.0 tests.
|
|
#
|
|
call mtr.add_suppression("Can't open and lock privilege tables: Table 'user' was not locked with LOCK TABLES");
|
|
#
|
|
# WL#4284: Transactional DDL locking
|
|
#
|
|
# FLUSH PRIVILEGES should not implicitly unlock locked tables.
|
|
#
|
|
DROP TABLE IF EXISTS t1;
|
|
CREATE TABLE t1 (c1 INT);
|
|
LOCK TABLES t1 READ;
|
|
FLUSH PRIVILEGES;
|
|
ERROR HY000: Table 'user' was not locked with LOCK TABLES
|
|
UNLOCK TABLES;
|
|
FLUSH PRIVILEGES;
|
|
DROP TABLE t1;
|
|
|