Host_cache null or wrong Host
Posted by: Dan Kelly
Date: November 22, 2023 04:51AM

We're having problems with the host_cache table on our MySQL 8.0.35 / Windows Server 19 instance

We've recently updated our users so that the "From Host" is "" rather than just "%"
However we are seeing two, possibly related issues, where users either being rejected or are being allowed in with the wrong IP address.

Scenario 1:
User is rejected with "Host xx.xx.xx.xx is not allowed to connect" errors.
Checking the performance_schema.host_cache the IP Address is listed but has a NULL entry in the Host column

TRUNCATE TABLE performance_schema.host_cache generally resolves the issue

Scenario 2:
User is connected over the VPN and gets the same error as above.
Check host_cache and NULL entry.
Disconnect from VPN and reconnect at some point later. Connection is now granted

*However* the VPN has assigned a new IP address to the connection and checking host_cache that IP is assigned to a different Host than the current host. In our currecnt scenario this isn't an issue but could become one.

Both scenarios suggest that the host_cache is not refreshing the correctly. What are our options?

We'd prefer to use the Domain Name as we have a fairly complex array of subnets that users could connect from depending on Location/Wifi/LAN etc.

At this point we are flushing the cache 2-3 times a day which feels wrong. Generally the cache is well below the limit of number of records.
How much of a performance hit would disabling the host cache be?

Options: ReplyQuote

Written By
Host_cache null or wrong Host
November 22, 2023 04:51AM

Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.