使用 local_infile 的 MariaDB 版本不允许使用的命令 [英] The used command is not allowed with this MariaDB version with local_infile ON

问题描述

我知道，有几个问题集中在同一个问题上，但所有建议的修复都不适用于我.

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 版本不允许使用的命令.

The used command is not allowed with this MariaDB version.

即使我在 phpMyAdmin 中运行该语句，它也会导致

Even if I run the statement in phpMyAdmin it results in

2000 - LOAD DATA LOCAL INFILE 被禁止，检查 mysqli.allow_local_infile

2000 - LOAD DATA LOCAL INFILE is forbidden, check 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}

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 开启

local_infile ON

据我所知，一切都配置得很好，可以运行 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 版本不允许使用的命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！