We're trying to troubleshoot a number of issues with the host_cache on a MySQL 8 server
The issues are all tied to a recent update where we tightened our user "From Host" definitions from @"%" to @"%.ourdomain"
An example issue was this morning when I attempted to connect over our VPN and was given an "Access denied from IP Address" error. Checking the performance_schema.host_cache on the server my IP Address was listed but the "HOST" column held a NULL - which explains why I was denied access.
Ran TRUNCATE TABLE to flush the hosts and the issue persisted with the same entry recreated in the host_cache. (Flushing the cache has resolved the issue in 90% of the cases)
Reconnecting to the VPN 1/2 hour later I could now reconnect to the database.
However thia exposed a second issue. Checking the host_cache for the new IP Address the list host is not the host name for my machine.
So 2 issues
- IP Address resolving as Null in Host cache
- IP Address resolving to incorrect Host Name in host_cache
We'd like to keep the restrictions on the users, but would rather not use IP Addresses as suggested in the MySQL 8 troubleshooting as we'd need to recreate users for each subnet that we have on our network
MySQL 8.0.35/Windows Server 2019
