9

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?

Improve this question

2 Answers 2

Reset to default
10

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

Improve this answer
4
  • Much appreciated Rolando! Fantastic reply. Wish there was a way to repair that db. Would have liked it to be the same name :)
    – giorgio79
    Commented Mar 5, 2013 at 12:08
  • I ran MySQL for a while now, had this happen the first time the other day, thanks for the explanation.
    – webLacky3rdClass
    Commented Dec 6, 2013 at 14:43
  • 1
    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
    Commented Apr 1, 2014 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
    Commented Sep 5, 2014 at 20:49
0

First of all check MySQL (MariaDB) log. The problem may be about InnoDB. If it is the case, you can disable InnoDB and then drop the database. Then you can enable it.

Here is how you can disable InnoDB.

In my.cnf

[mysqld]
skip-innodb
default-storage-engine = myisam
Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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