Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-28218

Spider: thread hang/deadlock as result of INSTALL PLUGIN and DROP TABLE

    XMLWordPrintable

    Details

      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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              nayuta-yanagisawa Nayuta Yanagisawa
              Reporter:
              Roel Roel Van de Paar
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Dates

                Created:
                Updated:

                  Git Integration

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