How can I correct MySQL Load Error

Question

I'm not quite sure a similar question to this was closed by I'm trying to execute the following MySQL program.

mysql -e "load data local infile \
'/tmp/ept_inventory_wasp_export_04292013.csv' into \
table wasp_ept_inv fields terminated by ',' \
lines terminated by '\n' ;"

at the bash command line and get this error

ERROR 1148 (42000) at line 1: The used command is not allowed with this MySQL version

How can I work around this problem?

I am actually running this command from a Python program, but pulled the command out to try fiddling with it at the bash command line.

I've seen how I can modify my.cnf (local-infile), but I do not want that global a change if I can avoid it.

Here's the MySQL version.

mysql Ver 14.14 Distrib 5.5.31, for debian-linux-gnu (i686) using readline 6.2

Answer 1

local-infile needs to enabled on both the server and the client. You can accomplish this by adding local-infile = 1 to the appropriate section in each end's my.cnf (Unix) or my.ini (Windows) file. For example, on the client:

[client]
local-infile = 1

You can also enable this at runtime on the server by setting the system variable local_infile:

SET GLOBAL local_infile=1;

However, you still need to enable it on the client. You can do this at runtime by adding a command-line parameter to the mysql command:

mysql --local-infile=1 ...

If you're using Amazon Web Services RDS, you can configure the server setting by editing or creating a Parameter Group. Look for the local_infile parameter. You may need to restart your server after applying the changes.

Answer 2

The workaround for this is to modify the command line mysql -e to pass in the --local-infile=1 argument like this:

mysql --local-infile=1 -u username -p `

Then run the LOAD DATA LOCAL INFILE command again.

Answer 3

My guess is that your MySQL server does not have LOAD DATA LOCAL enabled. See this section of MySQL documentation:

If LOAD DATA LOCAL is disabled, either in the server or the client, a client that attempts to issue such a statement receives the following error message:

ERROR 1148: The used command is not allowed with this MySQL version

Here is link to the page I got this from:

http://dev.mysql.com/doc/refman/5.5/en/load-data-local.html

Answer 4

As documented under Security Issues with LOAD DATA LOCAL:

To deal with these problems, we changed how LOAD DATA LOCAL is handled as of MySQL 3.23.49 and MySQL 4.0.2 (4.0.13 on Windows):

• By default, all MySQL clients and libraries in binary distributions are compiled with the --enable-local-infile option, to be compatible with MySQL 3.23.48 and before.

• If you build MySQL from source but do not invoke configure with the --enable-local-infile option, LOAD DATA LOCAL cannot be used by any client unless it is written explicitly to invoke mysql_options(... MYSQL_OPT_LOCAL_INFILE, 0). See Section 20.6.6.49, “mysql_options()”.

• You can disable all LOAD DATA LOCAL statements from the server side by starting mysqld with the --local-infile=0 option.

• For the mysql command-line client, enable LOAD DATA LOCAL by specifying the --local-infile[=1] option, or disable it with the --local-infile=0 option. For mysqlimport, local data file loading is off by default; enable it with the --local or -L option. In any case, successful use of a local load operation requires that the server permits it.

• If you use LOAD DATA LOCAL in Perl scripts or other programs that read the [client] group from option files, you can add the local-infile=1 option to that group. However, to keep this from causing problems for programs that do not understand local-infile, specify it using the loose- prefix:

  [client]
  loose-local-infile=1
  

• If LOAD DATA LOCAL is disabled, either in the server or the client, a client that attempts to issue such a statement receives the following error message:

  ERROR 1148: The used command is not allowed with this MySQL version