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

Creating ER Diagrams from Your Database

About a week ago, I discovered that schemabank.com was gone. The site seems to have been terminated, which is really sad. On the other hand, I think it is rude from the developers to just shut the site down without any warning. At least I didn’t get any. There is people who used to rely on this service. At the moment, I was using the service in collaboration with another person creating a small application.

This situation force me to look for a solution to one of the problems the site left me with. I am too used to seeing an ER diagram of the databases I work with. Now I had no way to do this. I finally decided to install MySQL workbench, but I found an interesting solution. I am not using that because it was not exactly what I needed. However, I think it is interesting enough to share it here.

The solution is posted on: https://nsaunders.wordpress.com/2009/01/11/easy-visualisation-of-database-schemas-using-sqlfairy/

It uses SQL Fairy And I think it is an interesting solution for when you want to, for example, share a visual interpretation of your database.

It is really a shame that schemabank.com is gone. The site never really had a way to contact the developer, and if it did, it was well hidden. I send them a couple of tweets, but nothing. I never got a reply.

Problems Updating Database Tables (MySQL)

I run into a little problem a few days ago when I was trying to update a database table’s structure. The table had been created with the wrong settings, and I had to go and add an AUTO_INCREMENT to the primary key. Before you get out of here thinking I’m such a stupid person, you should know that the table was created, not by me, but by a person who is bravely learning about this stuff.

The table is using InnoDB engine, which enforces constraints between tables, and this was causing me not to be able to change the table. Everytime I tried, I jut got back an error similar to this:

ERROR 1025 (HY000): Error on rename of './e3/#sql-17f3_f894' to './e3/cls_ev_items' (errno: 150)

To solve this problem, one just needs to remove the contraint, change the database, and add the constraint again.

To remove the contraint you would do something like this:

ALTER TABLE table_name DROP FOREIGN KEY relationship_name;

Then you can alter your table, in my case I did something like this:

ALTER TABLE table_name CHANGE id id INT UNSIGNED NOT NULL AUTO_INCREMENT

Then add the constraint again:

ALTER TABLE table_name ADD CONSTRAINT `relationship_name` FOREIGN KEY(`field_in_table`) REFERENCES `other_table` (`foreing_key`);

And that is basically all you have to do. Im my case, all the tables were missing the AUTO_INCREMENT, so I just DROPed them all and creted them anew with the right settings, but sometimes that is not possible.

Some links that were useful, or maybe not, are recorder here for future reference:
http://www.simplicidade.org/notes/archives/2008/03/mysql_errno_150.html
http://stackoverflow.com/questions/160233/what-does-mysql-error-1025-hy000-error-on-rename-of-foo-errorno-150-me