We help IT Professionals succeed at work.

#2000 - LOAD DATA LOCAL INFILE is forbidden, check mysqli.allow_local_infile error when loading data into tables in XAMPP

James Froggatt
James Froggatt used Ask the Experts™
on
Hello,

I am using XAMPP (v3.2.4) on my Win10 computer to develop a website with. I have recently purchased a world database of places and the instructions to set up the database (load the data into tables that I've created) tell me to use the SQL

LOAD DATA LOCAL INFILE '/xampp/mysql/country.dat' INTO TABLE country CHARACTER SET UTF8 fields terminated by '\t' LINES TERMINATED BY '\r\n'; 
LOAD DATA LOCAL INFILE '/xampp/mysql/states.dat' INTO TABLE states CHARACTER SET UTF8 fields terminated by '\t' LINES TERMINATED BY '\r\n';
LOAD DATA LOCAL INFILE '/xampp/mysql/city.dat' INTO TABLE city CHARACTER SET UTF8 fields terminated by '\t' LINES TERMINATED BY '\r\n'; 
LOAD DATA LOCAL INFILE '/xampp/mysql/timezone.dat' INTO TABLE timezone CHARACTER SET UTF8 fields terminated by '\t' LINES TERMINATED BY '\r\n'; 

Open in new window


Within phpMyAdmin when I run the SQL above, I keep getting the error:

#2000 - LOAD DATA LOCAL INFILE is forbidden, check mysqli.allow_local_infile


I have researched online and have changed, in the php.ini file

;mysqli.allow_local_infile

to

mysqli.allow_local_infile=On

After restart of Mysql and Apache in XAMPP, I am still getting the error


#2000 - LOAD DATA LOCAL INFILE is forbidden, check mysqli.allow_local_infile


I have also read something about adding details to my.cnf file, but I have't done anything with that as I can't even find the file, I'm not sure it exists.

Please help,

Thank you
James
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
The solution is to remove LOCAL in the SQL queries.

I.e. use

LOAD DATA INFILE '/xampp/mysql/country.dat' INTO TABLE country CHARACTER SET UTF8 fields terminated by '\t' LINES TERMINATED BY '\r\n'; 

LOAD DATA INFILE '/xampp/mysql/states.dat' INTO TABLE states CHARACTER SET UTF8 fields terminated by '\t' LINES TERMINATED BY '\r\n';

LOAD DATA INFILE '/xampp/mysql/city.dat' INTO TABLE city CHARACTER SET UTF8 fields terminated by '\t' LINES TERMINATED BY '\r\n'; 

LOAD DATA INFILE '/xampp/mysql/timezone.dat' INTO TABLE timezone CHARACTER SET UTF8 fields terminated by '\t' LINES TERMINATED BY '\r\n'; 

Open in new window