[MDEV-28218] Spider: thread hang/deadlock as result of INSTALL PLUGIN and DROP TABLE Created: 2022-04-02  Updated: 2023-12-07  Resolved: 2023-10-06

Status: Closed
Project: MariaDB Server
Component/s: Plugins, Server, Storage Engine - Spider
Affects Version/s: 10.5, 10.6, 10.9, 10.10, 10.11, 11.0, 11.1
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Roel Van de Paar Assignee: Yuchen Pei
Resolution: Duplicate Votes: 0
Labels: deadlock, regression-10.5, thread_hang

Issue Links:
Blocks
is blocked by MDEV-22979 "mysqld --bootstrap" / mysql_install_... Closed
Relates
relates to MDEV-28219 Spider: Could not remove temporary ta... Closed

 Description   

Scenario: simply execute the following SQL in some automated fashion (copy/paste into CLI for example):

INSTALL PLUGIN spider SONAME 'ha_spider.so';
 
DROP TABLE IF EXISTS mysql.spider_tables;

In some instances (timing dependent), this will result in:

10.9.0 5be92887c2caacb45af87b1131db952ce627e83a (Debug)

 
10.9.0-dbg>INSTALL PLUGIN spider SONAME 'ha_spider.so';
 
Query OK, 0 rows affected, 1 warning (0.028 sec)
 
 
 
10.9.0-dbg>DROP TABLE IF EXISTS mysql.spider_tables;
 
<CLI Thread Hang>

Using CLI in another session shows the issue;

10.9.0 5be92887c2caacb45af87b1131db952ce627e83a (Debug)

 
10.9.0-dbg>show processlist;
 
+----+-------------+-----------+------+---------+------+----------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+
 
| Id | User        | Host      | db   | Command | Time | State                                              | Info                                                                                                 | Progress |
 
+----+-------------+-----------+------+---------+------+----------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+
 
|  4 | root        | localhost | test | Query   | 1344 | closing tables                                     | DROP TABLE IF EXISTS mysql.spider_tables                                                             |    0.000 |
 
|  5 | system user |           | NULL | Daemon  | NULL | Spider table background cardinality action handler | NULL                                                                                                 |    0.000 |
 
|  6 | system user |           | NULL | Daemon  | NULL | Spider table background cardinality 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 |
 
| 10 | system user |           | NULL | Daemon  | NULL | Spider table background cardinality action handler | NULL                                                                                                 |    0.000 |
 
| 12 | system user |           | NULL | Daemon  | NULL | Spider table background cardinality action handler | NULL                                                                                                 |    0.000 |
 
| 13 | system user |           | NULL | Query   | 1344 | Waiting for table metadata lock                    | create table if not exists mysql.spider_tables(  db_name char(64) not null default '',  table_name c |    0.000 |
 
| 17 | system user |           | NULL | Daemon  | NULL | Spider table background statistics action handler  | NULL                                                                                                 |    0.000 |
 
| 11 | system user |           | NULL | Daemon  | NULL | Spider table background cardinality action handler | NULL                                                                                                 |    0.000 |
 
| 15 | system user |           | NULL | Daemon  | NULL | Spider table background statistics action handler  | NULL                                                                                                 |    0.000 |
 
| 16 | 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 |
 
| 22 | 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 |
 
| 19 | 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 |
 
| 23 | 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 |
 
| 24 | system user |           | NULL | Daemon  | NULL | Spider table background cardinality action handler | NULL                                                                                                 |    0.000 |
 
| 25 | root        | localhost | test | Query   |    0 | starting                                           | show processlist                                                                                     |    0.000 |
 
+----+-------------+-----------+------+---------+------+----------------------------------------------------+------------------------------------------------------------------------------------------------------+----------+
 
22 rows in set (0.000 sec)

i.e. mysql.spider_table is still being created, even though the INSTALL PLUGIN had already returned as completed/finished.
Fix proposal: do not return as "query complete/finished" until any necessary table creations are finished.

Likely present in 10.4+. Discovered in 10.9

 Comments   
Comment by Nayuta Yanagisawa (Inactive) [ 2022-04-03 ]

Roel I agree with your proposal. I believe that it is highly likely that the fix for MDEV-27233 will also fix the present bug.

Comment by Yuchen Pei [ 2023-05-07 ]

This looks like it could be a duplicate of MDEV-22979, see [1].

MDEV-22979 is under review. I will look into this ticket once that ticket is assigned back to me after the first round of review.

[1] https://jira.mariadb.org/browse/MDEV-22979?focusedCommentId=256406&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-256406

Comment by Yuchen Pei [ 2023-05-26 ]

Indeed, this is caused by the server immidiately executing the drop table statement, without waiting for the spider to finish initialisation. The assumption is that plugin initialisation is done synchronously. Spider breaks this assumption.

Here the deadlock is different from those of MDEV-27233, MDEV-30370 and MDEV-22979. It is caused by spider init thread waiting for a MDL to create the system table, which is presumably held by the "main" thread when trying to drop the same table, but also trying to create a spider handler for the drop, which waits on the spider init to finish.

We can make spider comply with the assumption, by making its initialisation synchronous (i.e. Roel's proposal), or we can patch the current async arrangement. We will continue using MDEV-22979 as the main ticket for discussions on all these init bugs.

Comment by Roel Van de Paar [ 2023-06-05 ]

Bug confirmed present in 10.5-11.1 (EOL versions not tested). Bug apparently not present in 10.4, so seems to be a 10.5 regression.

Comment by Yuchen Pei [ 2023-10-06 ]

Fix for spider init bugs are tracked in MDEV-22979 (10.10+) and MDEV-29870 (10.4-10.6)

Generated at Thu Feb 08 09:59:00 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.