如何更正 MySQL 加载错误

How can I correct MySQL Load Error

提问by octopusgrabbus

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

我不太确定我正在尝试执行以下 MySQL 程序是否关闭了与此类似的问题。

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

在 bash 命令行并收到此错误

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

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.

我实际上是从 Python 程序中运行此命令，但将命令拉出以尝试在 bash 命令行中摆弄它。

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.

我已经看到了如何修改 my.cnf (local-infile)，但是如果可以避免的话，我不希望全局更改。

Here's the MySQL version.

这是 MySQL 版本。

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

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

回答by octopusgrabbus

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

解决方法是修改命令行mysql -e以传入--local-infile=1参数，如下所示：

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

Then run the LOAD DATA LOCAL INFILEcommand again.

然后LOAD DATA LOCAL INFILE再次运行该命令。

回答by eggyal

As documented under Security Issues with LOAD DATA LOCAL:

如安全问题中所述LOAD DATA LOCAL：

To deal with these problems, we changed how LOAD DATA LOCALis 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-infileoption, 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-infileoption, LOAD DATA LOCALcannot 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 LOCALstatements from the server side by starting mysqldwith the --local-infile=0option.

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

• If you use LOAD DATA LOCALin Perl scripts or other programs that read the [client]group from option files, you can add the local-infile=1option 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 LOCALis 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

为了解决这些问题，我们更改了LOAD DATA LOCALMySQL 3.23.49 和 MySQL 4.0.2（Windows 上的 4.0.13）的处理方式：

• 默认情况下，二进制发行版中的所有 MySQL 客户端和库都使用该--enable-local-infile选项进行编译，以与 MySQL 3.23.48 及之前版本兼容。

• 如果您从源代码构建 MySQL 但不使用该--enable-local-infile选项调用 configure ，LOAD DATA LOCAL则不能被任何客户端使用，除非它明确写入 invoke mysql_options(... MYSQL_OPT_LOCAL_INFILE, 0)。见第 20.6.6.49 节，“ mysql_options()”。

• 您可以LOAD DATA LOCAL通过使用该选项启动mysqld来禁用服务器端的所有语句--local-infile=0。

• 对于mysql命令行客户端，LOAD DATA LOCAL通过指定--local-infile[=1]选项启用，或使用--local-infile=0选项禁用它。对于mysqlimport，默认情况下本地数据文件加载是关闭的；使用--local或-L选项启用它。在任何情况下，成功使用本地加载操作都需要服务器允许。

• 如果您LOAD DATA LOCAL在 Perl 脚本或其他[client]从选项文件中读取组的程序中使用，则可以将local-infile=1选项添加到该组。但是，为了防止这对不理解的程序造成问题，请local-infile使用loose-前缀指定它：

  [client]
  loose-local-infile=1
  

• 如果LOAD DATA LOCAL在服务器或客户端中禁用，则尝试发出此类语句的客户端会收到以下错误消息：

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

回答by Zenexer

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

local-infile需要在服务器和客户端上启用。您可以通过添加local-infile = 1到每一端的my.cnf(Unix) 或my.ini(Windows) 文件中的适当部分来完成此操作。例如，在客户端：

[client]
local-infile = 1

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

您还可以通过设置系统变量在服务器上的运行时启用此功能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 mysqlcommand:

但是，您仍然需要在客户端上启用它。您可以在运行时通过向命令添加命令行参数来mysql执行此操作：

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_infileparameter. You may need to restart your server after applying the changes.

如果您使用的是 Amazon Web Services RDS，则可以通过编辑或创建参数组来配置服务器设置。寻找local_infile参数。应用更改后，您可能需要重新启动服务器。

回答by Mike Brant

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

我的猜测是您的 MySQL 服务器没有LOAD DATA LOCAL启用。请参阅 MySQL 文档的这一部分：

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

如果在服务器或客户端中禁用 LOAD DATA LOCAL，则尝试发出此类语句的客户端会收到以下错误消息：

错误 1148：此 MySQL 版本不允许使用的命令

Here is link to the page I got this from:

这是我从中获得此页面的链接：

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

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