Fixing: mysql can't create/write to file (errcode 2)
Today when running query Mysql returned strange error:
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:
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.
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
2. Drop all databases (except information_schema, mysql, performance_schema)
3. Stop Mysql
4. Modify /etc/my.cnf to include the following:
_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
6. Restart Mysql
7. Reload data back into Mysql
That’s it. Now when you drop tables, InnoDB will remove it’s files as well and space will be reclaimed.