問題描述
我最近將我的 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 '
' 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 中.
- DB 用戶是 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 客戶端使用，為什么我不能將 LOAD DATA LOCAL 與 PDO 一起使用?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！