Is there a standard way to get LOAD INFILE working?-CodePudding

Load data infile has always been a pain in the ass to set up and there does not seem to be standard way to get it to work but I have always been able to get it to work ….eventually except now.

I am struggling with

Error Code: 29. File '/loadinfile/file.csv' not found (OS errno 13 - Permission denied)

The system is as follows:

MySQL version 8.0.29-0ubuntu0.22.04.2 Ubuntu 22.04

The load file directory is owned by mysql:mysql and same with the file and I have tried chmod 757 and even 777

In the config file I have tried the following variations:

secure_file_priv = "/loadinfile/"

secure_file_priv = '/loadinfile/'

secure_file_priv = ""

secure_file_priv = ''

The following is also set :

[mysqld]

local-infile = 1

[client]

loose-local-infile=1

Changing


LOAD DATA INFILE '/loadinfile/file.csv' INTO TABLE table

LOAD DATA LOCAL INFILE '/loadinfile/file.csv' INTO TABLE table

changes the error message to:

Error Code: 2. File '\loadinfile\file.csv' not found (OS errno 2 - No such file or directory)

CodePudding user response：

Error Code: 2. File '\loadinfile\file.csv' not found (OS errno 2 - No such file or directory)

Above message indicates that your directory where the file is located is wrong! You must define the full path:

For example In Mac If your file resides on Desktop,It must be like this :

LOAD DATA LOCAL INFILE '/Users/computer_name/Desktop/filename.file_extension' INTO TABLE table

In windows

LOAD DATA LOCAL INFILE 'C:\Users\computer_name\Desktop\filename.file_extension' INTO TABLE table

I hope It solves your problem.

CodePudding user response：

Error Code: 2. indicates invalid file path. Keep the LOCAL INFILE option and either specify the full path of the CSV file or make sure the CSV file path is correct. give it a try with Full path and see if it solves the problem or not. From MySQL Docs

If LOCAL is specified, the file must be located on the client host. The client program reads the file, locating it as follows:

If the file name is an absolute path name, the client program uses it as given.

If the file name is a relative path name, the client program looks for the file relative to its invocation directory

Edited Make sure your mysqld.cnf or your main MySQL config file has the following variables

secure_file_priv = ""
local_infile=1

And your Global variable local_infile is OFF, turn it ON. you can check that if you run this query

SHOW GLOBAL VARIABLES LIKE 'local_infile';

if it is OFF, turn it ON

And lastly, Make sure you restart your SQL service to reflect the changes. you can do that if you execute

service mysql restart

Hope that solves your problem