Rare MyISAM failing to delete MYD file for temporary tables.

I manage a few systems that every hundred million disk temporary table deletions or so one of them will fail. MySQL will delete the MYI file but leave the MYD behind. It’s very strange. There isn’t an error in the error log but subsequent queries that try to use the same temporary table name will error because the MYD file still exists. The queries fail with an error like ERROR 1 (HY000): Can’t create/write to file ‘/tmp/#sql_25d1_0.MYD’. Fortunately the client error gives the temporary table name so it’s easy to clean up by deleted the MYD file. While I still don’t know what the root cause is but I was able to patch MySQL to fix the issue.

The old mi_delete_table function which is responsible for deleting only the MYI and MYD file would delete the MYI file and leave the MYD file behind. This function would try to delete the MYI file, get an error back from the filesystem and not attempt to delete the corresponding MYD file. The strange part is that the file is actually deleted. The error is captured in mi_delete_table but is lost by the functions calling it. I’ve tried to patch some parts of the temporary table code to try to track down the actual filesystem error but haven’t seen any errors returned. I don’t think I’ve correctly patched all the temporary table code to capture the error. This hasn’t been a priority because the patch clears up the symptom of the issue which is the MYD file being left behind.

In my new mi_delete_table function I try to delete both the MYD and MYI file before returning an error. This has fixed the issue of MYD files being left behind but I still don’t know the root cause of why they weren’t being deleted in the first place. I’m not sure about filing a bug for this because even though I think my mi_delete_table is better than the stock one I can’t really reproduce the issue.

Anyway here is the patch if anyone is interested.

3 Comments

  1. I had a situation some months ago where I found a ‘left over’ .frm file. It caused mysql_upgrade to fail.

    I don’t know if the .frm originally belonged to a temporary file or not. But whether temporary or not I am almost sure that the table must have been an InnoDB table (as I very rarely create anything else). The name of this .frm started (weirdly) with the characters ‘@@’.

    Also this I have tried to repeat but with no luck.

  2. James Day says:

    Better to file the bug report than not. Hard to reproduce problems are a fact of life and at least it’ll indicate a need for more error logging to help to track down what’s happening.

  3. Mike Kelly says:

    I am sorry to ask a newbie question here but I am suffering from rogue temporary MYD files as you describe. You also say it is easy to clean up by deleting the MYD file. I have tried to rm -f #sql_a430_0.MYD but with no luck. I get no errors but the file remains. This is a Virtual server running Plesk on CentOS. Oddly, the temp files are being created in /var/lib/mysql which is the data directory, and not in a tmp directory. I am logged in as root and the file permissions are -rw-rw—-. I do not know what is causing the MYD file to be orphaned but I would be happy if I could simply clean up. I am afraid I have no idea how to patch a server and so cannot benefit from your solution.

Leave a Reply