[MDEV-29760] DROP DATABASE hangs when particular query cache is present Created: 2022-10-11  Updated: 2022-11-25  Resolved: 2022-11-25

Status: Closed
Project: MariaDB Server
Component/s: Locking, Query Cache
Affects Version/s: 10.6.10, 10.5, 10.6, 10.7, 10.8, 10.9, 10.10
Fix Version/s: 10.11.2, 10.5.19, 10.6.12, 10.7.8, 10.9.5, 10.9.6, 10.10.3

Type: Bug Priority: Critical
Reporter: Neven Ivanov Assignee: Daniel Black
Resolution: Fixed Votes: 0
Labels: None
Environment:

query_cache_type = 1
query_cache_size = 64M
CentOS 7
Almalinux 8

Attachments: File all.sql     File source_db_schema.sql    
Issue Links:
Duplicate
is duplicated by MDEV-23527 mariadb-10.5 hangs on "query cache lo... Closed
is duplicated by MDEV-25023 MariaDB 10.5 - DROP DATABASE locked Closed

 Description   

Hi,

With MariaDB 10.6 ( no matter of the subversion but tested mostly with 10.6.10 ) when particular query cache is present you are unable to drop database.

You will need the files all.sql and source_db_schema.sql which are attached to this task.

Then just execute from bash console:

mysql -e "create database source_db"
 
 
 
 
 
 
mysql source_db < source_db_schema.sql
 
 
 
mysql -e 'RESET QUERY CACHE';
 
 
 
mysql -e "create database destination_db" ;  mysqldump source_db | mysql destination_db
 
 
 
mysql 'source_db' -e "select option_name from VbVoptions;" ;
 
 
 
 
 
 
    mysql destination_db < all.sql; 
 
 
    mysql -e "drop database destination_db ;"
 
 
 
    echo "drop successfully"

source_db_schema.sql
all.sql

In summary if specific query cache is present, you are unable to drop database and it just hangs (MariaDB uses 100% cpu) and no restart ( without "kill -9" ) can be performed .

 Comments   
Comment by Elena Stepanova [ 2022-10-24 ]

Thanks for the report and the test case. Reproducible as described on 10.5+.

A reduced and MTR-ed version:

SET @qcache= @@global.query_cache_type;
 
SET GLOBAL query_cache_type= 1;
 
SET query_cache_type= 1;
 
 
 
CREATE TABLE t1 (a VARCHAR(8)) ENGINE=MyISAM;
 
SELECT a FROM test.t1;
 
 
 
CREATE DATABASE db;
 
USE db;
 
 
 
CREATE TABLE t2 (b INT) ENGINE=MyISAM;
 
CREATE TABLE t3 (c INT) ENGINE=MyISAM;
 
 
 
SELECT 't2' table_name FROM t2;
 
 
 
SELECT 't2' table_name FROM t2
 
UNION ALL
 
SELECT 't3' table_name FROM t3;
 
 
 
DROP DATABASE db;
 
 
 
# Cleanup
 
USE test;
 
DROP TABLE t1;
 
SET GLOBAL query_cache_type= @qcache;

DROP DATABASE db hangs (loops at high CPU), one of stack traces:

10.5 dca4fc24

 
#3  0x000055f04f7e908d in DbugMalloc (size=568) at /src/dbug/dbug.c:2071
 
#4  0x000055f04f7e7143 in PushState (cs=0x606000015fe0) at /src/dbug/dbug.c:1596
 
#5  0x000055f04f7e12f3 in _db_push_ (control=0x55f0508ff460 "") at /src/dbug/dbug.c:864
 
#6  0x000055f04f7884ca in safe_mutex_lock (mp=0x62b00006b030, my_flags=0, file=0x55f04f97cd60 "/src/sql/sql_class.h", line=4239) at /src/mysys/thr_mutex.c:236
 
#7  0x000055f04da0fe9b in inline_mysql_mutex_lock (that=0x62b00006b030, src_file=0x55f04f97cd60 "/src/sql/sql_class.h", src_line=4239) at /src/include/mysql/psi/mysql_thread.h:750
 
#8  0x000055f04da321a8 in THD::set_killed (this=0x62b000069218, killed_arg=KILL_CONNECTION, killed_errno_arg=0, killed_err_msg_arg=0x0) at /src/sql/sql_class.h:4239
 
#9  0x000055f04dba9a84 in Query_cache::wreck (this=0x55f051c29f40 <query_cache>, line=867, message=0x55f04fa1c840 "incorrect block type") at /src/sql/sql_cache.cc:4691
 
#10 0x000055f04db991bc in Query_cache_block::table (this=0x7f88d71f87f8) at /src/sql/sql_cache.cc:867
 
#11 0x000055f04db9f75e in Query_cache::invalidate (this=0x55f051c29f40 <query_cache>, thd=0x62b000069218, db=0x62b0000382c8 "db") at /src/sql/sql_cache.cc:2380
 
#12 0x000055f04dbf735b in mysql_rm_db_internal (thd=0x62b000069218, db=0x62b00006e0c8, if_exists=false, silent=false) at /src/sql/sql_db.cc:972
 
#13 0x000055f04dbf7eb3 in mysql_rm_db (thd=0x62b000069218, db=0x62b00006e0c8, if_exists=false) at /src/sql/sql_db.cc:1103
 
#14 0x000055f04dcd6ff0 in mysql_execute_command (thd=0x62b000069218) at /src/sql/sql_parse.cc:5241
 
#15 0x000055f04dce8c53 in mysql_parse (thd=0x62b000069218, rawbuf=0x62b000038238 "DROP DATABASE db", length=16, parser_state=0x7f88d767ec40, is_com_multi=false, is_next_command=false) at /src/sql/sql_parse.cc:8087
 
#16 0x000055f04dcc1449 in dispatch_command (command=COM_QUERY, thd=0x62b000069218, packet=0x62900024e219 "DROP DATABASE db", packet_length=16, is_com_multi=false, is_next_command=false) at /src/sql/sql_parse.cc:1891
 
#17 0x000055f04dcbe682 in do_command (thd=0x62b000069218) at /src/sql/sql_parse.cc:1375
 
#18 0x000055f04e09d7d0 in do_handle_one_connection (connect=0x608000002538, put_in_cache=true) at /src/sql/sql_connect.cc:1416
 
#19 0x000055f04e09d198 in handle_one_connection (arg=0x6080000024b8) at /src/sql/sql_connect.cc:1318
 
#20 0x000055f04ebb9b1c in pfs_spawn_thread (arg=0x615000005d18) at /src/storage/perfschema/pfs.cc:2201
 
#21 0x00007f88e1078ea7 in start_thread (arg=<optimized out>) at pthread_create.c:477
 
#22 0x00007f88e0c65aef in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:95

The problem was introduced in 10.5.4 by this commit:

commit dfb41fddf69ccbca89fd322901f2809bc3bcc0e9
 
Author: Monty
 
Date:   Fri Jun 5 18:55:11 2020 +0300
 
 
 
    Make error messages from DROP TABLE and DROP TABLE IF EXISTS consistent

(despite the main line, there were "other things" in that commit).

Comment by Alex [ 2022-10-28 ]

I can reproduce the deadlock with Nevens example everytime also with the latest MariaDB 10.6.10.
It is only working if the Query-cache-reset is immediately executed before the drop.

Related to MDEV-23527 and MDEV-25023.

Comment by Daniel Black [ 2022-11-25 ]

Github PR #2346 for review.

Comment by Daniel Black [ 2022-11-25 ]

Thanks NevenIvanov for the test case, and elenst for the reduction/bisect down to commit to blame. Both helped immensely.

Generated at Thu Feb 08 10:11:05 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.