MySQL Bugs: #14147: load_file only works from certain directories

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

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 .

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>

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)

And what are permissions of /home and /home/zabbix ?

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

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.

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 ?

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

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.

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.

Ah, I forget to say that if the file was located in /var/lib/sql, for an instance, load_file works properly.

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.

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

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

duplicate of 10418, fixed on [15 May 2006 14:37] in 4.1.20, 5.0.22