Problem with mysql LOAD DATA INFILE

[dws]

I have an script that make use of LOAD DATA INFILE and SELECT INTO OUTFILE functions of mysql, i have give file privileges to mysql user and edited /etc/my.cnf to
[mysqld]
local-infile=1

Files to load are located in /home/..../tmp/filetoload.csv and permisión of files are 777

All querys with select into OUTFILE results in:

Can't create/write to file '/home/user/public_html/Nueva1/tmp/temp_clientes.csv' (Errcode: 13)

And those using load local data infile results in:

#2000 - Can't find file '/home/user/public_html/Nueva1/lectura/clientes.csv'.

I have Mysql 5.1
php5 CGI

Could somebody help me please, I´m bagging agaisnt my monitor without having a clue of what it`s happening

Thanks in advance

 

[SeLLeRoNe]

Ensure that /home/user/public_html/Nueva1/tmp/

have the right owner and permissions.

He cant write it say, so, permission error.

Regards

 

[zEitEr]

@dws

I'd rather say that MySQL does not have permissions to get into /home/user/, as it runs from username mysql. Thus, you should better select different file location or change owner on /home/user/, the second is not save.

 

[dws]

@dws

I'd rather say that MySQL does not have permissions to get into /home/user/, as it runs from username mysql. Thus, you should better select different file location or change owner on /home/user/, the second is not save.

I`m almost sure that this the problem. But it`s rare as perm of dir is set as 777 and this script has been moved from another server with the same config and worked perfectly.

Why mysql does not read nor write a dir in /home/user/public_html? this is the question. I have added mysql to acces group as mail etc. even i have give mysql as a secondary group the user group, but nothing of this has worked.

I can't move files as dependens on external programs that conet to this directory

Thanks very much for reply

 

[zEitEr]

Try manually check it:

sudo -u mysql ls -l /home/user/public_html/Nueva1/tmp/
sudo -u mysql ls -l /home/user/public_html/Nueva1/
sudo -u mysql ls -l /home/user/public_html
sudo -u mysql ls -l /home/user/

and find what is wrong.

 

[dws]

Hi Alex there are no problem with ls command

[root@mail Nueva1]# sudo -u mysql ls -l /home/user/public_html/Nueva1/tmp/total 0

with any other user says acces denied except propietary user,
but when I make a query in mysql via phpmyadmin it throw this error

Query:

SELECT * INTO OUTFILE '/home/user/public_html/Nueva1/tmp/temp_clientes.csv' FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '%' LINES TERMINATED BY '\r\n' FROM temp_clientes

Response:

Can't create/write to file '/home/user/public_html/Nueva1/tmp/temp_clientes.csv' (Errcode: 13)

 

[zEitEr]

And if you change just for testing purposes the file location to something like /home/tmp/temp_clientes.csv, will it work?

And is there anything in mysql error log about the issue?

 

[zEitEr]

And what about if you do some writing?

sudo -u mysql touch /home/user/public_html/Nueva1/tmp/test.file
sudo -u mysql touch /home/user/public_html/Nueva1/test.file
sudo -u mysql touch /home/user/public_html/test.file
sudo -u mysql touch /home/user/test.file

 

[dws]

And what about if you do some writing?

sudo -u mysql touch /home/user/public_html/Nueva1/tmp/test.file
sudo -u mysql touch /home/user/public_html/Nueva1/test.file
sudo -u mysql touch /home/user/public_html/test.file
sudo -u mysql touch /home/user/test.file

Thanks very much for your patien zEitEr the problem was that after checking permissions over all directories and server with script for setting up php as cgi

drwx--x--- 10 user access 4096 Sep 19 20:55 user

and has to be chomoded to +x

drwx--x--x 10 user access 4096 Sep 19 20:55 user

Now is working

 

[veramix]

solved directadmin + lodal data infile

When you want run local data infile with directadmin you must disabled open_basedir in php.ini and
in httpd.conf on users in section VirtualHost in /usr/local/directadmin/data/users/user/httpd.conf

must hash this line:
# php_admin_value open_basedir /home/aligato/:/tmp:/var/tmp:/usr/local/lib/php/:/usr/local/php54/lib/php/