MySQL Bugs: #75656: 5.7.5 skip_name_resolve stops all connection attempts

Bug #75656	5.7.5 skip_name_resolve stops all connection attempts
Submitted:	27 Jan 2015 16:01	Modified:	13 Oct 2015 16:18
Reporter:	IGG t	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Options	Severity:	S2 (Serious)
Version:	5.7.5	OS:	Windows (windows 7)
Assigned to:		CPU Architecture:	Any
Tags:	Connection, skip_name_resolve

Description:
I have just installed a clean version of MySQL 5.7.5 (installed from the zip file). I created the windows service. 
Finally I made a few changes to the my.ini file and started the service.

I tried to connect to MySQL, and was unable to:

c:\mysql\57\2\bin>mysql.exe -hlocalhost -P3338 -uroot
ERROR 1130 (HY000): Host '::1' is not allowed to connect to this MySQL server

I tried -h127.0.0.1 with the same result:
c:\MySQL\57\2\bin>mysql.exe -h127.0.0.1 -P3338 -uroot
ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to this MySQL server

After a process of elimination I found the entry in the my.ini file that was causing this was:
skip_name_resolve

hashing this out and restarting the service allowed me to connect fine.

I have various other MySQL databases running on this same machine (5.5 and 5.6) with this in the my.ini file which all work fine.

How to repeat:
install MySQL 5.7.5 on windows 7. 
Open my.ini and add the line:
skip_name_resolve

Start the Database Service

Try and connect

Stop the Database Service

Hash out skip_name_resolve in the my.ini file

Start the Database Service

Try and Connect

Thank you for the bug report!  Verified as reported:

R:\ade\mysql-5.7.6-m16-winx64>start bin\mysqld --no-defaults --port=3312 --console --skip-name-resolve

R:\ade\mysql-5.7.6-m16-winx64>bin\mysql -uroot -P3312
ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to this MySQL server

I seem this is not server-side problem.
5.7.5's mysql_install_db only creates root@localhost, doesn't create root@127.0.0.1.

$ bin/mysql_install_db --basedir=./ --datadir=data --verbose=1
2015-02-04 17:13:08 [NOTE]    Creating data directory data
2015-02-04 17:13:08 [NOTE]    Generating random password to /home/ttanaka/.mysql_secret...done.
2015-02-04 17:13:08 [NOTE]    Executing /usr/mysql/5.7.5/bin/mysqld --no-defaults --bootstrap --datadir=data --lc-messages-dir=./share --lc-messages=en_US --basedir=.
2015-02-04 17:13:11 [NOTE]    Creating system tables...done.
2015-02-04 17:13:11 [NOTE]    Filling system tables with data...done.
2015-02-04 17:13:12 [NOTE]    Filling help table with data...done.
2015-02-04 17:13:12 [NOTE]    Creating default user root@localhost
2015-02-04 17:13:12 [NOTE]    Creating default proxy root@localhost
2015-02-04 17:13:13 [NOTE]    Success!

I found entry in relnote.

http://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-5.html

But that should be emphasized in release note, "mysql_install_db creates *only one* user which is specified in --admin-user and --admin-host, their defaults are root@localhost".

This behavior is not serious for Linux users, because of they generally use unix-socket to log-in as root account.
But Windows users generally use TCP/IP socket, I think.

I tried it with localhost, it also didn't work

C:\MySQL\57\2\bin>mysql -hlocalhost -P3338 -uroot
ERROR 1130 (HY000): Host '::1' is not allowed to connect to this MySQL server

Patch for mysql_install_db.cc, default admin-host set 127.0.0.1 in Windows build. (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: Bug75656.patch (application/octet-stream, text), 740 bytes.

I patched mysql_install_db for setting --admin-host's implicit value to 127.0.0.1 in Windows build.

But I don't have environment which can build MySQL on Windows.. :(

@IGG t

You can avoid this issue by following way.

1. Starting mysqld without skip-name-resolve,
2. Logging in mysql,
3. Updating mysql.user like `UPDATE mysql.user SET host= '127.0.0.1' WHERE user = 'root' AND host = 'localhost';`
4. Restarting mysqld with skip-name-resolve.
5. Connecting to 127.0.0.1.

>bin\mysql -uroot -h127.0.0.1
ERROR 1130 (HY000): Host '127.0.0.1' is not allowed to connect to this MySQL server

::Restart without skip-name-resolve::

>bin\mysql -uroot -h127.0.0.1
mysql> UPDATE mysql.user SET host= '127.0.0.1' WHERE user= 'root' AND host= 'localhost';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

::Restart with skip-name-resolve::

>bin\mysql -uroot -h127.0.0.1
Welcome to the MySQL monitor.  Commands end with ; or \g.

If you want to connect via IPv6's localhost("::1"), please modify UPDATE statement which I provided.

Posted by developer:
 
--skip-name-resolve is not supported out of the box in 5.7 anymore. 
If you want to use this option you need to also amend the user definitions so they work with it. 

We do plan on adding a warning that certain accounts will never match if that mode is on when reading mysql.user.

http://dev.mysql.com/doc/refman/5.7/en/server-options.html#option_mysqld_skip-name-resolve says:

An attempt to connect to the host 127.0.0.1 normally resolves to the localhost account. However, this fails if the server is run with the --skip-name-resolve option, so make sure that an account exists that can accept a connection. For example, to be able to connect as root using --host=127.0.0.1 or --host=::1, create these accounts:

CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY 'root-password';
CREATE USER 'root'@'::1' IDENTIFIED BY 'root-password';

So, localhost will not work with --skip-name-resolve. There was some suggestion that it would work earlier in the option description; I'll remove that.