Mysql couldn't write to /tmp then failed to restart

I was running orthomcl program which uses mysql. This program has operated just fine on the same computer before. This time I had an error message.

DBD::mysql::st execute failed: Can't create/write to file '/tmp/#sql_700_1.MYI' (Errcode: 13) at /usr/local/bioinf/orthomclSoftware-v2.0.9/bin/orthomclPairs line 709, <F> line 14.

I tried to restart mysql server, the server shut off but failed to start.

sudo service mysql start
start: Job failed to start

/var/log/mysql/error.log and /var/log/mysql.err are empty. I've tried a few things that I found here including purging and reinstalling mysql-server and removing ib_logfile* in /var/lib/mysql.

The other thing I tried is

sudo dpkg-reconfigure mysql-server-5.5

The error messages are:

/usr/sbin/mysqld: Can't create/write to file '/tmp/ibNzqwO0' (Errcode: 13)
InnoDB: Error: unable to create temporary file; errno: 13
[ERROR] Plugin 'InnoDB' init function returned error.
[ERROR] Plugin 'InnoDB' registration as a STORAGE ENGINE failed.
[ERROR] Unknown/unsupported storage engine: InnoDB
[ERROR] Aborting

Here are some information from dmesg | grep mysql:

[3115187.338273] init: mysql main process (44255) terminated with status 1
[3115187.338417] init: mysql main process ended, respawning
[3115187.356361] init: mysql post-start process (44256) terminated with status 1
[3116827.473256] type=1400 audit(1396394069.224:132): apparmor="DENIED" operation="open" parent=45387 profile="/usr/sbin/mysqld" name="/home/tmp/" pid=45396 comm="mysqld" requested_mask="r" denied_mask="r" fsuid=0 ouid=0
[3116827.582489] type=1400 audit(1396394069.332:133): apparmor="DENIED" operation="mknod" parent=45387 profile="/usr/sbin/mysqld" name="/home/tmp/#sql_b154_0.MYI" pid=45402 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=0 ouid=0
[3116827.908448] type=1400 audit(1396394069.660:134): apparmor="DENIED" operation="mknod" parent=45317 profile="/usr/sbin/mysqld" name="/home/tmp/ibbPfaqP" pid=45415 comm="mysqld" requested_mask="c" denied_mask="c" fsuid=118 ouid=118
[3116841.623608] type=1400 audit(1396394083.372:139): apparmor="STATUS" operation="profile_replace" name="/usr/sbin/mysqld" pid=45443 comm="apparmor_parser"
[3116841.771621] init: mysql pre-start process (45474) terminated with status 1

There were many many lines like this.

Could anybody help me figure out what's wrong with my MySQL server, please?

Many thanks.

Solution 1:

The reason for this is that your /tmp is symlinked to another location, /home/tmp. But MySQL is armored by AppArmor, which does not allow writing freely to /home/tmp. To remedy the situation, you can edit the /etc/apparmor.d/abstractions/user-tmp and add the /home/tmp there appropriately; for me the extra lines

  owner /home/tmp/**    rwkl,
  /home/tmp/            rw,

did the trick.

Solution 2:

It sounds like you don't have the right permissions on /tmp, which should be world writable. You should be able to fix it with this command:

sudo chmod 1777 /tmp

That will set the permissions to 777 (rwx for everyone) and also set the sticky bit which is recommended for the /tmp dir. As explained in man chmod:

RESTRICTED DELETION FLAG OR STICKY BIT

The restricted deletion flag or sticky bit is a single bit, whose interpretation depends on the file type. For directories, it prevents unprivileged users from removing or renaming a file in the directory unless they own the file or the directory; this is called the restricted deletion flag for the directory, and is commonly found on world-writable directories like /tmp. For regular files on some older systems, the bit saves the program's text image on the swap device so it will load more quickly when run; this is called the sticky bit.