此MariaDB版本在local_infile ON的情况下不允许使用命令 [英] 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);

结果语句看起来像这样，并且在语法上是正确的.表中存在的所有列.

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 '\n' 
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-禁止加载数据本地文件，请检查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"，访问权限: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?

我的用户确实具有以下授予权限

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 *.*到'user'@'localhost'由密码'asdf'识别

成功了！

这篇关于此MariaDB版本在local_infile ON的情况下不允许使用命令的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！