LOAD DATA INFILE Error Code 13

Question

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

User · Answer

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 !

User · Answer

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

User · Answer

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  gt  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

User · Answer

If you are using Fedora RHEL CentOO you can disable temporarily SELinux   setenforce 0   Load your data and then enable it back   setenforce 1

User · Answer

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

User · Answer

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

User · Answer

GRANT ALL PRIVILEGES ON db name   TO  db user   localhost   GRANT FILE on    to  db user   localhost  IDENTIFIED BY  password   FLUSH PRIVILEGES  SET GLOBAL local infile   1  Variable in the config of the MYSQL  secure-file-priv  must be empty line

User · Answer

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 gt  LOAD DATA LOCAL INFILE   home hongsoog study mysql member dat      - gt  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      you will get      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

User · Answer

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

User · Answer

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    change you csv file to  tmp folder so that it mysqls can read it  import to db   mysql gt  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

User · Answer

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  amp  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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

I use Ubuntu 12 04  I had to create the table  then do one of these   Using local gives an error  mysql 5 5     gt  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

User · Answer

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

[mysql] LOAD DATA INFILE Error Code : 13

Examples related to mysql

Examples related to file-io