不允许在文件中加载数据 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]
ofmy.cnf
- the line
mysql.allow_local_infile=On
under the[mysql]
section ofmy.cnf
- the line
mysql.allow_local_infile=On
under the[MySQLi]
section ofphp.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.
相关文章