LinLog

I ran into an interesting PHP bug at work the other day. Actually, it was a bug in PDO in a particular version of PHP.

Here's the situation. I needed to write a script to do some fairly extensive data massaging for a database integration. Since we have PHP installed on pretty much all of the production and development servers, I wrote it in PHP. It worked perfectly on the test server, so I figured it was safe to use it in production. And I was right, to a certain extent: it was safe in the sense that it didn't break anything - it's just that the script didn't work.

I won't get too much into the details of the script, as it's kind of long and not particularly interesting, but here's some sample code with the key points: while ($rec = $data_stmt->fetch()) { $db->beginTransaction(); $get_id_statement->execute(array($rec['something'])); $id = $get_id_statement->fetchColumn(); $update_statement->execute(array($somevar, $id)); $db->commit(); } The upshot here is that I was looping through the result set from a query and performing other select and update queries based on the current record. Simple enough, right?

The error message I received when I tried to run the script on our production database server was this:

PDO::prepare(): SQLSTATE[HY000]: General error: 2014 Cannot execute queries while other unbuffered queries are active. Consider using PDOStatement::fetchAll(). Alternatively, if your code is only ever going to run against mysql, you may enable query buffering by setting the PDO::MYSQL_ATTR_USE_BUFFERED_QUERY attribute.

The wierd thing about this is that, as I said, the script worked perfectly on the development server. And the development servers are supposed to be configured the same as the production servers. Of course, that obviously wasn't the case, so we ended up spending the next hour or so messing around with various code configurations with regard to transactions, prepared statements, and all that good stuff.

To make a long story short, it turned out that the explanation was simply that the development database server was running PHP 5.2.x and the production database server was running 5.1.x. The version on the production server still has an experimental version of PDO which, as it turns out, didn't work quite right. If I had just adjusted the IP address in the PDO connection string and run the script from our production staging server, which has PHP 5.2, it would have worked perfectly the first time. Unfortunately, I didn't discover that until I had already wasted an hour bug-hunting and messing with the script until I got it to work on PHP 5.1 (by moving all the fetch and fetchColumn method calls to fetchAll). Live and learn.