1

I am trying to run the following command from inside the mysql command line client in the CentOS terminal: 

SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';')  
FROM INFORMATION_SCHEMA.TABLES where  table_schema in ('justatest')  
INTO OUTFILE '/home/user/tmp/truncate.sql';

The result is this error: 

ERROR 1 (HY000): Can't create/write to file '/home/user/tmp/truncate.sql' 
(Errcode: 13 - Permission denied)

I then opened a new terminal, typed su -, gave the password, and then typed the following commands: 

chown root:root /home/user/tmp
chmod 1777 /home/user/tmp

But I still get the same error when I run the original SQL code at top above.

EDIT:

I also get the same error when I try: 

chown username:username /home/user/tmp
chmod 1777 /home/user/tmp

and the same error message when I try: 

chown mysql:mysql /home/user/tmp
chmod 1777 /home/user/tmp

How can I resolve this error?

Improve this question
17
  • You need to install a LAMP server and then add phpMyAdmin Commented Dec 1, 2014 at 19:29
  • Which user are you logged in as before you login to MySQL? Even if you've chmod'ed 1777 /home/user/tmp that may not mean that the user has permissions to access directories below that. Commented Dec 1, 2014 at 19:32
  • @eyoung100 Thank you but I do not need the P (PHP or Python). I already have apache and mysql installed in CentOS. Isn't there some way to just change the write permissions via the command prompt using without adding more software? Commented Dec 1, 2014 at 19:33
  • 1
    Have you considered SELinux? Try setenforce 0 as root then try again. Commented Dec 1, 2014 at 20:17
  • 1
    Then the error was due to SELinux blocking MySQL writing to your home directory. You should follow the instructions in the alert to enable MySQL to write to your home directory and then re-enable SELinux with setenforce 1. Don't leave SELinux disabled as that is a security issue. Commented Dec 1, 2014 at 20:34

3 Answers 3

Reset to default
2

Just an other way to achieve what you're looking for...

Execute the mysql command without the path, just the wanted file name.

SELECT Concat('TRUNCATE TABLE ',table_schema,'.',TABLE_NAME, ';')
FROM INFORMATION_SCHEMA.TABLES where  table_schema in ('justatest')  
INTO OUTFILE 'truncate.sql';

This will save your file in the default mysql dir (/var/lib/mysql/database)

Then you can move the file and chage the owner

$ sudo mv /var/lib/mysql/database/truncate.sql /home/user/tmp
$ sudo chown user:user /home/user/tmp/truncate.sql
Improve this answer
1
  • hours of googling, and this was the solution. Thanks! Commented Sep 24, 2016 at 0:52
1

1. Setup correct rights:

Your path /home/user/tmp must have correct rights on all levels:

/home
/home/user
/home/user/tmp

all these folders must be available for mysql user, not only endpoint tmp directory

this command is not complete:

chown mysql:mysql /home/user/tmp

it adds owner for tmp folder only, it doesn't provide access to home or user

2. check SELinux activity: configure that or disable (if you cannot)

for disable:

edit /etc/selinux/config:

SELINUX=disabled

then restart or execute command

setenforce 0
Improve this answer
1
  1. Make sure your sql account have File Priviledges
  2. Outfile into temp directory. example 'INTO OUTFILE /tmp/yourfile' Or directrory that mysql has access
  3. For download the file within your site, just make symlink that '/tmp' into your public html folder

note : if you cant delete your file because of permission issue, then your filename must be unique.

Improve this answer

You must log in to answer this question.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.