Our office is in the throes of a large-scale upgrade of the servers in our data center. The new version of Debian (version 7, or "Wheezy") has been officially released, having been in beta for the last few millenia, and our Ops team are slowly installing it across all our servers.

This is great news, as it means we get to upgrade to PHP 5.4. Woohoo! New shininess. Yeah, I know... just as 5.5 is coming out. Debian is known for many things, but being cutting edge isn't one of them. But never mind, 5.4 is a nice upgrade.

We've known this was coming for quite a long time -- in fact, we've been waiting for it. All our recent PHP code has been written in the knowledge that it would need to run in 5.4, and older code has been scoured in case it used any deprecated features. We were ready. The upgrade should have been a breeze.

But it wasn't.

One of our most important pieces of PHP software is our Data Importer. We receive regular dumps of data from various sources. This data comes in a variety of formats; some CSV, some XML, etc, so we've written an import tool that takes the various formats we're given and funnels them all into a common DB table which we can then use to do further processing.

For the CSV imports, the program creates a mySQL LOAD DATA INFILE query. This is a nice little syntax that mySQL provides which allows it to do a bulk import from a CSV or similar file. It allows for some quite complex queries, including cross-referencing lookup tables to get IDs, etc. All in a single query. I'm still amazed that so many people seem to think they need to write complex (and very slow) PHP programs to loop though their CSV files when they could be doing it this way. With LOAD DATA INFILE, it's just a single query to import your entire CSV file; you can import tens of thousands of records in seconds. A PHP loop doing the same thing would be measured in minutes.

And all this has been working beautifully for us... until now.

So what happened?

We weren't expecting any problems. Upgrading to PHP 5.4 should have virtually transparent.

What actually happened was that the program failed, and gave us a strange error message to the effect that LOAD DATA INFILE was forbidden from use.

This came as a surprise because we knew for a fact that we did have permission to use it. Testing the query manually using the same DB credentials on the command line worked perfectly fine, and in any case the database server itself had not been upgraded at all; just the PHP server, so we knew none of the DB permissions had changed. And PHP was quite happy to make other updates to the DB.

Searching the web for relevant information did yield a few pointers, but little in the way of hard facts or useful advice. We found a couple of Stackoverflow questions (here and here), and a bug report on php.net that the PHP devs seem to be insisting is not their problem (here).

These links do seem to be discussing the same problem that we were having, but none of them give any conclusive answer as to why, and how to resolve it. Some of the comments are saying that mySQL needs to be compiled with a specific flag to allow LOAD DATA INFILE. That can't be our issue; as I say, the DB server is unchanged.

The fix... uh, work-around

It can't be an issue for everyone, because there are enough people using PHP 5.4 that there would surely be more noise being raised about it. But judging by those three links above, it is clearly an issue for a significant number of people, and an issue that doesn't seem to have a satisfactory resolution.

In fact, the only sensible solution we've been able to come up with is to save the query to a temp file and then use the exec() in PHP to call the query via the command-line rather than inside the program.

exec("mysql -h{$host} -u{$username} -p$password -D $dbname < {$tmpSQLFile}");

This works - and is what we're using now in production - but it's hardly an elegant solution to the problem. And it definitely qualifies as a work-around, rather than a fix; we may have something that works, but we don't have an answer to why it broke in the first place.

The PHP devs comments in the bug ticket make it clear that they think this issue has been fixed. The replies make it clear that other people disagree, but the PHP devs don't seem to be listening any more (several recent comments have gone un-answered).

The one potential angle is that it might be something Debian-specific. If the PHP build supplied by Debian has an issue because they've standardised on a version prior to PHP fixing it, then that would explain it. Debian works that way: they fix the version number, and back-port security fixes. But they don't touch any non-security bug fixes or new features; on Debian, you're stuck with the version they supply. If that's what it is, it would remind me of a similar issue we had trying to use the password_compat library with PHP 5.3 before this upgrade, due to Debian's PHP being stuck a 5.3.3, and not having the patch from 5.3.7 that password_compat required. That one caused a lot of frustration too.

One of the most annoying aspects of the whole thing is that it does work if we use the old mysql_query() function; it just doesn't work using either mysqli or PDO. We're making an effort to stick to modern PHP coding standards, so we're using PDO throughout, which makes is all the more galling. (not to mention that I'm one of those annoying people on Stackoverflow that routinely tells newbies not to use the old mysql API, so the fact that something works with it that doesn't work in PDO or mysqli is... well... annoying).

Given that we do have a work-around now and everything is back up and running, I'm going to have to let this one drop; I don't have the time to try chasing it any more. But I hope this blog post will prove useful to anyone else having the same issue.

Comments

There are no comments on this post.

Sorry, commenting on this post is disabled.