CODEWITHSUNDEEP
mysqlsqldatabase
Mellon
Mellon

Reputation: 38902

LOAD DATA INFILE does not work

I am running MySQL on my Ubuntu machine. I checked /etc/mysql/my.cnf file, it shows my database temporary directory:

...
basedir     = /usr
datadir     = /var/lib/mysql
tmpdir      = /tmp
...

As it shows, my MySQL server temporary directory is /tmp .

I have a students.dat file, the content of this file is like following:

...
30  kate    name
31  John    name
32  Bill    name
33  Job     name
...

I copied the above students.dat file to /tmp directory. Then, I run the following command to load the data from students.dat file to the students table in my database:

LOAD DATA INFILE '/tmp/students.dat'
            INTO TABLE school_db.students
            FIELDS TERMINATED BY '\t'
            LINES TERMINATED BY '\n'
            (student_id, name, attribute)

But I got the error message in MySQL console:

ERROR 29 (HY000): File '/tmp/students.dat' not found (Errcode: 13)

Why mysql can not find the students.dat file though the file is under mysql temporary directory?

P.S.

The students table is like following (there are already 4 records in the table before run the LOAD DATA INFILE... query):

mysql> describe students;

    +-------------------+--------------+------+-----+---------+-------+
    | Field             | Type         | Null | Key | Default | Extra |
    +-------------------+--------------+------+-----+---------+-------+
    | student_id        | int(11)      | YES  |     | NULL    |       |
    | name              | varchar(255) | YES  | MUL | NULL    |       |
    | attribute         | varchar(12)  | YES  | MUL | NULL    |       |
    | teacher_id        | int(11)      | YES  |     | NULL    |       |
    +-------------------+--------------+------+-----+---------+-------+
    4 rows in set (0.00 sec)

Upvotes: 7

Views: 18173

Answers (2)

dhiman
dhiman

Reputation: 21

ERROR 29 (HY000): File '/tmp/file_name' not found (Errcode: 13)

This error occurs mainly when we try to load data file from any location to any table in mysql database.

Just change the owner of a file.

1) Check permissions of the file with this command: ls -lhrt <filename>

2) Then change ownership: chown mysql.mysql <filename>

3) Now try LOAD DATA INFILE command. It will work.

Upvotes: 2

cristian
cristian

Reputation: 8744

Have a look at the sixth post from file not found error. It seems if you specify LOAD DATA LOCAL INFILE should work (They added the LOCAL keyword)

Upvotes: 10

Related Questions