I tried to alter a table without paying attention to the size, and MySQL ate up all the free space on the drive. I deleted a million or so old records but the space was still being used.

Googling suggested that restarting the MySQL server would help, but the server won't start: [FAIL] /etc/init.d/mysql: ERROR: The partition with /var/lib/mysql is too full! ... failed!

So now I'm a bit stuck. My database only has one (very big) table:

# ls -lh
total 18G
-rw-r--r-- 1 mysql mysql    0 Sep 25  2014 debian-5.5.flag
-rw-rw---- 1 mysql mysql  18G Jul 13 15:17 ibdata1
-rw-rw---- 1 mysql mysql 5.0M Jul 13 15:17 ib_logfile0
-rw-rw---- 1 mysql mysql 5.0M Jul 13 15:15 ib_logfile1
drwx------ 2 mysql mysql 4.0K Jul 13 11:44 log
drwx------ 2 mysql mysql 4.0K Sep 25  2014 mysql
-rw------- 1 mysql mysql    6 Sep 25  2014 mysql_upgrade_info
drwx------ 2 mysql mysql 4.0K Sep 25  2014 performance_schema
drwx------ 2 mysql mysql 4.0K Dec  4  2013 test

So, specifically, my question is: how do I free up the drive space that MySQL used when I tried to alter a large table?

[edit]I should mention that I've got no space on any other drives, and I can't change the size of the drive either :-(

[edit2]In response to comments:

mysql> OPTIMIZE TABLE `emails`;
| Table      | Op       | Msg_type | Msg_text                                                          |
| log.emails | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| log.emails | optimize | error    | The table '#sql-8aa_25' is full                                   |
| log.emails | optimize | status   | Operation failed                                                  |
3 rows in set, 1 warning (1 hour 12 min 10.62 sec)

# df -h
Filesystem      Size  Used Avail Use% Mounted on
rootfs           20G   19G     0 100% /
udev             10M     0   10M   0% /dev
tmpfs           100M  128K  100M   1% /run
/dev/xvda1       20G   19G     0 100% /
tmpfs           5.0M     0  5.0M   0% /run/lock
tmpfs           200M     0  200M   0% /run/shm
  • What is the output of df -h? You may need to be root to run this command. – Vérace Jul 14 '16 at 3:58
  • you may have to run optimize table to claim the free space, MySQL doesn't free up space by just deleting the records. This usually happens due to alter table's default behavior of copying the table, do check of using alter table inplace rather than alter table copy if you are using MySQL>5.6 – Nawaz Sohail Jul 14 '16 at 8:45
  • @NawazSohail - response in the original post, to preserve formatting. – Grim... Jul 14 '16 at 9:34
  • @Vérace - response in the original post, to preserve formatting. – Grim... Jul 14 '16 at 9:35
  • @NawazSohail I've only got version 5.5.38. Would upgrading help? – Grim... Jul 14 '16 at 9:39

Find the file #sql-8aa_25 and remove it. It is probably in the same directory where the database is.

If you did a kill -9, mysqld did not get a chance to remove the tmp file.

| improve this answer | |

I would do 1 of two things:

Use a zip drive or similar to copy the db to another system. With disks being so cheap - 32GB USB stick available in Europe for €12, can't you use one of those? Then, copy your datadir onto it, restore it on another machine. These days, even the most feeble laptop (or a decent tablet) should be able to cope with a db of 32GB. You might want to gzip it first before copying.

Failing that, you could install more RAM and set up an in-memory file system as explained very well in these two articles (first, second) - other links available on Googling "linux put a partition in ram".

Furthermore, after recovering my data, I would consign that machine to the great server room in the sky. It has apparently come to the end of its useful life (although see this :-) ).

Finally, in future remember that Unix tends to do lots of caching and swapping and gets very cranky if you don't have some disk space in reserve. So set up some sort of monitoring that warns you when disk space usage is over 75%.

p.s. out of curiosity, what is this machine and what OS are you running?

| improve this answer | |
  • Thanks for your comment. It's a cloud server running Debian. Since writing this question I've created an instance of it and rebuilt it on a bigger server, which feels a bit cheaty, but there you go. – Grim... Jul 14 '16 at 15:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.