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.