Knowledge Base
MilesWeb / Database

Ideas To Fix Corrupted Tables in MySQL

Approx. read time : 3 min

MySQL tables can sometimes get corrupted. It means that an error can occur and the data held within them can become unreadable. So, the attempts to read from a corrupted table will generally result into the server crashing.

Below are common causes of corrupted tables:

  • The MySQL server stops in between of a write.
  • Similar table that is modified by the server is modified by an external program.
  • The machine gets shut down without giving any warning.
  • The computer hardware fails.
  • The MySQL code consists of a software bug.

In case you detect that one of your tables has been corrupted, take a backup of your data directory prior to troubleshooting or attempting to fix the table. This will help to reduce the risk of data loss.

First, stop the MySQL service:

After this, copy your entire data into a new backup directory. The data directory is /var/lib/mysql/ is the default one on Ubuntu systems:

Once the backup is taken, you can begin investigating if the table is in fact corrupted. In case the table uses the MyISAM storage engine, you can verify if it’s corrupted by running a CHECK TABLE statement from the MySQL prompt:

A message will get displayed after using the above command which will help you know whether or not it’s corrupted. If the MyISAM table is actually corrupted, it can basically be repaired by issuing a REPAIR TABLE statement:

If you assume that the corrupted table was repaired successfully, you will get the below output:

If the table corruption isn’t resolved yet, you can check the alternative methods for repairing the corrupted tables in the MySQL documentation.

Alternatively, if the corrupted table uses the InnoDB storage engine, then it will have a different process for repairing. InnoDB is the default storage engine in MySQL since the version 5.5, and it offers automated corruption checking and repair operations. InnoDB finds corrupted pages by performing checksums on every page it reads, and if there is a checksum discrepancy it will automatically stop the MySQL server.

You rarely need to repair InnoDB tables, as InnoDB offers a crash recovery process that can resolve most issues when the server restarts. But, if you face a situation where you need to rebuild a corrupted InnoDB table, use the “Dump and Reload” method recommended by the MySQL documentation. This includes regaining access to the corrupted table by using the mysqldump utility for creating a logical backup of the table. This will keep the table structure and the data within it, and then reload the table back into the database.

Keeping this in mind, try to restart the MySQL service to see if it allows you access to the server:

In case the server remains crashed or otherwise inaccessible, then it is better to enable InnoDB’s force_recovery option. This can be done by editing the mysqld.cnf file:

In the [mysqld] section, add the following command:

Now, save and close the file, and then try to restart the MySQL service again. If you are successfully able to access the corrupted table, use the mysqldump utility to dump your table data to a new file. This file can be named as per your preference, but here it’s named as out.sql:

Drop the table from the database. To avoid the need to reopen the MySQL prompt, use the following syntax:

After this, restore the table with the dump file you just created:

Remember that the InnoDB storage engine is generally highly fault-tolerant as compared to the older MyISAM engine. Tables using InnoDB can still be corrupted, but due to its auto-recovery features, the risk of table corruption and crashes gets much lowered.

Also Read:

How To Create MySQL Tables In phpMyAdmin?

How to search MySQL tables in phpMyAdmin?

Pallavi is a Digital Marketing Executive at MilesWeb and has an experience of over 4 years in content development. She is interested in writing engaging content on business, technology, web hosting and other topics related to information technology.