Fixing: mysql can't create/write to file (errcode 2)

Today when running query Mysql returned strange error:

1	Can't create/write to file '/tmp/#sql_2d8_1.MYI' (Errcode: 2)

Here is how to fix it.

Usually this error means that your hard drive (or partition) is out of space or there’s something wrong with permissions and Mysql can’t write to it.

Since everything worked fine up to that moment, I assumed the former. Okay, let’s check free space:

1
2
3

$ df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda1       8G  7.9G  0.1G  99% /

What’s that? Disk is almost filled, but there shouldn’t be anything particularly large on that machine. Let’s check space consumed by Mysql.

1 2	$ sudo du -sh /var/lib/mysql 6.5G /var/lib/mysql

That’s a lot! I removed couple of ‘backup’ tables and checked space again, but nothing changed.

After some Googling I found out that this issue is related to very old bug report. Basically InnoDB engine (which is default these days) never shrinks ibdata files which contain the data for all tables and indexes. Even when you delete rows or drop tables, they are only marked as deleted. So space is never freed.

Luckily it’s easy to tell Mysql to store each table in it’s own file and reclaim free space. But before that make sure that you have enough space somewhere to store dumps.

If you are running Amazon EC2 instance, you may try expanding your disk (see instructions here).

Let’s fix this issue. Here is an adaptation of awesome instructions.

1. Dump all databases

1	$ mysqldump -uroot -ppassword --databases your_db_name > /tmp/db.sql

2. Drop all databases (except information_schema, mysql, performance_schema)

3. Stop Mysql

1	$ sudo service mysqld stop

4. Modify /etc/my.cnf to include the following:

innodb_file_per_table
innodb_flush_method=O_DIRECT
innodb_log_file_size=256M
innodb_buffer_pool_size=1G
innodb_purge_threads=1

_innodb_buffer_poolsize is size of memory buffer that stores cache data and indexes of tables.

If you have a lot of RAM, you might set it up to 80% of RAM available. Depends on your use case for this machine.

_innodb_log_file_size _should be quarter of innodb_buffer_pool_size.

5. Remove ibdata and ib_logfile files

1	$ sudo rm -f /var/lib/mysql/ibdata* /var/lib/mysql/ib_logfile*

6. Restart Mysql

1	$ sudo service mysqld start

7. Reload data back into Mysql

$ mysql -uroot -ppassword
CREATE DATABASE your_db_name;
USE your_db_name;
SOURCE /tmp/db.sql;

That’s it. Now when you drop tables, InnoDB will remove it’s files as well and space will be reclaimed.