Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

1 Answer 1

up vote 4 down vote accepted

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 !!!

share|improve this answer
    
Much appreciated Rolando! Fantastic reply. Wish there was a way to repair that db. Would have liked it to be the same name :) –  giorgio79 Mar 5 '13 at 12:08
    
I ran MySQL for a while now, had this happen the first time the other day, thanks for the explanation. –  webLacky3rdClass Dec 6 '13 at 14:43
    
Thank you very much! –  jamix Jan 5 at 15:06
    
How can you perform InnoDB infrastructure cleanup if you cannot drop the database and the step #2 of the cleanup is to drop all databases? –  bancer Apr 1 at 9:30
    
Probably a rare situation, but I had this happen while restoring a dump as a background job. The parent terminal died, and the db couldn't be dropped until the restore connection was killed. –  STW Sep 5 at 20:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.