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

ANALYZE INSERT DELAYED locks the table forever and causes hang on shutdown

    XMLWordPrintable

Details

    Description

      CREATE TABLE t (a INT) ENGINE=MyISAM;
      ANALYZE INSERT DELAYED INTO t VALUES (1),(2);
      UPDATE t SET a = 3;
       
      #--source include/restart_mysqld.inc
       
      DROP TABLE t;
      

      The test case above hangs on UPDATE already. Stack traces from UPDATE and the delayed thread:

      10.3 02e85aea

      Thread 7 (Thread 0x7f1439fb6700 (LWP 1161347) "mysqld"):
      #0  futex_abstimed_wait_cancelable (private=0, abstime=0x7f1439fb5e50, clockid=972774384, expected=0, futex_word=0x7f142812ec3c) at ../sysdeps/nptl/futex-internal.h:323
      #1  __pthread_cond_wait_common (abstime=0x7f1439fb5e50, clockid=972774384, mutex=0x7f142812eb88, cond=0x7f142812ec10) at pthread_cond_wait.c:520
      #2  __pthread_cond_timedwait (cond=0x7f142812ec10, mutex=0x7f142812eb88, abstime=0x7f1439fb5e50) at pthread_cond_wait.c:656
      #3  0x000056047aaca034 in safe_cond_timedwait (cond=0x7f142812ec10, mp=0x7f142812eb60, abstime=0x7f1439fb5e50, file=0x56047ab5c770 "/data/src/10.3/include/mysql/psi/mysql_thread.h", line=1211) at /data/src/10.3/mysys/thr_mutex.c:546
      #4  0x0000560479ee7dc7 in inline_mysql_cond_timedwait (that=0x7f142812ec10, mutex=0x7f142812eb60, abstime=0x7f1439fb5e50, src_file=0x56047ab5cf60 "/data/src/10.3/sql/sql_insert.cc", src_line=3157) at /data/src/10.3/include/mysql/psi/mysql_thread.h:1211
      #5  0x0000560479ef0564 in handle_delayed_insert (arg=0x7f1428128ec0) at /data/src/10.3/sql/sql_insert.cc:3157
      #6  0x000056047aa52d04 in pfs_spawn_thread (arg=0x56047d056a10) at /data/src/10.3/storage/perfschema/pfs.cc:1869
      #7  0x00007f1440172ea7 in start_thread (arg=<optimized out>) at pthread_create.c:477
      #8  0x00007f14400a2def in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
       
      Thread 6 (Thread 0x7f143a001700 (LWP 1161344) "mysqld"):
      #0  futex_abstimed_wait_cancelable (private=0, abstime=0x7f1439fff590, clockid=973075248, expected=0, futex_word=0x7f1428000b90) at ../sysdeps/nptl/futex-internal.h:323
      #1  __pthread_cond_wait_common (abstime=0x7f1439fff590, clockid=973075248, mutex=0x7f141c005778, cond=0x7f1428000b68) at pthread_cond_wait.c:520
      #2  __pthread_cond_timedwait (cond=0x7f1428000b68, mutex=0x7f141c005778, abstime=0x7f1439fff590) at pthread_cond_wait.c:656
      #3  0x000056047aaca034 in safe_cond_timedwait (cond=0x7f1428000b68, mp=0x7f141c005750, abstime=0x7f1439fff590, file=0x56047aee4730 "/data/src/10.3/include/mysql/psi/mysql_thread.h", line=1211) at /data/src/10.3/mysys/thr_mutex.c:546
      #4  0x000056047aac63c4 in inline_mysql_cond_timedwait (that=0x7f1428000b68, mutex=0x7f141c005750, abstime=0x7f1439fff590, src_file=0x56047aee4760 "/data/src/10.3/mysys/thr_lock.c", src_line=576) at /data/src/10.3/include/mysql/psi/mysql_thread.h:1211
      #5  0x000056047aac6ac8 in wait_for_lock (wait=0x7f141c005820, data=0x7f14280ac680, in_wait_list=0 '\000', lock_wait_timeout=86400) at /data/src/10.3/mysys/thr_lock.c:576
      #6  0x000056047aac7623 in thr_lock (data=0x7f14280ac680, owner=0x7f14280028a8, lock_wait_timeout=86400) at /data/src/10.3/mysys/thr_lock.c:1014
      #7  0x000056047aac7f16 in thr_multi_lock (data=0x7f1428013590, count=1, owner=0x7f14280028a8, lock_wait_timeout=86400) at /data/src/10.3/mysys/thr_lock.c:1295
      #8  0x000056047a38a72a in mysql_lock_tables (thd=0x7f1428000d90, sql_lock=0x7f1428013568, flags=0) at /data/src/10.3/sql/lock.cc:347
      #9  0x000056047a38a530 in mysql_lock_tables (thd=0x7f1428000d90, tables=0x7f1428013560, count=1, flags=0) at /data/src/10.3/sql/lock.cc:299
      #10 0x0000560479e9b80a in lock_tables (thd=0x7f1428000d90, tables=0x7f1428012ba8, count=1, flags=0) at /data/src/10.3/sql/sql_base.cc:5370
      #11 0x000056047a034f74 in mysql_update (thd=0x7f1428000d90, table_list=0x7f1428012ba8, fields=..., values=..., conds=0x0, order_num=0, order=0x0, limit=18446744073709551615, ignore=false, found_return=0x7f1439fffef0, updated_return=0x7f1439ffffb0) at /data/src/10.3/sql/sql_update.cc:372
      #12 0x0000560479f2f05a in mysql_execute_command (thd=0x7f1428000d90) at /data/src/10.3/sql/sql_parse.cc:4343
      #13 0x0000560479f3b090 in mysql_parse (thd=0x7f1428000d90, rawbuf=0x7f1428012ad8 "UPDATE t SET a = 3", length=18, parser_state=0x7f143a0005b0, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:7870
      #14 0x0000560479f278c5 in dispatch_command (command=COM_QUERY, thd=0x7f1428000d90, packet=0x7f1428008f31 "UPDATE t SET a = 3", packet_length=18, is_com_multi=false, is_next_command=false) at /data/src/10.3/sql/sql_parse.cc:1852
      #15 0x0000560479f26283 in do_command (thd=0x7f1428000d90) at /data/src/10.3/sql/sql_parse.cc:1398
      #16 0x000056047a0a388a in do_handle_one_connection (connect=0x56047d0182e0) at /data/src/10.3/sql/sql_connect.cc:1403
      #17 0x000056047a0a35f5 in handle_one_connection (arg=0x56047d0182e0) at /data/src/10.3/sql/sql_connect.cc:1308
      #18 0x000056047aa52d04 in pfs_spawn_thread (arg=0x56047d118b30) at /data/src/10.3/storage/perfschema/pfs.cc:1869
      #19 0x00007f1440172ea7 in start_thread (arg=<optimized out>) at pthread_create.c:477
      #20 0x00007f14400a2def in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95
      

      Process list:

      +----+---------+-----------------+------+----------------+------+------------------------------+--------------------+----------+
      |  4 | root    | localhost       | test | Query          |  145 | Waiting for table level lock | UPDATE t SET a = 3 |    0.000 |
      |  5 | DELAYED | localhost       | test | Delayed insert | NULL | Waiting for INSERT           | t                  |    0.000 |
      |  6 | root    | localhost:39844 | NULL | Query          |    0 | Init                         | show processlist   |    0.000 |
      +----+---------+-----------------+------+----------------+------+------------------------------+--------------------+----------+
      

      Locks:

      +-----------+-------------------------+---------------+---------------------+--------------+------------+
      | THREAD_ID | LOCK_MODE               | LOCK_DURATION | LOCK_TYPE           | TABLE_SCHEMA | TABLE_NAME |
      +-----------+-------------------------+---------------+---------------------+--------------+------------+
      |         5 | MDL_INTENTION_EXCLUSIVE | NULL          | Global read lock    |              |            |
      |         4 | MDL_INTENTION_EXCLUSIVE | NULL          | Global read lock    |              |            |
      |         5 | MDL_SHARED_WRITE        | NULL          | Table metadata lock | test         | t          |
      |         4 | MDL_SHARED_WRITE        | NULL          | Table metadata lock | test         | t          |
      +-----------+-------------------------+---------------+---------------------+--------------+------------+
      

      If you run UPDATE in the test case with a short enough lock wait timeout or remove it altogether, and uncomment restart, you can observe the hang on shutdown.

      Reproducible on 10.3-10.10.
      INSERT DELAYED without ANALYZE doesn't cause the problem, neither does EXPLAIN INSERT DELAYED.

      Attachments

        Activity

          People

            Johnston Rex Johnston
            elenst Elena Stepanova
            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.