Friday, March 7, 2014

LOAD DATA INFILE tips

If you're having troubles with LOAD DATA INFILE ("Errcode 13"):
  • copy the CSV file to the DB folder (/var/lib/mysql/DATABASE), and
  • use the filename only, not the full path.


If you're having troubles with LOAD DATA LOCAL INFILE ("The used command is not allowed with this MySQL version"):
  • temporary solution: mysql -uUSER -p --local-infile DATABASE
  • permanent solution: sudo nano /etc/mysql/my.cnf and insert local-infile under [mysql]; save the file, close the editor and type sudo service mysql restart.

Note: LOAD DATA LOCAL INFILE uses IGNORE. If you want to know why some records were skipped, you must use LOAD DATA INFILE (without LOCAL). (Reference)