LOAD DATA LOCAL INFILE 在 php 5.5 中使用 PDO 不起作用

2021-12-26 00:00:00 php mysql pdo

我最近移动了网络服务器，新的网络服务器有不同版本的 PHP.

I've recently moved web servers, and the new web server has a different version of PHP.

新服务器:PHP 5.5.3-1ubuntu2 (cli)旧服务器:PHP 5.3.10 (cli)

New Server: PHP 5.5.3-1ubuntu2 (cli) Old Server: PHP 5.3.10 (cli)

在数据库服务器上，my.cnf 设置为允许 local-infile.我可以从以下位置使用加载数据本地 infile:

On the database server, my.cnf is set to allow local-infile. I can use load data local infile from:

- HeidiSQL
- The command line client running on the new web server using --local-infile=1
- PHP, using PDO, from the old web server

但是，当我尝试从新的 Web 服务器连接时，使用带有 PDO 的 PHP，我得到:出现数据库问题:SQLSTATE[42000]: Syntax error or access conflict: 1148 The used command is not allowed with this MySQL version

However, when I try to connect from the new web server, using PHP with PDO, I get: A database problem has occurred: SQLSTATE[42000]: Syntax error or access violation: 1148 The used command is not allowed with this MySQL version

php.ini:

[MySQL]
; Allow accessing, from PHP's perspective, local files with LOAD DATA statements
; http://php.net/mysql.allow_local_infile
mysql.allow_local_infile = On

PDO 构造函数:

$this->db_conn = new PDO("mysql:host=$host;dbname=$dbname;port=$port", $user, $pass, array(PDO::MYSQL_ATTR_LOCAL_INFILE => 1));

我也在 PHP 脚本中尝试过 ini_set('mysql.allow_local_infile', 1) 和 ini_set('mysql.allow_local_infile', true).

I've also tried ini_set('mysql.allow_local_infile', 1) and ini_set('mysql.allow_local_infile', true) in the PHP script.

需要 LOCAL 关键字，因为文件托管在 Web 服务器上，而不是数据库服务器上.

The LOCAL keyword is needed because the files are hosted on the web server, not the database server.

PHP 5.5 还想从我这里得到什么?

What else does PHP 5.5 want from me?

推荐答案

您需要同时配置客户端和服务器以允许此命令:

You need to configure both the client and the server to allow this command:

  1. 确保服务器允许 LOAD DATA LOCAL INFILE.在 MySQL 服务器上编辑/etc/my.cnf:

  1. Make sure the server allows LOAD DATA LOCAL INFILE. Edit /etc/my.cnf on the MySQL server:

[server]
local-infile=1

  • 在您的 PHP 脚本中设置 PDO 属性:

  • Set the PDO attribute in your PHP script:

    <?php

$dsn = "mysql:host=localhost;dbname=test";
$user = "root";
$password = "root";
$pdo = new PDO($dsn, $user, $password, array(PDO::MYSQL_ATTR_LOCAL_INFILE=>1));
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$pdo->exec("LOAD DATA LOCAL INFILE 'foo.csv' INTO TABLE foo FIELDS TERMINATED BY ','");

    这需要在构造函数的驱动程序选项参数中设置，而不是在对 setAttribute() 的后续调用中设置.

    This needs to be set in the driver options argument to the constructor, not in a subsequent call to setAttribute().

    我刚刚在 CentOS Linux 6.5 上使用 PHP 5.5.8 和 Percona Server 5.6.15 成功测试了上述代码示例.

    I just tested the above code example successfully with PHP 5.5.8 and Percona Server 5.6.15 on CentOS Linux 6.5.

    如果您仍然遇到问题，您可能有一个不允许 local-infile 的 MySQL 客户端或 PDO 版本.http://dev.mysql 中描述了这些要求.com/doc/refman/5.6/en/load-data-local.html

    If you still have trouble, you may have a build of the MySQL client or PDO that does not permit local-infile. The requirements are described in http://dev.mysql.com/doc/refman/5.6/en/load-data-local.html

    php.ini 中的 mysql.allow_local_infile 选项与 PDO 无关.

    The mysql.allow_local_infile option in your php.ini is not relevant to PDO.

    • 相关文章