If anyone of you trying to import some data from CSV directly into MySQL database, always use LOAD DATA LOCAL INFILE command. It is quite faster and easier to import lots of data within few seconds even more than 25MB of CSV data.
The syntax for the command can be found in details at http://dev.mysql.com/doc/refman/5.1/en/load-data.html but the simple one would be something like below:
> Login to the MySQL server first i.e. mysql -h localhost -u root -p (And give the root password).
> Use the specific database i.e. use
> The run the following command:
LOAD DATA LOCAL INFILE "/path/to/csvfile.csv" INTO TABLE
Note: Remember the fields should be exactly same as number of columns in the CSV.
The used command is not allowed with this MySQL version
This problem makes crazy and I had spent around 4 hours (half of the working day) to fix it. I was on Ubuntu 12.04 LTS and I was not sure where exactly to look into for this to fix. I tried to look at my.cnf but could not find anything out there. Actually this problem comes when the flag/setting “local-infile” is disabled.
First solution would be to add the following on the my.cnf itself:
Or Secondly you can simply enable it while logging into the MySQL server.
> mysql --local-infile=1 -u root -p
Then run the above LOAD DATA LOCAL INFILE… command and it will work like a charm and you will get all the data from CSV to your MySQL database table.
Hope this helps you !
2 Comments to “MySQL LOAD DATA LOCAL INFILE command and “The used command is not allowed with this MySQL version” error”