MySQL Bugs: #100643: Can't connect to MySQL with sync plugin enabled after user creation

Bug #100643	Can't connect to MySQL with sync plugin enabled after user creation
Submitted:	26 Aug 2020 11:43	Modified:	28 Aug 2020 5:53
Reporter:	Hugo Dubois	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	8.0.21	OS:	Ubuntu
Assigned to:		CPU Architecture:	Any
Tags:	replication, semi-sync

Description:
When semi-synchronous replication is setup but a slave is not present to acknowledge, a user creation statement causes some locks and it is not possible to create new connection on the server (replica can't connect to acknowledge writes and unblock this situation or admin user cannot not connect to disable semi-sync).

The solution to unblock this situation is to disable semi sync on master but only if admin user has a client already connected. The other solution is a server restart.

How to repeat:
# Install semi-sync plugin:
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';

# Configure semi-sync with high timeout
set global rpl_semi_sync_master_timeout=3600000;

# Enable semi-sync on master
set global rpl_semi_sync_master_enabled=1;

# Before next statement, create another session to display processlist
session1 > create user test; # Wait for acknowledgement

# Try to connect to the mysql server:
mysql -uroot .... # Connection is hangging

session2 > show processlist;
+----+----------------------+-----------------+-------+---------+------+--------------------------------------+-------------------------+
| Id | User                 | Host            | db    | Command | Time | State                                | Info                    |
+----+----------------------+-----------------+-------+---------+------+--------------------------------------+-------------------------+
|  5 | event_scheduler      | localhost       | NULL  | Daemon  | 4019 | Waiting on empty queue               | NULL                    |
| 10 | root                 | 127.0.0.1:34382 | mysql | Sleep   |    4 |                                      | NULL                    |
| 11 | root                 | localhost       | NULL  | Query   |    0 | starting                             | show processlist        |
| 12 | root                 | localhost       | NULL  | Query   | 4008 | Waiting for semi-sync ACK from slave | CREATE USER 'test'@'%' |
| 22 | unauthenticated user | localhost       | NULL  | Connect | 3576 | Waiting for acl cache lock           | NULL

Thank you for the report, verified as described.

mysql> mysql> SELECT * FROM performance_schema.metadata_locks WHERE object_type LIKE '%ACL%';
+-------------+---------------+-------------+-------------+-----------------------+-----------+---------------+-------------+------------------------+-----------------+----------------+
| OBJECT_TYPE | OBJECT_SCHEMA | OBJECT_NAME | COLUMN_NAME | OBJECT_INSTANCE_BEGIN | LOCK_TYPE | LOCK_DURATION | LOCK_STATUS | SOURCE                 | OWNER_THREAD_ID | OWNER_EVENT_ID |
+-------------+---------------+-------------+-------------+-----------------------+-----------+---------------+-------------+------------------------+-----------------+----------------+
| ACL CACHE   | NULL          | NULL        | NULL        |       139978487984224 | EXCLUSIVE | EXPLICIT      | GRANTED     | sql_auth_cache.cc:3439 |              47 |        1482556 |
| ACL CACHE   | NULL          | NULL        | NULL        |       139978218719248 | SHARED    | EXPLICIT      | PENDING     | sql_auth_cache.cc:3439 |              50 |             12 |
+-------------+---------------+-------------+-------------+-----------------------+-----------+---------------+-------------+------------------------+-----------------+----------------+
2 rows in set (0.00 sec)

Bug #116238 is marked as duplicate of this one