LOAD DATA LOCAL INFILE在使用PDO的php 5.5中不起作用 [英] LOAD DATA LOCAL INFILE does not work from php 5.5 using PDO
问题描述
我最近移动了Web服务器，而新的Web服务器具有不同版本的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设置为允许本地文件. 我可以从以下位置使用加载数据本地文件:
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
但是，当我尝试使用PHP和PDO从新的Web服务器连接时，我得到: 发生数据库问题:SQLSTATE [42000]:语法错误或访问冲突:1148此MySQL版本不允许使用的命令
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:
-
确保服务器允许LOAD DATA LOCAL INFILE.在MySQL服务器上编辑/etc/my.cnf:
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 ','");
这需要在构造函数的driver options参数中设置，而不是在随后的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.
如果仍然遇到问题，则可能是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.
这篇关于LOAD DATA LOCAL INFILE在使用PDO的php 5.5中不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
