Doctrine DBAL和LOAD DATA LOCAL INFILE

Question

Both my MySql server and client are running with local-infile=1. It makes it possible form my to execute queries like these:

LOAD DATA LOCAL INFILE /var/data/report.csv INTO TABLE MyTable FIELDS TERMINATED BY ',' LINES TERMINATED BY ' ' IGNORE 2 LINES (column1, column2, column3)';

All is good when I log-in to MySql server from the command line and then execute the query.

However, when I want to execute the same query with my application that's using Doctrine DBAL and db.driver: pdo_mysql to conntect to the same database, I'm getting this error:

Syntax error or access violation: 1148 The used command is not allowed with this MySQL version

It appears that my setting of mysql client and server (local-infile=1) are not 'respected' by Doctrine DBAL. The application's connection does not allow for LOAD DATA LOCAL INFILE while the client and the db host do. Why is that? How can I configure my db connection to be able to execute LOAD DATA LOCAL INFILE?

Answer 1

It appears that this is a common problem. I guess it stems from the fact that connections to the mysql server are established differently by various applications (regardless of the fact that they all might be using the same driver). Depending on a particular use-case, the solution would be one of the following:

1. If it's possible, try using LOAD DATA INFILE (no LOCAL). There are a cople prerequisites for this to work:

   • It's best if your mysql server is installed on the same machine as your client.
   • If you're on Linux, you should configure Apparmour to allow mysqld acceess to the location in your filesystem where the data source file is stored (/etc/apparmor.d/usr.sbin.mysqld, then reload apparmor). You will also have to grant the FILE priviledge to the mysql user.

2. If you know that LOAD DATA INFILE is not an option and you need LOCAL, you can try executing the query in your program directly through the connection established directly with PDO, see here: https://stackoverflow.com/a/18459367. Remember also that the user you're running your mysql client as should have read access to the file you're trying to load.

3. You can also try to establish the client connection through Doctrine DBAL with the (unfortunately) not well documented option "\PDO::MYSQL_ATTR_LOCAL_INFILE" (see here https://stackoverflow.com/a/24759583 )