Normally, when you start working with databases, you can suffer a dissaster, and you will be lucky if you do not got only one. And that’s what it has happended to me with the database of my thesis, becuase suddenly, my database has been corrupted, and I cannot use it. Or even worse, I have access but I have no data although the file size are large enough (some files, of course).
Important: This tutorial is been writen at the same time as I am executing instructions, without knowing what result I will have. So, after knowing this and before doing any action, be sure you make a backup of the database before. Everything can happend!!
At the beginning, it was even impossible for my to start the server, but after some attemps and some configs modifications, I could start it.
But, although the server was running, I can see no table. Also, as you can check, there is a problem in the log secuence, and even been administrator of the database, I could see the structure but I couldn’t read any record of any table:
If you move to a file explorer, you can see that the files are filled with data by their size:
After some investigations and Internet searching, I ended up that the problem is due to the ibdata1 file, becuase it is corrupted or it is a bad file, or even it is missing.
Recover from a backup
As you can imagine, I have a backup of the database, done some time ago, and part of the database were available, but not with the last changes. With this scenario, one of the solution I found was:
- Migrate only the structure of the database
- Stop the server
- Copy the directory of the database to be repair
- Start the server again
Let’s go for it. I loaded the .sql file with the copy of the directory but, I got a “nice” 1010 error:
Basically, the directory containing the database cannot be deleted, as I learnt here. So, let’s move again to a file explorer and remove it from here:
Let’s go back to heidiSQL administrator (also, you can use MySQL WorkBench) and let’s try to execute again the script to recreate the database structure, because now It can be executed perfectly:
Next image shows the structure of the database that I am trying to recover:
Now, it is time to stop the database. Because I am working with xampp, I can execute mysql_stop from a command line:
Now it is time to replace the directory created with the one that really have the data (previously you have copied the data in a selected path).
As a prevention, it is better to rename the directory instead of removeng it (you have time to delete it lated, when all process is done). So, let’s start the server again:
Sadly, MySQL is aware of the “trick” and it shows the error because it detects that the id doesn’t fit what expected, and also it sends you the solution: https://dev.mysql.com/doc/refman/5.6/en/innodb-troubleshooting-datadict.html.
Again, time to re-execute the recovery of the database once the database have been deteled. Now, you need to execute this instruction after the database is reloaded again: ALTER TABLE solar_comun._version DISCARD TABLESPACE;
Now, you can copy the files of the _version table with no problem. And execute this sentence: ALTER TABLE solar_comun._version IMPORT TABLESPACE;
SHOW WARNINGS;
And here you have the outcome:
Let’s try what have happend with the _version table:
And, here you have the table! Now, you can do the same process wth the rest of the tables.
Lessons I leant from this problem
I have to be positive about this problem I try to solve today, and the best part is that if you have the .frm and .idb files, it is possible to rebuild a database corrupted although it is not easy.
It is compulsory that you know exactly what database version you are using (in my case, I was working with MySQL 5.6.24), because it is a basic data if you want to recover your database.
Because I already had the problem, I recommend you do it table by table, in order, because there are too much steps to go, and if you do not follow them, you can have a inesperate result.
And last, database backups must be done always. If you do not know how to do it, you can read this entrance I wrote in 2013, or you can wait to a new update in some weekd related to how to backup a MySQL or MariaDB database.
For you curiosity, I could recover the whole database:
I hope this entrance can be of your interest, and if you can, help me with the maintainance of this blog with a small donation.