LOAD DATA LOCAL INFILE only works sometimes

I have a LOAD DATA command that works fine in the MySQL terminal. It works fine in a test PHP file served from my local server. It works fine in phpMyAdmin on the same server. It inexplicably breaks down when run from Magento, also on the same server.

I have confirmed all methods are using the same login credentials. If I copy the failed command (including all previous queries from connection onwards) from the query log and paste into any other method it mysteriously works again. The query itself cannot be at fault.

Therefore I deduce the problem must be a permissions one. File privilege is set and works for other implementations on the same account. Magento is using pdo_mysql as it's connection. If I duplicate that in a test file it works. Magento must be setting some parameter that is interfering.

The error I get is SQLSTATE[42000]: Syntax e开发者_Go百科rror or access violation: 1148 The used command is not allowed with this MySQL version. I must use the "LOCAL" modifier keyword because I cannot guarantee the database server will be accessible.

The nearest previous question here on SO concerns drupal, the error is different but I recall drupal also uses pdo_mysql to connect, perhaps it is related. The only other mention of this error on Magento is this thread which also fails on a LOAD DATA LOCAL INFILE command.

Per @DeveloperChris' suggestion, you can enable SQL logging in Magento in the Varien_Db_Adapter_Pdo_Mysql class (in the lib\Varien path). Change the $_debug and $_logAllQueries class variables to true. You can also change the file path where the queries are logged in $_debugFile.

Don't forget to turn it off before production!

HTH, JD