Incorruptibles

From UnifiedBlue

Following recent database pagefile corruption, I thought it important to log the recovery methods. When accessing the wiki dabase, I was receiving a message detailing that the SQL connection had "gone away". On further investigation I determined that the MySQL server was running, but every time access of the database was attempted, it would terminate and reload... sort of a protection mechanism.

On investigation of the logfile I discovered:

InnoDB: Database page corruption on disk or a failed file read of page 1414.
InnoDB: You may have to recover from a backup.
090402 18:29:27  InnoDB: Page dump in ascii and hex (16384 bytes): (ascii/checksum/location of corruption data snipped)
InnoDB: Page may be an update undo log page
InnoDB: Page may be an index page where index id is 0 77
InnoDB: (index keyname of table ufblue_wikidb/objectcache)
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating system has corrupted its own file cache and rebooting your computer removes the error.
InnoDB: If the corrupt page is an index page you can also try to fix the corruption by dumping, dropping, and reimporting the corrupt table.
InnoDB: You can use CHECK TABLE to scan your table for corruption.

Luckily, the corruption was stored in the objectcache table, which simply stores frequently used data for quick retrieval, but as with most caches, the data is dual sighted, so if it cannot be found in the cache, it is simply accessed from the main store. It brings to light a secondary usage for wiki caches; frequently accessed data is at most risk from corruption, so by using a cache, it is essentially acting as an internal backup, helping to prevent repetitive access to the main store compromising data integrity!

Accessing SQL in CLI form was something I was fairly unfamiliar with. Its interesting how heavily we rely on interfaces such as phpMyAdmin to perform even the most simple tasks. Fortunately for me, I found a well written MySQL Commands page, which allowed me to connect to MySQL by simply running mysql from the command line with a user and password flag. I was then able to run the command use databasename; to switch focus.

The next challenge was actually accessing the database. In its present state, no access whatsoever could me made, it would crash out on any attempt. To correct this I had to start MySQL in recovery mode. I found detail on this mode from a blog entitled MySQL Performance Blog This mode essentially opens the databases in a safe mode preventing background operations from running. The mode is enabled by simply adding innodb_force_recovery=1 to the config file (usually my.cnf) and restarting MySQL.

This allows for accessing the data in a read only mode. That way I was able to make a connection. Once in, I simply pulled a table list by running show tables and then began extracting the tables one at a time. To do this I used the following command:

mysqldump -c -u username -ppassword databasename tablename > /tmp/databasename.tablename.sql

Then once I had the entire DB extracted, I created an empty database and began restoring the tables with:

mysql -u username -ppassword databasename < /tmp/databasename.sql

Once I had restored the tables I ran a check table command on each to check integrity, but as no corruption was found it was most probably index corruption. Needless to say, as I am reporting from a full and operative wiki, the DB was completely restored.