2

On our productive System we have recently dropped a 1TB Table. After the drop was complete the Table disappeared in mysql but the file was still present in the /lib/mysql/dbname/ folder. (We use the one file per table setting) I deleted the files associated with the table.

So I checked with 

lsof  | grep crawl_link | grep deleted

and found that there are still open handles by the mysql process

mysqld    38115            mysql   11uW     REG                8,3 1016938364928  182524780 /var/lib/mysql/seobility/_crawl_links_new.ibd (deleted)
mysqld    38115  2110      mysql   11uW     REG                8,3 1016938364928  182524780 /var/lib/mysql/seobility/_crawl_links_new.ibd (deleted)
mysqld    38115  4530      mysql   11uW     REG                8,3 1016938364928  182524780 /var/lib/mysql/seobility/_crawl_links_new.ibd (deleted)
mysqld    38115  8192      mysql   11uW     REG                8,3 1016938364928  182524780 /var/lib/mysql/seobility/_crawl_links_new.ibd (deleted)
(Total 120 Lines)

I know the issue would be resolved by restarting MySql but our Database is running at 2TB+ and I would really like to avoid downtime.

Is there a way to drop the file handle and free the disk space occupied?

KTB
  • 123
  • 4
  • Try to connect to your DB and issue the command `show full processlist;` at mysql prompt. – Khaled Oct 05 '16 at 12:48
  • There is traffic on the database but no query is accessing the deleted table. The deleted table was never part of the application, we just copied an existing one to test something, so there is no part of the application doing query on it, nor has there ever been. – KTB Oct 05 '16 at 12:59
  • Maybe you can try > FLUSH TABLES; – mick Oct 06 '16 at 08:01

1 Answers1

0

The obvious solution is to kill (or restart) the process which is holding the file descriptor (process ID 38115 as shown in lsof output).

If you don't want to do that, you can truncate the file (even if it is deleted) to free up disk space. You can try the following:

$ :> /proc/38115/fd/11

Where 38115 is the process ID and 11 is the file descriptor. Don't forget to execute as root when required.

Khaled
  • 36,533
  • 8
  • 72
  • 99
  • Thx, I ended up sceduling a maintanance and restarted mysql. I believe your solution would have worked but I don't trust mysql to handle such a change without issue. – KTB Oct 06 '16 at 08:57