Common MySQL Issues And Fixes

From ISoft Wiki
Jump to: navigation, search

'Phantom Tables'

Phantom tables are tables that don't exist in the schema, cannot be queried from or inserted to, and cannot be dropped OR added. Trying to drop it results in an error where the table does not exist, and trying to add the table results in an error where the table already exists. Since this can happen in a wide variety of ways, the following steps should be followed in order of least destructive to most destructive:

  • Note: Always ALWAYS make a backup if you can. This is a case of corruption or miscopied files, usually, and that means you may not be able to, but try anyways.
  1. Flush tables using mysqladmin
    • mysqladmin --flush-tables -u<user_name> -p
    • OR the query `FLUSH TABLES <offending_table_name>`
  2. Delete <table_name>.frm and rebuild it from scratch
    • This is DESTRUCTIVE! Be sure you've made a backup and that you have the right table name!
    • If you can't find the .frm, make it in a different database and copy the file to the offending location, and then do `DROP TABLE <offending_table_name>`