MySQL client fails to connect to localhost on Windows, due to IPv6
A MySQL client running on Windows 2008 or Windows 7 may fail to connect to the host name of “localhost”. This is due to windows resloving the host name of localhost to the IPv6 loopback address of ::1. Windows will resolve localhost to ::1, even if IPv6 is disabled on all local network adapters.
MySQL IPv6 status
MySQL Server version 5.5.3 (March 2010) and later, support IPv6 connections to localhost, using the ::1 IPv6 address. So recent MySQL installations can at least listen for IPv6 client connections. But previous versions of MySQL server interpret ::1 as a string or host name, rather than an IP address.
On the client software side, things are not as simple.
We have the official MySQL Connector client libraries of connector/j, connector/odbc, connector/net, connector/c, connector/c++, connector/mxj, and the MySQL-PHP native driver (mysqlnd). On PHP we have ext/mysql, mysqli and pdo_mysql. ColdFusion uses its own MySQL client, with a different version of that client in each version of ColdFusion (mx, 7, 8, 9). Then there are many third party MySQL client libraries for Perl, ASP.Net, etc, etc, etc.
You just can’t trust the client to support IPv6. Only a few MySQL client libraries currently support IPv6 connections, and of those that do support IPv6, only the most recent versions may properly support IPv6.
The localhost problem
So here is where we run into a problem…
By default, Windows 7 and Windows 2008 R2 resolve the localhost host name to the ::1 IPv6 loopback address, rather than the 127.0.0.1 IPv4 loopback address. Windows will resolve “localhost” to ::1, even if you have disabled IPv6 on all of the installed network addapters.
This doesn’t just affect MySQL. This affects any TCP/IP client/server program that may use “localhost” as a connection parameter.
As you can see in the image below (mouse over images, for larger image) a “Ping localhost” command, on a default w2k8 r2 install, returns the IPv6 ::1 loopback address.
The images below show that ping localhost returns the ::1 IPv6 address, even when IPv6 is disabled on all local network adapters.
Fixing localhost IPv4 resolution
So we’ve decideded that we don’t like this sneaky IPv6 result for localhost resolution. The software that we are running prefers that localhost resolve to trusty old 127.0.0.1.
What to do?
We can correct this behavior in the Windows Hosts file, or by using the Windows registry to modify how IPv6 works or disable IPv6 altogether. My personal preference is to use the Windows hosts file to specifically map localhost to 127.0.0.1 and to modify the IPv4/IPv6 resolution preference.
Via Windows Hosts file
First, let’s take a look at the default Windows hosts file, at C:\Windows\System32\drivers\etc\hosts (open with notepad)
In previous versions of Windows, we find the following active entry for “localhost”
127.0.0.1 localhost
But in Windows 7 and Windows 2008 R2, the hosts file is effectively empty. While there are both IPv4 and IPv6 entries for “localhost”, both are disabled by being commented out.
# 127.0.0.1 localhost
# ::1 localhost
The image below shows the default hosts file, on a clean install of w2k8 r2.
If both “localhost” entries are disabled (commented out), localhost will resolve to the ::1 IPv6 address.
If both “localhost” entries are enabled (active), localhost will still resolve to the ::1 IPv6 address.
If we only enable the 127.0.0.1 entry for localhost, then we get the proper IPv4 resolution for a ping of localhost.
Via Windows Registry
We can modify the behavior of Windows IPv6 from the “HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\services\TCPIP6\Parameters\” registry key. The entry we are interested in is named “DisabledComponents”. But the DisabledComponents entry probably doesn’t exist on your system. So we need to create a new dword entry named “DisabledComponents”.
You can now disable IPv6 support by setting the value of DisabledComponents to a hex value of ffffffff (that’s 8 “f’s”). After you reboot, a ping of localhost will return 127.0.0.1, even if you don’t touch the hosts file.
But instead of completely disabling IPv6 support in Windows, you can just tell Windows to prefer IPv4 over IPv6. You can do this by setting the value of DisabledComponents to a hex value of 20. After you reboot, a ping of localhost will return 127.0.0.1, even if you don’t touch the hosts file.
My personal preference is a belt and suspenders approach to make sure that localhost resolves to 127.0.0.1, while trying not to break other (or future) Windows features by ripping out IPv6. I set a 127.0.0.1 IPv4 address in the hosts file and I set DisabledComponents to a hex value of 20, to adjust the IPv4/IPv6 preference.
Recent Comments