[MDEV-29704] Thread hang in 'Waiting for table metadata lock' on CREATE TABLE Created: 2022-10-05  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Locking, 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, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Roel Van de Paar Assignee: Yuchen Pei
Resolution: Unresolved Votes: 0
Labels: affects-tests, shutdown_hang, thread_hang

Issue Links:
Relates

 Description   

Not related to MDEV-29676. This bug affects all versions.

--let $SOCKET= `SELECT @@global.socket`
 
--source include/have_innodb.inc
 
INSTALL PLUGIN Spider SONAME 'ha_spider.so';
 
CREATE USER Spider@localhost IDENTIFIED BY 'PWD123';
 
eval CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$SOCKET", DATABASE 'test', USER 'Spider', PASSWORD 'PWD123');
 
CREATE TABLE t (c INT) ENGINE=InnoDB;
 
CREATE TABLE t1 (a INT) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv", TABLE "t"';
 
LOCK TABLES t1 WRITE;
 
--error 12622
 
ALTER TABLE t1 CHANGE a b INT,ADD PRIMARY KEY(b);
 
UNLOCK TABLES;  # Not necessary to reproduce, but increases severity
 
CREATE TABLE t (a INT) ENGINE=InnoDB;
 
 
 
DROP TABLE t, t1;  # Cleanup

Leads to:

10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Debug)

 
...
 
10.11.0>ALTER TABLE t1 CHANGE a b INT,ADD PRIMARY KEY(b);
 
ERROR 12622 (HY000): Can't use this operation before executing 'unlock tables'
 
10.11.0>UNLOCK TABLES;
 
Query OK, 0 rows affected (0.000 sec)
 
10.11.0>CREATE TABLE t (a INT) ENGINE=InnoDB;  # Hangs

10.11.0 6ebdd3013a18b01dbecec76b870810329eb76586 (Debug)

 
10.11.0-dbg>SHOW FULL PROCESSLIST;
 
+----+-------------+-----------+------+---------+------+----------------------------------------------------+--------------------------------------+----------+
 
| Id | User        | Host      | db   | Command | Time | State                                              | Info                                 | Progress |
 
+----+-------------+-----------+------+---------+------+----------------------------------------------------+--------------------------------------+----------+
 
|  4 | root        | localhost | test | Query   |  164 | Waiting for table metadata lock                    | CREATE TABLE t (a INT) ENGINE=InnoDB |    0.000 |
 
|  5 | 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 |
 
|  6 | 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 |
 
| 10 | 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 |
 
| 12 | system user |           | NULL | Daemon  | NULL | Spider table background cardinality action handler | NULL                                 |    0.000 |
 
| 14 | system user |           | NULL | Daemon  | NULL | Spider table background cardinality action handler | NULL                                 |    0.000 |
 
| 13 | system user |           | NULL | Daemon  | NULL | Spider table background cardinality action handler | NULL                                 |    0.000 |
 
| 16 | system user |           | NULL | Sleep   |  164 | Reset for next command                             | NULL                                 |    0.000 |
 
| 15 | 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 |
 
| 19 | system user |           | NULL | Daemon  | NULL | Spider table background cardinality action handler | NULL                                 |    0.000 |
 
| 20 | system user |           | NULL | Daemon  | NULL | Spider table background statistics action handler  | NULL                                 |    0.000 |
 
| 18 | 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 |
 
| 21 | system user |           | NULL | Daemon  | NULL | Spider table background statistics action handler  | NULL                                 |    0.000 |
 
| 25 | Spider      | localhost | test | Sleep   |  164 |                                                    | NULL                                 |    0.000 |
 
| 26 | root        | localhost | test | Query   |    0 | starting                                           | SHOW FULL PROCESSLIST                |    0.000 |
 
+----+-------------+-----------+------+---------+------+----------------------------------------------------+--------------------------------------+----------+
 
23 rows in set (0.001 sec)

Bug confirmed present in:
MariaDB: 10.4.27 (dbg), 10.4.27 (opt), 10.5.18 (dbg), 10.5.18 (opt), 10.6.10 (dbg), 10.6.10 (opt), 10.7.6 (dbg), 10.7.6 (opt), 10.8.5 (dbg), 10.8.5 (opt), 10.9.3 (dbg), 10.9.3 (opt), 10.10.2 (dbg), 10.10.2 (opt), 10.11.0 (opt), 10.11.0 (dbg)

 Comments   
Comment by Roel Van de Paar [ 2022-10-05 ]

Additional testcase with same outcome. 

--let $SOCKET= `SELECT @@global.socket`
 
--source include/have_innodb.inc
 
INSTALL PLUGIN Spider SONAME 'ha_spider.so';
 
CREATE USER Spider@localhost IDENTIFIED BY 'PWD123';
 
eval CREATE SERVER srv FOREIGN DATA WRAPPER MYSQL OPTIONS (SOCKET "$SOCKET", DATABASE 'test', USER 'Spider', PASSWORD 'PWD123');
 
CREATE TABLE t (c INT) ENGINE=InnoDB;
 
CREATE TABLE t2 (i INT,j BLOB) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv", TABLE "t"';
 
CREATE TABLE t4 (c1 CHAR(1)) DEFAULT CHARSET=ujis ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv", TABLE "t"';
 
CREATE TABLE t1 (id INT(11) NOT NULL, id2 INT(11) NOT NULL, UNIQUE (id,id2),CONSTRAINT t1_id_fk FOREIGN KEY(id2,id) REFERENCES t1 (id,id2)) ENGINE=Spider COMMENT='WRAPPER "mysql", srv "srv", TABLE "t"';
 
CREATE TABLE t3 (c1 INT NOT NULL);
 
LOCK TABLES t3 WRITE, t2 WRITE, t4 WRITE, t1 WRITE;
 
--error 1146
 
LOCK TABLES performance_schema.setup_actors WRITE;
 
UNLOCK TABLES;  # Not needed, but increases severity
 
CREATE TABLE t (c INT) ENGINE=InnoDB;

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