26

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

shgnInc
  • 2,054
  • 1
  • 23
  • 34
octopusgrabbus
  • 10,555
  • 15
  • 68
  • 131

4 Answers4

48

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.

Eric Leschinski
  • 146,994
  • 96
  • 417
  • 335
octopusgrabbus
  • 10,555
  • 15
  • 68
  • 131
30

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

eggyal
  • 122,705
  • 18
  • 212
  • 237
  • 1
    That's not helping me at all. I want a workaround. And I put local-infile in my.cnf, and that did not change the error. I start MySQL with the statements provided when installed. I don't do anything special. Your answer still does not provide a workaround, because I have not specifically disabled anything. – octopusgrabbus Apr 29 '13 at 18:45
  • @octopusgrabbus if it's not helping you at all, why did you accept the answer then?? Why didn't you accept your own answer instead??? – Tomas Sep 23 '15 at 18:26
10

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.

Zenexer
  • 18,788
  • 9
  • 71
  • 77
0

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

Community
  • 1
  • 1
Mike Brant
  • 70,514
  • 10
  • 99
  • 103