Description:
After calling a stored procedure which modifies grant tables and calls FLUSH PRIVILEGES
on of mysql threads hangs in state of 'Closing tables' on 'FLUSH PRIVILEGES' query
How to repeat:
mysql> show create procedure DELETE_USER\G;
*************************** 1. row ***************************
Procedure: DELETE_USER
sql_mode:
Create Procedure: CREATE PROCEDURE `test`.`DELETE_USER`( IN username CHAR(255), IN hostname CHAR(255))
DETERMINISTIC
BEGIN
DELETE FROM mysql.user WHERE user = username and host = hostname;
FLUSH PRIVILEGES;
END
mysql> SELECT user, host FROM mysql.user;
+------+------------------+
| user | host |
+------+------------------+
| | blend.ensita.net |
| root | blend.ensita.net |
| | localhost |
| m | localhost |
| root | localhost |
+------+------------------+
5 rows in set (0.01 sec)
mysql> call DELETE_USER('m', 'localhost');
and it hangs. mysqld can't accept new connections, existing connections work. From one
of them I could see:
mysql> show processlist;
+----+------+-----------+------+---------+------+----------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+------+-----------+------+---------+------+----------------+------------------+
| 2 | root | localhost | test | Query | 0 | NULL | show processlist |
| 3 | root | localhost | test | Query | 10 | closing tables | FLUSH PRIVILEGES |
+----+------+-----------+------+---------+------+----------------+------------------+
Suggested fix:
Fix it
Description: After calling a stored procedure which modifies grant tables and calls FLUSH PRIVILEGES on of mysql threads hangs in state of 'Closing tables' on 'FLUSH PRIVILEGES' query How to repeat: mysql> show create procedure DELETE_USER\G; *************************** 1. row *************************** Procedure: DELETE_USER sql_mode: Create Procedure: CREATE PROCEDURE `test`.`DELETE_USER`( IN username CHAR(255), IN hostname CHAR(255)) DETERMINISTIC BEGIN DELETE FROM mysql.user WHERE user = username and host = hostname; FLUSH PRIVILEGES; END mysql> SELECT user, host FROM mysql.user; +------+------------------+ | user | host | +------+------------------+ | | blend.ensita.net | | root | blend.ensita.net | | | localhost | | m | localhost | | root | localhost | +------+------------------+ 5 rows in set (0.01 sec) mysql> call DELETE_USER('m', 'localhost'); and it hangs. mysqld can't accept new connections, existing connections work. From one of them I could see: mysql> show processlist; +----+------+-----------+------+---------+------+----------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+------+-----------+------+---------+------+----------------+------------------+ | 2 | root | localhost | test | Query | 0 | NULL | show processlist | | 3 | root | localhost | test | Query | 10 | closing tables | FLUSH PRIVILEGES | +----+------+-----------+------+---------+------+----------------+------------------+ Suggested fix: Fix it