MySQL Bugs: #31577: Allow the localhost setting to access the localhost (127.0.0.1) port not socket

Bug #31577	Allow the localhost setting to access the localhost (127.0.0.1) port not socket
Submitted:	13 Oct 2007 5:47	Modified:	23 Apr 2009 15:35
Reporter:	Marc Perkel
Status:	Not a Bug
Category:	Server	Severity:	S1 (Critical)
Version:	5.0.27	OS:	Any
Assigned to:		Target Version:
Tags:	localhost, tcp, socket, override

Description:
The localhost behavior is INSANE. Usually localhost=127.0.0.1.

What I need is an override so that when "localhost" is specified that it does a TCP
connection to 127.0.0.1 and not the unix socket.

I am trying to move the MySQL DB from the main web server to another server. There are
hundreds of applications that would take forever to find and reconfigure. Stuff set up by
other people. So what I want to do is remove MySQL and put it on another machine and use
and SSH tunnel to connect them.

How to repeat:
set host to localhost

Suggested fix:
Add an option to my.cnf to disable sockets and make localhost access the localhost.

I just wanted to add a workaround for all the people who are running into the same problem
I'm having. This is what I did to get aroun this limitation. First I use socat to create a
unix socket and pipe it to the TCP port 3306 on 127.0.0.1.

socat UNIX-LISTEN:/var/lib/mysql/mysql.sock,fork,user=mysql,group=mysql,mode=777
TCP:localhost:3306 2> /dev/null &

Then this connects the local port 3306 to the remote server. I use autossh which is a
wrapper around ssh tunnels but can reestablish the connection should it break.

autossh -M 3337 -f -N -g -L 3306:localhost:3306 mysqlserver.domain.com

What would be nice is some sort of setting in the my.cnf file that would eliminate the
need for the socat trick.

--protocol=tcp forces a TCP connection even if host is localhost. Can be used on the
command line,
or in a my.cnf file like this:

[client]
protocol=tcp

Thank you for the bug report. Please see Paul's comment.

I see Paul's comment but I tried it and it doesn't work. I added:

# The following options will be passed to all MySQL clients
[client]
port            = 3306
protocol        = tcp 

But the setting is ignored.

Apparently the rule to use the socket when specifying localhost is hard coded into the
client library. Out here in the real world localhost means 127.0.0.1. MySQL redefines
localhost to use a socket. This is INSANE. When I specify localhost I mean to use
localhost - not the socket!

I'm trying to make it clear that I DON'T WANT TO USE THE SOCKET !!!!

I have hundreds of user installed apps that other people have configured and they all
specify localhost which is the default so going back and changing them all isn't an
option. I feel like I'm talking to a brick wall here. What is it going to take to get
someone to WAKE UP AND FIX THIS BUG!!!!!

Localhost = 127.0.0.1

FIX IT!!

It appears that my socat solution isn't stable either. It randomly just quits working.
This is really beginning to piss me off. I'm going to start writing articles about this
bug. It seems that when I google it that the problem is widely known and has been
ignored.

Thank you for the feedback.

I actually can not repeat described behaviour:

apple@apple ~
$mysql50 --protocol=tcp --port=3351 -hlocalhost
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 468
Server version: 5.1.23-beta-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> \q
Bye

apple@apple ~
$mysql50 --protocol=tcp --port=3350 -hlocalhost
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 473
Server version: 5.0.52-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> 

Please check if your client library is looking to [client] section of MySQL configuration
file

Is a bug. Quit switching it to not a bug. Why don't you google it and you'll see how many
people are struggling with this same problem.

Install Wordpress configured for localhost.

Then using any configuration commands show me how to get it to NOT use the Unix socket.

If you think this isn't a bug then state the procedure to get PHP applications that are
configured to LOCALHOST to use TCP/127.0.0.1 and not the socket. The MySQL client library
insists on overriding the settings and using the socket instead.

DON'T SWITCH THE STATUS TO NOT A BUG AGAIN UNTIL YOU FIX THIS BUG!!!

I wish someone would reply who at least understands the problem.

The 'bug' (besides the debatable "localhost" design choice
which can't be reverted at this point anymore) is that
PHP by default comes with a bundled mysql client library
that is 

 a) rather old (which is not really MySQLs fault)

 b) does not know where to look for my.cnf on the
    client it runs on so that it doesn't bother to 
    check for settings in the [client] section 

I'm not perfectly sure whether this really works
but i *think* recompiling PHP against the libmysqlclient
that came with the actual MySQL server binary you're
using solves this, at leats for the newer ext/mysqli.

Whether the older ext/mysql (which is deprecated by now)
already supported reading [client] settings from my.cnf
i do not remember, i think it doesn't though.

Anyway, what you try to get accepted as a bug was
indeed a design choice that is not going to be
reverted now for backwards compatibility reasons.

And PHP not being able to work around this out of
the box even when the right configuration options
are present in my.cnf [client] isn't something
to be discussed here either, this is something you'd
have to bring up on http://bugs.php.net/ or the
PHP developer mailing lists instead.

... or you might actually look into MySQL Proxy to solve your port forwarding issues? 

I'm not 100% sure, but as far as i know it can listen
on a domain socket and have it forward to the other 
machine?

I have to agree with everyone here saying this is a bug.

localhost means localhost, not some stupid socket.

Right now I am trying to get 400+ websites to point to another mysql server. This would
be no problem, if mysql's client library's did not foolishly redefine 'localhost' to a
socket.

You guys really need to fix this. Its not going to hurt anyone in any big way if mysql
listens on 127.0.0.1:3306 anyway.

I also think it is a bug.  Not only does it not work in PHP - it does not work in C
either.  Right now we are going through and updating all of our applications to use
127.0.0.1 instead of localhost.  There is no way in the API to set the protocol - socket
vs. port.  Even if for backward compatibility you need to leave things the way they are,
you can add an option in the API to override it.  Then PHP can be fixed as well.

Hello

I have the same problem. I'am  going now to do a load balancing cluster of apache
webservers, wich have to use a central mysql server. It is a half commercial hosting so i
can not just change the configuration and tell the clients they should rewrite their
config files. No this is not the way.

I'am using mysql-proxy for my own applications wich are configured to use 127.0.0.1:3306.
Mysql-proxy 0.6.1 IS NOT CAPABLE to bind to a socket. It just can use a socket for backend
mysql.

I would be very happy if this would be cleared.

I agree this is a bug ... I have a similar problem when issuing requests from php and from
python (could well be any other language I guess).

I would put the limit even further: connections should *always* be performed through tcp
by default, while connecting through socket should require some specific keywords in the
url (like mysqls:/var/lib/mysql32/mysql.sock32 or whatever...).

I also have to agree that this is a bug.  In fact I also have to agree that it is insane. 
How can you claim that you can't revert it for backwards compatibility reasons when this
change broke backwards compatibility with tons of systems that relied on the fact that
--host=localhost meant to generate a TCP connection to localhost, like every other
program does and mysql did forever previously?  Aren't you breaking more backwards
compatibility by LEAVING THE CHANGE IN?

Perhaps some sort of compromise is possible, like when host and/or port are specified
explicitly then the connection will automatically use TCP rather than sockets?

It certainly seems to violate the rule of least surprise when I open an ssh tunnel from
the local port 33060 to a remote 3306 (`ssh -L localhost:33060:localhost:3306
remotehost') and yet the command `mysql -hlocalhost -P33060' manages to connect to the
local MySQL instance listening only to the socket and on local port 3306.

I'm using the python mysql client and would also like to specify that connections to
localhost use the tcp socket instead of the unix domain socket. The available options to
"connect(...)" at http://mysql-python.sourceforge.net/MySQLdb.html don't look like they
support this either.

Here's a work around until MySQL stops ignoring the REAL BUG!!!

socat
UNIX-LISTEN:/var/lib/mysql/mysql.sock,fork,reuseaddr,unlink-early,user=mysql,group=mysql,mode=777
TCP:mysql.example.com:3306 &

Just adding my grievances... I've just spent many hours trying to get a tunnel working,
following many of the tutorials on the 'net. Many of those actually (and, apparently all
of which I tried) mention -h localhost, even though it doesn't work that way. 

Thanks to Mitch Frazier at Linux Journal to point out the problem (and solution).
If everyone expects a certain behaviour, and it doesn't work that way, then it's a bug...
Particularly if man pages and help files don't mention the unusual behaviour.

I too agree it's a bug. I don't have a problem with it defaulting to the socket, and I
understand why that's desirable, but localhost=localHOST, not localsocket, and getting
around it when you actually want localHOST is just too difficult. It's also a shame to
change the system wide connection default just because you want to actually specify
localHOST.

At time where this code was written UNIX socket file connection was faster than TCP
connection. 

So for UNIX machines default behavior is using (default) socket file, unless 'host'
parameter has numeric TCP address .

If you wish to force client to connect via TCP there is option to do so.
(protocol=tcp for config file and MYSQL_OPT_PROTOCOL option for other connectors).

This was design decision made many years ago, so please stop complaining. In any case, if
something is aligned with documentation it cannot be a bug.
(Even if many people think so).