I have a PHP script that calls MySQL's LOAD DATA INFILE to load data from CSV files. However, on production server, I ended up with the following error:
Access denied for user ... (using password: yes)
As a quick workaround, I changed the command to LOAD DATA LOCAL INFILE which worked. However, the same command failed on client's server with this message:
The used command is not allowed with this MySQL version
I assume this has something to do with the server variable: local_infile = off as described here.
Please suggest a workaround that does not involve changing server settings. Note that phpMyAdmin utility installed on the same server appears to accept CSV files though I am not sure it it uses LOAD DATA (LOCAL) INFILE.
Ran into the same issue as root and threw me for a moment
could be an issue with your server settings set with compile
to test login to console with the same user and try your load data command
if you get the same error, try closing console and running
mysql -u USER -p --local-infile=1 DATABASE
now try running load data command again
if it works then you're going to need to restart mysqld with command line option or re-install with configuration option
references (references are for 5.0 but worked for me with 5.5):
http://dev.mysql.com/doc/refman/5.0/en/load-data-local.html
http://dev.mysql.com/doc/refman/5.0/en/mysql-command-options.html#option_mysql_local-infile