How to detect corrupt indexes

Is there any tool, which can check if some secondary index has different data than the table itself?

For example, I have a simple query, that uses secondary index (“Using index” in explain) and if I run it with IGNORE INDEX, I get different result. There is an index corruption and the index shows a row, which is not present in the table.

I can resolve this using OPTIMIZE TABLE, but I would like to detect this problem in all tables before it spoils our application data. I cannot run OPTIMIZE on all tables every hour and hope everything is OK )

I’m thinking about a tool like mk-table-checksum, which would take every secondary index and do something like:

LOCK TABLES mytable READ;Do column checksum with FORCE INDEXDo column checksum with IGNORE INDEXUNLOCK TABLES;

Currently we are running XtraDB 9 - 5.1.42, the index corruption might be connected with online index creation included in the innodb plugin.

There is no such tool. But you could sponsor adding this type of feature to mk-table-checksum.

I’ve realized that CHECK TABLE does this, however it scans the whole adaptive hash index, which stalls our mysqld instance for few minutes.

Hopefully, I don’t need the check any more, because the fast creation bug will be fixed in the next innnodb plugin:

http://forum.percona.com/index.php/m/5798/#msg_5798
http://bugs.mysql.com/bug.php?id=54330