[MDEV-29760] DROP DATABASE hangs when particular query cache is present - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Critical
Resolution: Fixed
Affects Version/s: 10.6.10, 10.5, 10.6, 10.7(EOL), 10.8(EOL), 10.9(EOL), 10.10(EOL)
Fix Version/s: 10.11.2, 10.5.19, 10.6.12, 10.7.8, 10.9.5, 10.9.6, 10.10.3
Component/s: Locking, Query Cache
Labels:
None
Environment:
query_cache_type = 1
query_cache_size = 64M
CentOS 7
Almalinux 8

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 .

Attachments

- Sort By Name
- Sort By Date
- Ascending
- Descending
- Thumbnails
- List
- Download All

all.sql
1 kB
2022-10-11 06:12
source_db_schema.sql
5 kB
2022-10-11 06:12

Issue Links

is duplicated by

MDEV-23527 mariadb-10.5 hangs on "query cache lock" on DROP DATABASE

Closed

MDEV-25023 MariaDB 10.5 - DROP DATABASE locked

Closed

Activity

Ascending order - Click to sort in descending order

Neven Ivanov created issue - 2022-10-11 06:17

Elena Stepanova added a comment - 2022-10-24 19:22

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).

Elena Stepanova added a comment - 2022-10-24 19:22 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).

Elena Stepanova made changes - 2022-10-24 19:22

Field	Original Value	New Value
Fix Version/s		10.5 [ 23123 ]
Fix Version/s		10.6 [ 24028 ]
Fix Version/s		10.7 [ 24805 ]
Fix Version/s		10.8 [ 26121 ]
Fix Version/s		10.9 [ 26905 ]
Fix Version/s		10.10 [ 27530 ]
Affects Version/s		10.5 [ 23123 ]
Affects Version/s		10.6 [ 24028 ]
Affects Version/s		10.7 [ 24805 ]
Affects Version/s		10.8 [ 26121 ]
Affects Version/s		10.9 [ 26905 ]
Affects Version/s		10.10 [ 27530 ]
Assignee		Oleksandr Byelkin [ sanja ]

Elena Stepanova made changes - 2022-10-24 19:22

Status

Open [ 1 ]

Confirmed [ 10101 ]

Elena Stepanova made changes - 2022-10-24 19:22

Summary

MariaDB 10.6 DROP DATABASE when particular query cache is present

DROP DATABASE when particular query cache is present

Elena Stepanova made changes - 2022-10-24 19:22

Summary

DROP DATABASE when particular query cache is present

DROP DATABASE hangs when particular query cache is present

Alex added a comment - 2022-10-28 13:23

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~~.

Alex added a comment - 2022-10-28 13:23 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 .

Sergei Golubchik made changes - 2022-10-31 21:03

Link

This issue is duplicated by ~~MDEV-23527~~ [ ~~MDEV-23527~~ ]

Sergei Golubchik made changes - 2022-10-31 21:04

Link

This issue is duplicated by ~~MDEV-25023~~ [ ~~MDEV-25023~~ ]

Daniel Black made changes - 2022-11-25 01:11

Assignee

Oleksandr Byelkin [ sanja ]

Daniel Black [ danblack ]

Daniel Black made changes - 2022-11-25 01:11

Status

Confirmed [ 10101 ]

In Progress [ 3 ]

Daniel Black added a comment - 2022-11-25 06:38

Github PR #2346 for review.

Daniel Black added a comment - 2022-11-25 06:38 Github PR #2346 for review.

Daniel Black made changes - 2022-11-25 06:38

Assignee	Daniel Black [ danblack ]	Oleksandr Byelkin [ sanja ]
Status	In Progress [ 3 ]	In Review [ 10002 ]

Daniel Black added a comment - 2022-11-25 07:17

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

Daniel Black added a comment - 2022-11-25 07:17 Thanks NevenIvanov for the test case, and elenst for the reduction/bisect down to commit to blame. Both helped immensely.

Daniel Black made changes - 2022-11-25 17:07

Fix Version/s		10.5.19 [ 28511 ]
Fix Version/s		10.6.12 [ 28513 ]
Fix Version/s		10.7.8 [ 28515 ]
Fix Version/s		10.9.5 [ 28519 ]
Fix Version/s		10.9.6 [ 28520 ]
Fix Version/s		10.10.3 [ 28521 ]
Fix Version/s		10.11.2 [ 28523 ]
Fix Version/s	10.5 [ 23123 ]
Fix Version/s	10.6 [ 24028 ]
Fix Version/s	10.7 [ 24805 ]
Fix Version/s	10.8 [ 26121 ]
Fix Version/s	10.9 [ 26905 ]
Fix Version/s	10.10 [ 27530 ]
Assignee	Oleksandr Byelkin [ sanja ]	Daniel Black [ danblack ]
Resolution		Fixed [ 1 ]
Status	In Review [ 10002 ]	Closed [ 6 ]

People

Assignee:: Daniel Black

Reporter:: Neven Ivanov

Votes:: 0 Vote for this issue

Watchers:: 8 Start watching this issue

Dates

Created:: 2022-10-11 06:17

Updated:: 2022-11-25 17:07

Resolved:: 2022-11-25 17:07

Git Integration

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

MariaDB Server

Details

Description

Attachments

Attachments

Issue Links

Activity

People

Dates

Git Integration