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.
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
