MySQL Bugs: #81441: Warning about localhost when using skip-name-resolve

Bug #81441	Warning about localhost when using skip-name-resolve
Submitted:	16 May 2016 21:55	Modified:	19 Dec 2018 5:33
Reporter:	monty solomon	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.7	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
When I start the server I get warnings similar to

2016-05-16T21:42:01.795474Z 0 [Warning] 'user' entry 'rw_user@localhost' ignored in --skip-name-resolve mode.
2016-05-16T21:42:01.795639Z 0 [Warning] 'db' entry '% rw_user@localhost' ignored in --skip-name-resolve mode.
2016-05-16T21:42:01.796601Z 0 [Warning] 'proxies_priv' entry 'rw_user@localhost @' ignored in --skip-name-resolve mode.

but when I log in to the server using a proxy user mapped to that user it works properly. What does it mean when it states that the user is ignored?

How to repeat:
mysql> show grants\G
Grants for rw_user@localhost: GRANT SELECT, INSERT, UPDATE, DELETE, EXECUTE, SHOW VIEW ON `%`.* TO 'rw_user'@'localhost'

mysql>  show create user rw_user@localhost\G
CREATE USER for rw_user@localhost: CREATE USER 'rw_user'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*redacted' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK

There are similar warnings about the 'root@localhost' user

2016-05-16T21:42:01.795354Z 0 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
2016-05-16T21:42:01.796577Z 0 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.

# mysql -uroot

mysql> show grants;
+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION        |
+---------------------------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select distinct User,Host from mysql.user where User='root';
+------+-----------+
| User | Host      |
+------+-----------+
| root | localhost |
+------+-----------+
1 row in set (0.01 sec)

mysql> status

Current user:		root@localhost
Connection:		Localhost via UNIX socket

Hi Monty,

Thank you for your bug report. However, this is not a bug. If skip-name-resolve is used, how do you expect that "localhost" is resolved ????

Try using instead 127.0.0.1 and see if you get the warning. If you continue getting it then we shall have to take a look. Otherwise, it is not a bug ....

Your feedback is truly required.

The hostname "localhost" is a special case and it works even when skip_name_resolve is set.

Here is an excerpt from the manual

https://dev.mysql.com/doc/refman/5.7/en/connecting.html

On Unix, MySQL programs treat the host name localhost specially, in a way that is likely different from what you expect compared to other network-based programs.

mysql> select @@skip_name_resolve\G
*************************** 1. row ***************************
@@skip_name_resolve: 1
1 row in set (0.00 sec)

mysql> status
--------------
Current user:		test_user@localhost
Connection:		Localhost via UNIX socket

Hi Monty,

Would you be so kind and point me to the chapter in our user manual where it states that localhost works even when skip_name_resolve is set.

Thanks in advance.

http://dev.mysql.com/doc/refman/5.7/en/windows-testing.html

If mysqld is slow to respond to TCP/IP connections from client programs, there is probably a problem with your DNS. In this case, start mysqld with the --skip-name-resolve option and use only localhost and IP addresses in the Host column of the MySQL grant tables.

Hi Mr. Solomon,

You are correct. This requires a change in our documentation.

Verified as a documentation bug.

How/where is the documentation in error?

The server is working as expected -- it is the warnings that are in error. The warnings state that the "localhost" entries in the user, db, and proxies_priv will be ignored but they are used and not ignored.

mysql> select User,Host from user where User='monty';
+-------+-----------+
| User  | Host      |
+-------+-----------+
| monty | localhost |
+-------+-----------+
1 row in set (0.00 sec)

mysql> select @@skip_name_resolve\G
*************************** 1. row ***************************
@@skip_name_resolve: 1
1 row in set (0.00 sec)

# mysql -umonty -p

mysql> show grants\G
*************************** 1. row ***************************
Grants for monty@localhost: GRANT ALL PRIVILEGES ON *.* TO 'monty'@'localhost' WITH GRANT OPTION
1 row in set (0.00 sec)

mysql> select @@skip_name_resolve\G
*************************** 1. row ***************************
@@skip_name_resolve: 1
1 row in set (0.00 sec)

Not a documentation bug

This warning could be ignored, but it pollutes the logs. As monty solomon already mentioned, 'localhost' is a special case and was treated this way before as well as now too, but now we get this misleading warning.

Could the devs please implement a fix to not produce the warning when the host == 'localhost'?

Changing category to server bug.

I also have those warnings in my logs on MySQL start.

And I also have it when doing a GRANT a user @'localhost'.  Se test case below.

As Anatoli points out, the warnings can be ignored, but this is not a good habit.  I blogged about this in the past:

http://jfg-mysql.blogspot.com/2017/01/do-not-ignore-warnings-in-mysql-mariadb.html

Please consider fixing this to avoid warning fatigue.

Many thanks,

JFG

---

This is my setup with my user list:

mysql [localhost] {root} ((none)) > select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.22    |
+-----------+
1 row in set (0.00 sec)

mysql [localhost] {root} ((none)) > select host, User from mysql.user;
+-----------+---------------+
| host      | User          |
+-----------+---------------+
| 127.%     | msandbox      |
| 127.%     | msandbox_ro   |
| 127.%     | msandbox_rw   |
| 127.%     | rsandbox      |
| localhost | msandbox      |
| localhost | msandbox_ro   |
| localhost | msandbox_rw   |
| localhost | mysql.session |
| localhost | mysql.sys     |
| localhost | root          |
+-----------+---------------+
10 rows in set (0.00 sec)

When restarting MySQL with skip-name-resolve, I have this in the logs:

2018-07-20T13:09:16.498992Z 0 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
2018-07-20T13:09:16.499036Z 0 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-07-20T13:09:16.499049Z 0 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
2018-07-20T13:09:16.499060Z 0 [Warning] 'user' entry 'msandbox@localhost' ignored in --skip-name-resolve mode.
2018-07-20T13:09:16.499070Z 0 [Warning] 'user' entry 'msandbox_rw@localhost' ignored in --skip-name-resolve mode.
2018-07-20T13:09:16.499083Z 0 [Warning] 'user' entry 'msandbox_ro@localhost' ignored in --skip-name-resolve mode.
2018-07-20T13:09:16.499102Z 0 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-07-20T13:09:16.499109Z 0 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
2018-07-20T13:09:16.499117Z 0 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
2018-07-20T13:09:16.499732Z 0 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-07-20T13:09:16.499753Z 0 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.

Let's create a new user, but first, let's show it does not exist:

jgagne@jgagne-mysql-tests-01:~/sandboxes/msb_5_7_22$ ./use -u jfg --password=jfg_pwd
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'jfg'@'localhost' (using password: YES)

mysql [localhost] {root} ((none)) > SELECT password('jfg_pwd');
+-------------------------------------------+
| password('jfg_pwd')                       |
+-------------------------------------------+
| *03529A0422017594FAD0A0D498979FADE08A2BE2 |
+-------------------------------------------+
1 row in set, 1 warning (0.01 sec)

mysql [localhost] {root} ((none)) > CREATE USER 'jfg'@'localhost';
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {root} ((none)) > ALTER USER 'jfg'@'localhost' IDENTIFIED WITH 'mysql_native_password' AS '*03529A0422017594FAD0A0D498979FADE08A2BE2' REQUIRE NONE PASSWORD EXPIRE DEFAULT ACCOUNT UNLOCK;
Query OK, 0 rows affected (0.00 sec)

mysql [localhost] {root} ((none)) > GRANT ALL PRIVILEGES ON *.* TO 'jfg'@'localhost';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql [localhost] {root} ((none)) > SHOW WARNINGS;
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Level | Code | Message                                                                                                                                                    |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Error | 1064 | You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WARNINSG' at line 1 |
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Even if there is a warning above, I can connect with this user:

jgagne@jgagne-mysql-tests-01:~/sandboxes/msb_5_7_22$ ./use -u jfg --password=jfg_pwd
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.22 MySQL Community Server (GPL)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql [localhost] {jfg} ((none)) > 

And if I restart MySQL, I get this in the logs:

2018-07-20T13:32:10.449721Z 0 [Warning] 'user' entry 'root@localhost' ignored in --skip-name-resolve mode.
2018-07-20T13:32:10.449770Z 0 [Warning] 'user' entry 'mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-07-20T13:32:10.449784Z 0 [Warning] 'user' entry 'mysql.sys@localhost' ignored in --skip-name-resolve mode.
2018-07-20T13:32:10.449795Z 0 [Warning] 'user' entry 'msandbox@localhost' ignored in --skip-name-resolve mode.
2018-07-20T13:32:10.449801Z 0 [Warning] 'user' entry 'msandbox_rw@localhost' ignored in --skip-name-resolve mode.
2018-07-20T13:32:10.449816Z 0 [Warning] 'user' entry 'msandbox_ro@localhost' ignored in --skip-name-resolve mode.
2018-07-20T13:32:10.449828Z 0 [Warning] 'user' entry 'jfg@localhost' ignored in --skip-name-resolve mode.
2018-07-20T13:32:10.449846Z 0 [Warning] 'db' entry 'performance_schema mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-07-20T13:32:10.449850Z 0 [Warning] 'db' entry 'sys mysql.sys@localhost' ignored in --skip-name-resolve mode.
2018-07-20T13:32:10.449859Z 0 [Warning] 'proxies_priv' entry '@ root@localhost' ignored in --skip-name-resolve mode.
2018-07-20T13:32:10.450504Z 0 [Warning] 'tables_priv' entry 'user mysql.session@localhost' ignored in --skip-name-resolve mode.
2018-07-20T13:32:10.450525Z 0 [Warning] 'tables_priv' entry 'sys_config mysql.sys@localhost' ignored in --skip-name-resolve mode.

Posted by developer:
 
Fixed in 8.0.14.

When the server was started with the --skip-name-resolve option,
spurious warnings could be written to the error log about ignoring
accounts with a host name part of localhost. (The accounts in fact
were used and not ignored.)

Does this mean it won't be fixed in 5.7?

Please backport the fix into version 5.7.

Will backport to 5.7 as well

Posted by developer:
 
Fixed in 5.7.26.

I've created a followup on Bug #97399 (for grant execution rather than logs)