Mysql_Connect (Localhost/127.0.0.1) Slow on Windows Platform

mysql_connect (localhost / 127.0.0.1) slow on Windows platform

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.

localhost vs. 127.0.0.1 in mysql_connect()

1. Differs between Windows and Linux. If you use a unix domain socket it'll be slightly faster than using TCP/IP (because of the less overhead you have).
2. Windows is using TCP/IP as a default, whereas Linux tries to use a Unix Domain Socket if you choose localhost and TCP/IP if you take 127.0.0.1.

mysql_connect warning, database host issue

Are you using Linux? Then it happens because of the sockets. If you specify 127.0.0.1 PHP uses TCP/IP communication. If you type in localhost it tries to communicate with the socket (mysql.sock). It is possible that MySQL is not configured to support them or your system is blocking PHP from accessing the socket file.

EDIT: Possibly related: mysql_connect (localhost / 127.0.0.1) slow on Windows platform

PDO slow on PHP 5.4 and Apache 2.4.3

It was just a guess - but on windows machines changing to 127.0.0.1 instead of localhost does the trick.

PHP to EasyPHP MySQL server 1 second connection delay

_{If you are having this problem and using a version of Windows before Windows 7, this is probably not the answer to your problem.}

Why is this happening?

The cause of this problem is IPv4 vs IPv6.

When you use a host name instead of an IP address, the MySQL client first runs an AAAA (IPv6) host lookup for the name, and tries this address first if it successfully resolves the name to an IPv6 address. If either step fails (name resolution or connection) it will fallback to IPv4, running an A lookup and trying this host instead.

What this means in practice is that if the IPv6 localhost lookup is successful but MySQL is not bound to the IPv6 loopback, you will need to wait for one connection timeout cycle (evidently on the OP's machine this is 1 second) before the IPv4 fallback occurs and the connection succeeds.

This was not an issue prior to Windows 7, because localhost resolution was done via the hosts file, and it came preconfigured with only 127.0.0.1 - it did not come with it's IPv6 counterpart ::1.

Since Windows 7, however, localhost resolution is built into the DNS resolver, for reasons outlined here. This means that the IPv6 lookup will now succeed - but MySQL is not bound to that IPv6 address, so the connection will fail, and you will see the delay outlined in this question.

That's Nice. Just tell me how to fix it already!

You have a few options. Looking around the internet, the general "solution" seems to be to use the IP address explicitly instead of the name, but there are a couple of reasons not to do this, both portability related, both arguably not important:

• If you move your script to another machine that only supports IPv6, your script will no longer work.

• If you move your script to a *nix-based hosting environment, the magic string localhost would mean the MySQL client would prefer to use a Unix socket if one is configured, this is more efficient than IP loopback based connectivity

They sound pretty important though?

They aren't. You should be designing your application so that this sort of thing is defined in a configuration file. If you move your script to another environment, chances are other things will need configuring as well.

In summary, using the IP address is not the best solution, but it is most likely an acceptable one.

So what's the best solution?

The best way would be to change the bind address that the MySQL server uses. However, this is not as simple as one might like. Unlike Apache, Nginx and almost every other sane network service application ever made, MySQL only supports a single bind address, so it's not just a case of adding another one. Luckily though, operating systems do support a bit of magic here, so we can enable MySQL to use both IPv4 and IPv6 simultaneously.

You need to be running MySQL 5.5.3 or later, and you need to start MySQL with the --bind-address= command line argument (or set the corresponding option in my.ini). You have 4 options^docs, depending on what you want to do:

• The one you are probably familiar with, and the one that you are most likely (effectively) using, 0.0.0.0. This binds to all available IPv4 addresses on the machine. This actually is probably not the best thing to do even if you don't care about IPv6, as it suffers the same security risks as ::.

• An explicit IPv4 or IPv6 address (for example 127.0.0.1 or ::1 for loopback). This binds the server to that address and only that address.

• The magic string ::. This will bind MySQL to every address on the machine, both loopback and physical interface addresses, in IPv4 and IPv6 mode. This is potentially a security risk, only do this if you need MySQL to accept connections from remote hosts.

• Use an IPv4-mapped IPv6 address. This is a special mechanism built into IPv6 for backwards compatibility during the 4 -> 6 transition, and it allows you bind to a specific IPv4 address and it's IPv6 equivalent. This is quite unlikely to be useful to you for anything other than the "dual loopback" address ::ffff:127.0.0.1. This is most likely the best solution for most people, only binding to the loopback but allowing both IPv4 and IPv6 connections.

Do I need to modify the hosts file?

NO. Don't modify the hosts file. The DNS resolver knows what to do with localhost, redefining it will at best have no effect, and at worst confuse the hell out of the resolver.

What about --skip-name-resolve?

This may also fix the problem/be required to fix the problem, for a related but slightly different reason.

Without this configuration option, MySQL will attempt to resolve all client connection IP addresses to a hostname via a PTR DNS query. If your MySQL server is already enabled to use IPv6 but connections are still taking a long time, it may be because the reverse DNS (PTR) record is not correctly configured.

Disabling name resolution will fix this problem, but it does have other ramifications, notably that any access permissions configured to use a DNS name in the Host condition will now fail.

If you are going to do this, you will need to configure all your grants to use IP addresses instead of names.

Can't connect to local hosted mysql using PHP script

What is the output of the following snippet?

<?php

// test.php

$host = 'localhost:8889';
$username = 'root';
$password = 'root';
$database = 'your_database';

if(!$connection = mysql_connect($host, $username, $password))
  die('Error connecting to '.$host.'. '.mysql_error());

if(!mysql_select_db($database))
  die('Error selecting '.$database.'. '.mysql_error());

echo 'Connection successful.';

?>

If this executes without an error, then you're probably just setting your constants incorrectly. Note that mysql_connect takes the port as part of the host parameter, not as a separate argument.

---

Related Topics