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

TABLE CHECKSUM calculation sometimes ignores columns

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Minor
    • Resolution: Duplicate
    • Affects Version/s: 5.5.44, 5.5.60, 5.5.61, 10.2.11
    • Fix Version/s: N/A
    • Labels:
    • Environment:

      Description

      How to reproduce:

      Run this SQL snippet (saved to the file checksumbug.sql, or just paste in a console):

      DROP TABLE IF EXISTS mytable1;
      CREATE TABLE mytable1 (
      `ID` INTEGER NOT NULL,
      `FILTER` MEDIUMTEXT DEFAULT NULL,
      `PORT` INTEGER,
      `VPI` INTEGER,
      `VCI` INTEGER,
      `CID` VARCHAR(150),
      `LINK_NAME` VARCHAR(64),
      `IID` VARCHAR(40) DEFAULT NULL,
      `PROTOCOL_PACKAGE` VARCHAR(40),
      `CONFIG_XML` MEDIUMTEXT DEFAULT NULL,
      `LICENCE_NAME_ID` INTEGER DEFAULT -1,
      `DESCRIPTION` VARCHAR(50) DEFAULT NULL,
      `INTERFACE_ID` INTEGER DEFAULT -1,
      `SEND_TO_PROBE` BOOLEAN DEFAULT 1,
      `MSU_IP` VARCHAR(45) DEFAULT NULL,
      `MTP2_OPC` INTEGER,
      `MTP2_DPC` INTEGER
      ) ENGINE=InnoDB CHARSET=UTF8;

      INSERT INTO `mytable1` VALUES (6,'<dummy_tag \>',80,8,35,'','mylink',NULL,'DEFAULT',NULL,12,'myDescription1',2,1,NULL,NULL,NULL);
      CHECKSUM TABLE `mytable1`;

      UPDATE `mytable1` SET DESCRIPTION = "myDescription2" WHERE ID = 6;
      CHECKSUM TABLE `mytable1`;

      Actual:

      C:\Users\lennartsc\Desktop\linkdesc>mysql test < checksumbug.sql
      Table Checksum
      test.mytable1 1288645903
      Table Checksum
      test.mytable1 1288645903

      Expected:

      The two checksums should be different (I don’t care so much about the actual value).

      Analysis:

      The table I used in the SQL snippet originally had many more columns. I have tried to reduce the number of columns as much as possible. Removal of any more columns will make the bug not reproducible.

      It does not matter if InnoDB or MyISAM table engine is used.

      The CHECKSUM TABLE command is documented here: https://mariadb.com/kb/en/library/checksum-table/
      (see specially the chapter “Differences between MariaDB and MySQL” and the --old parameter.)

      If I start MariaDB with the --old command line parameter or adding “old” to [mysqld] to my my.cnf I can no longer reproduce the problem.

      Since the old parameter affects the behavior I guessed that the bug is somewhere around this code: https://github.com/MariaDB/server/blob/a49ec980422e04ce1a37344be09aa6254f16fa2a/sql/sql_table.cc#L7889-L7890:
      if (! thd->variables.old_mode && f->is_real_null(0))
      continue;
      I debugged and saw that that the DESCRIPTION field somehow has been classified as f->is_real_null(0), that is, it went into the if-statement and executed continue and thus skipped the entire column.

      Upon further debugging I saw that every column that was after the column CONFIG_XML is skipped in the checksum calculation. It looks like the what f->null_ptr is pointing to has the value 255 (11111111(bin)).

      The byte that f->null_ptr points to can each store NULL-value indications for 8 columns. If the value is set to 255 (11111111(bin)) then 8 columns are all NULL (in consecutive order as they are declared). From what I can see the default value for a bit is 1, when there does not exist any more columns.

      I guess it instead should have the value 225 (11100001(bin)): the first bit is set to indicate CONFIG_XML is NULL, and bit number 2, 3, 4, and 5 should each be 0 to indicate that the following 4 columns (after CONFIG_XML) are not NULL, and the last 3 bits set to 1 to indicate they are NULL in my example row.

      My guess is that the code that is calculating the NULL indicators has failed and has set it to the 255 value, thus effectively set it so 8 columns is treated as NULL.

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              Unassigned
              Reporter:
              lennartschedin Lennart Schedin
              Votes:
              0 Vote for this issue
              Watchers:
              3 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: