ubuntu 12 mysql,在Ubuntu 12.04和MySQL中无法加载数据本地文件

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

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

解决方案

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.

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

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

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:

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>

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.