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 3:47	Modified:	9 Dec 2010 17:11
Reporter:	Marc Perkel	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.27	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	localhost, override, socket, tcp

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).

I ran into the same problem, and I'm pretty sure the perl client library I need to use is not looking at the [client] section /etc/my.cnf.  I tried that first to no avail.

One work-around that seems to be effective is making another entry in /etc/hosts for "localhost" and then ask your mysql client library to connect to that.  Ex:

# echo "127.0.0.1    foobarbaz" >> /etc/hosts

Now make your client connect to "foobarbaz", which is actually 127.0.0.1.

Hope that helps...

This should be changed in 5.5 or 5.6.  It is ludicrous that localhost is interpreted to mean unix domain sockets.  

connecting to a HOST with -h should use the local name resolver

This is both aggravating and confusing to admins not experienced with MySQL administration and the historical reasons for preferring domain sockets of TCP sockets.

I think domain socket should be a compile option in MySQL 5.6 and that MySQL shouldn't use domain sockets unless they are compiled in and I ask that they are used.

Change this to FEATURE REQUEST, not "not a bug".

--Justin

It seems that people nowadays can't imagine communication methods besides TCP/IP. However, there are so many of them. Why should the selection of a certain host name imply a certain communication means?

"localhost" at first means the host on which the program is running. If a MySQL application claims that the server runs on the local host, why shouldn't MySQL select the most efficient communication means, unless the user forces a certain one?

People have described ways to force certain methods, but they work for the MySQl clients only. There are also ways that work with the C API, and probably for the connectors too.

But as far as I understand, people can not, or do not want to, change their applications. These applications use "localhost" to locate the MySQL server. Now they want to play tricks with tunnels and the like to let applications, which are designed for the local host, to connect to other hosts. Now I wonder, where is the bug?

MySQL has all means to connect to other hosts. It can used compressed and uncompressed connections. It can use connections with or without SSL. It is really ridiculous to let the client connect to "localhost" only, and to use tunnels to other hosts. Why would anybody want to develop a MySQL application that cannot select the host to connect to?

Anyway, let's assume for a moment, MySQL would change. It would interpret "localhost" as a request to use a TCP/IP connection. At that moment all existing applications would use TCP/IP on the local host. That would slow down many thousands of installations. If not really slow down, then at least increase CPU usage. If these applications would want to go back to their known efficiency, they would have to use the protocol option, which the poeple in this thread don't want, or cannot use. While it might be an interesting experiment to see, how many complaints this would produce, I wonder, what would be a safe bet for MySQL? Dropping efficiency for many thousands of installations, which really want to connect to a local server, or withstand the complaints of a few dozen users with broken applications?

Disclaimer: I might have misunderstood the real problem. If so, can someone please explain in more detail, what it is about?

Ingo,
IMU the biggest frustration of users (with limited mysql experience) is that when port value (e.g. 3306) is explicitly  provided, it is just ignored. More logically it would be forcing TCP connection in such cases (if no explicit --protocol parameter):

localhost = socket file
localhost:3306 = TCP connection

But I agree that we shouldn't change this because of possible problems with existing deployments.

The concern is breaking existing installations. What about adding some sort of parameter such as:

do_not_use_socket=yes

in order to force a tcp/ip connection?

The problem I started with was an old server with hundreds of web sites running a lot of different MySQL apps all configured by default to LOCALHOST. As the system grew it made more sense to add a dedicated MySQL server and redirect the request to the external server. The first attempt was to use an SSH tunnel assuming that LOCALHOST meant 127.0.0.1 as it commonly means.

Eventually I managed to get socat to work to do the redirection but it would be better if there were a more elegant solution.

Maybe a better solution would be a LOCALHOST override that would essentially redifine the meaning of LOCALHOST.

redefine_localhost=mysql.mydomain.com

This would tell MYSQL to treat the LOCALHOST settiong to mean something else.

"A Unix socket file is used if you don't specify a host name or if you specify the special host name localhost."

This is a gratuitously meddlesome design decision.  localhost has meant 127.0.0.1 for decades.  MySQL's current special case behavior for localhost provides negligible convenience value, makes configuration unnecessarily opaque, and has causes configuration problems or other unintended consequences[1].  It's an acknowledged common problem[2]. And, it surely requires more code in mysql itself.

Recommendation: Deprecate this feature immediately. Create a switch to preserve current behavior. Switch defaults to ON for a release or two, then off, and gut the code on the next major release.

[1] http://bugs.mysql.com/bug.php?id=41570
[2] http://dev.mysql.com/doc/refman/5.5/en/can-not-connect-to-server.html

Sorry to necro an old post but feel this should help someone who may stumble on it looking for an answer

You should not need to force a TCP connection when using a UNIX socket.
Ensure your my.cnf is set correctly to be able to communicate with mysql locally
For example:
[mysql.server]
datadir=/home/mysql/users
socket=/home/mysql/users/mysql.sock

[mysqladmin]
socket=/home/mysql/users/mysql.sock

[client]
port=3306
socket=/home/mysql/users/mysql.sock

[mysqld]
port=3306
datadir=/home/mysql/users
socket=/home/mysql/users/mysql.sock
user=mysql

[mysql]
socket=/home/mysql/users/mysql.sock

Try the above, restart mysqld, then do:
mysqladmin version

You should see something like the following:

Server version          5.5.16
Protocol version        10
Connection              Localhost via UNIX socket
UNIX socket             /home/mysql/users/mysql.sock
Uptime:                 1 min 40 sec

For applications such as PHP that define the socket file at compile time you may need to recompile mySQL and PHP for them to communicate properly or you can define the socket in the php.ini file

php.ini:
mysql.default_socket = /home/mysql/users/mysql.sock
mysqli.default_socket = /home/mysql/users/mysql.sock
pdo_mysql.default_socket = /home/mysql/users/mysql.sock

For compilation configure with mySQL:
--with-unix-socket-path=/home/mysql/users/mysql.sock

For compilation configure with PHP:
--with-mysql-sock=/home/mysql/users/mysql.sock

The above will change the UNIX socket for all mysql extensions at compile time.

So this issue is not really a bug, but a lack of configuration.
Hope this helps someone who had the same issue I did.

References:
mySQL
http://dev.mysql.com/doc/refman/5.0/en/problems-with-mysql-sock.html
http://dev.mysql.com/doc/refman/5.0/en/source-configuration-options.html
http://dev.mysql.com/doc/refman/5.0/en/option-files.html

PHP
http://php.net/manual/en/ref.pdo-mysql.php
https://bugs.php.net/bug.php?id=41422

Sorry guys, but I'm still running into this problem. Most of the above comments are beside the point because they fail to understand Marc Perkel's use case, which is the same as mine.

The case is as follows. We run a webserver on which we host websites (in my case, over 250 sites, with over a million files). Lots of sites connect to our MySQL database, mostly through PHP. We now want to migrate the server environment such that in the new situation, the MySQL server still listens on localhost:3306, but it can no longer listen on a local socket. (The MySQL server will run elsewhere; using networking configuration trickery, we can connect it through to localhost:3306, but we can't make it listen to a local socket.)

The challenge is now to make the existing sites understand they will actually need to connect to localhost:3306, rather than a local socket that no longer exists, *without rewriting them*. We can't rewrite them for the following reasons: 1) those 1 million files aren't ours; we just host the sites. 2) Even if we get permission to rewrite them, we don't know any of that code and it's undoable to correctly rewrite all MySQL calls that may or may not occur anywhere in those million files. 3) Even if we can leave the calls alone and only rewrite a bunch of configuration files with MySQL connection parameters, it's not easy to identify those configuration files. We don't know for sure what our users use to connect to their databases.

However, all of that code uses the same MySQL client library code. If the library had a configuration file with an option to specify: always interpret localhost as 127.0.0.1 and never as a local socket, then Marc Perkel and I could edit that file and our migration would be good to go.

That is the feature we need. All comments above suggesting changes to the MySQL server configuration are beside the point. The issue is at the client end, and while we control the server, we don't control the client code.