Fixing MySQL Corrupt Table “ERROR 2013 (HY000): Lost connection to MySQL server during query”

Today I tried to drop a database in mysql

DROP DATABASE db_name;

Which resulted in a error: “ERROR 2013 (HY000): Lost connection to MySQL server during query”. At that point everything started to go wrong, to the point where the MySQL server went completely down and I was unable to run it again. In short this is how things happened:

  1. Try to drop the database, which resulted in an error
  2. Keep trying to drop it, which resulted in the same error.
  3. Try increasing the net_read_timeout and net_write_timeout on the server, as well as –connect-timeout in the mysql client. No luck.
  4. Search the web, find no useful advice, but found a possible reason “data corruption”
  5. Try to recover data with a backup of the database from the production server. Shit went dow!
  6. The server stopped and refuse to start again. Following the instructions in the stop error lead me to new search terms.
  7. We have a new problem now, do more web searching.
  8. Find a useful page which recommended disabling innodb, and setting myisam as the default database engine.
  9. After doing that, the server worked, but trying to dump the database resulted in an error: ERROR 1010 (HY000): Error dropping database (can’t rmdir ‘./db_name’, errno: 39 “Directory not empty”)
  10. I decided to manually remove the directory, which removed the database.
  11. Create the database again and use the backup to restore it.
  12. Got an error saying that the table ‘table’ already existed. ‘table’ is the first table that the backup file tries to create.
  13. Try to dump the database, but got the same error as in #9.
  14. Manually delete the database again, Create it and the do a proper DROP.
  15. Create the database again and restore from backup. It worked!

I read a few pages during this problem, and found these useful:

SOLVED: InnoDB Error: space header page consists of zero bytes: xampp

MySQL: Error dropping database (errno 13; errno 17; errno 39)

Advertisements