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?

  • You need to install a LAMP server and then add phpMyAdmin – eyoung100 Dec 1 '14 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. – Chris Davidson Dec 1 '14 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? – CodeMed Dec 1 '14 at 19:33
  • 1
    Have you considered SELinux? Try setenforce 0 as root then try again. – garethTheRed Dec 1 '14 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. – garethTheRed Dec 1 '14 at 20:34
1

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
  • hours of googling, and this was the solution. Thanks! – dmohr Sep 24 '16 at 0:52
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.