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 'rn' 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 of
my.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?
2
Answers
The guide at
https://mariadb.com/kb/en/library/load-data-infile/
says
You best bet is to change the my.ini file that’s being used.
Moreover the used database user needs the FILE privilege.
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.