即使我可以从cli客户端使用,我为什么也不能对PDO使用LOAD DATA LOCAL? [英] Why can't I use LOAD DATA LOCAL with PDO even though I can from cli client?

查看:47
本文介绍了即使我可以从cli客户端使用,我为什么也不能对PDO使用LOAD DATA LOCAL?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我最近将Ubuntu 10.04开发服务器升级到14.04.它实际上是全新安装. PHP版本是5.4.15,现在是PHP 5.5.9. MySQL从5.1.67升级到5.5.37.

I recently upgraded my Ubuntu 10.04 dev server to 14.04. It is actually a fresh install. PHP version was 5.4.15, and is now PHP 5.5.9. MySQL went from 5.1.67 to 5.5.37.

我正在尝试在新服务器上加载本地数据输入文件.它可以使用本地mysql cli客户端运行命令.使用PDO执行时不起作用:

I am trying to LOAD LOCAL DATA INFILE on a new server. It works running the command using the local mysql cli client. It does not work when executing using PDO:

PHP Fatal error:  Uncaught exception 'PDOException' with message 'SQLSTATE[42000]: Syntax error or access violation: 1148 The used command is not allowed with this MySQL version' in /home/ubuntu/mysqltest.php:21
Stack trace:
#0 /home/ubuntu/mysqltest.php(21): PDO->exec('LOAD DATA LOCAL...')
#1 {main}
  thrown in /home/ubuntu/mysqltest.php on line 21

产生错误的代码:

ini_set('mysql.allow_local_infile', 1);
echo "mysql.allow_local_infile: ".ini_get("mysql.allow_local_infile").PHP_EOL;

$options = array(PDO::MYSQL_ATTR_LOCAL_INFILE => 1);
$db = new PDO("mysql:host=$dbHost;dbname=$dbName;charset=utf8", $dbUser, $dbPassword, $options);
$db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

$db->exec("CREATE TEMPORARY TABLE tmp_table ( id INT(4), col1 varchar(128) ) ENGINE MYISAM");

$loadSQL = <<<'EOT'
LOAD DATA LOCAL INFILE '/tmp/test.csv' INTO TABLE `tmp_table` FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' ESCAPED BY '\\' LINES TERMINATED BY '\n' IGNORE 1 LINES (id, col1)
EOT;

$db->exec($loadSQL);

到目前为止为解决问题所采取的步骤:

Steps taken so far to address problem:

  • 我已将local-infile = 1添加到/etc/mysql/my.cnf的[mysql]和[mysqld]部分
  • 我已经检查了mysql.allow_local_infile = On在/etc/php5/cli/php.ini中.
  • 数据库用户是root用户,因此具有所有特权.
  • /tmp/test.csv确实存在,并且用户具有全部权限.

推荐答案

这是php中的错误.请参阅 https://bugs.php.net/bug.php?id=68226进行解释和解决.

This is a bug in php. Please see https://bugs.php.net/bug.php?id=68226 for explanation and workaround.

这篇关于即使我可以从cli客户端使用,我为什么也不能对PDO使用LOAD DATA LOCAL?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆