Ghost Tables

[YoungManRiver]

All,

I have a MySQL containing 12 databases, one which has 3 tables. I've looked in the docs to determine how to re-index and run maintenance on my databases and it leaves me cold.

Anyway I login at the console and:

show databases;           ==>> DB shows
use <<dbname>>;           ==>> DB selects
show tables;              ==>> Tables show
select * from <<tbl>>;    ==>> No such table
drop table <<tbl>>;       ==>> Table does not exist
drop database <<dbname>>; ==>> DB not empty drop tables tbl1, tbl2, tbl3

Need to run maintenance on the DB that either restores the DBs and Tables or purges the DB so I can rebuild my DB as several apps work from this table.

Thanks!

YMR

 

[segana]

Hi YoungManRiver,

I have 2 possible solutions for you, but i'm sure others will post better ones (i'm still fairly new to MySQL myself)

However 2 ways are this:

CREATE TABLE new_table AS
SELECT * FROM existing_table
ORDER BY primary_key;

This will re-create a new table which contains all of the exisitng values from the copied table, but orders them by a field determined by you (primary_key is just an example) you can simply then drop the original table and re-name your new table.

The only thing with this solution is that the constraints aren't copied over (e.g. primary key) but you can set these yourself.

The second option is:

CREATE TABLE new_table
LIKE existing table;

This will again re-create the original table, but does not copy any values over, but it does copy all constraints. This would be similar to the purge you suggested, but would leave the DB structure intact.

Again, these are just 2 possible solutions.

If your having trouble with the DROP TABLE command however, you may want to look at your permissions as this command is normally only given to the Root user.

Also, from my own experience can i suggest downloading the MySQL GUI Tools pack (it's free from MySQL) as this contains MySQL Query Browser which is just like the console, but i find it much easier to use as it's..well, it's a GUI.

Also included is MySQL Administrator, which will allow you to admin your DB much more easily, including setting permissions and also setting up constraints on your tables.

I hope this information helps.

 

[YoungManRiver]

Thanks! Downloading the files, will see if they fix it.

YMR

 

[YoungManRiver]

Segana,

Using the Maintenance mode in the "Administrator" tool also give error "table does not exist", though they show in the list.

What next?

YMR

 

[YoungManRiver]

All,

It was suggested by contributors to other brds, that I try to fix my problem with the customary "MySQL" Toolset. I downloaded and implemented the toolset, but still had same results.

Also posted this at:

http://forums.mysql.com/read.php?34,191303,191723#msg-191723

http://forums.devshed.com/mysql-help-4/ghost-tables-501171.html

http://www.weberforums.com/sutra32362.html#32362

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

I've made several contribution/replies to some of these and based on the responses and in some case the lack thereof I think I found a legitimate bug in MySQL.

I uploaded the mysql console screenshot at:

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

Hope all this helps. I'm looking into "how to post a bug" for MySQL as I think that is my next step.

Thanks!

OMR

 

[YoungManRiver]

All,

Never did get any response on this.

Opened a Bugzilla on it and the MySQL folks went "HUH??"

Yeah, not reproducable, not capturable, not trackable so they are clueless, but exists on both my machines.

Huh, Just a thought! I have WAMP on both these machines.

Wonder if somehow the WAMP settings would cause this fake-out????

It's got me puzzled!

Thanks!

YMR

 

[ingresman]

I can't get the site point site (prvacy at my site).
Can you recreate the output and post here.
You might have to pipe the output which on windows you do like mysql -uxxx -pxxx >> c:\fred.log
You won't get a prompt to either put your commands in a commandfile and \. it to read it in.
The bit I'm interested in is seeing that your use command works and you are supplying the correct table name.

 

[YoungManRiver]

Ingresman,

Sorry, I can maybe post snapshot to another site you can see.

This one doesn't take file uploads and pastebin sites only take text.

Do you have one you preferr?

YMR

 

[ingresman]

i can seethe sitepoint site from home but i have to register, which i dont want to do.
Can you just paste in the error here?

 

[YoungManRiver]

All,

The official bug site for this problem at MySQL is:

http://bugs.mysql.com/bug.php?id=33869&thanks=2&notify=67

Thought this might help!

I'm looking to see if I have the original .sql build files, so I can post and all can follow along.

YMR

 

[YoungManRiver]

All,

Got this fixed. Had to:

1. Use the mysqldump with "ignore errors" option to
dump the DBs on both machines, then hand correct the
resulting .sql file.
2. Un-install MySQL on both,
3. Re-install MySQL on both,
4. Import the data back in the new DB with restore.

Thanks!

YMR

 

[ingresman]

thanks for the update on this. Many wouldn't bother, thanks again