Percona server 5.7 stalls on drop database

[masood alam]

I am doing some testing and got stuck into a weird situation. I created a database and populated it with 16 or so tables. To retest my data population scripts I dropped the database and it works fine. I populate the database again with the scripts and repeat the above process.

However on the second occasion it does not work. The drop database statement completely stalls and do nothing.

Following appears in show processlist;

 34 | root | localhost | bi_staging | Query | 60 | Waiting for table metadata lock | drop database bi_staging

Although I know what it means but it should not apply in my case because there is no traffic on this machine as I am using it for a dedicated task.

I tried restarting the MYSQL but it do not work either, in the error log it says read the security section of MYSQL on how to run as root. I am root.

I restarted the linux machine and MYSQL back online and repeated the above process. However on the second attempt of the drop database statement similar behavior and the statement stalls.

[Muhammad Irfan (muhammad-irfan)]

Masood,

Can you please show us your script and along with that provide my.cnf is in use. Also, provide processlist/innodb status output during stall.

mysql> SHOW FULL PROCESSLIST\G
mysql> SHOW ENGINE INNODB STATUS\G

[masood alam (masood-jevot)]

Hello,

The process list is the same as I am not running anything on the machine while I execute drop database command. Please also note that when the command stalls I am able to quit the database or open another session to the database aswell. But when I try to execute systemctl restart mysql nothing happens and mysql never restarts or stops. The only option left for me is to reboot the linux server.

I am not pasting the scripts to load data as it has nothing to do with the problem, since during execution on the drop command nothing is running on the Percona MYSQL.

[masood alam (masood-jevot)]

in this test machine I am using basic my.cnf

[client]

port = 3306

[mysqld]

port = 3306
socket = /var/lib/mysql/mysql.sock
datadir = /var/lib/mysql/
symbolic-links = 0

#BINARY LOGGING#
#expire_logs_days = 10
#server-id =1
#log_bin = mysql-bin
#max_binlog_size = 104857600
#sync-binlog = 1
#binlog_format = MIXED
bind-address = 0.0.0.0

#InnoDB Parameters
#buffer pool will be sized by script to 80% of total memory
innodb_buffer_pool_size = 300M
innodb_buffer_pool_instances = 8
innodb_file_per_table = 1
innodb_flush_log_at_trx_commit = 1
innodb_numa_interleave = 1
innodb_io_capacity = 200
innodb_flush_method = O_DIRECT
innodb_data_home_dir = /var/lib/mysql/
innodb_log_file_size = 256M

# Caches
max_heap_table_size = 100M
tmp_table_size = 100M

sort_buffer_size = 20M
join_buffer_size = 20M
innodb_log_buffer_size = 8M
innodb_adaptive_hash_index = 1
query_cache_size = 0
table-definition-cache = 1024
table-open-cache = 2048
max_allowed_packet = 300M
lower_case_table_names=1
sql_mode=""

[markus_albe (markus-albe)]

Hello, as colleague said before: please attach output of SHOW ENGINE INNODB STATUS\G captured while the problem is happening. Output of SHOW GLOBAL VARIABLES would also come in handy

[Launchpad Janitor (janitor)]

[Expired for Percona Server because there has been no activity for 60 days.]

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-3747