MySQL Bugs: #1807: mysql.user table in 4.0.16 works for Host=IP but not Host=Hostname

Bug #1807	mysql.user table in 4.0.16 works for Host=IP but not Host=Hostname
Submitted:	11 Nov 2003 11:22	Modified:	15 Nov 2003 1:37
Reporter:	Justin Cranford	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.16	OS:	Linux (Redhat 9)
Assigned to:		CPU Architecture:	Any

Description:
The utility install_mysql_db creates two default entries for root in mysql.user. They differ only in their value for Host. One row has Host='localhost' and the other has Host='ESFP008'. My hostname is ESFP008 (192.168.2.21) which matches the entry in the mysql.user table.

In 4.0.13, I can connect to the database with /usr/bin/mysql from either 'localhost' or 'ESFP008' (-h option). In 4.0.16, I can connect from 'localhost' but not from 'ESFP008'. However, if I changed Host from 'ESFP008' to 192.168.2.21 in the mysql.user table I can connect from 'ESFP008'.

I am running the mysql client on the server each time so it is not a problem with the network. The behaviour is different between 4.0.13 and 4.0.16. I can connect from ESFP008 if my IP address is in the mysql.user table but not if that hostname is there. The address resolves correctly according to nslookup so I believe this is a bug.

How to repeat:
Install MySQL 4.0.16 on Redhat 9 using RPMs from MySQL.com. Here is what I installed on my system.

> rpm -qa|grep -i mysql
MySQL-server-4.0.16-0
MySQL-shared-compat-4.0.16-0
MySQL-devel-4.0.16-0
MySQL-client-4.0.16-0

Initialize the database with install_mysql_db and try to connect with these commands.

> /usr/bin/mysql -h localhost --port 3306 -u root --password=
> /usr/bin/mysql -h ESFP008   --port 3306 -u root --password=

The first command connects ok but not the second one. I get a message saying that host is not allowed to connent. Both commands worked when I installed 4.0.13 on another system so the bahaviour is inconsistent.

Here is the workaround I used to be able to connect from ESFP008. I changed Host in mysql.user to use my IP address instead of my hostname.

> hostname
ESFP008

> host ESFP008
ESFP008.easylink.ca has address 192.168.2.21

> mysql_install_db > /dev/null

> mysql -h ESFP008 --port 3306 -u root --password=
ERROR 1130: Host 'esfp008.easylink.ca' is not allowed to connect to this MySQL server

> mysql -h localhost --port 3306 -u root --password=
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7 to server version: 4.0.16-standard-log
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> SELECT Host FROM mysql.user WHERE User='root';
+-----------+
| Host      |
+-----------+
| localhost |
| ESFP008   |
+-----------+
2 rows in set (0.00 sec)
 
mysql> UPDATE mysql.user SET Host='192.168.2.21' WHERE Host='ESFP008';
Query OK, 2 rows affected (0.00 sec)
Rows matched: 2  Changed: 2  Warnings: 0

mysql> FLUSH PRIVILEGES;
Query OK, 0 rows affected (0.00 sec)
 
mysql> SELECT Host FROM mysql.user WHERE User='root';
+--------------+
| Host         |
+--------------+
| 192.168.2.21 |
| localhost    |
+--------------+
2 rows in set (0.00 sec)
 
mysql> quit
Bye

> mysql -h ESFP008 --port 3306 -u root --password=
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8 to server version: 4.0.16-standard-log
 
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> quit
Bye

It was only after changing Host in mysql.user from hostname to IP address that I was able to connect from that hostname.

Suggested fix:
Quick fix: When initializing a database, have install_mysql_db add a third entry for root in mysql.user where Host=IP.

Slow fix: Find and correct the erroneous permission checking logic in the server.

Are you able to connect if you change ESFP008 to
esfp008.easylink.ca in the user table entry?

It appears that your DNS is resolving ESFP008 to
the full name, based on the command you report here:

> mysql -h ESFP008 --port 3306 -u root --password=
ERROR 1130: Host 'esfp008.easylink.ca' is not allowed to connect to this
MySQL server

I tried using the full name esfp008.easylink.ca but that did not work. Besides IP the only other thing that worked was Host='%'.

Strike my last comment. Using the full domain name worked. I mispelled it earlier. Thanks for your time and sorry for the trouble. It is much appreciated.

On second thought I think there may still be a problem. Version 4.0.16 appears to be working so really the problem was with 4.0.13 all along. Both servers and databases were set up the same way (hostname without domain name in mysql.user).

The 4.0.13 host is called mgmtsrv. When I run nslookup on that box it resolves as mgmtsrv.easylink.ca and 192.168.2.103. However, there is no entry for mgmtsrv.easylink.ca in mysql.user, only mgmtsrv. Why can I connect to 4.0.13 if mysql is matching up against the hostname and domainname?

Thank you for taking the time to write to us, but this is not
a bug. Please double-check the documentation available at
http://www.mysql.com/documentation/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php

All your questions are answered in manual.

For last one - why *you* can connect if host is not in mysql.user table check as *who* you are connected. Most probably as anonymous user