不允许在文件中加载数据 MariaDB

2022-01-15 00:00:00 bulkinsert mariadb php mysql wordpress

我创建了一个 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=Onmy.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.

相关文章