Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-24287

mysqlcheck unexpectedly causes table to be marked as corrupt

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Not a Bug
    • Affects Version/s: 10.4.13
    • Fix Version/s: N/A
    • Component/s: OTHER
    • Labels:
      None
    • Environment:
      Ubuntu 20.04.1 LTS (GNU/Linux 5.4.0-52-generic x86_64)

      Description

      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

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              Shuttlewood Richard
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved:

                  Git Integration