Load Data Local Infile Forbidden

[southcot]

I am working with my site host with regard to an issue of omporting data. I am attempting to import data into a table using LOAD DATA LOCAL INFILE. I can connect and manage my data using SQLyog without any difficulty. However the following fails:-

 

“LOAD DATA LOCAL INFILE ‘W:\new sites\xxxxxxx\parties.csv’ INTO TABLE `xxxxxxl`.`je_parties` FIELDS ESCAPED BY ‘\’ TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘ LINES TERMINATED BY ‘rn’ (`PClass`, `PFullName`, `PRegNum`, `PAddress`, `PFriendlName`, `PContactDate`);”

 

… with the following message:-

 

“Load data local infile forbidden”

 

Now my host advise me that LOAD DATA LOCAL INFILE is not restricted. The command above work perfectly with another test MySQL databse I created on my local MySQL server. I can’t see that this can be a firewall issue my end; as I say above SQLyog is working fine for this DB with this one excep[tion. And anyway my firewall is producing no errors/messages in the log when I try and run it.

 

Can you suggest how I might resolve this issue?

[Abhishek Kumar Pandey]

Please execute SHOW VARIABLES LIKE ‘%local_infile%’; and paste the result here. 

[southcot]

“Variable_name”    “Value”

“local_infile”    “ON”

[Abhishek Kumar Pandey]

Hi,

 

If you are using HTTP tunneling can you please confirm with your system admin following-

1. Is mysqlnd is used  for connection to mysql in php?
2. Is open_basedir enabled?

Actually this is a bug with php link-https://bugs.php.net/bug.php?id=55737&edit=3

[southcot]

I’m not i’m simply connectingi via IP address

[peterlaursen]

When you are getting the error “Load data local infile forbidden” was there an error no. too? Please provide a screenshot of the error dialog. This would help us to determine the context/the origins of the error message.

 

Also please connect with SQLyog and execute “SWO GRANTS;” and paste the result here. 

[peterlaursen]

I have been googling the error “Load data local infile forbidden”. I can only find it in a PHP-context. The error received if ‘local_infile’ variable is the culprit is “ERROR 1148: The used command is not allowed with this MySQL version” (refer: https://dev.mysql.com/doc/refman/5.5/en/load-data-local.html).

 

I believe this is a configuration issue of some kind on the server. If you try any other client, such as ‘mysql’ commandline and executing same statement you will undoubtedly receive the same error. You can try.

[southcot]

I’ll have to do the error screenshot and testing the mysql command line later. However executing “SWO GRANTS;” :-

 

1 queries executed, 0 success, 1 errors, 0 warnings

Query: SWO GRANTS

Error Code: 1064

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘SWO GRANTS’ at line 1later,

[southcot]

I have just tried to run the command to take a screen shot of the error and miraculously it has worked. Given I have changed nothing my end, including not having closed SQLyog or rebooted my machine since I started this ticket I can only surmise something has changed at the hosting end. Thanks for all the help

[peterlaursen]

I meant “SHOW GRANTS;”

 

But anyway. let us see if it happens again. 

[peterlaursen]

This looks OK to me.  But I also understand that the problem has gone away now. I am not sure what the reason could have been.

[southcot]

Just wait and see I guess

[Author]

Posts