[MDEV-30049] Thread hang in 'Waiting for table metadata lock' on INSERT Created: 2022-11-21  Updated: 2023-11-22  Resolved: 2023-11-17

Status: Closed
Project: MariaDB Server
Component/s: Storage Engine - Spider
Affects Version/s: 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10, 10.11
Fix Version/s: 10.4.33, 10.5.24, 10.6.17, 10.11.7, 11.0.5, 11.1.4, 11.2.3

Type: Bug Priority: Major
Reporter: Roel Van de Paar Assignee: Yuchen Pei
Resolution: Fixed Votes: 0
Labels: locking, shutdown_hang, thread_hang

Issue Links:
Blocks
is blocked by MDEV-30014 heap-use-after-free in ha_spider::lo... Closed
PartOf
is part of MDEV-30014 heap-use-after-free in ha_spider::lo... Closed
Relates

 Description    

--let $SOCKET= `SELECT @@global.socket`
 
--source include/have_innodb.inc
 
INSTALL PLUGIN Spider SONAME 'ha_spider.so';
 
CREATE USER Spider@localhost IDENTIFIED BY 'PWD1';
 
GRANT ALL PRIVILEGES ON test.* to Spider@localhost;
 
eval CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$SOCKET",DATABASE 'test',user 'Spider',PASSWORD 'PWD1');
 
CREATE TABLE t (c INT) ENGINE=InnoDB;
 
CREATE TABLE t2 (c INT) ENGINE=Spider COMMENT='WRAPPER "mysql",srv "srv",TABLE "t"';
 
CREATE TABLE t3 (c INT) ENGINE=InnoDB;
 
LOCK TABLES t2 WRITE;
 
--error 1428
 
LOCK TABLES mysql.proc WRITE,mysql.user WRITE;   # ERROR 1428 (HY000): You can't combine write-locking of system tables with other tables or lock types
 
INSERT INTO t3 SELECT * FROM t;

Leads to:

10.11.2 8283948846740a22f96bbe7bccf250708406d5d9 (Optimized)

 
10.11.2-opt>SHOW FULL PROCESSLIST;
 
+----+-------------+-----------+------+---------+------+----------------------------------------------------+--------------------------------+----------+
 
| Id | User        | Host      | db   | Command | Time | State                                              | Info                           | Progress |
 
+----+-------------+-----------+------+---------+------+----------------------------------------------------+--------------------------------+----------+
 
|  4 | root        | localhost | test | Query   |  272 | Waiting for table metadata lock                    | INSERT INTO t3 SELECT * FROM t |    0.000 |
 
|  5 | system user |           | NULL | Sleep   |  272 | Reset for next command                             | NULL                           |    0.000 |
 
|  6 | system user |           | NULL | Daemon  | NULL | Spider table background statistics action handler  | NULL                           |    0.000 |
 
|  7 | system user |           | NULL | Daemon  | NULL | Spider table background statistics action handler  | NULL                           |    0.000 |
 
|  8 | system user |           | NULL | Daemon  | NULL | Spider table background statistics action handler  | NULL                           |    0.000 |
 
|  9 | system user |           | NULL | Daemon  | NULL | Spider table background statistics action handler  | NULL                           |    0.000 |
 
| 11 | system user |           | NULL | Daemon  | NULL | Spider table background statistics action handler  | NULL                           |    0.000 |
 
| 10 | system user |           | NULL | Daemon  | NULL | Spider table background statistics action handler  | NULL                           |    0.000 |
 
| 12 | system user |           | NULL | Daemon  | NULL | Spider table background statistics action handler  | NULL                           |    0.000 |
 
| 13 | system user |           | NULL | Daemon  | NULL | Spider table background statistics action handler  | NULL                           |    0.000 |
 
| 14 | system user |           | NULL | Daemon  | NULL | Spider table background statistics action handler  | NULL                           |    0.000 |
 
| 15 | system user |           | NULL | Daemon  | NULL | Spider table background cardinality action handler | NULL                           |    0.000 |
 
| 16 | system user |           | NULL | Daemon  | NULL | Spider table background cardinality action handler | NULL                           |    0.000 |
 
| 17 | system user |           | NULL | Daemon  | NULL | Spider table background cardinality action handler | NULL                           |    0.000 |
 
| 18 | system user |           | NULL | Daemon  | NULL | Spider table background cardinality action handler | NULL                           |    0.000 |
 
| 19 | system user |           | NULL | Daemon  | NULL | Spider table background cardinality action handler | NULL                           |    0.000 |
 
| 20 | system user |           | NULL | Daemon  | NULL | Spider table background cardinality action handler | NULL                           |    0.000 |
 
| 21 | system user |           | NULL | Daemon  | NULL | Spider table background cardinality action handler | NULL                           |    0.000 |
 
| 22 | system user |           | NULL | Daemon  | NULL | Spider table background cardinality action handler | NULL                           |    0.000 |
 
| 23 | system user |           | NULL | Daemon  | NULL | Spider table background cardinality action handler | NULL                           |    0.000 |
 
| 24 | system user |           | NULL | Daemon  | NULL | Spider table background cardinality action handler | NULL                           |    0.000 |
 
| 25 | Spider      | localhost | test | Sleep   |  272 |                                                    | NULL                           |    0.000 |
 
| 26 | root        | localhost | test | Query   |    0 | starting                                           | SHOW FULL PROCESSLIST          |    0.000 |
 
+----+-------------+-----------+------+---------+------+----------------------------------------------------+--------------------------------+----------+
 
23 rows in set (0.000 sec)



 Comments   
Comment by Roel Van de Paar [ 2022-11-21 ]

Even though the LOCK TABLES mysql.proc WRITE,mysql.user WRITE; produces an error, it is also what causes the issue. When removing it, we get ER_TABLE_NOT_LOCKED (1100): Table 't3' was not locked with LOCK TABLES on the INSERT.

Comment by Yuchen Pei [ 2023-10-20 ]

Just to clarify, the ER_TABLE_NOT_LOCKED is expected, see main.create:

#
 
# CREATE TABLE ... SELECT and LOCK TABLES
 
#
 
# There is little sense in using CREATE TABLE ... SELECT under
 
# LOCK TABLES as it mostly does not work. At least we check that
 
# the server doesn't crash, hang and produces sensible errors.
 
# Includes test for bug #20662 "Infinite loop in CREATE TABLE
 
# IF NOT EXISTS ... SELECT with locked tables".
 
--disable_service_connection
 
create table t1 (i int);
 
insert into t1 values (1), (2);
 
lock tables t1 read;
 
--error ER_TABLE_NOT_LOCKED
 
create table t2 select * from t1;

The original case seems to be the same problem as
MDEV-29456/MDEV-29667.

Comment by Yuchen Pei [ 2023-11-17 ]

fixed by MDEV-30014

Generated at Thu Feb 08 10:13:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.