不允许在文件中加载数据MariaDB [英] load data infile is not allowed MariaDB

问题描述

我创建了一个PHP脚本，该脚本将CSV文件中的帖子导入到WordPress网站中.

I created a PHP script that imports posts from a CSV file into a WordPress website.

为此，我首先将帖子批量导入WP网站数据库的表中，然后PHP脚本创建帖子. 我使用的批量插入MYSQL查询如下:

To do this, I first bulk import the posts into a table of the WP website database and then the PHP script creates the posts. The bulk insert MYSQL query I use is the following:

load data local infile '/var/www/vhosts/sitenamehere.test/test.csv' into table test_table character set latin1 fields terminated by ';' lines terminated by '\r\n' ignore 1 lines;

当我从服务器运行脚本时，出现以下错误:

When I run the script from the server I get the following error:

此MariaDB版本不允许所使用的命令用于查询加载数据本地文件..."

"the used command is not allowed with this MariaDB version for the query load data local infile..."

仅当我从服务器执行脚本时才会出现问题，实际上，如果我从phpMyAdmin运行相同的查询，则可以导入文件.

The problem occurs only when I execute the script from the server, in fact if I run the same query from phpMyAdmin, it lets me import the file.

由于我的脚本不仅导入而且更新帖子，所以其目的是创建cron作业，以便每天执行多次脚本.显然，如果我不断遇到相同的错误，这是不可能的.

Since my scripts not only imports but also updates posts, the intention was to create a cron job so that the script is executed multiple times a day. Obviously this is not possible if I keep getting the same error.

我尝试添加:

• my.cnf的[client]和[mysqld]部分下的local-infile=1行
• [mysql]部分下的mysql.allow_local_infile=On行 my.cnf
• 位于/opt/plesk/php/7.1/etc的php.ini的[MySQLi]部分下的mysql.allow_local_infile=On行

• the line local-infile=1 under the section [client] and [mysqld] of my.cnf
• the line mysql.allow_local_infile=On under the [mysql] section of my.cnf
• the line mysql.allow_local_infile=On under the [MySQLi] section of php.ini located at /opt/plesk/php/7.1/etc

但是没有任何帮助.有什么想法吗?

But nothing helped. Any ideas?

推荐答案

当要加载本地文件时，必须将AllowLoadLocalInfile=true;添加到MySQL/MariaDB服务器连接字符串中.

You must add AllowLoadLocalInfile=true; to your MySQL/MariaDB server connection string when you want to load a local file.

如果使用诸如LOAD LOCAL INFILE命令之类的东西，则将--local_infile=1添加到命令本身，它应该可以工作.

If using something like a LOAD LOCAL INFILE command then add --local_infile=1 to the command itself and it should work.

在两台服务器的最新版本中，默认情况下均禁用此功能，并且仅在必要时启用.

In recent versions of both servers this functionality is disabled by default and should only be enabled when necessary.

这篇关于不允许在文件中加载数据MariaDB的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！