Question 112412 in Ubuntu Question posted in
The official Ubuntu documentation can be found here.

Ubuntu – Is there a standard way to get LOAD INFILE working?

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

to

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

changes the error message to:

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

2

Answers


  1. 0 
    Error Code: 2. File 'loadinfilefile.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:Userscomputer_nameDesktopfilename.file_extension' INTO TABLE table

    I hope It solves your problem.

    Login or Signup to reply.
  2. 0

    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

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search