Bug #94054 can we make ACL_CACHE_LOCK_TIMEOUT configurable?

• Submitted: 25 Jan 2019 8:11
• Reporter: Shane Bester (Platinum Quality Contributor)
• Status: Verified
• Category: MySQL Server: Connection Handling
• Severity: S4 (Feature request)
• Version: 8.0.11
• OS: Any
• CPU Architecture: Any

[25 Jan 2019 8:11] Shane Bester

Description:
New connections hang for 3600 seconds in state 'Waiting for acl cache lock' before failing with an incorrect error:

E:\mysql-8.0.11-winx64\bin>mysql -uroot
ERROR 1130 (HY000): Host 'localhost' is not allowed to connect to this MySQL server

mysql> show processlist;
+----+----------------------+-----------------+------+---------+------+---------------------------------+------------------
| Id | User                 | Host            | db   | Command | Time | State                           | Info
+----+----------------------+-----------------+------+---------+------+---------------------------------+------------------
|  4 | event_scheduler      | localhost       | NULL | Daemon  | 2749 | Waiting on empty queue          | NULL
|  8 | root                 | localhost:38852 | NULL | Query   |    0 | starting                        | show processlist
|  9 | root                 | localhost:38853 | NULL | Query   | 2685 | Waiting for table metadata lock | grant select(a) o
| 10 | unauthenticated user | localhost:38855 | NULL | Connect | 2678 | Waiting for acl cache lock      | NULL
+----+----------------------+-----------------+------+---------+------+---------------------------------+------------------
4 rows in set (0.00 sec)

Connection 9 waits for lock_wait_timeout seconds for connection 8 who has an open transaction reading from mysql.user.
Connection 10 waits for connection 9 for ACL_CACHE_LOCK_TIMEOUT (1 hour).

I would prefer to fail a new connection faster for future locking issues.

How to repeat:
On 8.0.11, 8.0.12, or 8.0.13 with autocommit=0 in the my.cnf

connection 1:  setup the testcase.
----------------------------------
create database if not exists test;
drop table if exists test.t;
create table test.t(a int)engine=innodb;
drop user if exists 'sbester';
create user 'sbester';
commit;

connection 2: open a read transaction
----------------------------------
select * from mysql.user;

connection 3: grant privileges to user
----------------------------------
grant select(a) on test.t to 'sbester';

connection 4:
----------------------------------
try to login,  it hangs for *1 hour* before erroring out.

Suggested fix:
As workaround, found the following helpful:

1.  set a low lock_wait_timeout.   The default of 1 year is too long...
2.  do not use autocommit=0 on the global setting
3.  explicitly COMMIT all transactions ASAP.
4.  lower wait_timeout to let idle connections die sooner.
5.  avoid accessing grant tables directly
6.  upgrade to 8.0.14 where is _particular_ instance of hang is solved.