MySQL: Enable LOAD DATA LOCAL INFILE

Question

I'm running Mysql 5.5 on Ubuntu 12 LTS. How should I enable LOAD DATA LOCAL INFILE in my.cnf?

I've tried adding local-infile in my config at various places but I'm still getting the "The used command is not allowed with this MySQL version"

Answer 1

I used below method, which doesn't require any change in config, tested on mysql-5.5.51-winx64 and 5.5.50-MariaDB:

put 'load data...' in .sql file (ex: LoadTableName.sql)

LOAD DATA INFILE 'D:\\Work\\TableRecords.csv' INTO TABLE tbl1 FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES (col1,col2);

then:

mysql -uroot -pStr0ngP@ss -Ddatabasename -e "source D:\Work\LoadTableName.sql"

Answer 2

The my.cnf file you should edit is the /etc/mysql/my.cnf file. Just:

sudo nano /etc/mysql/my.cnf

Then add:

[mysqld]
local-infile 

[mysql]
local-infile 

The headers [mysqld] and [mysql] are already given, just locate them in the file and add local-infile underneath each of them.

It works for me on MySQL 5.5 on Ubuntu 12.04 LTS.

Answer 3

I solved this problem on MySQL 8.0.11 with the mysql terminal command:

SET GLOBAL local_infile = true;

I mean I logged in first with the usual:

mysql -u user -p*

After that you can see the status with the command:

SHOW GLOBAL VARIABLES LIKE 'local_infile';

It should be ON. I will not be writing about security issued with loading local files into database here.

Answer 4

For those of you looking for answers to make LOAD DATA LOCAL INFILE work like me, this might probably work. Well it worked for me, so here it goes. Install percona as your mysql server and client by following the steps from the link. A password will be prompted for during the installation, so provide one that you'll remember and use it later. One the installation is done, reboot your system and test if the server is up and running by going to the terminal and typing mysql -u root -p and then the password. Try running the command LOAD DATA LOCAL INFILE now.. Hope it works :)

BTW I was working on Rails 2.3 with Ruby 1.9.3 on Ubuntu 12.04.

Answer 5

in case your flavor of mysql on ubuntu does NOT under any circumstances work and you still get the 1148 error, you can run the load data infile command via command line

open a terminal window

run mysql -u YOURUSERNAME -p --local-infile YOURDBNAME

you will be requested to insert mysqluser password

you will be running MySQLMonitor and your command prompt will be mysql>

run your load data infile command (dont forget to end with a semicolon ; )

like this:

load data local infile '/home/tony/Desktop/2013Mini.csv' into table Reading_Table FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n';

Answer 6

This went a little weird for me, from one day to the next one the script that have been working since days just stop working. There wasn´t a newer version of mysql or any kind of upgrade but I was getting the same error, so I give a last try to the CSV file and notice that the end of lines were using \n instead of the expected ( per my script ) \r\n so I save it with the right EOL and run the script again without any trouble.

I think is kind of odd for mysql to tell me The used command is not allowed with this MySQL version since the reason was completely different.

My working command looks like this:

LOAD DATA LOCAL INFILE 'file-name' IGNORE INTO TABLE table-name CHARACTER SET latin1 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"' LINES TERMINATED BY '\r\n' IGNORE 1 LINES.