How to detect corrupt indexes

  • Filter
  • Time
  • Show
Clear All
new posts

  • 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.

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


    • #3
      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: