#sql ibd files filling up filesystem

[Sistemisti Sisal]

Hi, we have a server with Nagios XI 5.5.7 running with database MariaDB version 5.5.60
Sometimes, every 3 or 4 weeks, the filesystem get filled up, and the database (and web interface) stops working, so we have to recover free space and run "repair_databases.sh" script to make it work again.

I noticed there are some files in /var/lib/mysql/nagiosxi/ with names similar to #sql-XXXX_XXXXXX.ibd and .frm, which seem to be leftover files from dbmaint procedures, and they are about 17-18 Gb in size.
There are no tables with the same names in the database, and the modification date of these files is > 1 week.

I'd like to ask you a couple of questions:

1. Is it normal that these files remain on the filesystem until it is full, or there is a way to make the application clean them automatically?
2. Is it safe to delete them manually, when we have this issue, without affecting the productivity of the platform?

Thanks

[jdunitz]

The ibd files are leftovers that should be cleared. However, you don't just want to delete them directly without first trying to get MySQL to delete them:

mysql -h localhost -uroot -pnagiosxi nagiosxi -e 'DROP TABLE `#mysql50##sql-767_db57d`;'

Note the backticks (`) and the # and ## placement; you must make sure you add those as I've shown.

If you get an error like:

ERROR 1051 (42S02) at line 1: Unknown table '#mysql50##sql-767_db57d'

then that means that MySQL is done with those files and doesn't know about them anymore. In that case, you can safely remove them with a regular rm. Always try to remove each one from within MySQL first, though.

--Jeffrey