PHP 5.3 and MySQL connectivity problem

Posted: Jun 11, 2010  9 comments  

Average Rating

Tags
MySQL
PHP

Share this Post

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.