Bug #14147 load_file only works from certain directories
Submitted: 19 Oct 2005 16:47 Modified: 25 Jan 2007 14:45
Reporter: Matt Singleton Email Updates:
Status: Duplicate Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1.14 OS:Linux (Fedora 4 (kernel 2.6.12-1.1447))
Assigned to: Assigned Account CPU Architecture:Any

[19 Oct 2005 16:47] Matt Singleton
Description:
the load file function only loads files from certain directories. The statement:

select load_file('/etc/my.cnf');

works fine, but the statement:

select load_file('/images/my.cnf'); 

does not and returns the error:

ERROR 13 (HY000): Can't get stat of '/images/my.cnf' (Errcode: 13)

This is with identical files and permissions on the directories. The directories are on the same physical filesystem on the same server.

How to repeat:
Try the statements above with the same file and permissions. The load_file function only seems to work for files directly under /etc .
[19 Oct 2005 19:08] Miguel Solorzano
It is a permission issue or a wrong file path. See below:

miguel@hegel:~/dbs/4.1> echo "this a test" > myfile.txt
miguel@hegel:~/dbs/4.1> bin/mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 5 to server version: 4.1.16-debug-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select load_file('/home/miguel/dbs/4.1/myfile.txt');
+----------------------------------------------+
| load_file('/home/miguel/dbs/4.1/myfile.txt') |
+----------------------------------------------+
| this a test
                                 |
+----------------------------------------------+
1 row in set (0.00 sec)

mysql> select load_file('/ome/miguel/dbs/4.1/myfile.txt');
ERROR 13 (HY000): Can't get stat of '/ome/miguel/dbs/4.1/myfile.txt' (Errcode: 2)
mysql>
[20 Oct 2005 11:54] Matt Singleton
It appears you are using a slightly older version of MySQL. My identical test below still does not work:

[zabbix@odin ~]$ echo "this is a test " > myfile.txt
[zabbix@odin ~]$ pwd
/home/zabbix
[zabbix@odin ~]$ ls -l myfile.txt
-rw-rw-r--  1 zabbix zabbix 16 Oct 20 12:51 myfile.txt
[zabbix@odin ~]$ mysql test -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 93 to server version: 4.1.14

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> select load_file('/home/zabbix/myfile.txt');
ERROR 13 (HY000): Can't get stat of '/home/zabbix/myfile.txt' (Errcode: 13)
[20 Oct 2005 13:45] Sergei Golubchik
And what are permissions of /home and /home/zabbix ?
[20 Oct 2005 13:59] Matt Singleton
Here are the directory permission details:

[zabbix@odin ~]$ ls -ld /home
drwxr-xr-x  7 root root 4096 Oct 17 17:19 /home
[zabbix@odin ~]$ ls -ld /home/zabbix
drwxr-xr-x  7 zabbix zabbix 4096 Oct 20 12:51 /home/zabbix
[20 Oct 2005 16:18] Sinisa Milivojevic
Hi!

Thank you for writting to us.

In short, this is expected behaviour. When MySQL is not run under uid of root, then some directories are visible and some are not.
[21 Oct 2005 10:16] Sergei Golubchik
This is of course wrong, whether MySQL  is run as root is irrelevant.

I'm verifying it because LOAD_FILE should not return 'cannot stat' error, it is documented to return NULL if permissions prohibit reading the file.

As for the problem itself - unfortunately we weren't able to repeat it :(
You can try to strace mysqld to see what it cannot stat.
Could it have something to do with SELinux ?
[21 Oct 2005 10:57] Alexander Keremidarski
Hello,

The fact that load_file() works in some directories, but not in others with same permissions is clear indication that SELinux is involved somehow.

Take a look please at bug report #12676 and check if disabling SELinux for mysqld resolves the problem. 
http://bugs.mysql.com/bug.php?id=12676
[21 Oct 2005 11:10] Matt Singleton
Yes, that fixes the problem.

When i changed SELinux to permissive mode using:

setenforce 0 [ or "Permissive" ]

the problem is resolved and there is no problem using the "load_file" function on files where permissions are set correctly.

SELinux was in "targeted" mode previously.
[5 Mar 2006 23:18] Hossam Hossny
I have the same problem on mysql 4.1.15 Fedora Core 3
File permissions are ok, SELinux was disabled and yet I still get the "ERROR 13 (HY000): Can't get stat of bluh.txt" error message.
[5 Mar 2006 23:21] Hossam Hossny
Ah, I forget to say that if the file was located in /var/lib/sql, for an instance, load_file works properly.
[15 Mar 2006 3:06] tvk kamtikar
Hi,
   I was also facing the same problem.But,after sevral trail n error ,cunclusion is that, if we copy the file (which is to be loaded) under /tmp folder, then it works fine.
TruptiK.
[21 Jun 2006 18:41] Rosa Morales
Hi,

 I am using a mysql server version: 5.0.22-max. I am trying to load data in a mysql table using:

 LOAD DATA INFILE 'Users/rosamorales/Documents/etc/1990list.txt' INTO TABLE first_list FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r\n';

 and I get the following error message:

 ERROR 13 (HY000): Can't get stat of '/usr/local/mysql/data/Users/rosamorales/Documents/etc/1990list.txt' (Errcode: 2)

Then I tried to disable SELinux By using: /usr/sbin/setenforce Permissive;

and I got the following message:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '/usr/sbin/setenforce Permissive' at line 1
[25 Jan 2007 14:42] Tatiana Azundris Nuernberg
Hi Rosa,
- this is a LOAD_FILE() bug, not a LOAD DATA INFILE bug
- The SELinux bit fails with a SQL error because it is not a SQL command, it should presumably be entered on the linux prompt instead
- The LOAD DATA gives an error that has a path that looks UNIXish, while the command gives a path that looks vaguely Windowsish; please note that if the INFILE is *not* on the machine the MySQL server runs on, you need to give the LOCAL keyword

http://dev.mysql.com/doc/refman/4.1/en/load-data.html
[25 Jan 2007 14:45] Tatiana Azundris Nuernberg
duplicate of 10418, fixed on [15 May 2006 14:37] in 4.1.20, 5.0.22