Gotcha of the Day: PHP Generates Random MySQL Access Denied Errors

Over the last few weeks, one of my PHP + MySQL apps has suddenly started issuing random access denied errors. The timing and query involved seem to change, but the general message is something like:

Error Message:  SQL exec failed: [ ... SQL trimmed ...]:
  Access denied for user 'root'@'localhost' (using password: NO)

This is whacky because I'm obviously calling mysql_connect with credentials that most certainly not root and an empty password.

Most of the time, the system will have run a few queries before the query in question fails.

The problem popped up out of the blue, so my first strategy was to hope it would simply go away (with it happening so infrequently, and at off hours, what harm could it cause?). It didn't, and appeared to be getting worse.

My mind immediately conjured up some wickedly complex scenario where another PHP process was polluting mine, and therefore causing the credentials to be overwritten. I thought about contacting the hosting provider to try to explain the problem, but in my experience, anything involving PHP code is immediately written off as programmer error (which, in their defense is probably mostly the case - except when I file a bug, that is).

Tonight I was determined to figure the problem out. I knew I had to think simpler than a massive PHP bug involving corrupt address spaces.

And then it hit me -- what if the database connect was getting closed by the host? This happens all the time when I leave my command line mysql session untouched for a while. How would mysql_query react if the database connection was closed or invalid? The answer was promising:

If the link identifier is not specified, the last link opened by mysql_connect() is assumed. If no such link is found, it will try to create one as if mysql_connect() was called with no arguments.

Aha! So now this was starting make sense. If the database connection was getting closed, then the next call to mysql_query would result in an invocation of mysql_connect() without any arguments. Which would no doubt result in some basic set of credentials, like root and empty password being sent.

That's so PHP - trying to find a default behavior that kinda sorta makes sense, rather than just throwing an exception and dying. It's too kind at times, which can lead to sloppiness.

Regardless, the documentation for mysql_connect mentioned that special INI settings could be used to provide default settings. As a result, I went into my database configuration and added:

 ini_set("mysql.default_host", DB_HOST);
 ini_set("mysql.default_user", DB_USER);
 ini_set("mysql.default_password", DB_PASS);

Then came the big test. I wrote up a quick test that worked as follows:

 <?
  /* Assume the DB connection is ready to use */
  $x = mysql_query("SELECT COUNT(*) from foo");
  var_dump($x);
  mysql_close(); /* Shut her down. */
  $y = mysql_query("SELECT COUNT(*) from foo");
  var_dump($y);
 ?>

Without the ini_set(...)'s in place, the above code failed on the second query. But, with the ini_set(...)'s in place, invoking mysql_query worked because I gave MySQL the recipe for reconnecting.

I was pleased to see that even though there's no ini setting for choosing a default database, the invocation to mysql_select_db() was remembered even after I closed the connection.

Time will tell if my fix above works. But, it certainly feels like its on the right track.