MySQL load data infile error 1148

The MySQL LOAD DATA INFILE can be very useful if you need to import hundreds of thousands of records from a CSV file. It inserts these records in practically no time (few seconds) and mostly if your table structure is correct, you won't get any errors or warnings.

If everything works fine using this kind of command:

LOAD DATA INFILE '/path/to/my/data.csv' INTO TABLE db.test
  FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\'
  LINES TERMINATED BY '\n' STARTING BY ''

But you get the following error message 1148:

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

In my version:

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

The fix is easy, and you might need both options (I did). Both resides in the /etc/mysql/my.cnf file. The first one under [mysql] and the second one under [client].

[mysql]
local-infile = 1

[client]
loose-local-infile=1

Restart Mysql daemon:

sudo /etc/init.d/mysql restart

Now time for import. You can use the same command in the MySQL client or if you run from the command line you have to specific the --local-infile option.

Then once the query is executed, you get something like this:

Query OK, 3167348 rows affected (0 min 41.37 sec)
Records: 3167348 Deleted: 0 Skipped: 0 Warnings: 0