Bug 30645 - mysqlimport, mysqldump, and 'load data file' return "stat" error (13)
mysqlimport, mysqldump, and 'load data file' return "stat" error (13)
Status: CLOSED CURRENTRELEASE
Product: Red Hat Linux
Classification: Retired
Component: mysql (Show other bugs)
7.0
i386 Linux
medium Severity medium
: ---
: ---
Assigned To: Patrick Macdonald
David Lawrence
:
Depends On:
Blocks:
  Show dependency treegraph
 
Reported: 2001-03-05 10:03 EST by danmcnaul
Modified: 2008-08-01 12:22 EDT (History)
1 user (show)

See Also:
Fixed In Version:
Doc Type: Bug Fix
Doc Text:
Story Points: ---
Clone Of:
Environment:
Last Closed: 2001-03-23 20:10:41 EST
Type: ---
Regression: ---
Mount Type: ---
Documentation: ---
CRM:
Verified Versions:
Category: ---
oVirt Team: ---
RHEL 7.3 requirements from Atomic Host:
Cloudforms Team: ---


Attachments (Terms of Use)
This is a test table definition for testing (417 bytes, text/plain)
2001-03-06 17:04 EST, danmcnaul
no flags Details
This is test data for loading into table "app" (138 bytes, text/plain)
2001-03-06 17:07 EST, danmcnaul
no flags Details

  None (edit)
Description danmcnaul 2001-03-05 10:03:19 EST
This bug is probably related to other user 'mysql' permissions bugs that I 
read in the Query list.

mysqlimport & 'load data file':

Even though I run mysqlimport as me (in my home directory), the program 
executes as user mysql.  If the permission on the data/text files are not 
set to 644, mysqlimport fails reporting a stat error code 13.  A work 
around for this problem is to make sure that the permissions on the 
data/text files are set to 644.

mysqldump:

Similar problem.  I run mysqldump as me (in my home directory), the 
program executes partially as me and partially as user 'mysql'.  The .sql 
file is created with me as the owner.  mysqldump then fails reporting the 
stat error code 13 problem.  Changing the permissions on the directory to 
757 causes the .sql and .txt files to be created, however, the resulting 
owner and group on the .txt files is user 'mysql' and group 'mysql'.  As I 
stated earlier, the .sql files are created as me as their owner.

Conclusion:

The utilities are still usable, it's just that they are cumbersome.

Dan McNaul
Comment 1 Trond Eivind Glomsrxd 2001-03-05 10:53:16 EST
Which versions of mysql do you have installed? Try getting the latest updates,
install them, make sure /var/lib/mysql is 755, remove all files from that
directory, start mysql and check if you still have the problem.
Comment 2 danmcnaul 2001-03-05 17:14:39 EST
Sorry about that.  I should have said.

I'm using the latest versions of mysql from the updates site.  The rpms are:

mysql-3.23.32-1.7.i386.rpm        mysql-server-3.23.32-1.7.i386.rpm
mysql-devel-3.23.32-1.7.i386.rpm  mysqlclient9-3.23.22-3.i386.rpm

I verified that all rpms were applied and are in the rpm database.


The /var/lib/mysql is already set at 755.  The problem continues to occur.
Comment 3 Trond Eivind Glomsrxd 2001-03-05 18:23:04 EST
Try removing the files in /var/lib/mysql, start from scratch and log what you do
so I can have a look at it.
Comment 4 danmcnaul 2001-03-06 16:59:03 EST
LOG of mysql actions diagnosing RedHat Bug #30645

1)  Shutdown mysql server (run /etc/init.d/mysqld stop)

2) Remove all files from directory /var/lib/mysql  (proof)

	[root@zegers mysql]# pwd 
	/var/lib/mysql
	[root@zegers mysql]# ls -l
	total 0
	[root@zegers mysql]# 

3) Restart mysql server (Server will see missing mysql database and reinit it)

	[root@zegers mysql]# pwd
	/var/lib/mysql
	[root@zegers mysql]# ls -l
	total 2
	drwx------    2 mysql    mysql        1024 Mar  6 16:10 mysql
	srwxrwxrwx    1 mysql    mysql           0 Mar  6 16:10 mysql.sock
	drwx------    2 mysql    mysql        1024 Mar  6 16:10 test
	[root@zegers mysql]# 

4)  Show permission settings on root home directory (this can be any user's 
home directory)  (set to 750)

	[root@zegers /]# ls -l  
	total 132
	drwxr-x---   18 root     root         1024 Mar  6 16:14 root
	[root@zegers /]# 

5)  Show permissions settings on root home directory containing database backup 
files (set to 750) (again this can be any user's home directory)

	[root@zegers /root]# pwd
	/root
	[root@zegers /root]# ls -l
	total 1027
	drwxr-x---    3 root     root         1024 Mar  6 16:13 db_bkup
	[root@zegers /root]# 

6)  Create a table in "test" database to prepare for import

	[root@zegers db_bkup]# pwd
	/root/db_bkup
	[root@zegers db_bkup]# ls -l
	total 5
	-rw-r--r--    1 root     root          396 Mar  6 16:29 app.sql
	-rw-r--r--    1 root     root          138 Mar  6 16:29 app.txt
	[root@zegers db_bkup]# mysql test < app.sql
	[root@zegers db_bkup]# 

7)  "test" database now has a table called "app"

	[root@zegers db_bkup]# mysql test
	Reading table information for completion of table and column names
	You can turn off this feature to get a quicker startup with -A

	Welcome to the MySQL monitor.  Commands end with ; or \g.
	Your MySQL connection id is 3 to server version: 3.23.32

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

	mysql> show tables;
	+----------------+
	| Tables_in_test |
	+----------------+
	| app            |
	+----------------+
	1 row in set (0.00 sec)
	
	mysql> 

8)  Now, try to use mysqlimport to load table "app" with data

	[root@zegers db_bkup]# cat app.txt
	1|Development Support|Email Settings for all Development Support 
messages
	2|Helix Schedule|Email Settings for all Helix Schedule messages
	[root@zegers db_bkup]# mysqlimport -d --fields-terminated-by=\| test 
app.txt
	mysqlimport: Error: Can't get stat of '/root/db_bkup/app.txt' (Errcode: 
13), when using table: app
	[root@zegers db_bkup]# 

9)  Step 8 fails because mysqlimport runs as user "mysql", who doesn't have 
read permissions.  This happens
	in regular user home directories too.  To fix it chmod the directories 
(/root and /root/db_bkup) to 755.

	[root@zegers /root]# ls -l /
	total 132
	drwxr-xr-x   18 root     root         1024 Mar  6 16:14 root
	[root@zegers /root]# ls -l
	total 1027
	drwxr-xr-x    4 root     root         1024 Mar  6 16:29 db_bkup
	[root@zegers /root]#

10)  Now, run step 8 again

	[root@zegers db_bkup]# cat app.txt
	1|Development Support|Email Settings for all Development Support 
messages
	2|Helix Schedule|Email Settings for all Helix Schedule messages
	[root@zegers db_bkup]# mysqlimport -d --fields-terminated-by=\| test 
app.txt
	test.app: Records: 2  Deleted: 0  Skipped: 0  Warnings: 0
	[root@zegers db_bkup]#

11)  Step 10 is successful because user mysql can now read the directory.

	[root@zegers db_bkup]# mysql test
	Reading table information for completion of table and column names
	You can turn off this feature to get a quicker startup with -A

	Welcome to the MySQL monitor.  Commands end with ; or \g.
	Your MySQL connection id is 11 to server version: 3.23.32

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

	mysql> select * from app;
	+--------+---------------------+----------------------------------------
-------------+
	| app_id | app_name            | 
app_desc                                            |
	+--------+---------------------+----------------------------------------
-------------+
	|      1 | Development Support | Email Settings for all Development 
Support messages |
	|      2 | Helix Schedule      | Email Settings for all Helix Schedule 
messages      |
	+--------+---------------------+----------------------------------------
-------------+
	2 rows in set (0.00 sec)

	mysql> 

NOTE:  The same result occurs with the LOAD DATA FILE directive as described 
above for the 
	mysqlimport utility.


=======================================================================
=======   Now dump the app table ==============
=======================================================================

12)  Clear out the db_bkup directory to prepare for mysqldump.

13)  Run mysqldump to dump the test database.

	[root@zegers db_bkup]# mysqldump --opt -T. --fields-terminated-by=\| 
test
	mysqldump: Got error: 1: Can't create/write to 
file '/root/db_bkup/app.txt' (Errcode: 13) when executing 'SELECT INTO OUTFILE'
	[root@zegers db_bkup]# ls -l
	total 1
	-rw-r--r--    1 root     root          393 Mar  6 16:45 app.sql
	[root@zegers db_bkup]#

14)  Step 13 fails because mysqldump runs as user "mysql" who doesn't have 
write permissions
	to the directory.  This is true in regular user home directories too.  
Notice though
	that the app.sql (table definition) file is created as root.root.  Set 
the /root and
	/root/db_bkup directories to 757 

	[root@zegers db_bkup]# ls -l /
	total 132
	drwxr-xrwx   18 root     root         1024 Mar  6 16:14 root
	[root@zegers db_bkup]# ls -l /root
	total 1027
	drwxr-xrwx    4 root     root         1024 Mar  6 16:45 db_bkup
	[root@zegers db_bkup]# 

15)  Now, run step 13 again.  (mysqldump)


	[root@zegers db_bkup]# mysqldump --opt -T. --fields-terminated-by=\| 
test

	[root@zegers db_bkup]# ls -l
	total 2
	-rw-r--r--    1 root     root          393 Mar  6 16:50 app.sql
	-rw-rw-rw-    1 mysql    mysql         138 Mar  6 16:50 app.txt
	[root@zegers db_bkup]# 

16)  This time it worked.  Notice that the app.txt file belongs to mysql.mysql.


If I can figure out how to do it, I'll post the app.txt and app.sql files.
Comment 5 danmcnaul 2001-03-06 17:04:21 EST
Created attachment 11945 [details]
This is a test table definition for testing
Comment 6 danmcnaul 2001-03-06 17:07:07 EST
Created attachment 11946 [details]
This is test data for loading into table "app"
Comment 7 Trond Eivind Glomsrxd 2001-03-22 18:50:27 EST
After having looked at it more closely (sorry for the delay), this is not a bug:

You need to use the "-L" switch to make the client read it, otherwise it just
asks the database to read the file itself - which it doesn't have permission to do.
Comment 8 danmcnaul 2001-03-23 19:52:07 EST
I'll check out your suggestion on mysqlimport using the -L option (and report 
back later), but you have not addressed the problem with mysqldump.  There is 
no -L option for mysqldump and that fails too.  You have also failed to comment 
on the "LOAD DATA" directive which fails as well.

When you install mysql on a linux box using mysql's packages, not Redhat's, you 
don't have any of these problems and you don't have to use the -L option.  The 
problem is the mysql user you've decided to create.  If you are going to 
integrate mysql in with RedHat (which is a good idea), then it should work as 
though I had gone through the work of downloading, building, and installing 
mysql myself.
Comment 9 danmcnaul 2001-03-23 20:10:37 EST
This is definitely a bug.  Don't confuse workarounds for "notabug".  
The "LOCAL" option was intended for loading data from a remote location, not to 
workaround a "mysql" userid's inability to read and write to user directories.

I ran some tests:

mysqlimport will work using the -L option.  This is a reasonable TEMPORARY 
workaround, but should be published as such.  Users will need to modify their 
backup/restore scripts.

"LOAD DATA" will work adding the "local" keyword.  This is a TEMPORARY 
reasonable workaround, but should be published as such.  Users will need to 
modify their sql scripts to now use this feature.

mysqldump is still broke.
Comment 10 Trond Eivind Glomsrxd 2001-03-23 20:14:00 EST
When you use the "LOAD DATA" directive, the database tries to load it: If you
don't give it permission, it can't. This is what the mysqlimport program tries
to do by default. Mysqldump has the same design issue - it asks the database to
do it. If you don't use the -T option, you can just redirect STDOUT to your
desired file.


Running the program of root is not an option - we care about the security of the
entire system. If the standard MySQL rpm doesn't do that, that means they put
that responsibility on to you: We choose to not ship it in a dangerous state by
default (and if it was, it would be vulnerable to attacks like the one on
Bugtraq this week)
Comment 11 Trond Eivind Glomsrxd 2001-03-23 20:15:07 EST
Oh - and if you want to break your own system (as in: We're _not_ going to do it
or recommend it), you could change user in /etc/my.cnf
Comment 12 danmcnaul 2001-03-25 15:42:40 EST
I now agree.....  LEAVE AS RESOLVED!

I studied closely one of our Solaris v8 Ultra Sparcs that contains the mysql 
package as a compiled install.  I learned that the problem I describe here 
actually occurs there.  However, you don't notice it because they run as root 
and they can create/read the necessary files in the user's home directory.  
Thus, this is not a RedHat distribution bug, but more a mysql inconvience.

Now that I'm aware of the problem, steps can be taken to work around these 
restrictions even if it means revamping several cron jobs.

Thank you for your support.
Comment 13 malei 2005-08-25 01:41:46 EDT
problem still in the latest stable version ,4.1.13
I think there is an easy method to resolve this problem for user. U never need
to change any priviledge, just put the .txt file into /tmp directory. All users
got priviledge here, and mysqlimport does run ok.

Note You need to log in before you can comment on or make changes to this bug.