如何更正 MySQL 加载错误 [英] How can I correct MySQL Load Error

问题描述

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

mysql -e "加载数据本地 infile \'/tmp/ept_inventory_wasp_export_04292013.csv' 到 \表 wasp_ept_inv 字段以 ',' 结尾 \以 '\n' 结尾的行；"

在 bash 命令行中出现此错误

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

我该如何解决这个问题?

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

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

这是 MySQL 版本.

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

解决方案

如LOAD DATA LOCAL 的安全问题:

<块引用>

为了解决这些问题，我们改变了LOAD DATA LOCAL 从 MySQL 3.23.49 和 MySQL 4.0.2(Windows 4.0.13)开始处理:

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

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

• 您可以禁用所有LOAD DATA LOCAL 来自服务器端的语句，通过 mysqld 以 开头href="http://dev.mysql.com/doc/en/server-system-variables.html#sysvar_local_infile" rel="noreferrer">--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- 前缀指定它:

  <前>[客户]松散本地文件=1

• 如果 LOAD DATA LOCAL 被禁用，无论是在服务器还是客户端，尝试发出此类语句的客户端会收到以下错误消息:

  <块引用>

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

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

解决方案

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

这篇关于如何更正 MySQL 加载错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！