Can't drop tables (or even whole database)

[omatic]

I had some trouble with a database recently, and it was necessary to create a new database and restore my database to it, then point my apps at the new database.

Creating the new database and plopping my data into it was fairly trouble free, and it's working fine so far. So, thankfully, my site is operating again. The problem is, in trying to clean up the wreckage of my old database (about 10MB worth of data), it refuses to be dropped. The problem seems to be two toxic tables... read on.

The database originally had ten tables in it. Eight of them were no problem to eliminate using DROP TABLE tablename command. However the two remaining tables are apparently so corrupt, that I can neither drop them, query them, repair them, check them, or view the table's structure*. Nor can I drop the database entirely. Any of these actions will cause whatever tool I'm using, to hang. I then have to use a secondary tool and kill the hung process, before I can try anything else.

To eliminate the tables and/or drop the database, I've tried phpMyAdmin to deliver a DROP TABLE tablename, and also tried the "drop database" button on the Services|MySQL tab in Ops. I've also tried the excellent (and extremely lightweight --only about 10K!!) phpMiniAdmin interface (http://phpminiadmin.sourceforge.net/)... but no joy.

Unfortunately, the same thing happens regardless of tool; the problem is apparently the DB and/or its remaining two corrupt tables, not how one delivers the commands.:confused:

Anybody have any ideas about what I could try next?
Thanks,
Mike AKA omatic



* = Strangely, while I can't browse the table or view its structure with any STRUCTURE links in phpMyAdmin, the DESCRIBE tablename command works without any problem. It's about the only thing I CAN do with it! Even a count(*) query freezes things up.

[omatic]

AND THEN A MIRACLE OCCURRED...

I used phpMiniAdmin to check my process list just now and found that it couldn't connect to the database I was trying to drop. Upon checking my DB list in ops, I discovered that the database is now gone (which is good!).

Somewhere along the line, a DROP DATABASE command must have actually worked. Waiting for a table lock to timeout or something, perhaps?

[Dbrazzell]

I saw something similar recently.

The database had a few tables that were not marked as crashed but no queries could be run against them. Doing a standard myisamck -r on the mysql server itself fixes it. Phpmyadmin was unable to run the repair table command to fix the issue.

This isnt a great situation to be in since it requires someone in support to escalate your ticket to get the issue fixed. Nothing that you can do from your end to fix it.

Luckily this situation is extremely rare. Ive only seen it 2 times and I have been working here for quite some time.

Sorry that you had it happened to you.

[omatic]

Doing a standard myisamck -r on the mysql server itself fixes it.Yep, that, and similar tasks, are tough to do without shell access. I suppose you could write a CLI-inducing script to execute it, if you already knew what specifically to do (which I didn't).

BTW, are we customers ever gonna get shell access? That's not uncommon, even for shared hosts. Yeah, I know -- I believe I already know the answer to this one!!

This isnt a great situation to be in since it requires someone in support to escalate your ticket to get the issue fixed. Nothing that you can do from your end to fix it. Ding-ding-ding! Winner winner, chicken dinner! And when you finally DO get escalated (which can be arduous and time-consuming), the truly skilled engineers are very busy folks. I was told that it'd be 24-48 hours until resolution. Ouch. And that (eventual) engineer may not be familiar with this particular nugget-of-nasty. In fact, there's a good chance they wouldn't, due to its rarity. Sometimes it's just better to punt.

Luckily this situation is extremely rare. Ive only seen it 2 times and I have been working here for quite some time.Yes, thank goodness for that!

If nothing else, it underscores the need for backups, as well as the ability to switch to another DB quickly. Which is to say, never hardcode your DB credentials in your scripts! Use symbols and configuration files of some kind.

Sorry that you had it happened to you.Thanks. I am also! Live and learn. And, not to gloss over it, I definitely appreciate the additional information you've put in your reply.

[omatic]

Doing a standard myisamck -r on the mysql server itself fixes it.[...snip...]

Nothing that you can do from your end to fix it. BTW, it sounds like a judiciously-selected subset of MySQL commands might be a kick-butt addition to the MySQL admin tab in Ops. I would have been SO happy to be able to run a myIsamChk by just clicking a button there!