MySQL Bugs: #71192: Please report socket connections properly and not as localhost

Bug #71192	Please report socket connections properly and not as localhost
Submitted:	20 Dec 2013 15:45	Modified:	23 Dec 2013 15:23
Reporter:	Simon Mudd (OCA)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S3 (Non-critical)
Version:	all versions to 5.7.3	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
This has been like this forever but it also sometimes causes a lot of confusion, or can do under certain circumstances, and it really shouldn't be that hard to do the right thing to make things clearer.

localhost has 2 meanings in MySQL:
(1) the hostname that usually resolves to 127.0.0.1
(2) the unix socket path which MySQL might be listening on

I'm not 100% sure whether we can include IPv6's ::1 too

However, in SHOW PROCESSLIST the connection is shown as coming from localhost so it's not obvious which connection is being used as all you see is localhost.

How to repeat:
configure a server connection like this:

$ cat .my.cnf
[client]
user=xxxx
socket=/path/to/datadir/mysql.sock
password=verysecret

Connect to the server and do SHOW PROCESSLIST. you'll see the connection seems to come from localhost.

Connect to the server using ip v4 address 127.0.0.1 and probably you'll see the same thing.
Connect to the server using ip v6 address ::1 and you'll probably see the same thing.

All potentially confusing.

Suggested fix:
I would suggest showing the unixpath such as /path/to/datadir/mysql.sock above. That would be clearly different to a hostname and would not confuse. I'm not sure what you'd show in Windows land, but something with a windows path probably would make sense.

So perhaps the current behaviour is something that we're all brought up to know as being normal but I think it would be better to clean this up.

Note this would also affect grants as grants creation/usage also overloads the localhost name, so would need to be taken into account too.

The reason is simply to identify the origin of connections especially since there are now 3 different types of localhost access methods.

Thank you for the feature request.

How should that be possible? To my best knowledge most *nix systems will silently translate 'localhost' (but not 127.0.0.1) to the socket for the service and MySQL (nad other daemons) will even not know that 'localhost' was used/specified by the client.

This is an issue with Linux/Unix and not an issue with MySQL as far as I can understand.

a Unix SOCKET is a bad joke in today's networked world IMO (.. and I don't expect SM to agree!).

One reason for using unix sockets: security. If you only allow access to unix sockets the connection MUST be local and not from the network. So I tend to configure the "root" account to only work from there, thus preventing access from outside.

Yes, ::1 or 127.0.0.1 have the same behaviour and right now from a performance point of view I am not sure if there is a difference in performance. This may depend on the platform concerned.

In any case the reason for the request is that you can not identify currently from any of the internal MySQL tables the exact origin of the connection if it says localhost.  I've been confused by that in the past and speaking to others this does crop up from time to time. This ticket was inspired by a conversation with someone where I was not able to immediately identify the source of a connection so I had to check on the client to be sure how it was connecting to the server. That should not be necessary.

Note: Althought the Category has been assigned Server: I_S there are also P_S tables which include hostnames which are equally affected.

IPv6 connections to ::1 seem to behave the same though this may be the resolver.

myhost:~ root# mysql --host=::1 --user=root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5
Server version: 5.6.14-log MySQL Community Server (GPL)
...
mysql> show processlist;
+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host            | db   | Command | Time | State | Info             |
+----+------+-----------------+------+---------+------+-------+------------------+
|  5 | root | localhost:62375 | NULL | Query   |    0 | init  | show processlist |
+----+------+-----------------+------+---------+------+-------+------------------+
1 row in set (0.00 sec)

mysql> exit

see this also:
http://dev.mysql.com/doc/refman/5.6/en/socket-instances-table.html