MySQL : Load data infile

Question

I am getting error when using load data to insert the query .

"load data infile '/home/bharathi/out.txt' into table Summary"

This file is there in the location . But mysql throws the below error . ERROR 29 (HY000): File '/home/bharathi/out.txt' not found (Errcode: 13)

show variables like 'data%';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /var/lib/mysql/ |
+---------------+-----------------+

Data Dir is pointing to root permissioned folder . I can't change this variable because it's readonly .

How can I do the load data infile operation ?

I tried changing file permissions , load data local infile . It wont work .

Answer 1

As documented under LOAD DATA INFILE Syntax:

For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all. Also, to use LOAD DATA INFILE on server files, you must have the FILE privilege. See Section 6.2.1, “Privileges Provided by MySQL”. For non-LOCAL load operations, if the secure_file_priv system variable is set to a nonempty directory name, the file to be loaded must be located in that directory.

You should therefore either:

• Ensure that your MySQL user has the FILE privilege and, assuming that the secure_file_priv system variable is not set:

  • make the file readable by all; or

  • move the file into the database directory.

• Or else, use the LOCAL keyword to have the file read by your client and transmitted to the server. However, note that:

  LOCAL works only if your server and your client both have been configured to permit it. For example, if mysqld was started with --local-infile=0, LOCAL does not work. See Section 6.1.6, “Security Issues with LOAD DATA LOCAL”.

Answer 2

The solution which really worked for me was to:

sudo chown mysql:mysql /path/to/the/file/to/be/read.csv

Adding it for future reference.