[mysql] LOAD DATA INFILE Error Code : 13

In my remote MySQL, when I try to execute this query, I am getting the MySQL Error Code : 13.

Query -

LOAD DATA INFILE 
'/httpdocs/.../.../testFile.csv'
INTO TABLE table_temp
FIELDS TERMINATED BY ','
LINES TERMINATED BY '\\r \\n'
(sku, qty);

Error Code : 13 Can't get stat of '/httpdocs/.../.../testFile.csv' (Errcode: 2)

a. The database userlogin has all the grant priviliges.

CREATE USER 'userName'@'%' IDENTIFIED BY '************';

GRANT ALL PRIVILEGES ON * . * TO 'userName'@'%' IDENTIFIED BY '************' WITH GRANT OPTION MAX_QUERIES_PER_HOUR 0 MAX_CONNECTIONS_PER_HOUR 0 MAX_UPDATES_PER_HOUR 0 MAX_USER_CONNECTIONS 0 ;

GRANT ALL PRIVILEGES ON `userName\_%` . * TO 'userName'@'%';

b. I have also set the file and folder permission to chmod 777 (rwxrwxrwx) using FTP Tool

I have experienced same problem and applied the solutions above.

First of all my test environment are as follows

• Ubuntu 14.04.3 64bit
• mysql Ver 14.14 Distrib 5.5.47, for debian-linux-gnu (x86_64) using readline 6.3 (installed by just 'sudo apt-get install ...' command)

My testing results are

i) AppArmor solution only work for /tmp cases.

ii) Following solution works without AppArmor solution. I would like to appreciate Avnish Mehta for his answer.

$ mysql -u root -p --in-file=1
...
mysql> LOAD DATA LOCAL INFILE '/home/hongsoog/study/mysql/member.dat'
    -> INTO TABLE member_table;

Important three points are

• start mysql client with --in-file=1 option
• use LOAD DATA LOCAL INFILE instead of LOAD DATA INFILE

• check all path element have world read permission from the / to data file path. For example, following subpath should be world readable or mysql group readable if INFILE is targeting for '/home/hongsoog/study/mysql/memer.dat'

  • /home
  • /home/hongsoog
  • /home/hongsoog/study/mysql
  • /home/hongsoog/study/mysql/member.data

When you start mysql client WITHOUT "--in-file=1" option and use

LOAD DATA LOCAL INFILE ...

ERROR 1148 (42000): The used command is not allowed with this MySQL version

In summary, "--in-file=1" option in mysql client command and "LOAD DATA LOCAL INFILE ..." should go hand in hand.

Hope to helpful to anyone.

for Ubuntu users

I'm running mysql 5.6.28 on Ubuntu 15.10 and I just ran into the exact same problem, I had all the necessary flags in my.cnf tmpdir = /tmp local-infile=1 restarted mysql and I would still get LOAD DATA INFILE Error Code : 13

Just like Nelson mentionned the issue was "apparmor", sort of patronising mysql about permissions, I then found the solution thanks to this quick & easy tutorial.

basically, assuming your tmp dir would be /tmp :

Add new tmpdir entries to /etc/apparmor.d/local/usr.sbin.mysqld

sudo nano /etc/apparmor.d/local/usr.sbin.mysqld

*add this

/tmp/ r,
/mnt/foo/tmp/** rw,

Reload AppArmor

sudo service apparmor reload

Restart MySQL

sudo service mysql restart

I hope that'll help few Ubuntu-ers

Adding the keyword 'LOCAL' to my query worked for me:

LOAD DATA LOCAL INFILE 'file_name' INTO TABLE table_name

A detailed description of the keyword can be found here.

1. GRANT ALL PRIVILEGES ON db_name.* TO 'db_user'@'localhost';
2. GRANT FILE on .* to 'db_user'@'localhost' IDENTIFIED BY 'password';
3. FLUSH PRIVILEGES;
4. SET GLOBAL local_infile = 1;
5. Variable in the config of the MYSQL "secure-file-priv" must be empty line!!!

If you're using cPanel or phpmyadmin to import the CSV using LOAD DATA then be sure to Enable Use LOCAL keyword. This worked for me in a shared server environment.

I had a lot of trouble to fix this, there are two things to do :

• update file /etc/mysql/my.cnf with local-infile =1 at two locations after [mysql] paragraphs (this allows to use LOCAL INFILE sql command).

• update via sudo gedit /etc/apparmor.d/usr.sbin.mysqld so apparmor will allow you to write into the special files of /var/www/ by adding the lines:

/name of your directory/ r,

/name of your directory/* rw,

The name of your directory can be as /var/www/toto/ (this allows to use the directory where your files are located).

1. checkout this system variable local_infile is on

 SHOW VARIABLES LIKE 'local_infile';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| local_infile  | ON    |
+---------------+-------+

If not, restart mysqld with:

sudo ./mysqld_safe --local-infile

2. change you csv file to /tmp folder so that it mysqls can read it.

3. import to db

mysql> LOAD DATA INFILE '/tmp/a.csv'  INTO TABLE table_a  FIELDS TERMINATED BY ','  ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
Query OK, 18 rows affected (0.17 sec)
Records: 18  Deleted: 0  Skipped: 0  Warnings: 0

I too have struggled with this problem over the past few days and I too turned to stack overflow for answers.

However, no one seems to mention the simplest way to import data into MySQL is actually through their very own import data wizard tool!!

Just right-click on the table and it comes up there.

None of the above answers helped me, so just use this if you're stuck! :)

I know that this post is old, but this still comes up in search results. I couldn't find the solution to this problem online, so I ended up figuring it out myself. If you're using Ubuntu, then there is a program called "Apparmor" that is preventing MySQL from seeing the file. Here's what you need to do if you want MySQL to be able to read files from the "tmp" directory:

sudo vim /etc/apparmor.d/usr.sbin.mysqld

Once you are in the file, you're going to see a bunch of directories that MySQL can use. Add the line /tmp/** rwk to the file (I am not sure that it matters where, but here is a sample of where I put it):

  /etc/mysql/*.pem r,

  /etc/mysql/conf.d/ r,

  /etc/mysql/conf.d/* r,

  /etc/mysql/*.cnf r,

  /usr/lib/mysql/plugin/ r,

  /usr/lib/mysql/plugin/*.so* mr,

  /usr/sbin/mysqld mr,

  /usr/share/mysql/** r,

  /var/log/mysql.log rw,

  /var/log/mysql.err rw,

  /var/lib/mysql/ r,

  /var/lib/mysql/** rwk,


  /tmp/** rwk,


  /var/log/mysql/ r,

  /var/log/mysql/* rw,

  /var/run/mysqld/mysqld.pid w,

  /var/run/mysqld/mysqld.sock w,

  /run/mysqld/mysqld.pid w,

  /run/mysqld/mysqld.sock w,

Now all you need to do is reload Apparmor:

sudo /etc/init.d/apparmor reload

Note I used "vim", but substitute that with whatever your favorite text editor is that you know how to use.

Error 13 is nothing but the permission issues. Even i had the same issue and was unable to load data to mysql table and then resolved the issue myself.

Here's the solution:

Bydefault the

--local-infile is set to value 0

, inorder to use LOAD DATA LOCAL INFILE it must be enabled.

So Start mySQL like this :

mysql -u username -p --local-infile

This will make LOCAL INFILE enabled during startup and thus there wont be any issues using it !

CentOS 7+ Minimal Secure Solution: (should work with RedHat too)

chcon -Rv --type=mysqld_db_t /YOUR/PATH/

Explanation:

Knowing that you applied the good practice of using secure-file-priv=/YOUR/PATH/ in my.cnf in order to use load data infile sql statement, you still see the following:

ERROR 13 (HY000): Can't get stat of '/YOUR/PATH/FILE.EXT' (Errcode: 13 "Permission denied")

That's caused by SELinux Enforcing mode, it's not secure to change the mode to Permissive or disable SELinux.
In short,

chcon change SELinux security context of a file or files/directories in a similar way to how 'chown' or 'chmod' may be used to change the ownership or standard file permissions of a file.

SELinux Enforcing mode prevents mysqld from accessing directories with secure-context-type default_t, hence we need to change the secure-context-type of our path to mysqld_db_t. To see the current secure-context-type use the command:

ls --directory --scontext /YOUR/PATH/

In case you want to reset/undo the solution, then apply below command:

restorecon -Rv /YOUR/PATH/

The solution I shared is referenced in below links:

https://wiki.centos.org/HowTos/SELinux
https://mariadb.com/kb/en/selinux/
https://linux.die.net/man/8/mysqld_selinux

load data infile '/root/source/in.txt' into table employee; ==> Error Code:13

load data infile '/tmp/in.txt' into table employee; ==> works

CentOS release 6.6 (Final) - 5.5.32 MySQL Community Server (GPL)

something related to Linux file permissions

This is normally a file access permissions issue but I see your already addressing that in point b, but it's worth going over just in case. Another option is to use LOAD DATA LOCAL INFILE which gets past a lot of these issues of file access permissions. To use this method though you need to copy the file locally (in the mysql folder is best) first. •If LOCAL is specified, the file is read by the client program on the client host and sent to the server.

Insufficient Directory Permissions

The error in this example has resulted because the file you are trying to import is not in a directory which is readable by the user the MySql server is running as. Note that all the parent directories of the directory the is in need to be readable by the MySql user for this to work. Saving the file to /tmp will usually work as this is usually readable (and writable) by all users. The error code number is 13. Source

EDIT:

Remember you will need permissions on not just the folder the holds the file but also the upper directories.

Example from this post on the MySql Forums.

If your file was contained within the following strucutre: /tmp/imports/site1/data.file

you would need (I think, 755 worked) r+x for 'other' on these directories:

/tmp

/tmp/imports

As well as the main two:

/tmp/imports/site1

/tmp/imports/site1/data.file

You need the file and directory to be world-readable. Apologies if you've already tried this method but it may be worth retracing your steps, never hurts to double check.

If you are using Fedora/RHEL/CentOO you can disable temporarily SELinux:

setenforce 0

Load your data and then enable it back:

setenforce 1

I use Ubuntu 12.04, I had to create the table, then do one of these:

Using local gives an error (mysql 5.5):

> LOAD DATA LOCAL INFILE "file.csv" INTO table inverter FIELDS TERMINATED BY ',';
ERROR 1148 (42000): The used command is not allowed with this MySQL version

LOAD DATA INFILE command is disabled by default for security reasons, re-enable it here and it should work: https://stackoverflow.com/a/16286112/445131

You can import the csv-file using mysqlimport:

mysqlimport -u root -p --fields-terminated-by=',' --local dbname tablename.csv

Note the csv-file must have same name before the extension as the table.

I know, this is an old thread but there are still many folks facing problems with LOAD DATA INFILE!

There are quite a few answers which are great but for me, none of those worked :)

I am running MariaDB using mariadb/server docker container, it runs on Ubuntu but I did not have any issues with apparmor

For me the problem was quit simple, I had the file in /root/data.csv and of course mysql user can't access it!

On the other hand, folks recommending LOAD DATA LOCAL INFILE as an alternative, while it works, but its performance is not as good as the standard "LOAD DATA INFILE" because when using "LOCAL" it assumes the file is being loaded from a remote terminal and its handling becomes different.

For best performance, use "LOAD DATA INFILE" always unless you have to load the file from a remote server or your laptop/desktop directly. This is of course disabled due to security reasons so you have to allow "LOCAL_INFILE" through your server's config file "/etc/my.cnf" or "/etc/my.cnf.d/server.cnf" depending on your OS.

Finally, for me the container had "secure_file_priv" parameter defined to "/data"

b510bf09bc5c [testdb]> SHOW GLOBAL VARIABLES LIKE '%SECURE_FILE_%';
+------------------+--------+
| Variable_name    | Value  |
+------------------+--------+
| secure_file_priv | /data/ |
+------------------+--------+

This means, that the LOAD DATA INFILE will only work if the data file is being loaded from "/data" folder! Watch out for that and use the correct folder :)

Hope this helps someone.

Cheers.

There is one property in mysql configuration file under section [mysqld] with name - tmpdir

for example:

tmpdir = c:/temp (Windows) or tmpdir = /tmp (Linux)

and LOAD DATA INFILE command can perform read and write on this location only.

so if you put your file at that specified location then LOAD DATA INFILE can read/write any file easily.

One more solution

we can import data by following command too

load data local infile

In this case there is no need to move file to tmpdir, you can give the absolute path of file, but to execute this command, you need to change one flag value. The flag is

--local-infile

you can change its value by command prompt while getting access of mysql

mysql -u username -p --local-infile=1

Cheers

If you are using XAMPP on Mac, this worked for me:

Moving the CSV/TXT file to /Applications/XAMPP/xamppfiles/htdocs/ as following

LOAD DATA LOCAL INFILE '/Applications/XAMPP/xamppfiles/htdocs/file.csv' INTO TABLE `tablename` FIELDS TERMINATED BY ',' LINES TERMINATED BY ';'

OLD:

LOAD DATA INFILE '/home/root12/Downloads/task1.csv' INTO TABLE test.task FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

File '/home/root12/Downloads/task1.csv' not found (Errcode: 13 - Permission denied)

NEW WORKING FOR ME:

LOAD DATA LOCAL INFILE '/home/root12/Downloads/task1.csv' INTO TABLE test.task FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;

Query OK, 500 rows affected, 284 warnings (1.24 sec)
Query OK, 5000 rows affected, 2846 warnings (1.24 sec)