MySQL: load infile data

Question

I get an error when using load data to insert a request.

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

This file is in this place. But mysql throws the following 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 points to the root folder. I cannot change this variable because it is read-only.

How can I perform a load data entry operation?

I tried changing file permissions, uploading data locally. This will not work.

Answer 1

As described in LOAD DATA INFILE Syntax :

For security reasons, when reading text files located on the server, the files should either be in the database directory or read by all. In addition, to use the LOAD DATA INFILE server files, you must have the FILE privilege. See section 6.2.1, “Privileges Granted by MySQL” . For boot operations without LOCAL , if the secure_file_priv system variable secure_file_priv set to a non-empty directory name, the downloaded file must be located in this directory.

Therefore, you must:

• Make sure your MySQL user has the FILE privilege and assuming that the secure_file_priv system variable secure_file_priv not set:

  • make the file readable by everyone; or

  • move the file to the database directory.

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

  LOCAL only works if your server and your client are configured to allow 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 that really worked for me was this:

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

Add it for future reference.