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:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.1.49-1ubuntu OS:Linux (Ubuntu 10.10)
Assigned to:
Tags: SQL Query, tmp file

[11 Sep 2010 17:55] wong disaster
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
[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.