問題描述

我最近移動了網絡服務器，新的網絡服務器有不同版本的 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 ','");