How can I get around MySQL Errcode 13 with SELECT INTO OUTFILE

Question

I am trying to dump the contents of a table to a csv file using a MySQL SELECT INTO OUTFILE statement   If I do   SELECT column1  column2 INTO OUTFILE  outfile csv  FIELDS TERMINATED BY     FROM table name    outfile csv will be created on the server in the same directory this database s files are stored in     However  when I change my query to   SELECT column1  column2 INTO OUTFILE   data outfile csv  FIELDS TERMINATED BY     FROM table name    I get   ERROR 1  HY000   Can t create write to file   data outfile csv   Errcode  13    Errcode 13 is a permissions error  but I get it even if I change ownership of  data to mysql mysql and give it 777 permissions   MySQL is running as user  mysql    Strangely I can create the file in  tmp  just not in any other directory I ve tried  even with permissions set such that user mysql should be able to write to the directory   This is MySQL 5 0 75 running on Ubuntu

User · Answer

You can do this     mysql -u USERNAME --password PASSWORD --database DATABASE --execute  SELECT  FIELD    FIELD  FROM  TABLE  LIMIT 0  10000   -X  gt  file xml

User · Answer

This problem has been bothering me for a long time  I noticed that this discussion does not point out the solution on RHEL Fecora  I am using RHEL and I do not find the configuration files corresponding to AppArmer on Ubuntu  but I solved my problem by making EVERY directory in the directory PATH readable and accessible by mysql  For example  if you create a directory  tmp  the following two commands make SELECT INTO OUTFILE able to output the  sql AND  sql file  chown mysql mysql  tmp chmod a rx  tmp   If you create a directory in your home directory  home tom  you must do this for both  home and  home tom

User · Answer

Which particular version of Ubuntu is this and is this Ubuntu Server Edition   Recent Ubuntu Server Editions  such as 10 04  ship with AppArmor and MySQL s profile might be in enforcing mode by default  You can check this by executing sudo aa-status like so     sudo aa-status 5 profiles are loaded  5 profiles are in enforce mode      usr lib connman scripts dhclient-script     sbin dhclient3     usr sbin tcpdump     usr lib NetworkManager nm-dhcp-client action     usr sbin mysqld 0 profiles are in complain mode  1 processes have profiles defined  1 processes are in enforce mode       usr sbin mysqld  1089  0 processes are in complain mode    If mysqld is included in enforce mode  then it is the one probably denying the write  Entries would also be written in  var log messages when AppArmor blocks the writes accesses  What you can do is edit  etc apparmor d usr sbin mysqld and add  data  and  data   near the bottom like so          usr sbin mysqld                     var log mysql  r         var log mysql   rw         var run mysqld mysqld pid w         var run mysqld mysqld sock w           data  r         data   rw          And then make AppArmor reload the profiles     sudo  etc init d apparmor reload   WARNING  the change above will allow MySQL to read and write to the  data directory  We hope you ve already considered the security implications of this

User · Answer

I had the same problem on a CentOs 6 7 In my case all permissions were set and still the error occured  The problem was that the SE Linux was in the mode  enforcing    I switched it to  permissive  using the command sudo setenforce 0  Then everything worked out for me

User · Answer

I just ran into this same problem  My issue was the directory that I was trying to dump into didn t have write permission for the mysqld process  The initial sql dump would write out but the write of the csv txt file would fail  Looks like the sql dump runs as the current user and the conversion to csv txt is run as the user that is running mysqld  So the directory needs write permissions for both users

User · Answer

You need to provide an absolute path  not a relative path   Provide the full path to the  data directory you are trying to write to

User · Answer

Does Ubuntu use SELinux   Check to see if it s enabled and enforcing    var log audit audit log may be helpul  if that s where Ubuntu sticks it -- that s the RHEL Fedora location

User · Answer

In my case  the solution was to make every directory in the directory path readable and accessible by mysql  chmod a rx   The directory was still specified by its relative path in the command line   chmod a rx  tmp chmod a rx  tmp migration etc

User · Answer

Some things to try    is the secure file priv system variable set  If it is  all files must be written to that directory  ensure that the file does not exist - MySQL will only create new files  not overwrite existing ones

User · Answer

I have same problem and I fixed this issue by following steps    Operating system   ubuntu 12 04 lamp installed suppose your directory to save output file is     var www csv       Execute following command on terminal and edit this file using gedit editor to add your directory to output file    sudo gedit  etc apparmor d usr sbin mysqld   now file would be opened in editor please add your directory there   var www csv   rw  likewise I have added in my file  as following given image          Execute next command to restart services     sudo  etc init d apparmor restart     For example I execute following query into phpmyadmin query builder to output data in csv file   SELECT colName1  colName2 colName3 INTO OUTFILE   var www csv OUTFILE csv  FIELDS TERMINATED BY     FROM tableName    It successfully done and write all rows with selected columns into OUTPUT csv file

User · Answer

MySQL is getting stupid here  It tries to create files under  tmp data      So what you can do is the following   mkdir  tmp data mount --bind  data  tmp data   Then try your query  This worked for me after hours of debugging the issue

User · Answer

I know you said that you tried already setting permissions to 777  but as I have an evidence that for me it was a permission issue I m posting what I exactly run hoping it can help  Here is my experience   tmp   pwd  Users username tmp tmp   mkdir bkptest tmp   mysqldump -u root -T bkptest bkptest mysqldump  Got error  1  Can t create write to file   Users username tmp bkptest people txt   Errcode  13  when executing  SELECT INTO OUTFILE  tmp   chmod a rwx bkptest  tmp   mysqldump -u root -T bkptest bkptest tmp   ls bkptest  people sql  people txt tmp

User · Answer

Ubuntu uses AppArmor and that is whats preventing you from accessing  data    Fedora uses selinux and that would prevent this on a RHEL Fedora CentOS machine    To modify AppArmor to allow MySQL to access  data  do the follow   sudo gedit  etc apparmor d usr sbin mysqld  add this line anywhere in the list of directories    data  rw   then do a    sudo  etc init d apparmor restart  Another option is to disable AppArmor for mysql altogether   this is NOT RECOMMENDED   sudo mv  etc apparmor d usr sbin mysqld  etc apparmor d disable   Don t forget to restart apparmor   sudo  etc init d apparmor restart

[mysql] How can I get around MySQL Errcode 13 with SELECT INTO OUTFILE?

Examples related to mysql

Examples related to sql

Examples related to into-outfile