26 October 2009

PITA 4: MySQL Error Messages

Today while connecting to my DB Server, I got a strange error message:
Got error 28 from storage engine
Tried to check the .err file located in var/lib/mysql (Xubuntu), couldn't open the file, when checked, the file size was more than 50GB.

This was the reason of the above error (No free space on the disk to write!). Stopped MySQL server and deleted the .err file. After starting the server I checked the file for errors and found this error repeated for millions times:
091026 ***** InnoDB: Error: page ***** log sequence number * *********
InnoDB: is in the future! Current system log sequence number 0 330424282.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/mysql/en/backing-up.html for more information.
Since I copied the complete data folder from one computer to another, the log files for InnoDB got corrupted. I could not restore them.

The only option I had to avoid any data loss or corruption was to change the Engine type to MyISAM, which doesn't look for log files and can be repaired with less effort.