4

My understanding is that exporting a file (e.g. csv) from MariaDB is restricted to specified folders by default. Thus, I have been accessing my exported files at the tmp folder so far. I wanted to ask if there was a way to change this to another folder i.e. /home/user/projects? I am using Raspbian Stretch as my OS. As a root user of my SQL database, I checked that I have full grant privileges. 

+---------------------------------------------------------------------+
| Grants for root@localhost                                           |
+---------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' WITH GRANT OPTION |
| GRANT PROXY ON ''@'%' TO 'root'@'localhost' WITH GRANT OPTION       |
+---------------------------------------------------------------------+

However, when I query with MariaDB [mydatabase]> SELECT * from table1 into outfile '/home/user/projects/file1.csv';, I get this error:

ERROR 1 (HY000): Can't create/write to file '/home/user/projects/file1.csv' (Errcode: 13 "Permission denied"),

Is there something else I can do to be able to export my files to a folder of my choosing?

Updates: So far, I tried chmod 755 and 777 methods and creating a new user in the SQL client with granting of full privileges and file (and then logging in as that user to make the query), but have had no success .

Craver2000
  • 433
  • 1
  • 7
  • 24
  • I am using Raspbian Stretch OS. I have been getting denied permission responses in mySQL program when trying to output the file to `home/pi/projects`. However, no problems when exporting file to `tmp` folder. – Craver2000 Dec 26 '17 at 12:24
  • Please provide the output of `ls -ld /home/user/projects/` – Rick James Dec 29 '17 at 21:47

2 Answers2

5

I had this same problem and managed to solve by exporting the data to:

 '/var/tmp/file1.csv'

I made the login in mariadb like this:

sudo mysql -u root -p

I hope this help you!

Alexandre Neukirchen
  • 2,713
  • 7
  • 26
  • 36
  • 1
    This works for me too. For some reason I can't seem to create a new folder anywhere with permissions to grant mysql/mariadb write access, but `/var/tmp` seems to have the necessary permissions. – rosscova Jan 06 '20 at 02:09
2

it's a os directory permission issue. Change ownership to mysql with permission and try.

Vaibhav
  • 41
  • 3
  • 1
    How can I change the ownership? Is there a file that I can edit? – Craver2000 Dec 26 '17 at 12:39
  • 1
    chown mysql:mysql /home/pi/projects/ and try. if this doesn't work, change the permission. chmod 755 /home/pi/projects/ and try. Hope this helps. – Vaibhav Dec 27 '17 at 12:30
  • Ok, I've tried both `chown mysql:mysql /home/pi/projects/` and `sudo chown mysql:mysql /home/pi/projects/` and launch the mysql client, but still the query to output the file to that folder doesnt work. I'm still getting `ERROR 1 (HY000): Can't create/write to file '/home/pi/projects/file1.csv' (Errcode: 13 "Permission denied")` What do you mean by changing permission? – Craver2000 Dec 27 '17 at 12:36
  • 1
    `chmod 777 /home/pi/projects/`is not working for me as well. I also tried the solution here:https://stackoverflow.com/questions/6091427/mysql-into-outfile-access-denied-but-my-user-has-all-access-and-the-fold, where I created another user (pi) and granted all and file privileges, then log into the mysql client again as the pi user, and did the same query, but still no success. – Craver2000 Dec 27 '17 at 13:18
  • ok. Can you check entries in below file. cat /etc/apparmor.d/usr.sbin.mysqld – Vaibhav Dec 28 '17 at 06:23
  • and add below entries in /etc/apparmor.d/usr.sbin.mysqld file and restart apparmor '/etc/init.d/apparmor restart' /home/pi/projects/ r, /home/pi/projects/** rwk, – Vaibhav Dec 28 '17 at 06:28
  • On opening `cat /etc/apparmor.d/usr.sbin.mysqld`, it states: – Craver2000 Dec 28 '17 at 11:17
  • `# This file is intensionally empty to disable apparmor by default for newer # versions of MariaDB, while providing seamless upgrade from older versions # and from mysql, where apparmor is used. # By default, we do not want to have any apparmor profile for the MariaDB # server. It does not provide much useful functionality/security, and causes` – Craver2000 Dec 28 '17 at 11:18
  • `# several problems for users who often are not even aware that apparmor # exists and runs on their system. # Users can modify and maintain their own profile, and in this case it will # be used. # When upgrading from previous version, users who modified the profile # will be promptet to keep or discard it, while for default installs # we will automatically disable the profile.` – Craver2000 Dec 28 '17 at 11:18
  • please share the below commands output ls -al /home/pi/ | grep projects AND ps -ef | grep mysql – Vaibhav Dec 28 '17 at 11:34
  • `mysqlgrep: projects: No such file or directory grep: AND: No such file or directory grep: ps: No such file or directory` – Craver2000 Dec 28 '17 at 15:36
  • Can you share the "projects" directory (which is under /home/pi/) ownership and permission. for e.g. refer to below output. drwxr-xr-x 2 root root 4.0K Dec 29 06:00 projects – Vaibhav Dec 29 '17 at 05:59
  • Irrespective of who runs mysql and the -u argument, the outfile is written by the DAEMON. If in doubt, try OUTFILE '/tmp/whatever' and check the ownership of the resulting file. – Ale Apr 04 '20 at 10:23