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.

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

Answer 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