MySQL into outfile not working from PHP application but working in mysql

Question

I am trying to do an "INTO OUTFILE" query to output a csv file on a Linux CentOS machine using PHP application.

Here is the query:

(SELECT 'Date', 'Customer', 'Customer name', 'Commission', 'User', 'Value') UNION (SELECT t1.`DATE`, t1.`CUSTOMER`, t1.`CUSTOMERNAME`, t1.`COMMISSION`, t1.`USER`, t1.`VALUE` FROM `raws` t1, `roles` t2 ORDER BY `CUSTOMER`, `CUSTOMERNAME`, `COMMISSION`, `USER`, `DATE` ASC INTO OUTFILE '/var/www/html/myapp/downloads/524e6b306c451.csv' FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY ' ')

Here are some details to the problem:

mysql runs the query fine and outputs a .csv file at the correct directory "/var/www/html/myapp/downloads"
The PHP app, however, returns an error that says "SQLSTATE[HY000]: General error: 1 Can't create/write to file"
both mysql and PHP are running using the same user, root
The PHP app runs fine on Windows machine with XAMPP
The PHP app runs if I exclude "into outfile" query.

Based on the above, I am convinced that it has something to do with file permission on my CentOS machine. However, even after I have done chmod -R 777 /var/www, I still get the same error.

Please help, and thanks in advance

EDIT:

ls -la /var/www/html/myapp/downloads looks like the following:

drwxrwsrwt. 2 mysql mysql  4096 Oct  4 10:37 .
drwxrwsrwx. 9 root  www    4096 Oct  4 07:56 ..
-rwxrwxrwx. 1 mysql mysql 55353 Oct  4 10:36 524e67124bba4.csv
-rwxrwxrwx. 1 mysql mysql 51195 Oct  4 10:04 524e7699a69a7.csv
-rwxrwxrwx. 1 mysql mysql 51195 Oct  4 10:07 524e7737c54ad.csv
-rwxrwxrwx. 1 mysql mysql 51195 Oct  4 10:08 524e7782a21a3.csv

What does ls -al /var/www/html/myapp/downloads output look like? — user1455836, Oct 04 '13 at 07:35

score 0 · Answer 1 · answered Oct 04 '13 at 07:32

It could be permission error. MySQL may not able to access your /tmp directory to wirte and create temporary files. Make sure /tmp is owned by root and sticky bit is set on /tmp directory. Type the following commands to see if it fixes the error:

chown root:root /tmp
chmod 1777 /tmp

and restart mysql server

score 0 · Answer 2 · edited May 23 '17 at 10:32

0

General error: 1 Can't create/write to file errcode:2 This would sort it out with chown.

Thanks & Regards,
Alok Thaker

edited May 23 '17 at 10:32

Community

1
1

answered Oct 04 '13 at 07:40

linux_fanatic

4,767
3
19
20

MySQL into outfile not working from PHP application but working in mysql

2 Answers2