LOAD DATA LOCAL INFILE 在 php 5.5 中使用 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

$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 ','");