[MDEV-29160] ANALYZE INSERT DELAYED locks the table forever and causes hang on shutdown Created: 2022-07-23  Updated: 2023-11-28

Status: Open
Project: MariaDB Server
Component/s: Admin statements, Data Manipulation - Insert
Affects Version/s: 10.3, 10.4, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.4, 10.5, 10.6

Type: Bug Priority: Major
Reporter: Elena Stepanova Assignee: Rex Johnston
Resolution: Unresolved Votes: 0
Labels: None


 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.
Generated at Thu Feb 08 10:06:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.