Summary

• mysqlcheck found some tables with defective secondary indexes
• The tables were marked as corrupt by mysqlcheck and could not be accessed
• resulted in a 1hr production outage on a public facing service

It is not clear from the documentation (https://mariadb.com/kb/en/mysqlcheck/) that this is intended behaviour. The implication is that a check is just a check and takes no action.

Expectation: A check should flag the problem but take no action OR the defective secondary indexes should be marked as unusable. Either way the table should remain accessible pending a planned response.

Detail

Server version: 10.4.13-MariaDB-1:10.4.13+maria~focal-log mariadb.org binary distribution

All tables are Innodb

This is a Master plus 2 slave replication topology

Initially both slaves showed the same replication errors:

2020-11-24 10:01:44 44 [ERROR] InnoDB: Record in index `idlocation_deleted_packetgw1_senddate` of table `xxx`.`yyy` was not found on update: TUPLE (info_bits=0, 5 fields):

{[2] p(0x0670),[1] (0x01),NULL,[5] (0x99A7F0A000),[4] @ (0x1340A38C)}

at: COMPACT RECORD(info_bits=0, 5 fields):

{[2] o(0x066F),[1] (0x01),[1]2(0x32),[5] (0x99A7F08000),[4] i f(0x1369C166)}

2020-11-24 10:01:44 44 [ERROR] InnoDB: Record in index `idloc_del_sentviagw_deliverystdone_senddate_packetgw1` of table `xxx`.`yyy` was not found on update: TUPLE (info_bits=0, 7 fields):

{[2] p(0x0670),[1] (0x01),[4] (0x80000001),[1] (0x01),[5] (0x99A7F0A000),NULL,[4] @ (0x1340A38C)}

at: COMPACT RECORD(info_bits=0, 7 fields):

{[2] p(0x0670),[1] (0x01),[4] (0x80000001),[1] (0x01),[5] (0x99A7EEA000),[1]2(0x32),[4] M!G(0x134D2147)}

2020-11-24 10:01:44 4 [ERROR] InnoDB: Unable to find a record to delete-mark
InnoDB: tuple DATA TUPLE: 7 fields;

This led me to do a mysqlcheck on the production master:

mysqlcheck --check --verbose --databases aaa bbb ccc

6 tables had errors of this type:

schema.tablex
Warning : InnoDB: Index 'xxxx_id' contains 77545619 entries, should be 77545621.
Warning : InnoDB: Index 'session_id' contains 77545620 entries, should be 77545621.
error : Corrupt

We then saw problems of this type in the log:

[ERROR] Got error 128 when reading table './schema/tablex'
[ERROR] Got error 180 when reading table './schema/tablex'

Fixed using optimize table

Proposed resolution

At the very least the documentation should be modified to warn this may happen

Preferably:

default to just do check, take no action even for corrupt tables or primary keys. In a production environment the DBA must be able to take any decision that impacts availability

Add switches:

--mark_corrupt_table
--mark_corrupt_sec_idx