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
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.
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.
Try removing the files in /var/lib/mysql, start from scratch and log what you do so I can have a look at it.
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.
Created attachment 11945 [details] This is a test table definition for testing
Created attachment 11946 [details] This is test data for loading into table "app"
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.
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.
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.
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)
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
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.
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.