MySQL load data infile local with PHP 5.4

In case you’ve not heard of it, MySQL’s “load data infile local” is a really useful bit of functionality that allows you to load a file directly into a database table.

The benefit of it is that the whole file is processed at once, so it’s very fast!

The downside is that the file has to be perfect, or nothing is loaded (I suppose atomic behaviour is really a benefit as well…). The columns must occur in the same order as they occur in the database, needless to say getting it to work initially can be a little fiddly, but when it works, it’s worth it!

Anyway, onto the reason for this article!

As part of a major rewrite of http://BayCrazy.com some scripts that had previously been written in Perl needed to be rewritten in PHP (to take advantage of our existing library, and to allow the whole team to be able to understand the whole system).

The trouble was that “load data infile local” simply would not work in PHP, the error given was: “#1148 - The used command is not allowed with this MySQL version”

Checking that it works on the command line MySQL client, it was in fact enabled, on the server, also enabled! Searching for the issue bought back information that relaed to one of that worked!

After a little digging, turns out that this is the root of the problem:

http://dev.mysql.com/downloads/connector/php-mysqlnd

In PHP 5.4 the default MySQL driver changed from libmysql to mysqlnd, which is a native PHP library. This is great, except it means that any configuration that PHP had previously been picking up from the MySQL client’s configuration is no longer being processed, and nobody knows why! :-)

There is a lot of information online about fixing this error, but what I found worked was using mysqli_init, and using the options function to set ‘MYSQLI_OPT_LOCAL_INFILE’ to TRUE, as shown in this function:

function loadData($configuration, $query) {
    $db = mysqli_init();
    $db->options(MYSQLI_OPT_LOCAL_INFILE, true);
    $db->real_connect($configuration['user_server'],
    $configuration['user_user'],
    $configuration['user_password'],
    $configuration['user_database']);

    if(!$db->query($query)) {
    return $db->error;
    }
    return 1;
    }

I did attempt to make it work using PDO (setting “\PDO::MYSQL_ATTR_LOCAL_INFILE” to TRUE when instantiating PDO), as we use elsewhere in our software, but had less luck! If anyone has had more luck, I’d love to hear from you.