MySQL Bugs: #2871: Too restricted LOAD DATA INFILE

Bug #2871	Too restricted LOAD DATA INFILE
Submitted:	18 Feb 2004 8:21	Modified:	18 Feb 2004 10:34
Reporter:	Peter Zaitsev (Basic Quality Contributor)	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	4.1.1	OS:	Other (IRIX)
Assigned to:	Dean Ellis	CPU Architecture:	Any

Description:
It looks like LOAD DATA INFILE is too restrictive:

mysql> load data infile "/home/projects/peterz/Contig.tab" into table Contig;
ERROR 1085 (HY000): The file '/home/projects/peterz/Contig.tab' must be in the database directory or be readable by all

This file is however in the database directory:

| connect_timeout                 | 5                                      |
| datadir                         | /home/projects/peterz/                 |
| date_format                     | %Y-%m-%d

How to repeat:
Just start server and attempt to load the file which is not readable by all but
in the database directory:

genome[peterz]:/home/projects/peterz> ls -la Contig.tab
-rw-r-----    1 peterz   visitor   933473866 Feb 18 14:24 Contig.tab

(MySQL Servrer is using "peterz")

The file needs to be in the subdirectory for the database itself, rather than the datadir.

Peter's example may not be quite correct, but the same result
occurs when you specify a filename that *is* in a database
directory.

Example:
My data directory is /var/mysql/40018/data and I have
a 400-mode file there named junk.  The following two
commands actually are equivalent, but only one of them
works:

mysql> load data  infile '/var/mysql/40018/data/test/junk' into table t;
ERROR 1085: The file '/var/mysql/40018/data/test/junk' must be in the database directory or be 
readable by all
mysql> load data  infile 'junk' into table t;
Query OK, 27 rows affected (0.00 sec)
Records: 27  Deleted: 0  Skipped: 0  Warnings: 0

Correction to my previous comment: the file 'junk' is
in the test subdirectory of the database directory.

Arghhhhh! Correction to my previous correction:
The file 'junk' is in the test subdirectory of the
*data* directory, not the test subdirectory of the
database directory.

Where's my coffee?

So, if you specify a path the file must be world-readable.  If you do not, it must be within the relevant directory (and the user mysqld runs under must still be able to access it).

We could convert this to a documentation bug.

This is expected behavior and is documented here:

http://dev.mysql.com/doc/refman/4.1/en/load-data.html
http://dev.mysql.com/doc/refman/5.0/en/load-data.html
(etc)
----------
For security reasons, when reading text files located on the server, the files must either reside in the database directory or be readable by all.
----------