I monitor MySQL databases by querying various things remotely. I recently came across an issue where by a table had become corrupted but I did not know about it until the daily mysqldump failed. Is there a way I can check for corruption (without running a CHECK TABLE on every table)? I thought I had it figured when I noticed information_schema.tables has a table_comment column which says this when it's corrupt:

TABLE_COMMENT: Table './demo/test' is marked as crashed and should be repaired

Turns out, when I try to search on that column, i.e. WHERE TABLE_COMMENT LIKE '%marked as crashed%', nothing is returned!

I need to be able to query this from a stored procedure, any suggestions?

3 Answers 3

and then run 
show warnings;

will show you what all tables are corrupted.


When you issue


On the database in question, corrupted tables will be shown. They will have no row count or size, most of the properties will be null, and in Comment column you will see your text:

Table './demo/test' is marked as crashed and should be repaired

  • That's for the suggestion but that is based on information_schema.tables so I still have the same issue.
    – dwjv
    Commented Apr 17, 2014 at 9:54
  • I am not sure I follow?
    – dkasipovic
    Commented Apr 17, 2014 at 10:02
  • This shows all tables, not the corrupted tables.
    – user90193
    Commented Jul 27, 2018 at 15:48
  • @HoldOffHunger Yeah but those that are crashed are clearly visible, as explained
    – dkasipovic
    Commented Jul 27, 2018 at 16:00

Usually a table that is corrupt will halt a mysqldump in its tracks. A very quick-and-dirty way to check for table corruption across all tables is to mysqldump the table schemas with no data:

mysqldump --no-data --all-databases > table_schema.sql

Go to the Last Line of the table_schema.sql. You can look for this pattern:

-- Dump completed on 2013-12-26  8:56:27

If you see Dump completed on, all the tables are fine. Otherwise, the mysqldump should halt at or before the corrupt table.

CAUTION: Doing mysqldump on a corrupt MyISAM table will halt the mysqldump. Doing mysqldump on a corrupt InnoDB table could crash mysqld.

In all honesty, the database needs to have REPAIR TABLE done periodically (at least once a month)

  • There is three hyphen in no-data :-)
    – RBB
    Commented Jul 10, 2018 at 2:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.