I was going to ask why mysqldump was trying to write dropped tables but I found that answer from this question, How do you remove reported non existing tables from mysql?.

Removing the .frm files from my datadir has resolved the mysqldump issue of trying to dump non-existent tables. Now my question is how do I drop a table and have all references to it removed? I would have thought issuing the drop table would remove all associated files in the directory. I am using MyISAM so perhaps the issue is related to that.

  • Hmmm, that is interesting. We do have lower_case_table_names=1 in our my.cnf but I wonder if that weren't in place when we created the table how it would have interacted after? The error message was about the lowercase table not existing but the system file was uppercase..
    – chris85
    Apr 16 '18 at 14:09
  • I've tried this but it made the file lowercase on the system. I don't have a test system where I can test the modified my.cnf for if the lower-case directive is off, table created, then the directive is set to on. I'm guessing that strange scenario might have been in affect here.
    – chris85
    Apr 17 '18 at 14:46
  • The real problem seems to be that DROP TABLE is failing to remove the .frm file? Please provide the DROP statement, the OS version, and the directory listing (dir or ls -l) for the file(s).
    – Rick James
    May 1 '18 at 4:31

Just remove all three of the correlated table data files from the file system and that should ensure all traces are removed as per my understanding of 15.2 The MyISAM Storage Engine

Each MyISAM table is stored on disk in three files:

  • .frm
  • .MYD
  • .MYI

Each MyISAM table is stored on disk in three files. The files have names that begin with the table name and have an extension to indicate the file type. An .frm file stores the table format. The data file has an .MYD (MYData) extension. The index file has an .MYI (MYIndex) extension.


Further Resources

  • So I have to run a DROP, then go to the filesystem and remove the files every time?
    – chris85
    Apr 16 '18 at 5:21
  • No, I used the DROP but the .frm files remained in the data-dir. So when I used mysqldump it always failed because the tables were missing. (I had to delete 5 .frm files from tables that were dropped over the past year)
    – chris85
    Apr 16 '18 at 5:24
  • Yes, you have stated the issue correctly there. The table123.frm lingered after the DROP.
    – chris85
    Apr 16 '18 at 5:29
  • Yes, I can run the mysqldump now but in the future when I want to be able to run it regardless of if I ran a drop at some point in the past. I don't want to have to remember to delete files from locations in the file system. I was thinking there was another way this should be done.
    – chris85
    Apr 16 '18 at 5:31
  • @chris85 Yeah, not sure.... maybe upgrade the version of MySQL running to see if it was fixed at this level. Otherwise, that's the workaround which appears to have worked per your comments. With that all said, will it suffice in this instance to accept this as the answer? Mar 20 '20 at 20:30

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.