[MDEV-23527] mariadb-10.5 hangs on "query cache lock" on DROP DATABASE Created: 2020-08-21  Updated: 2022-11-25  Resolved: 2022-11-25

Status: Closed
Project: MariaDB Server
Component/s: Locking, Query Cache
Affects Version/s: 10.5.5, 10.6
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: sjon Assignee: Daniel Black
Resolution: Duplicate Votes: 6
Labels: not-10.3, not-10.4
Environment:

mariadb 10.5.5-1
archlinux 5.7.12-arch1-1

query_cache_type = 1
query_cache_size = 64M

Attachments: File all.sql     File source_db_schema.sql    
Issue Links:
Duplicate
duplicates MDEV-29760 DROP DATABASE hangs when particular q... Closed

 Description   

ever since upgrading to 10.5 (from 10.4.14) we experience strange hangs of the mariadb server. This is a nice example:

818 xx localhost xxx-2_1-unittest Query 14945 closing tables DROP DATABASE IF EXISTS `xxx-2_1-20200821055757` 0.000
2002 xxx localhost xxx-1_3-20200821055909 Killed 14879 Waiting for query cache lock INSERT INTO xxx 0.000
2030 root localhost   Query 0 starting show processlist 0.000

there seems to be no real relation between the DROP and the INSERT as they operate on completely different databases (they have no relation). I also have examples where there are multiple (killed) Waiting for query cache lock threads and a single DROP closing tables

The mariadbd consumes 100% CPU but I'm not sure what it's doing. strace shows some futex calls

 Comments   
Comment by Elena Stepanova [ 2020-10-17 ]

Could you please paste or attach your cnf file(s) and also elaborate on what exactly your SQL inflow is – which engines are used, which connectors if any, are transactions or XA transactions or table locks are used explicitly, etc. I can't claim it's necessarily relevant, but something is, as so far I couldn't reproduce the described effect.

Comment by Jonny Wylie [ 2021-07-06 ]

Hi can we re open this issue?
I am experiencing the same thing.

I have integration tests which each create new databases and drop them.
The database runs in docker. I am using the docker hub images.

The latest working version is 10.5.2.
The docker image for 10.5.3 seems broken I get error: 

malloc(): mismatching next->prev_size (unsorted)
 
2210706  2:52:15 [ERROR] mysqld got signal 6 ;
 
3This could be because you hit a bug. It is also possible that this binary
 
4or one of the libraries it was linked against is corrupt, improperly built,
 
5or misconfigured. This error can also be caused by malfunctioning hardware.

10.5.4 is failing with the bug of getting stuck "closing tables", 10.5.2 does not get stuck.

It seems to be caused by the query cache. If I disable the query cache then there is no error.
In my processlist I only ever see a drop database query getting stuck.
eg:

MariaDB [(none)]> show full processlist;
 
+-----+----------+-------------------+------+---------+------+----------------+------------------------------------------------------+----------+
 
| Id  | User     | Host              | db   | Command | Time | State          | Info                                                 | Progress |
 
+-----+----------+-------------------+------+---------+------+----------------+------------------------------------------------------+----------+
 
| 337 | scivisum | 172.30.58.1:37368 | NULL | Sleep   |   44 |                | NULL                                                 |    0.000 |
 
| 340 | scivisum | 172.30.58.1:37374 | NULL | Sleep   |   42 |                | NULL                                                 |    0.000 |
 
| 343 | scivisum | 172.30.58.1:37380 | NULL | Sleep   |   41 |                | NULL                                                 |    0.000 |
 
| 347 | scivisum | 172.30.58.1:37388 | NULL | Sleep   |   41 |                | NULL                                                 |    0.000 |
 
| 349 | scivisum | 172.30.58.1:37392 | NULL | Query   |   40 | closing tables | DROP DATABASE db1879c190_5927_4529_9051_3ccf7e95f59c |    0.000 |
 
| 350 | scivisum | 172.30.58.1:37396 | NULL | Query   |    0 | starting       | show full processlist                                |    0.000 |
 
+-----+----------+-------------------+------+---------+------+----------------+------------------------------------------------------+----------+
 
6 rows in set (0.000 sec)

My cnf file is:

[mysqld]
 
max_connections=500
 
query_cache_size=10485760  # 10MB
 
query_cache_type=1
 
query_cache_limit=1048576  # 1MB
 
innodb_file_per_table=1
 
table_open_cache=2048
 
performance_schema=0
 
innodb_print_all_deadlocks=1
 
innodb_status_output=1
 
innodb_status_output_locks=1
 
idle_transaction_timeout=120
 
slow_query_log=1
 
log_output=FILE
 
skip-name-resolve=1

Comment by Jonny Wylie [ 2021-07-06 ]

I can issue a show tables query for the database, and it hasn't got any tables left. So they have all been dropped, but it's still stuck.

Comment by Jonny Wylie [ 2021-07-06 ]

I guess this is the same as: MDEV-25023

Comment by Jonny Wylie [ 2021-07-13 ]

Some more information. I found if I issue a 'RESET QUERY CACHE' before dropping the database, then it doesn't lock up.

Comment by Alex [ 2021-08-04 ]

Same error here, deadlock when dropping a small 8 MB database with 33 tables.
Also happening at 10.6, so please add 10.6 to affected versions.
Working without problems in 10.4.

Comment by Frederik Bosch [ 2021-09-02 ]

Today I also hit this issue from a 10.6 Docker environment, so I can confirm 10.6 is also affected. I am using the RESET QUERY CACHE suggestion from Johny to prevent the lock.

Comment by Rémi Augier [ 2022-01-11 ]

Same issue here in 10.5.12 and 10.6.5

Comment by Neven Ivanov [ 2022-10-07 ]

Hi,

I was able to reproduce the problem on 100% of the times with MariaDB 10.5/10.6 ( no matter of the subversion but tested mostly with 10.6.10 )

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.

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.

As written already last year, please add 10.6 to affected versions!

Generated at Thu Feb 08 09:23:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.