Error when dropping InnoDB database

I am trying to drop a database in mysql, but every time I get this error

ERROR 2013 (HY000): Lost connection to MySQL server during query

even logged in as root.

Originally, I had a corrupted table that I could not recreate, hence I decided to recreate the db. I have innodb file per table, not sure if that matters.

How can I drop this database?

I am afraid you cannot drop the database.

The problem comes the table that is corrupt. It's not necessarily the .ibd file. It is, more than likely, the data dictionary entry within ibdata1 (See pictorial representation of ibdata1).

I wrote about this back on Mar 02, 2012 : InnoDB table SELECT returns ERROR 2006 (HY000): MySQL server has gone away (after power outage)

Although tedious, here is what you can do to save the other tables in that corrupt database bad_db:

STEP 01) USE bad_db

STEP 02) Run SELECT COUNT(1) FROM tablename; for every table in bad_db. Collect all table names that do not crash mysqld when you attempt to run the count.

STEP 03) Run mysqldump -u... -p... bad_db table1 table2 ... tableN > bad_db_goodtables.sql
for all the tables that did not crash mysqld.

If you are going to use a different database name, then reload bad_db_goodtables.sql into that new database.

If you need the database to be the same name, BAD NEWS !!! You will have overhaul ibdata1. There are more steps:

STEP 04) mysqldump all the other databases into separate dump files

STEP 05) Perform InnoDB Infrastructure Cleanup (See my Oct 29, 2010 StackOverflow Post)

STEP 06) Reload all the other mysqldumps good databases.

Give it a Try !!!

StackExchange Database Administrators Q#35959, answer score: 10