| Bug #56732 | MySQL query cannot Access files in /tmp | ||
|---|---|---|---|
| Submitted: | 11 Sep 2010 17:55 | Modified: | 11 Jan 2011 14:16 |
| Reporter: | wong disaster | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.1.49-1ubuntu | OS: | Linux (Ubuntu 10.10) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | SQL Query, tmp file | ||
[12 Sep 2010 15:06]
wong disaster
I did a lot of google today. I found this is a common problem by MySQL server, not Workbench itself. I am still confused about false declaration "can't create/write /tmp", since mysqld started successfully with some tmp files created in /tmp. Also I modified tmpdir=/var/lib/mysql/tmp in my.cnf and it worked. It seems setting tmpdir to any directories in /tmp will cause this problem, so I still consider this is a bug, or an incorrect error message.
[15 Sep 2010 19:37]
Sveta Smirnova
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php You should tune either filesystem to allow MySQL user access to /tmp directory or all MySQL server settings related to temporary files/directories.
[19 Sep 2010 16:26]
wong disaster
I am not sure if you Sveta really recreated the environment that mysql will act exactly like my linux. I reproduced this issue again on my linux. I understand you had googled, and checked mysql document. That's what I used to do. In fact I tried the solution by suggested in document: chmod 1777 /tmp /var/run/~tmp and chmod 777 /tmp /var/run/~tmp None of those resolved the problem. I just modified tmpdir=/var/lib/mysql/tmp and it works. The owner of /var/lib/mysql/tmp is mysql:mysql. But when I tried to use /tmp(owner is root:root, mod is either 1777 or 777), all the syndromes occurred again. That's the reason I think this is a problem. If I can use /tmp for mysql, I won't try to report this. I linked /tmp to a ramdisk for reduce reads on my hard drive. If the whole point is use another temp dir, but ignoring mysql can't access /tmp when all other applications can access without problem, I don't really understand why mysql report "Access Denied". BTW, the mount status /dev/ram1 on /var/run/~tmp type xfs (rw)
[20 Sep 2010 12:29]
Susanne Ebrecht
Did you try to mount it with x? The files MySQL will need and store temporary sometimes need to be executable.
[21 Sep 2010 22:20]
wong disaster
As you can see, the mount option for /tmp(actually a ramdisk) is rw, and there is no noexec/nosuid/nodev which may affect execution behavior. The mount option of /tmp is almost the same as /, from where mysql can run query without errors. Mount options of some file system: /dev/sda6 on / type ext4 (rw,errors=remount-ro) /dev/ram1 on /mnt/ramdisk type xfs (rw) ls -ld /tmp lrwxrwxrwx 1 root root 13 2010-09-18 23:32 /tmp -> /mnt/ramdisk/ Please note the link destination of /tmp has changed. My system were wiped out by mistake. This is a new system.
[23 Sep 2010 8:10]
Susanne Ebrecht
What rights do you have here: $ ls -ld /mnt/ramdisk/
[23 Sep 2010 15:41]
wong disaster
I also tried chmod 777 also. Nothing works here. ls -ld /mnt/ramdisk drwxrwxrwt 19 root root 4096 2010-09-23 11:36 /mnt/ramdisk If /mnt/ramdisk doesn't allow to execute, how can mysqld run so smoothly? /var/log $ ls -l mysql* -rw-r----- 1 mysql adm 0 2010-09-19 12:06 mysql.err -rw-r----- 1 mysql adm 0 2010-09-23 07:54 mysql.log -rw-r----- 1 mysql adm 20 2010-09-22 07:36 mysql.log.1.gz -rw-r----- 1 mysql adm 20 2010-09-21 18:20 mysql.log.2.gz -rw-r----- 1 mysql adm 20 2010-09-19 12:06 mysql.log.3.gz mysql: total 0 -rw-rw---- 1 mysql adm 0 2010-09-23 07:54 error.log -rw-rw---- 1 mysql adm 0 2010-09-22 07:36 error.log-old
[26 Sep 2010 13:55]
Alexey Kishkin
Hi. What is output of getfacl /tmp and getfacl /mnt/ramdisk ?
[28 Sep 2010 23:55]
wong disaster
It's pretty weird that when I execute 'getfacl /tmp', getfacl automatically printed all subdirectories under /tmp. But getfcal just printed one directory when it runs on /mnt/ramdisk. $ getfacl -ae /tmp | head -n 8 getfacl: Removing leading '/' from absolute path names # file: tmp # owner: root # group: root # flags: --t user::rwx group::rwx other::rwx $ getfacl -ae /mnt/ramdisk/ getfacl: Removing leading '/' from absolute path names # file: mnt/ramdisk/ # owner: root # group: root # flags: --t user::rwx group::rwx other::rwx
[29 Sep 2010 5:55]
Alexey Kishkin
ok, one more question: lsattr -d /tmp lsattr -d /mnt/ramdisk df -h /mnt/ramdisk
[29 Sep 2010 23:44]
wong disaster
$ lsattr -d /tmp lsattr: Operation not supported While reading flags on /tmp $ lsattr -d /mnt/ramdisk/ ------------------- /mnt/ramdisk/ $ df -hT /mnt/ramdisk/ Filesystem Type Size Used Avail Use% Mounted on /dev/ram1 xfs 60M 24M 37M 39% /mnt/ramdisk
[28 Oct 2010 13:49]
wong disaster
I tried to do "synchronize model" in Workbench. Mysql failed as usual. I ran some commands as follow: ~ $ sudo -u mysql touch \#123 touch: cannot touch `#123': Permission denied ~ $ sudo -u mysql -g mysql touch \#123 Sorry, user [My User Name is Here] is not allowed to execute '/usr/bin/touch #123' as mysql:mysql on [My Host Name is Here]. So I used a root bash to run these commands again. root ~ $ sudo -u mysql -g mysql touch \#123 Sorry, user [My User Name is Here] is not allowed to execute '/usr/bin/touch #123' as mysql:mysql on [My Host Name is Here]. root /tmp $ sudo -u mysql touch \#123 root /tmp $ ll -rw-r--r-- 1 mysql mysql 0 2010-10-28 09:12 #123 Then funny thing happened. I exited the root bash and did all the commands again in bash as normal user. /tmp $ sudo -u mysql touch \#1233 /tmp $ ll -rw-r--r-- 1 mysql mysql 0 2010-10-28 09:13 #1233 Finally I can sudo create the #files without permission denied. However, when I rerun synchronize model in Workbench, it still got permission denied. /tmp $ ps --user mysql -o "%G %U %g %u %a" GROUP USER RGROUP RUSER COMMAND mysql mysql mysql mysql /usr/sbin/mysqld
[11 Jan 2011 14:16]
wong disaster
I finally figured out why mysqld was not allowed to access the file in /tmp. It was apparmor which denies mysqld to read/execute files in /tmp(precisely it followed the symbolic link, i.e. /mnt/ramdisk. My solution is modifying the apparmor.d/usr.sbin.mysqld. Now it works fine.
Description: I use reverse engineer. The error occurred in step "Retrieve Object Information" The log showed in dialog: Fetching table list. 36 items from typo3database OK Fetching view list. Operation failed: Can't create/write to file '/tmp/#sql_3ca_0.MYI' (Errcode: 13) When I created the file /tmp/#sql_3ca_0.MYI and rerun "Retrieve Object Information", then I got this error. The log showed Fetching table list. 36 items from typo3database OK Fetching view list. Operation failed: Can't create/write to file '/var/run/~tmp/#sql_3ca_0.MYI' (Errcode: 17) I can create/delete/modify this file /tmp/#sql_3ca_0.MYI. The workbench process is created by me. How to repeat: This is a list of installed mysql application on my system, mysql-client-5.1 5.1.41-3ubuntu12.1 mysql-client-core-5.1 5.1.41-3ubuntu12.1 mysql-common 5.1.41-3ubuntu12.1 mysql-navigator 1.4.2-12build1 mysql-server 5.1.41-3ubuntu12.1 mysql-server-5.1 5.1.41-3ubuntu12.1 mysql-server-core-5.1 5.1.41-3ubuntu12.1 mysql-workbench-gpl 5.2.27-1ubu1004 This is file system on my system, $ df -hT Filesystem Type Size Used Avail Use% Mounted on none tmpfs 470M 54M 416M 12% /var/run $ ls -l /tmp lrwxrwxrwx 1 root root 13 2010-09-04 12:50 /tmp -> /var/run/~tmp ls -ld /var/run/~* drwxrwxrwx 19 root root 460 2010-09-11 13:29 /var/run/~tmp The /tmp link is created by boot script rc.local Suggested fix: Do not create a temporary file start with pound(#). When I create this file #sql_3ca_0.MYI, touch/mv command refuse to create this file unless I backslash the pound. For example touch \#sql_3ca_0.MYI <- Work ok touch #sql_3ca_0.MYI <- touch: missing file operand