Thursday, June 27, 2013

Uploading data file to MySQL 5.5 on Ubuntu

I use MySQL 5.5.28 on my laptop running Ubuntu 12.04.3. Recently, I had tried to upload a datafile thus:

load data local infile 
'/home/nirmalya/GridSense/UserInfo.csv' 
into table gsdemoconfig.user_core_info 
fields terminated by ',' 
lines terminated by '\n';

Regular command, I have used it many times earlier. But, incidentally, never on this version of MySQL. MySQL came back with a stern negation:

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

Huh? What happened?

A bit of research brought up this little twist in the command to connect to MySQL:

mysql --host=localhost --port=3306 --user root -p --local-infile

The last option, 'local-infile' was the key.

Then, when I executed the same 'load data' command referred to earlier, result was as expected:

Query OK, 10 rows affected, 18 warnings (0.14 sec)
Records: 10  Deleted: 0  Skipped: 0  Warnings: 18


Nice little thing to know about, eh?

No comments:

Post a Comment