MySQL Bugs: #33869: Ghost Tables

Bug #33869	Ghost Tables
Submitted:	14 Jan 2008 17:22	Modified:	13 Feb 2012 8:55
Reporter:	Bugs System	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.1 or later	OS:	Any (WAMP & LAMP)
Assigned to:		CPU Architecture:	Any

Description:
Sirs;

I think I've found a legitimate problem with MySQL.  I opened up posts on several forums at:

http://forums.mysql.com/read.php?34,191303,191723#msg-191723
http://forums.devshed.com/mysql-help-4/ghost-tables-501171.html#post1964861
http://www.weberforums.com/sutra32362.html#32362
http://www.tek-tips.com/viewthread.cfm?qid=1441061&page=1
http://www.nabble.com/Ghost-Tables-to14780887.html
http://forum.mysqlperformanceblog.com/s/t/575/
http://forums.tizag.com/showthread.php?t=5784
http://www.sitepoint.com/forums/showthread.php?t=525160
http://www.devlib.org/forums/ghost-tables-t6779.html

and could not get a resolution for the problem.  Screen shot of the problem is at:

http://www.sitepoint.com/forums/showthread.php?t=525160

Basically the DB server shows a database with three tables, but no commands can be executed in any manor on the tables and even a server backup fails because of errors generated by these "Ghost Tables".

I have this database, mirrored and existing on 2 WAMP (Win 2K) machines and 2 LAMP machines (Ubuntu), so no the problem is OS/platform independent.  My config files show version 4.l on one of my WAMP installs, which is the original version packaged in WAMP, though I know I have run some upgrades since.  The versions on the LAMP are the latest Ubuntu - Gutsy distributions.

None of the tools I've tried executing on these can in anyway effect a "repair" on these tables, so need a fix to this, with code to resolve.

I would simply do a backup, delete/remove MySQL from my systems, then re-install and rebuild from the backup, if the tables in question were not also causing any backup attempt to error.

Please let me know what you can do as these tables are part of an on-going tool development for production.

Thanks!

How to repeat:
Always repeats per screenshot posted at:

http://www.sitepoint.com/forums/showthread.php?t=525160

Hi!  Please, show the exact output of all those sql commands you run.  Just cut 'n paste it from the mysql command line client session.

Also, directory listing of your datadir, so we may see what files are there.

Command and Errors Screenshot

Attachment: mysql-errors.jpg (image/jpeg, text), 44.13 KiB.

Shane Bester,

Exactly what do you want with:

<b>Also, directory listing of your datadir, so we may see what files are there.</b>

Please explain!

Thanks!

Thank you for a problem report. Please, copy and paste, as text, the commands you execute and their results from mysql command line client running in shell or cmd.exe. Also, please, try to explain how to get the same results on any other MySQL server.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

All,

Sorry you can not duplicate this, but the problem remain on both of my Windows machines.

Have not tried to dup this on Ubuntu machines, but will pass along if it occurs.

This still does not change the fact, that no DB "repair" tool exists, which can address issues like this.  The only way I currently know how to fix this is to 1.) Backup the entire DB, 2.) Completely un-install MySQL, 3.) Delete all files in the current directory, 4.) Re-install MySQL, 5. Initiate the Re-covery.

Since the Backup/Recovery doesn't work well with the "compressed file" option and since my DB is too large, this is not a good option, but rather do-or-die last resort option.

Obviously something internal, lost an index to the system tables, for the Ghost tables on my system, so the need exists to go find abandoned files in the DB structure and re-index these to the system files.  I can see the files in the directory tree and as I said ealier, "Show tables" lists them, but the tables can never be accessed.

Thanks!

OMR

Thank you for the feedback.

Please provide:

1. Output of SHOW VERSION() on one of hosts you have a problem
2. Output of  select * from information_schema.tables where table_schema='page_display'
3. MySQL error log file
4. "directory listing of your datadir, so we may see what files are there" as described below:
4.1. On Windows:
4.1.1. Open cmd.exe
4.1.2. cd directory where your datadir is located
4.1.3. Run command `dir`
4.2. On Linux:
4.2.1. Open console
4.2.2. cd directory where your datadir is located
4.2.3. Run command `ls -la`

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I think I found a way to duplicate the problem.  This involves two computers.

Create a new database, with tables on system one (source computer).  Without using MySQL or any other SQL interface, copy the folder and files from the source computer into the second system (target computer) and re-index, restart MySQL on the target machine.

The target machine will see the folder and files and create pointers in the master tables, but will not re-index the master index and the problem will then be duplicated with ghost tables showing.

Note:
This is not how this occurred, but it was the only way I found to duplicate the problem.

OMR

Thank you for the feedback.

>  copy the folder and files from the source computer into the
> second system (target computer) and re-index, restart MySQL on the target machine.

What do you mean by "re-index"? Also please specify exact versions of both MySQL servers.

Answering the Q:

What do you mean by "re-index"? Also please specify exact versions of
both MySQL servers.

Both the systems I tested on are WAMP5 and therefore MySQL 5.0.24a

By re-index I mean running the full series of REPAIR operations, needed.

They find the folder and link it in but do not build/re-build the master index(s) file in the information_schema tables.  I'm looking to confirm which write and which do not.

OMR

All,

The writes in the information_schema are to:

SCHEMATA,
TABLES

But there are no entries in the TABLE_CONSTRAINTS table, which I think is where the error comes from.

OMR

All,

If you look at the screen shot I posted at:

http://www.sitepoint.com/forums/showthread.php?t=525160

you will see the database is page_display and the tables are:

menu_disp
menu_item
page_fill

OMR

All,

From http://www.sitepoint.com/forums/showthread.php?p=3954949&posted=1#post3954949

[quote=longneck;3952853]my psychic tech support powers are saying that your tables are innodb tables, and that you either deleted the ibdata files, or you moved the data from one folder or PC to another.[/quote]

Your right have innodb, but did not delete files or move data, but was the only way I could duplicate the problem.

OMR

Text version

Attachment: lh _ info_schema_files.txt (text/plain), 2.39 KiB.

HTML Version

Attachment: lh _ info_schema.htm (text/html), 1.62 KiB.

MySQL Data directory

Attachment: dir_out.txt (text/plain), 24.40 KiB.

All,

Files submitted per request from [2 Aug 13:41] Sveta Smirnova

OMR

It looks like that your ghost tables are views.

Just use page_display and make a SHOW FULL TABLES;

Anyway, when you are sure, that you don't need this database, then just delete it out of your data directory. Be careful .... maybe first copy it to an other place before you delete it. When you WAMP or LAMP won't work anymore after you delete it, then you know, that WAMP/LAMP need this views and you should copy it back.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".