MySQL is considered a reliable, performance-driven database that is used by many organizations to develop cost-effective database applications. Albeit database corruption is rare with MySQL, you will find cases where your database fails to respond to queries, exhibits abnormal behavior, or becomes inaccessible. Let us delve deeper into some of these cases and discuss their suggested workarounds.
MyISAM Table Corruption in MySQL Database
MyISAM (the default database engine for MySQL before v5.5.5) uses a table format that is less vulnerable to corruption. However, corruption may hit MyISAM tables due to more than the obvious reasons, including disgraceful system shutdown, abrupt termination of MySQL, hardware failures, bugs in the MySQL or MyISAM code, and more.
Corrupt MyISAM Tables exhibit unexpected behavior that manifests itself when you run queries against these tables. You may come across a range of errors that indicate corruption in your MyISAM Tables. The following is a list of common errors generated due to MyISAM table corruption:
- ‘Index file is crashed’
- ‘Record-file is crashed’
- ‘Old database file’
- ‘Record was already deleted (or record file crashed)’
- ‘Duplicate unique key or constraint on write or update’
- ‘Table is crashed and last repair failed’
The above errors require you to use ‘myisamchk’ (a table-maintenance utility built into MyISAM). This utility helps you fix corruption in MyISAM tables. First up, you should switch to the database directory and verify the permissions on table files. Mentioned below is the definitive approach you should follow to repair your MyISAM tables:
I. Checking MyISAM Tables
You can check your tables by running myisamchk *.MYI or myisamchk -e *.MYI. If you need to skip all the unnecessary information, you can use ‘-s’ option along with the command.
If the server is stopped midway, you can inform the database engine to mark the table as corrupt using the ‘–update-state’ option. If any errors are returned, you can proceed to step II. If the engine crashes in the middle of the process or you get unusual errors (like ‘out of memory’), skip to step III.
II. Repairing Tables Using ‘myisamchk -r -q’
In this stage, you can simply run the ‘myisamchk’ utility with the ‘-r’ and ‘-q- options These options help you perform repair in ‘quick recovery mode’ that fixes corruption in the index file without modifying the data file. This method of repair is ideal for situations in which your data file is intact and the delete links in the data file are consistent. If this fails to work, then try the following procedure:
- Back up your data file to a different location.
- Run ‘myisamchk -r table_name’ to perform repair in ‘recovery mode’. This type of repair attempts to reconstruct the index file and delete all the incorrect rows from the data file.
- If the above step fails to make the repair, run ‘myisamchk –safe-recover table_name’. ‘–safe-recover’ means ‘safe recovery’ mode that can deal with some more cases of corruption other than those handled by the regular recovery mode.
III. Performing Difficult Repair
This method is applicable to cases where your first block in the index file is corrupt or the index file goes missing. To fix this, you need to create a new index file using the following steps:
- Copy your data file to another location.
- Create the new data and index files through the table description file.
shell> mysql db_name
mysql> SET autocommit=1;
mysql> TRUNCATE TABLE tbl_name;
- Copy the data file you copied earlier onto this new data file.
There are two other MySQL table errors that cannot be repaired using the above method:
135: No more room in record file
136: No more room in index file
To fix them, use ALTER TABLE command to increase the ‘MAX_ROWS’ and ‘AVG_ROW_LENGTH’ values for the problem table as shown below:
ALTER TABLE tbl_name MAX_ROWS=xxx AVG_ROW_LENGTH=yyy;
InnoDB Table Corruption in MySQL Database
InnoDB is the default database engine for MySQL since v5.5.5. InnoDB tables are less likely to fall prey to corruption as they are transactional and reliable. If you end up shutting down the database disgracefully, its internal recovery mechanism does a good job of restoring your database to a consistent state by running the log file. However, there is always a risk of corruption when you are running crappy hardware. In the event that some of the pages in your InnoDB tablespace get corrupt, the engine will crash displaying the following messages:
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 7.
InnoDB: Page checksum 587461377, prior-to-4.0.14-form checksum 772331632
InnoDB: stored checksum 2287785129, prior-to-4.0.14-form stored checksum 772331632
InnoDB: Page may be an index page where index id is 0 25556
InnoDB: (index “PRIMARY” of table “test”.”test”)
Follow the procedure given below to fix InnoDB table corruption:
- Open the /etc/my.cnf configuration file and add the following line to this file: [mysqld] innodb_force_recovery = 4
- Next, restart MySQL. This will start your database with ‘innodb_force_recovery’. You may not be able to perform any INSERTS and UPDATES while running your database in this mode.
- Use ‘mysqldump’ to dump all the tables within your database.
- Close your database and delete the data directory. You should now create MySQL default tables by running ‘mysql_install_db’ procedure.
- Remove the line in your configuration file that you added in the beginning.
- Use a backup to restore all your data.
If you still find issues with your MySQL database, you can opt for commercial software to fix MySQL database corruption. This software is equipped to deal with all types of database corruption issues and help you recover every single object in your MySQL database absolutely intact.