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

问题描述

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

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

将数据本地 infile '/var/www/vhosts/sitenamehere.test/test.csv' 加载到表 test_table 字符集 latin1 字段中以 ';' 结尾以 '
' 结尾的行忽略 1 行；

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

<块引用><块引用>

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

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

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

我尝试添加:

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

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

解决方案

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

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

在两个服务器的最新版本中，此功能默认禁用，仅应在必要时启用.

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

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 '
' ignore 1 lines;

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

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

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.

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.

I tried adding:

• 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?

解决方案

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

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屋！