cwc1911
Posts: 3
Joined: Sat May 26, 2018 3:25 pm

MySQL OUTFILE Errcode: 13 "Permission denied"

Sun Jun 10, 2018 8:35 pm

I am getting and error while trying to export data from a MySQL table to a file using the following command.

SQL:
select id, name
from test_data
into outfile '/home/pi/shared/test.csv'
fields terminated by ','
enclosed by '"'
lines terminated by '\n';

ERROR 1 (HY000): Can't create/write to file '/home/pi/shared/test.csv' (Errcode: 13 "Permission denied")

This is the logged MySQL user:
MariaDB [it_data]> select current_user();
+----------------+
| current_user() |
+----------------+
| pi@localhost |
+----------------+

This is the value of the secure_file_priv variable:
show variables like 'secure_file_priv' ;
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| secure_file_priv | |
+------------------+-------+

This is the permissions of the file and directory I am trying to write to:
pi@raspberrypi:/ $ ls -la /home/pi/shared
-rwxrwxrwx 1 pi pi 44 May 29 00:45 test.csv
drwxrwxrwx 2 pi pi 4096 Jun 10 15:56 shared

This is the my.cnf:
pi@raspberrypi:/ $ cat /home/pi/my.cnf
[mysqld]
secure-file-priv = "/home/pi/shared/"

This is the OS version:
pi@raspberrypi:/ $ cat /etc/os-release
PRETTY_NAME="Raspbian GNU/Linux 9 (stretch)"
NAME="Raspbian GNU/Linux"
VERSION_ID="9"
VERSION="9 (stretch)"
ID=raspbian
ID_LIKE=debian
HOME_URL="http://www.raspbian.org/"
SUPPORT_URL="http://www.raspbian.org/RaspbianForums"
BUG_REPORT_URL="http://www.raspbian.org/RaspbianBugs"

This is the MySQL version:
pi@raspberrypi:/ $ mysql -V
mysql Ver 15.1 Distrib 10.1.23-MariaDB, for debian-linux-gnueabihf (armv7l) using readline 5.2

I would appreciate some help getting this to work.

cwc1911
Posts: 3
Joined: Sat May 26, 2018 3:25 pm

Re: MySQL OUTFILE Errcode: 13 "Permission denied"

Mon Jun 11, 2018 1:59 pm

So I was able to create my output file by logging into MySQL as the root user and using a different path. I created this directory /tmp/data and used chmod 777 to grant everyone permissions. I am not sure if this the best solution.

I really want my database app user [ituser] to be able to connect to MySQL and create the output file in a directory my application has access too.

Maybe someone can give me some advice.

Thanks
CWC

User avatar
DougieLawson
Posts: 39813
Joined: Sun Jun 16, 2013 11:19 pm
Location: A small cave in deepest darkest Basingstoke, UK
Contact: Website Twitter

Re: MySQL OUTFILE Errcode: 13 "Permission denied"

Mon Jun 11, 2018 2:13 pm

mysql -u root -p

CREATE USER 'ituser'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL PRIVILEGES ON ituser.* TO 'ituser'@'localhost' IDENTIFIED BY 'mypass';
exit;

The GRANT allows ituser to do anything to his/her own database.

If you need ituser to be able to connect remotely you'll have to update /etc/mysql/my.cnf to change

Code: Select all

bind-address x.x.x.x
to

Code: Select all

bind-address 0.0.0.0
then restart mysql (aka mariadb). You'll have to add a second user entry
CREATE USER 'ituser'@'%' IDENTIFIED BY 'mypass';
to allow remote access. You can restrict remote access to your LAN only by replacing '%' with '192.168.1.0/24' [change that to your LAN subnet.]
Note: Any requirement to use a crystal ball or mind reading will result in me ignoring your question.

Criticising any questions is banned on this forum.

Any DMs sent on Twitter will be answered next month.
All fake doctors are on my foes list.

cwc1911
Posts: 3
Joined: Sat May 26, 2018 3:25 pm

Re: MySQL OUTFILE Errcode: 13 "Permission denied"

Mon Jun 11, 2018 4:12 pm

DougieLawson wrote:
Mon Jun 11, 2018 2:13 pm
mysql -u root -p

CREATE USER 'ituser'@'localhost' IDENTIFIED BY 'mypass';
GRANT ALL PRIVILEGES ON ituser.* TO 'ituser'@'localhost' IDENTIFIED BY 'mypass';
exit;

The GRANT allows ituser to do anything to his/her own database.

If you need ituser to be able to connect remotely you'll have to update /etc/mysql/my.cnf to change

Code: Select all

bind-address x.x.x.x
to

Code: Select all

bind-address 0.0.0.0
then restart mysql (aka mariadb). You'll have to add a second user entry
CREATE USER 'ituser'@'%' IDENTIFIED BY 'mypass';
to allow remote access. You can restrict remote access to your LAN only by replacing '%' with '192.168.1.0/24' [change that to your LAN subnet.]
Thanks, I will give your recommendations a try.

Return to “General discussion”