5

My Webmin displayed an error message saying the disk is full. Upon investigation, I found 3 files inside /var/lib/mysql have grown too big:

  1. mysql.ibd (8.8G)
  2. undo_001 (8.4G)
  3. undo_003 (2.5G)

I googled to check whether these files can be removed but the suggestion was against mysql.ibd while no clear information on undo_001 & undo_002.

How can I safely remove files and which one to restore to the server? As of now, 0 bytes is free, and nothing I can do on the server.

Another question is how to prevent these files from growing so big.

The 3 files

1
  • 2
    Those tables are big because you have uncommited changes and it's expecting a rollback. You should figure out which transaction is it. The innodb_trx table should give hints about it. Applications shouldn't have to do this.
    – Braiam
    Aug 8 at 11:27

1 Answer 1

9

How can I safely remove files and which one to restore to the server? As of now, 0 bytes is free, and nothing I can do on the server.

Not directly. You will need to drop tables from within mysql to free up space. Mind that this only works for innodb if you have set innodb_file_per_table Otherwise you will need to drop tables, create a dump of your whole database and reload it (and yes... that requires space). Otherwise the database file will not shrink.

The easier path would be to remove other files on your system.

Regarding the 2 UNDO file:

The commands to remove the UNDO tablespace:

ALTER UNDO TABLESPACE tablespace_name SET INACTIVE;
DROP UNDO TABLESPACE tablespace_name;

And you do that from within the MySQL prompt. You will loose the ability to do a rollback.


Please do not put your mysql database on the same system as your root system. Use a partition or a different disk. If you only use 1 disk it used to be common to put /var/ on its own partition. That will prevent root from going full.

To free up space also have a look at /var/log/ Anything with .gz at the end can be removed. The same for files ending in a digit *[0-9]. Those are all backups of log files and sometimes those tend to get big too.


This will automatically truncate undo logs:

SET GLOBAL innodb_undo_log_truncate=ON;
4
  • What are these undo files? It looks like they are some sort of special mySQL thing, right? They let you go back to previous versions of the DB or something?
    – terdon
    Aug 7 at 15:25
  • rollbacks. see the "You will loose the ability to do a rollback." ;) It is a part of innodb.
    – Rinzwind
    Aug 7 at 15:37
  • Isn't rollback transaction based? Isn't OP in the middle of a very big transaction that makes the rollback tables big? I would instead suggest to search for an uncommited transaction and commit it or rollback it, and try to figure out how not to have uncommited changes.
    – Braiam
    Aug 8 at 11:20
  • @Braiam I doubt there are any transactions open after a crash; restart of mysql killls any active session, all temporary files and all outstanding commits. These files are leftovers (and likely the reason) of the crash. The only way to redo the commands is when those commands are still available somewhere(ie in webmin/workbench)
    – Rinzwind
    Aug 8 at 11:45

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.