[MDEV-30049] Thread hang in 'Waiting for table metadata lock' on INSERT - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.4(EOL), 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL), 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
Component/s: Storage Engine - Spider
Labels:

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)

Attachments

Issue Links

is blocked by

MDEV-30014 heap-use-after-free in ha_spider::lock_tables(), highly sporadic SIGSEGV in intern_close_table

Closed

is part of

MDEV-30014 heap-use-after-free in ha_spider::lock_tables(), highly sporadic SIGSEGV in intern_close_table

Closed

Activity

Ascending order - Click to sort in descending order

Roel Van de Paar created issue - 2022-11-21 06:39

Roel Van de Paar made changes - 2022-11-21 06:39

Field	Original Value	New Value
Link		This issue relates to TODO-3120 [ TODO-3120 ]

Roel Van de Paar made changes - 2022-11-21 06:40

Description

{code:sql}
--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;
INSERT INTO t3 SELECT * FROM t;
{code}
Leads to:
{noformat:title=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)
{noformat}

{code:sql}
--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;
{code}
Leads to:
{noformat:title=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)
{noformat}

Roel Van de Paar made changes - 2022-11-21 06:45

Affects Version/s		10.4 [ 22408 ]
Affects Version/s		10.5 [ 23123 ]
Affects Version/s		10.6 [ 24028 ]
Affects Version/s		10.7 [ 24805 ]
Affects Version/s		10.8 [ 26121 ]
Affects Version/s		10.9 [ 26905 ]
Affects Version/s		10.10 [ 27530 ]

Roel Van de Paar made changes - 2022-11-21 06:45

Fix Version/s		10.4 [ 22408 ]
Fix Version/s		10.5 [ 23123 ]
Fix Version/s		10.6 [ 24028 ]
Fix Version/s		10.7 [ 24805 ]
Fix Version/s		10.8 [ 26121 ]
Fix Version/s		10.9 [ 26905 ]
Fix Version/s		10.10 [ 27530 ]
Fix Version/s	10.11 [ 27614 ]

Roel Van de Paar made changes - 2022-11-21 06:50

Labels

thread_hang

shutdown_hang thread_hang

Roel Van de Paar added a comment - 2022-11-21 07:02

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.

Roel Van de Paar added a comment - 2022-11-21 07:02 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.

Roel Van de Paar made changes - 2022-11-21 07:03

Assignee

Nayuta Yanagisawa [ JIRAUSER47117 ]

Julien Fritsch made changes - 2023-03-03 17:27

Fix Version/s

10.7 [ 24805 ]

Julien Fritsch made changes - 2023-04-27 14:48

Fix Version/s

10.8 [ 26121 ]

Yuchen Pei made changes - 2023-06-07 01:15

Assignee

Nayuta Yanagisawa [ JIRAUSER47117 ]

Yuchen Pei [ JIRAUSER52627 ]

Yuchen Pei made changes - 2023-10-12 04:26

Labels

shutdown_hang thread_hang

locking shutdown_hang thread_hang

Yuchen Pei made changes - 2023-10-12 05:13

Link

This issue is part of ~~MDEV-30014~~ [ ~~MDEV-30014~~ ]

Yuchen Pei made changes - 2023-10-12 05:13

Link

This issue is blocked by ~~MDEV-30014~~ [ ~~MDEV-30014~~ ]

Yuchen Pei added a comment - 2023-10-20 06:57

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

Yuchen Pei added a comment - 2023-10-20 06:57 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 .

Yuchen Pei added a comment - 2023-11-17 03:03

fixed by ~~MDEV-30014~~

Yuchen Pei added a comment - 2023-11-17 03:03 fixed by MDEV-30014

Yuchen Pei made changes - 2023-11-17 03:03

Fix Version/s		10.4.33 [ 29516 ]
Fix Version/s	10.4 [ 22408 ]
Fix Version/s	10.5 [ 23123 ]
Fix Version/s	10.6 [ 24028 ]
Fix Version/s	10.9 [ 26905 ]
Fix Version/s	10.10 [ 27530 ]
Resolution		Fixed [ 1 ]
Status	Open [ 1 ]	Closed [ 6 ]

JiraAutomate made changes - 2023-11-17 03:03

Fix Version/s		10.5.24 [ 29517 ]
Fix Version/s		10.6.17 [ 29518 ]
Fix Version/s		10.11.7 [ 29519 ]
Fix Version/s		11.0.5 [ 29520 ]
Fix Version/s		11.1.4 [ 29024 ]
Fix Version/s		11.2.3 [ 29521 ]

Yuchen Pei made changes - 2024-10-09 01:21

Comment

[ The error is legitimate, so the question is whether the assert is necessary. One possible solution to this issue is to try to detect the incomplete coverage of the partitions over the data node table rows earlier in the DELETE statement when opening the partition table, but I don't see how to justify the extra complexity introduced by this change as well as the fact that it is basically doing the same check as at the assertion failure, if we could simply remove the assert. Therefore my patch proposes the removal of the assertion.

Hi [~serg], ptal thanks

{noformat}
3116ab76fe9 upstream/bb-10.5-mdev-30067 bb-10.5-ycp ~~MDEV-30067~~ Remove an overly enthusiastic assert when deleting from a partitioned table
{noformat}
]

People

Assignee:: Yuchen Pei

Reporter:: Roel Van de Paar

Votes:: 0 Vote for this issue

Watchers:: 3 Start watching this issue

Dates

Created:: 2022-11-21 06:39

Updated:: 2024-10-09 01:21

Resolved:: 2023-11-17 03:03

Git Integration

Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.

MariaDB Server

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration