问题描述
我知道,有几个问题集中在同一个问题上,但所有建议的修复都不适用于我.
I know, there are a couple of questions out there, that are focusing on the same Issue, but all suggested fixes are not working for me.
我正在运行一个 PHP 脚本,我在其中尝试使用 LOAD DATA INFILE
将 CSV 文件插入到我的数据库中
I am running a PHP script in which I a trying to insert a CSV file into my DB using LOAD DATA INFILE
like this
$db = mysqli_init();
mysqli_options($db, MYSQLI_OPT_LOCAL_INFILE, true);
mysqli_real_connect($db, $db_host, $db_username, $db_password, $database);
$sql = "LOAD DATA LOCAL INFILE '" . $tpl_vars['filename'] . "' " .
"INTO TABLE " . $table_name . " " .
"FIELDS TERMINATED BY '" . $tpl_vars['delimiter'] . "' " .
"ENCLOSED BY '"" . $tpl_vars['encapsulation'] . "' " .
"LINES TERMINATED BY '\n' " .
($tpl_vars['contains_header'] == 1 ? "IGNORE 1 ROWS" : "") . " " .
"(" . $columns . ") " .
$set;
$db->query($sql);
生成的语句看起来像 thish 并且在语法上是正确的.表中存在的所有列.
The resulting statement looks like thish and is syntactically right. All columns to exist in the table.
LOAD data local INFILE '/path/to/file/file.csv'
INTO TABLE my_table
FIELDS TERMINATED BY ';'
ENCLOSED BY '"'
LINES TERMINATED BY '
'
IGNORE 1 rows (@category, @title, @price, @description)
SET category = @category, title = @title, price = @price, description = @description;
我得到的结果是
此 MariaDB 版本不允许使用的命令.
即使我在 phpMyAdmin 中运行该语句,它也会导致
Even if I run the statement in phpMyAdmin it results in
2000 - LOAD DATA LOCAL INFILE 被禁止,检查 mysqli.allow_local_infile
因为我没有对服务器的完全 root 访问权限,所以我使用 ini_get_all()
检查了我的 php.ini,它返回了
Because I do not have complete root access to my server I checked my php.ini using ini_get_all()
and it returned
mysqli.allow_local_infile: {global_value: "1", local_value: "1", access: 4}
为了检查 my.cnf,我在我的数据库上运行了 SHOW VARIABLES
.它回来了
To check the my.cnf I ran SHOW VARIABLES
on my DB. It returned
local_infile 开启
据我所知,一切都配置得很好,可以运行 LOAD DATA LOCAL INFILE
.我错过了什么吗?
As far as I can see, everything is configured totally fine to run LOAD DATA LOCAL INFILE
. Am I missing something?
我的用户确实有以下 GRANTS
My user does have the following GRANTS
GRANT USAGE ON *.* TO 'user'@'localhost' IDENTIFIED BY PASSWORD 'asdf'
GRANT ALL PRIVILEGES ON `db`.* TO 'user'@'localhost'
我使用的是 MariaDB 10.1.44 和 PHP 5.5.38.
I am on MariaDB 10.1.44 and PHP 5.5.38.
推荐答案
问题似乎是,我的用户没有文件授权.
It seems that the problem was, that my user did not have File grants.
GRANT FILE ON *.* TO 'user'@'localhost' IDENTIFIED BY PASSWORD 'asdf'
成功了.
这篇关于使用 local_infile 的 MariaDB 版本不允许使用的命令的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!