MySQL system tables and blank entries in the Host column
I discovered some interesting behaviour under MySQL 4.1 recently (I know it’s old, but it’s not within my power to upgrade it) whereby empty entries in the Host column of the users table under the mysql system database were causing all remote (i.e. TCP and not socket) connections to be refused with a message saying “Host ‘1.2.3.4’ is not allowed to connect to this MySQL server” without even asking for login credentials.
Telneting to the server’s IP address on port 3306 received the same message straight away and then disconnected the session.
The solution to this was simple, but somewhat obscure – update the Host column in the user table of the mysql system database so that any blank records are changed to “localhost” (secure) or ‘%’ (safer) and then run “flush privileges”.
I have no idea how the blank records got in there in the first place, but I couldn’t find any reference to this being the cause of such behaviour anywhere on the MySQL site. Everywhere seems to suggest that this message indicated that you need to grant privileges to the user for them to be able to connect remotely – which would be true, if I was getting as far as supplying login credentials!