mysql ghost table

Question

I am using mysql 5.5 on Windows XP. I have one table which was created through mysql workbench. After couple of sync from workbench to mysql database. This table becomes ghost. When I try to create it, system tells me it already exist. When I try to drop it, system tells me it does not exist. I don't know what to do.

I did changed my table name once to just avoid the problem. But after a while it comes back again even with the new table name. I am in the developing stage. So the database structure changes.

Need to shed some light on it.

Answer 1

You can see the location where your database files are stored by running this query.

show variables like '%datadir%'

Now look in explorer at this directory, you will see a folder per database.
Open the folder for your database

Your table will be in a group of files called tablename.*

*.frm = table definition file  
*.MYI = index file  
*.MYD = datafile

Maybe the permissions on the files are incorrect, check the permissions on a valid file and make sure your ghost files have the same permissions.

To remove the table from MySQL do:

1. Make a backup of that directory.
2. Next make a backup of the MySQL database using MySQL administrator or alike tool.
3. Make a subdir named "GhostAway"
4. Stop all MySQL clients and maybe the MySQL server as well
5. Move the ghost table files to dir "GhostAway"

Never delete stuff, just move it instead and always make a backup before.