mysql_connect (localhost / 127.0.0.1) slow on Windows platform

Question

I am using Windows 7, Apache 2, PHP 5, MySQL 5, all are on the same machine. I have found an interesting issue, I have the following code:

    $sql = "select * from user1";
    $conn = mysql_connect("localhost", "root", "xxxxxxxx");
    mysql_select_db("test1");
    mysql_query("set names utf8");
    $result = mysql_query($sql, $conn);
    while ($row = mysql_fetch_assoc($result)){
        foreach ($row as $key => $value){
            echo $key." => ".$value." || ";
        }
        echo "<br/>";
    }
    mysql_free_result($result);
    mysql_close($conn);

The running time for the above code is over 1 second.

When I use 127.0.0.1 instead of localhost, the running time is around 10 ms.

I tried to find the underlying reason on the internet, and this is the result:

I recently moved my development from XP to Windows 7 and found that webpages I had developed were taking 5 seconds long to load. This was unacceptable of course so I had to track down the problem. I eventually tracked down the offending function/method pdo::construct. I also found that mysql_connect was taking about 1 second to make a connection. After a little googling I found an explaination that php had issues with IPv6 and that you could fix the problem by either disabling IPv6 or switching to the ipaddress 127.0.0.1 when making your connection.

I wonder what the issue of IPv6 on PHP is, just want to get a deeper understaning. Thanks.

Solution 1

PHP is attempting to open a connection to localhost. Because your computer is connected to your network via IPv6 it's trying the IPv6 version of 'localhost' first, which is which is an IP address of ::1

http://en.wikipedia.org/wiki/IPv6_address#Special_addresses

::1/128 — The loopback address is a unicast localhost address. If an application in a host sends packets to this address, the IPv6 stack will loop these packets back on the same virtual interface (corresponding to 127.0.0.0/8 in IPv4).

It looks like your MySQL server isn't listening to that address, instead it's only bound to an IPv4 address and so once PHP fails to open the connection it falls back and tries to open localhost via IPv4 aka 127.0.0.1

I personally prefer to use either IP addresses or use ether the Windows hosts file or Mac equivalent to define 'fake' domain names and then use those when connecting to MySQL, which resolve to IP addresses. Either way I can know exactly whether an IPv4 or IPv6 address will be used.

Both MySQL and Apache support IPv6 but you have to tell them to use an IPv6 address explicitly. For MySQL see: http://dev.mysql.com/doc/refman/5.5/en/ipv6-server-config.html

For Apache config see: http://httpd.apache.org/docs/2.2/bind.html

Apache supports multiple IP addresses so you can use both at once - if the network card in the machine has both an IPv4 and IPv6 address. MySQL only supports one address.

Solution 2

PHP is trying to connect to "localhost" in Windows 7/8/10 it is ::1, but MySQL is not listening on IPv6 sockets, you can apply several fixes:

1) In your host file (C:/windows/system32/drivers/etc/host) set localhost to 127.0.0.1

2) In PHP the MySQL server change from localhost to 127.0.0.1

3) In my.ini, add or edit: bind-address = ::

If the address is ::, the server accepts TCP/IP connections on all server host IPv4 and IPv6 interfaces. Use this address to permit both IPv4 and IPv6 connections on all server interfaces.

Suggested option if you have MySQL >= 5.5.3

Comments

[konqi]

Is your mysql server running on ::1? If not php will probably attempt connecting via ipv6 and if that fails it will fall back to ipv4. That should require a bit longer.

[WaiKit Kung]

Sorry for that I don't understand what "Is your mysql server running on ::1?" means? Is it meant that running several mysql servers on the same machine?

[1' OR 1 --]

Thanks for this question. I just sped up my website tremendously by using 127.0.0.1 instead of localhost.

[WaiKit Kung]

I have done some steps to look into the problem. I first went to C:/Windows/System32/drivers/etc/ to look at the windows host file and found that all information inside was commented. So I uncommented "127.0.0.1 localhost" and execute the mysql connetion. The execution time is around 10 ms. Then I went back to the host file, commented "127.0.0.1 localhost" and uncommented "::1 localhost". After that, I ran the program again. As a result, the program could not make connection to the mysql server.

[WaiKit Kung]

The version of the mysql server is 5.1.03. Therefore, I conclude that the problem may probably be due to the lack of support of ipv6 by Mysql server. So solutions may be avoiding using ipv6 or enabling MySQL server ipv6 support. Any suggestions and complements are welcomed.

[WaiKit Kung]

I use "netstat -an" through cmd and find that there are two lines: "TCP 0.0.0.0:80 0.0.0.0:0 LISTENING" and "TCP 0.0.0.0:3306 0.0.0.0:0 LISTENING". But there are no [::]:80 or [::]:3306. Is this meant that both apache and mysql don't support ipv6?

[Danack]

They support them - added links to the main answer.

[Evan Lee]

bind-address = :: helped me!

[Jose Nobile]

@marcovtwout Which MySQL version? What error did you get?

[marcovtwout]

@Jose Nobile If I understand correctly, resolving to localhost is no longer done from the hosts file. See stackoverflow.com/a/15436435/729324

[Jose Nobile]

@marcovtwout from my test, localhost follow the host file. Anyway, the recommended option is #3