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: | |
Category: | MySQL Server | Severity: | S2 (Serious) |
Version: | 4.1 or later | OS: | Any (WAMP & LAMP) |
Assigned to: | CPU Architecture: | Any |
[14 Jan 2008 17:22]
Bugs System
[14 Jan 2008 17:55]
MySQL Verification Team
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.
[14 Jan 2008 19:21]
Bugs System
Command and Errors Screenshot
Attachment: mysql-errors.jpg (image/jpeg, text), 44.13 KiB.
[14 Jan 2008 19:24]
Bugs System
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!
[14 Jan 2008 19:37]
Valeriy Kravchuk
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.
[15 Feb 2008 0:00]
Bugs System
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".
[15 Feb 2008 21:11]
Bugs System
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
[2 Aug 2008 11:41]
Sveta Smirnova
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`
[2 Sep 2008 23:00]
Bugs System
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".
[5 Sep 2008 0:10]
Bugs System
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
[5 Sep 2008 6:38]
Sveta Smirnova
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.
[5 Sep 2008 13:59]
Bugs System
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
[5 Sep 2008 14:08]
Bugs System
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
[5 Sep 2008 14:12]
Bugs System
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
[7 Sep 2008 20:33]
Bugs System
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
[7 Sep 2008 20:41]
Bugs System
Text version
Attachment: lh _ info_schema_files.txt (text/plain), 2.39 KiB.
[7 Sep 2008 20:41]
Bugs System
HTML Version
Attachment: lh _ info_schema.htm (text/html), 1.62 KiB.
[7 Sep 2008 20:44]
Bugs System
MySQL Data directory
Attachment: dir_out.txt (text/plain), 24.40 KiB.
[7 Sep 2008 20:46]
Bugs System
All, Files submitted per request from [2 Aug 13:41] Sveta Smirnova OMR
[8 Sep 2008 12:48]
Susanne Ebrecht
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.
[8 Oct 2008 23:00]
Bugs System
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".