LOAD DATA LOCAL INFILE gives the error The used command is not allowed with this MySQL version

Question

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.

Answer 1

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

Answer 2

take a look to this permission list, you can add them separately, IE. you can insert but not update, or you can delete but not select, etc...

ALL [PRIVILEGES]    Grant all privileges at specified access level except GRANT OPTION
ALTER   Enable use of ALTER TABLE
ALTER ROUTINE   Enable stored routines to be altered or dropped
CREATE  Enable database and table creation
CREATE ROUTINE  Enable stored routine creation
CREATE TEMPORARY TABLES     Enable use of CREATE TEMPORARY TABLE
CREATE USER     Enable use of CREATE USER, DROP USER, RENAME USER, and REVOKE ALL PRIVILEGES
CREATE VIEW     Enable views to be created or altered
DELETE  Enable use of DELETE
DROP    Enable databases, tables, and views to be dropped
EVENT   Enable use of events for the Event Scheduler
EXECUTE     Enable the user to execute stored routines
FILE    Enable the user to cause the server to read or write files
GRANT OPTION    Enable privileges to be granted to or removed from other accounts
INDEX   Enable indexes to be created or dropped
INSERT  Enable use of INSERT
LOCK TABLES     Enable use of LOCK TABLES on tables for which you have the SELECT privilege
PROCESS     Enable the user to see all processes with SHOW PROCESSLIST
REFERENCES  Not implemented
RELOAD  Enable use of FLUSH operations
REPLICATION CLIENT  Enable the user to ask where master or slave servers are
REPLICATION SLAVE   Enable replication slaves to read binary log events from the master
SELECT  Enable use of SELECT
SHOW DATABASES  Enable SHOW DATABASES to show all databases
SHOW VIEW   Enable use of SHOW CREATE VIEW
SHUTDOWN    Enable use of mysqladmin shutdown
SUPER   Enable use of other administrative operations such as CHANGE MASTER TO, KILL, PURGE BINARY LOGS, SET GLOBAL, and mysqladmin debug command
TRIGGER     Enable trigger operations
UPDATE  Enable use of UPDATE
USAGE   Synonym for “no privileges”

I think you have permision to select, delete, insert, update, but no to do other stuff,

use this command:

SHOW GRANTS

he will show you what you are able to to, in my case.

jcho360> show grants;
+-------------------------------------------------------+
| Grants for jbolivar@localhost                         |
+-------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'jbolivar'@'localhost' |
+-------------------------------------------------------+
1 row in set (0.00 sec)

Answer 3

I found that I need to connect to database like this:

$dbh=mysql_connect($server,$dbuser,$dbpass,false,128);

Passing 128 in the flags parameter is the key.

See http://www.php.net/manual/en/mysql.constants.php#mysql.client-flags to read more about the flags.