MySQL error: table is marked as crashed and should be repaired

[kuldeep]

MySQL error: table is marked as crashed and should be repaired

You may experience crashed MyISAM tables in MySQL from times to times, especially after a forced shutdown (kill -9) of MySQL or a crash of the entire server. The errors will look like this in your logs. InnoDB has good crash recovery on its own and will probably never give you these errors as it self-repairs upon MySQL restart.

[ERROR] /usr/libexec/mysqld: Table './dbname/table_name' is marked as crashed and should be repaired
[ERROR] /usr/libexec/mysqld: Table './dbname/table_name' is marked as crashed and should be repaired
[ERROR] /usr/libexec/mysqld: Table './dbname/table_name' is marked as crashed and should be repaired

You can also find broken tables with the myisamchk tool that is provided by the MySQL server installation.

# myisamchk -s /var/lib/mysql/*/*.MYI
MyISAM-table '/var/lib/mysql/dbname/table_name.MYI' is marked as crashed and should be repaired

The above command will show you each table that is in need of repair.

If a table is reported as damaged, repair it with that same tool.

# myisamchk -r /var/lib/mysql/dbname/table_name.MYI

That solves the problem in most cases. If it doesn't, make sure to stop your webservice (so no new MySQL requests are being made), stop the MySQLd daemon itself and run the following command.

# myisamchk -r --update-state /var/lib/mysql/dbname/table_name.MYI

The --update-state tells MySQL to mark the table as "checked". Restart your MySQLd and webservice and you should be good go to. If you're up for more mild reading, have a look at the good documentation of MySQL itself on MyISAM repairs.