1. Home
  2. Databases
  3. MySQL Table Crash fix – marked as crashed and should be repaired?

MySQL Table Crash fix – marked as crashed and should be repaired?

One of the table in my MySQL database has been corrupted and the worst part of it is, the corrupted one was the sessions table – due to which the application was not able to store sessions for the users. Well, I got this error when I tried to take a backup of the database before fixing the issue.  Here’s the snapshot of the error.

# mysqldump -u root -p filecloud > filecloud.sql
 Enter password:
 mysqldump: Got error: 145: Table './filecloud/sessions' is marked as crashed and should be repaired when using LOCK TABLES

To fix this error, we need to repair the table.

How to check and repair a table in MySQL Database?

If your application or a mysql command gives an error as “Table is marked as crashed and should be repaired”, then use mysqlcheck command to confirm that the table is corrupted or crashed.

mysql corrupted table

The below command will check the table sessions in filecloud database.

# mysqlcheck -c filecloud sessions -u root -p

Alternatively, you can login to MySQL and execute the below query.

Query format:

CHECK TABLE tablename;

Example:

mysql> check table sessions;
 +--------------------+-------+----------+----------------------------------------------------------+
 | Table | Op | Msg_type | Msg_text |
 +--------------------+-------+----------+----------------------------------------------------------+
 | filecloud.sessions | check | warning | Table is marked as crashed |
 | filecloud.sessions | check | warning | 2 clients are using or haven't closed the table properly |
 | filecloud.sessions | check | error | Record at pos: 505884 is not remove-marked |
 | filecloud.sessions | check | error | record delete-link-chain corrupted |
 | filecloud.sessions | check | error | Corrupt |
 +--------------------+-------+----------+----------------------------------------------------------

You can also check all tables in the database.

# mysqlcheck -c filecloud -u root -p
 Enter password:
 filecloud.payment_log OK
 filecloud.plugin OK
 filecloud.premium_order OK
 filecloud.remote_url_download_queue OK
 filecloud.sessions OK
 filecloud.site_config

How to check all tables and all databases?

# mysqlcheck -c  -u root -p --all-databases

How to REPAIR tables using mysqlcheck?

The corrupted or crashed tables can be repaired using REPAIR query or mysqlcheck command. In mysqlcheck command, use ‘-r’ option to repair the table.

# mysqlcheck -r filecloud sessions -u root -p

The above command will repair the corrupted sessions table in filecloud database.

Alternatively, you can use REPAIR query as shown below:

mysql> repair table sessions;
+--------------------+--------+----------+-----------------------------------------------+
| Table | Op | Msg_type | Msg_text |
+--------------------+--------+----------+-----------------------------------------------+
| filecloud.sessions | repair | info | Wrong bytesec: 58- 49- 50 at 505884; Skipped |
| filecloud.sessions | repair | info | Wrong bytesec: 58- 49- 50 at 505884; Skipped |
| filecloud.sessions | repair | info | Wrong bytesec: 58- 49- 50 at 505884; Skipped |
| filecloud.sessions | repair | info | Wrong bytesec: 58- 49- 50 at 505884; Skipped |
| filecloud.sessions | repair | warning | Number of rows changed from 6140 to 6127 |
| filecloud.sessions | repair | status | OK |
+--------------------+--------+----------+-----------------------------------------------+

How to Optimize the table using mysqlcheck?

If you had deleted plenty of rows from a table, it’s a good practice to optimize the table to get unused space and defragment the data. To do that, use ‘-o’ option with the mysqlcheck command as shown below.

# mysqlcheck -o filecloud sessions -u root -p
Enter password:
filecloud.sessions OK

You can also choose to combine check, optimize and repair options with mysqlcheck command.

# mysqlcheck -u root -p --auto-repair -c -o filecloud
Updated on September 2, 2017

Was this article helpful?

Related Articles

Add A Comment