Amazon and MySQL: fixing “Can’t create/write to file” problem

Today I had a problem which almost made me crazy.

So I needed to install MySQL with InnoDB support on an EC2 box with Ubuntu.

sudo apt-get install mysql-server

Adding lines into /etc/mysql/my.cnf:

default-storage-engine=innodb
default-table-type=innodb

And I got:

[Note] Plugin ‘FEDERATED’ is disabled.
/usr/sbin/mysqld: Can’t create/write to file ‘/tmp/ib9jG4HQ’ (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 table type: innodb
[ERROR] Aborting

Just execute something like chmod 1777 /tmp and we’re done? No! I still got the same issue. Finally I went down to the use of strace:

open(“/tmp/ib69zupp”, O_RDWR|O_CREAT|O_EXCL|O_LARGEFILE, 0600) = -1 EACCES (Permission denied)

But why? I can write and read files with the same user. What the hell is going on? Looks like the problem is related to the flags O_EXCL|O_LARGEFILE. We have a pretty specific configuration on the Ubuntu box where /tmp folder is a simple symlink to the folder from EBS. This was done because we really need lots of space for GWT compilation in /tmp. So it looks like the problem was caused by combination of flags O_EXCL|O_LARGEFILE and EBS volume.

After I moved back to the usual approach to place /tmp on a usual folder on the root partition the error has vanished.