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
|
image-2020-11-26-10-43-29-157.png