4

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?

2

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 :-) – Spike Jul 10 '18 at 2:51
2
SHOW TABLE STATUS;
and then run 
show warnings;

will show you what all tables are corrupted.

1

When you issue

SHOW TABLE STATUS;

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 Apr 17 '14 at 9:54
  • I am not sure I follow? – Damir Kasipovic Apr 17 '14 at 10:02
  • This shows all tables, not the corrupted tables. – user90193 Jul 27 '18 at 15:48
  • @HoldOffHunger Yeah but those that are crashed are clearly visible, as explained – Damir Kasipovic Jul 27 '18 at 16:00

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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