PHP 5.3 and MySQL connectivity problem

I have seen so many post in our IIS forum mentioning that after moving to PHP5.3 they are not able to connect to MySQL database. So this means a simple program like the one below doesn’t work:

<?php
// hostname or ip of server (for local testing, localhost should work)
$dbServer='localhost';

// username and password to log onto db server
$dbUser='root';
$dbPass='yourpassword';

// name of database
$dbName='test';

$link = mysql_connect("$dbServer", "$dbUser", "$dbPass") or die("Could not connect");
print "Connected successfully<br>";
mysql_select_db("$dbName") or die("Could not select database");
print "Database selected successfully<br>";

// close connection
mysql_close($link);
?>

And trust me most of the users cannot even dig this further and just mention that they are getting a 500 or some sort of script timeout.

There are two workaround to overcome this situation:

  • Most elegant one is to replace the database server name ‘localhost’ with IP address of the machine or ‘127.0.0.1’ which is reserved for localhost.
  • Disable IPV6. This can be done by opening the file ‘%systemroot%\System32\drivers\etc\hosts’ and commenting out the line ‘::1             localhost’. I would not advise this as there might be some other components which is dependent on IPV6 and may break or stop working.

Now some people also deduce from the above workaround that it is a Windows OS bug or a PHP bug. Neither is correct. I have explained this on PHP Windows internal alias couple of times as well as on our forums. Let me explain it again.

Most of the Operating System today is building support for IPV6. IPV4 has its own set of limitation and hence the world is moving to IPV6. I am not going to explain differences between two, but for the sake of this discussion assume that they are different. A close look at PHP MySQL driver code indicates that mysql_connect() function uses mysql_real_connect() to connect to MySQL. The API mysql_real_connect() is an API which comes from MySQL dll (MySQL development team and is part of MySQL database installation). So this means the support for IPV6 in PHP MySQL driver is not possible unless there is a support for IPV6 in MySQL database. So the limitation (or no support for IPV6 or whatever word may be used to describe this situation) is coming from MySQL database limitation. So now it is clear that this is not a PHP bug in the sense that nothing can be changed in PHP code base to fix it.

Let's move on to operating system concern now. A typical host file (irrespective of OS) has two columns which contains the IP address and host name binding to it. In order to support both IPV4 and IPV6 (at least on Windows) localhost is getting mapped to two IP addresses. There can be other reasons for mapping same localhost to multiple IP address. What is important is that, there is a case where we have multiple IP address pointing to same host name. A close inspection of MySQL code reveals that the function mysql_real_connect() actually tries to connect to only the first IP address returned for the hostname and if this fails, no further attempt to connect using other IP address associated with same host name is made. This can be fixed and I believe a patch is already lying in some development branch of MySQL and please follow MySQL release to know which version has this fix integrated. I do not have any idea about that. But an important thing is that this bug can surface on any OS where you have above condition satisfied for the host file. Yes WINDOWS, Ubuntu, Solaris all will and may get impacted. So this means that this is not a problem with Windows OS or for that reason any other OS.

Hope this explain things. Thanks for the patient reading and till we meet again ‘Good Bye’.

Thanks,

Don.

PS: In this post you learnt one reason for using IP address as your database host name. There is another compelling reason. Read the blog by Kanwal here to know more about it.

7 Comments

  • I have disabled anonymous comments on my blog as anonymous comments are getting deleted periodically and the comment section looks odd. So please login and comment.

    Thanks for understanding.

  • I have not tried this, but it occurs to me that a simple solution would be to create an entry in ‘%systemroot%\System32\drivers\etc\hosts’ for something like "localhostv4" with only the 127.0.0.1 entry. Then refer to the database as "localhostv4". That way you don't need to mess with the "localhost" entry which everyone uses.

    Metro.

  • What you fail to mention is that this problem also affects other functions in the php code beside mysql - I had eg. to disable the IPV6 alias for localhost (btw, I never understood why there was no single-button to disable IPV6 on windows) to make the testsuite for the phpxmlrpc library work without timing out - that code uses fsockopen/fread/fwrite and curl...

  • Thanks for some awesome comments. Sharing knowledge is always good.
    To answer few things, if PDO MySQLi are also using the same MySQL API to connect the problem will be visible there too.

  • The reason it times out, is the dll hasn't loaded. Check your error logs and you'll see this.

    It appears that in the 5.3 builds they are not including php_mssql.dll & php_mysql.dll.

    From other reading, they said they stopped support at least on php_mssql.dll.

    Haven't tried getting a newer php_mysql.dll to see if that works on 5.3.x, but because of this, our server still remains on 5.2.13.

  • php_mysql.dll is very much part of PHP MSI and is bundled with ZIP package too. php_mssql.dll is for connecting and using Microsoft SQL Server. Yes php_mssql.dll is no longer distributed with PHP 5.3.2 MSI or ZIP package because the extension is old and has dependency on some old non supported Microsoft libraries. Microsoft SQL team has published a brand new extension which can be used as the replacement for this. Details at http://blogs.iis.net/donraman/archive/2010/01/25/working-with-microsoft-sql-server-driver-php-sqlsrv-dll-for-php-on-windows.aspx.

    Thanks,
    Don.

  • I had this problem when using Apache but i think the better solutions is not to regedit or change the localhost to 127.0.0.1 but only to add an entry in "hosts" file and point 127.0.0.1 to localhost. It will work perfectly. Don't forget to run notepad as Admin so you have privileges to save the hosts file.

    In windows 7 ... "c:\windows\system32\drivers\etc\hosts"
    add "127.0.0.1 localhost"

Comments have been disabled for this content.