MySQL Bugs: #6834: server&client 4.1.7 on a Mac OSX 10.3.6

Bug #6834	server&client 4.1.7 on a Mac OSX 10.3.6
Submitted:	26 Nov 2004 0:09	Modified:	1 Dec 2004 14:12
Reporter:	Jon	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	version: 4.1.7-standard-log	OS:	MacOS (Mac OSX 10.3.6)
Assigned to:		CPU Architecture:	Any

Description:
load data infile 'filenm.txt' into table 'tblname' ; as the client I can execute this. The server is
always started under the unix account user 'mysql' . As a client, I, the unix account user 'jondel',
have ALL privileges in MySQL. The 'filenm.txt' above points to a file in a directory OUTSIDE the
main MySQL directory, owned by the user 'mysql' and other unix account users do not have
read/write permissions . Consequently, 'filenm.txt' must be the FULL path name to the pertinent
file. Executing the above load ... statement results in the following error:
ERROR 13 (HY000): Can't get stat of '/usr/local/data/myorchids_data/OtherOrchids' (Errcode: 13)
Using the shell script perror 13 returns the answer 'permission denied'!??? But, the file does have
world read permissions set. The client 'jondel' is also the unix owner of the file! Also, a previous
version of MySQL, version 4.0.18, did not have this problem and I was able to load ... from that
file 'filenm.txt'; although, for that version the server was running under the unix root account and
the database in ../mysql/data/'databasenm' was owned by the user 'jondel' . So in the previous
version, 4.0.18, I could read from a file using a full path name, or just the relative path name if
the file was in ../mysql/data/databasenm/filenm.txt . In version 4.1.7, the .../mysql/data/
'databasenm' is owned by the unix account user 'mysql' , even though the MySQL user 'jondel'
created that database as a client. So the data file 'filenm.txt' cannot be put into the directory .../
mysql/data/'databasenm' because access permissions are denied. A full path filename must
always be provided, but this command does not work!

How to repeat:
always .

Suggested fix:
do not know a fix.

additional comments on Nov 26, 2004 16:15

The mysql client is owned by the client as a process, but the server is  a mysql process and it 
cannot access files out side of its domain, which is mysql as owner and mysql as group. But the 
pertinent file has world read access permissions! So why does the server not execute the 
command 'load data infile .. ' on this pertinent file? Is it an OS problem? Seems so!

The other problem, I think, is that the client has no access to the file .../mysql/data/'databasenm' 
into which data files could be placed for loading using 'load data infile....'  I think the directory .../
mysql/data/'databasenm' should be owned by the client, not the server ownership. because the 
client created the database and tables.   Of course, none of this would matter, if the server could 
access the pertinent data file in /usr/local/data/......

If the server has root permissions, then perhaps there is no problem, because then the server 
can access any file. So, perhaps the server should be started as a root process.The root just has 
to make sure its password is secure and robust.

But the pertinent file has world read permission set, so why is it not read?

additional comments on Nov 27,2004 15:17:
A simple test was accomplished. In the unix account jondel,  a list of the directory 
/usr/local can be done to obtain:

{the user alias  < l >  is short for <ls -l>}
> l /usr/local
total 8
drwxr-xr-x   4 jondel  admin  136 10 Oct 10:21 bin
drwxrw-rw-   3 jondel  admin  102 24 Mar  2004 data   "this is the pertinent directory"
drwxr-xr-x  19 mysql   admin  646  8 Nov 13:01 mysql
drwxr-xr-x  18 root    admin  612 30 Oct 16:34 mysql-4.0.18.old
lrwxr-xr-x   1 root    wheel    5  8 Nov 13:35 mysql-standard-4.1.7-apple-darwin7.5.0-powerpc -> 
mysql
drwxr-xr-x   3 root    admin  102 22 May  2004 share
drwxrwxr-t  10 root    admin  340  2 Oct 09:24 vscanx

And the directory data can be listed as:

> l /usr/local/data
total 0
drwxrw-rw-  6 jondel  admin  204 24 Nov 20:35 myorchids_data

However in the unix account mysql, this same command gives 

ls: myorchids_data   ls:  Permission denied

But the directory has world read permissions set!

And the directory ./myorchids_data contains the folowing files:

> ls -l /usr/local/data/myorchids_data
total 56
-rw-r--r--  1 jondel  admin  14750 24 Nov 20:31 OtherOrchids
-rw-r--r--  1 jondel  admin   3981 24 Nov 20:18 dendrobs
-rw-r--r--  1 jondel  admin   2766 24 Nov 20:24 masdevallias
-rw-r--r--  1 jondel  admin   2872 22 Nov 10:34 miltonias

And these also have world read permissions set!

Hence, the error report above is actually an operating system error, pertinent to OS 10.3.6 .

Nov 27, 2004 at 16:29 PST
Finally, re-starting mysqld the server, under the root account instead of the mysql account solves 
the problem of access permissions, and the command 'load data infile ...' executes and read the 
pertinent file. This is the solution as far as MySQL database and server/client is concerned.

This is not a bug! Set the read and execute permissions for world on the parent directories and 
MySQL server will access the pertinent file!