Page 1 of 1

MySQL OUTFILE Errcode: 13 "Permission denied"

Posted: Sun Jun 10, 2018 8:35 pm
by cwc1911
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.

Re: MySQL OUTFILE Errcode: 13 "Permission denied"

Posted: Mon Jun 11, 2018 1:59 pm
by cwc1911
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

Re: MySQL OUTFILE Errcode: 13 "Permission denied"

Posted: Mon Jun 11, 2018 2:13 pm
by DougieLawson
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.]

Re: MySQL OUTFILE Errcode: 13 "Permission denied"

Posted: Mon Jun 11, 2018 4:12 pm
by cwc1911
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.