Corrupted Table


Correct Database Issue #144 – Corrupted Table

I came across an interesting problem recently where one of my MySQL tables became corrupted and the automated repair feature was not able to fix it. It was a non-critical table so the website still mostly functioned, but it did limit some functionality. I had a backup, but it was a few days old and I had just recently made a lot of changes that impacted that particular table. So after searching for a hour I was finally able to find the issue and resolve it without needing root access to the server (which I don’t have).

Note: there are a number of reasons a MySQL table can become corrupted and I have not yet diagnosed what caused the issue for me. It could be anything from a hard disk failure to a software issue.

Background

  • I host everything with bluehost.com
  • I have shell access, but do not have root access – IMPORTANT
  • I have full access to phpMyAdmin and the MySQL Databases

The Error

Here is the error I was seeing in phpMyAdmin (sanitized of course)

#144 - Table './<database>/<tablename>' is marked as crashed and last (automatic?) repair failed

Nice cryptic error.

The Resolution

I logged into the shell interface using putty and logged into the database:

$ mysql -u <username> -p

Replace <username> with your database username. After pressing enter it will prompt you for your password.  After logging into MySQL we need to select the database that contains the corrupted table:

mysql> use <database>

Again, replace <database> with the name of the database.  You should see a confirmation message of “Database changed”.  Now let’s check the table that is causing the issue:

mysql> check table <tablename>;

As before, replace <tablename> with the name of the suspected table.  You should get output similar to:

+--------------------------------------+-------+----------+---------------------------------------------------+
| Table                                | Op    | Msg_type | Msg_text                                          |
+--------------------------------------+-------+----------+---------------------------------------------------+
| database.tablename                   | check | warning  | Table is marked as crashed and last repair failed |
| database.tablename                   | check | warning  | Size of datafile is: 20652 Should be: 19564       |
| database.tablename                   | check | error    | Key in wrong position at page 14336               |
| database.tablename                   | check | error    | Corrupt                                           |
+--------------------------------------+-------+----------+---------------------------------------------------+
4 rows in set (0.02 sec)

This is definitely the problem table. Now to run the command repair command:

mysql> repair table <tablename>;

You should get output similar to:

+--------------------------------------+--------+----------+----------------------------------------------------+
| Table                                | Op     | Msg_type | Msg_text                                           |
+--------------------------------------+--------+----------+----------------------------------------------------+
| database.tablename                   | repair | info     | Delete link points outside datafile at 18580       |
| database.tablename                   | repair | info     | Found block that points outside data file at 18908 |
| database.tablename                   | repair | info     | Found block that points outside data file at 18924 |
| database.tablename                   | repair | info     | Found block that points outside data file at 19064 |
| database.tablename                   | repair | info     | Delete link points outside datafile at 20152       |
| database.tablename                   | repair | status   | OK                                                 |
+--------------------------------------+--------+----------+----------------------------------------------------+
6 rows in set (0.00 sec)

Looks like everything is resolved, but run the check command again to verify. You should get something similar to:

+--------------------------------------+-------+----------+----------+
| Table                                | Op    | Msg_type | Msg_text |
+--------------------------------------+-------+----------+----------+
| database.tablename                   | check | status   | OK       |
+--------------------------------------+-------+----------+----------+
1 row in set (0.01 sec)

And that is it. I logged back into phpMyAdmin and everything seems to be working like a charm. All-in-all a scary error that was easy to fix.