MySQL Bugs: #897: could not set root password for hostname based account during install

Bug #897	could not set root password for hostname based account during install
Submitted:	23 Jul 2003 6:43	Modified:	6 Aug 2003 4:48
Reporter:	James Ponder	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Installing	Severity:	S3 (Non-critical)
Version:	3.23.57	OS:	Linux (Linux SuSE SLES 8)
Assigned to:	Lenz Grimmer	CPU Architecture:	Any

Description:
Having downloaded the Linux i386 binaries, I followed the instructions.  Everything went fine until the section which says:

PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
This is done with:
./bin/mysqladmin -u root  password 'new-password'
./bin/mysqladmin -u root -h loninwebid1  password 'new-password'
See the manual for more instructions.

The first password set went correctly, the second password set failed:

# ./bin/mysqladmin -u root -h loninwebid1 password xxxxxx
./bin/mysqladmin: connect to server at 'loninwebid1' failed
error: 'Host 'loninwebid1.uk.db.com' is not allowed to connect to this MySQL server'

How to repeat:
Simply download the Linux i386 binary release and follow the instructions on a SuSE SLES 8 system.

Suggested fix:
It appears that "scripts/mysql_install_db" uses /bin/hostname to get the hostname for insertion into the grant tables and it returned just "loninwebid1" (under the distribution I'm using, SuSE SLES 8, -f is used to get the FQDN).

Therefore, the user table contains "loninwebid1", and it appears from the error message that the MySQL server is using an FQDN in its checks.

I have fixed this on my server with "update user set host='loninwebid1.uk.db.com' where host='loninwebid1'" and flushing the privileges.

I'm not sure whether the intention was to have FQDNs in the user table or not.  If it was, the mysql_install_db script should put more effort into finding out the FQDN.

Yes, after SuSE Linux 7.3 "hostname" now only returns the unqualified hostname and one would need to use "hostname -f" to get the FQDN. MySQL actually requires
the FQDN in the privilege tables. However, using "hostname -f" is not portable and the behaviour of "hostname" seems to differ from platform to platform. So it's not a trivial task to always get the proper output here. This problem is actually documented in our manual, too:

http://www.mysql.com/doc/en/Access_denied.html

"If mysql -u root test works but mysql -h your_hostname -u root test results in Access denied, then you may not have the correct name for your host in the user table. A common problem here is that the Host value in the user table entry specifies an unqualified hostname, but your system's name resolution routines return a fully qualified domain name (or vice-versa). For example, if you have an entry with host 'tcx' in the user table, but your DNS tells MySQL that your hostname is 'tcx.subnet.se', the entry will not work. Try adding an entry to the user table that contains the IP number of your host as the Host column value. (Alternatively, you could add an entry to the user table with a Host value that contains a wildcard--for example, 'tcx.%'. However, use of hostnames ending with `%' is insecure and is not recommended!)"

I will try to find a way to at least use "hostname -f" where possible, but this will not fix it for all platforms.

I now added a workaround for this to the 4.0 tree, so it will be part of MySQL 4.0.15 and above (this is not a critical bug so it will not be fixed for MySQL 3.23):

ChangeSet@1.1560.1.2, 2003-08-06 12:13:08+02:00, lenz@mysql.com
   - try harder to obtain the FQDN with "hostname -f", if possible (BUG#897)
     Unfortunately the returned value of "hostname" is inconsistent on
     various platforms - some return the host name only, others return the
     FQDN by default. On Linux, one can use option "-f" to enable the output
     of the FQDN. Use it, if available.

Unfortunately I had to revert this fix, as it broke the installation on some
other operating systems - some implementations of "hostname" simply set
the host name to "-f" instead of giving an error about an unkown option :(