在Ubuntu 12.04和MySQL中无法加载数据本地文件 [英] Load data local infile does not work in Ubuntu 12.04 and MySQL

问题描述

使用MySQL，我无法使用加载数据本地infile导入文件.我的服务器在AWS RDS上.这适用于Ubuntu 10.04.我使用apt-get install mysql-client安装了客户端.如果在Python中使用mysqldb或mysql.connector，则会出现相同的错误.

Using MySQL I cannot import a file using load data local infile. My server is on AWS RDS. This works on Ubuntu 10.04. I installed the client using apt-get install mysql-client. Same error if I use mysqldb or mysql.connector in Python.

  File "/usr/lib/pymodules/python2.7/mysql/connector/protocol.py", line 479, in cmd_query
    return self.handle_cmd_result(self.conn.recv())
  File "/usr/lib/pymodules/python2.7/mysql/connector/connection.py", line 179, in recv_plain
    errors.raise_error(buf)
  File "/usr/lib/pymodules/python2.7/mysql/connector/errors.py", line 82, in raise_error
    raise get_mysql_exception(errno,errmsg)
mysql.connector.errors.NotSupportedError: 1148: The used command is not allowed with this MySQL version

我要上传大量数据...我不敢相信不支持12.04，必须使用12.04.

I have a lot of data to upload... I can't believe 12.04 is not supported and I have to use 12.04.

推荐答案

这不是一个真正的python问题...但是，长远来说，它是由Ubuntu> 12.04编译和分发的mysql，不支持使用load data local infile直接从mysql客户端直接获取.

Not really a python question... but the long and short of the matter is that mysql, as compiled and distributed by Ubuntu > 12.04, does not support using load data local infile directly from the mysql client as is.

如果您在MySQL参考文档中搜索错误1148，请在上面链接的页面的下方进一步添加注释:

If you search the MySQL Reference Documentation for Error 1148, further down the page linked above, in the comments:

亚伦·彼得森(Aaron Peterson)在2005年11月9日下午4:35发布

Posted by Aaron Peterson on November 9 2005 4:35pm

从FreeBSD端口进行默认安装后，我不得不使用命令行 mysql -u用户-p --local-infile管理 启动mysql监视器，否则LOAD DATA LOCAL命令失败，并显示如下错误 以下:

With a defalut installation from FreeBSD ports, I had to use the command line mysql -u user -p --local-infile menagerie to start the mysql monitor, else the LOAD DATA LOCAL command failed with an error like the following:

错误1148(42000):此MySQL版本不允许使用命令

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

...起作用的.

... which does work.

monte@oobun2:~$ mysql -h localhost -u monte -p monte --local-infile
Enter password: 

...

mysql> LOAD DATA LOCAL INFILE 'pet.txt' INTO TABLE pet;
Query OK, 8 rows affected (0.04 sec)
Records: 8  Deleted: 0  Skipped: 0  Warnings: 0

mysql> SELECT * FROM pet;
+----------+--------+---------+------+------------+------------+
| name     | owner  | species | sex  | birth      | death      |
+----------+--------+---------+------+------------+------------+
| Fluffy   | Harold | cat     | f    | 1993-02-04 | NULL       |
| Claws    | Gwen   | cat     | m    | 1994-03-17 | NULL       |
| Buffy    | Harold | dog     | f    | 1989-05-13 | NULL       |
| Fang     | Benny  | dog     | m    | 1990-08-27 | NULL       |
| Bowser   | Diane  | dog     | m    | 1979-08-31 | 1995-07-29 |
| Chirpy   | Gwen   | bird    | f    | 1998-09-11 | NULL       |
| Whistler | Gwen   | bird    | NULL | 1997-12-09 | NULL       |
| Slim     | Benny  | snake   | m    | 1996-04-29 | NULL       |
| Puffball | Diane  | hamster | f    | 1999-03-30 | NULL       |
+----------+--------+---------+------+------------+------------+
9 rows in set (0.00 sec)

mysql>

我通常不需要通过代码加载数据，因此足以满足我的需求.如果这样做，并且有能力/权限来编辑您的mysql配置文件，那么适当部分的local-infile=1行可能会更简单.

I generally don't need to load data via code, so that suffices for my needs. If you do, and have the ability/permissions to edit your mysql config file, then the local-infile=1 line in the appropriate section(s) may be simpler.

这篇关于在Ubuntu 12.04和MySQL中无法加载数据本地文件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！