原则DBAL和LOAD DATA LOCAL INFILE [英] Doctrine DBAL and LOAD DATA LOCAL INFILE

问题描述

MySql服务器和客户端都使用 local-infile = 1 运行。这样可以使我可以执行以下查询：

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插入表MyTable字段终止由'，'行以'\n'忽略的2行（第1列，第2列，第3列）终止；

当我从命令行登录到MySql服务器，然后执行查询。

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

但是，当我想对使用Doctrine DBAL和 db.driver的应用程序执行相同的查询时：pdo_mysql 连接到相同的数据库，出现此错误：

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:

语法错误或访问冲突：1148不允许使用所使用的命令使用此MySQL版本

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

看来我对mysql客户端和服务器的设置（ local-infile = 1 ）不受Doctrine DBAL的尊重。在客户端和数据库主机允许的情况下，该应用程序的连接不允许 LOAD DATA LOCAL INFILE 。这是为什么？如何配置数据库连接以执行 LOAD DATA LOCAL INFILE ？

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?

推荐答案

看来这是一个常见问题。
我想这是由于各种应用程序以不同的方式建立了与mysql服务器的连接（无论它们都使用同一个驱动程序）。根据特定的用例，解决方案将是以下之一：

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. 如果可能的话，请尝试使用 LOAD DATA INFILE （没有 LOCAL ）。要使之正常工作，必须满足一些先决条件：

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

• 最好将mysql服务器与客户端安装在同一台计算机上。

• 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.

如果您知道 LOAD DATA INFILE 不是一个选项，您需要 LOCAL ，您可以尝试通过直接与PDO建立的连接在程序中直接执行查询，参见此处： https://stackoverflow.com/a/18459367 。还请记住，您正在运行mysql客户端的用户应具有对要加载的文件的读取访问权限。

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.

您还可以尝试通过Doctrine DBAL使用（不幸的）记录不完整的选项 \PDO :: MYSQL_ATTR_LOCAL_INFILE建立客户端连接（请参阅此处 https：// stackoverflow .com / a / 24759583 ）

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 )

这篇关于原则DBAL和LOAD DATA LOCAL INFILE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！