Friday, March 15, 2013

Partly to make it easy for me to find again, and partly for anyone else who might have this issue:

Partly to make it easy for me to find again, and partly for anyone else who might have this issue:

If you are trying to import a CSV file into MYSQL from the command line and you are getting the error ERROR 1148 (42000): The used command is not allowed with this MySQL version

It is because they have tightened security in the later versions of MYSQL like the one UBUNTU is using. You could  edit your .cnf file to enable this option or recompile the binary with local-infile enabled.

However assuming it really is more secure with this option disabled the best thing is just to over-ride the setting once when you do the actual import.

Here is what just worked for me:

Fire up the client from the command line using the --local-infile option to overide the default settings:

mysql -u user -p  name_of_db --local-infile

load data local infile 'yourcsv.txt' into table 'Your_Table' fields terminated by ',' 
enclosed by '"'
lines terminated by '\n'

(somecolumn, anothercolum, asmanycolumnnamesasyoulike);

Don't forget the semi colon at the end, hit return and wait for a confirmation of success.

Query OK, 1772 rows affected, 1771 warnings (1 min 1.96 sec)

Translate