As I reported some days ago to MariaDB bug tracker the following bug: https://jira.mariadb.org/browse/MDEV-17420, slave replicas with version 10.2.x are leaking temporary tables on MyISAM files.

Our temp directory is filling with .MYD/.MYI files up to the point that the process file descriptor limit is reached, for example:

-rw-rw---- 1 mysql mysql    1024 Oct 24 13:25 #sql324a_1a28c_e4b18.MYI
-rw-rw---- 1 mysql mysql       0 Oct 24 13:25 #sql324a_1a28c_e4b18.MYD
-rw-rw---- 1 mysql mysql    1024 Oct 24 13:25 #sql324a_1a28c_e4b19.MYI
-rw-rw---- 1 mysql mysql       0 Oct 24 13:25 #sql324a_1a28c_e4b19.MYD
-rw-rw---- 1 mysql mysql  189376 Oct 24 13:25 #sql324a_1a28c_e4b0c.MYD
-rw-rw---- 1 mysql mysql    5120 Oct 24 13:26 #sql324a_1a28c_e4b58.MYI
-rw-rw---- 1 mysql mysql    3575 Oct 24 13:26 #sql324a_1a28c_e4b58.MYD
-rw-rw---- 1 mysql mysql    1024 Oct 24 13:26 #sql324a_1a28c_e4b59.MYI
-rw-rw---- 1 mysql mysql       0 Oct 24 13:26 #sql324a_1a28c_e4b59.MYD
-rw-rw---- 1 mysql mysql    1024 Oct 24 13:27 #sql324a_1a28c_e4b6e.MYI
-rw-rw---- 1 mysql mysql    1204 Oct 24 13:27 #sql324a_1a28c_e4b6e.MYD
-rw-rw---- 1 mysql mysql 1256372 Oct 24 13:28 #sql324a_1a28c_e4ae3.MYD

After that, the only solution is to stop the mysqld process and then start it again.

Of courdse, we have checked on the master and these same files are not leaked or stale.

The question: does anyone know how to find some information of these temporary tables (at least their names) to help in debugging this case with more detail?


  • Are you performing ALTER TABLE? Or do you think these are coming from complex SELECTs?
    – Rick James
    Oct 26 '18 at 23:16
  • I'm pretty sure these temporary tables are coming from complex SELECTs Oct 28 '18 at 14:59
  • When you see this happening again, do SHOW FULL PROCESSLIST; in hopes of grabbing some of the offending queries. Then let's see some of them for discussion.
    – Rick James
    Oct 28 '18 at 17:40
  • Also try SHOW OPEN TABLES;
    – Rick James
    Oct 28 '18 at 17:42
  • @RickJames It's happening all the time. Maybe you should read the bug report to get better insight of the problem. Show open tables does not give any information from temporary tables Oct 29 '18 at 18:32

I ask about the PROCESSLIST in hopes of discovering whether only certain types of queries cause the leaked tmp table. Knowing that might expedite finding a solution.

Based on a recent comment in the bug report, it sounds like a cron job doing this might be a workaround (but apparently not):

  • Rick, that's not the case. Unfortunately, the process also does not delete the temporary table files when you stop the slave, neither when you stop the whole process. Oct 30 '18 at 20:44
  • @GabrielGomiz - Oh, well. I was hoping to provide a workaround pending the fixing of the bug you mentioned.
    – Rick James
    Oct 30 '18 at 22:25

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.