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

Database corruption involving an index on a virtual column and a charset change



    • Type: Bug
    • Status: Confirmed (View Workflow)
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.4.13, 10.4, 10.5
    • Fix Version/s: 10.4, 10.5
    • Component/s: Virtual Columns
    • Labels:
    • Environment:
      Ubuntu 18.04 x86_64


      First off, sorry that I can't narrow this bug down further - for one thing, I'm not an expert on the internals of mariadb, and also, when I tried reducing the reproduction code further it started exhibiting "heisenbug" behaviour, vanishing intermittently. The code below always managed to trigger the bug though.

      A little bit of background first: The use case here is a table (TableA) with "versioned" data. The ValidTo column represents the end of validity of a particular entry and is set to '2099-12-31' for the active (currently valid) entries (there can be multiple). In order to allow having a unique constraint that only checks among the active entries, I introduced the ActiveOrNull column which is 1 for active rows, and null for inactive ones. Since unique keys don't see values as duplicate as long as one column is null, tagging this column to the end of a unique key will "weaken" that key to only consider collisions among active rows.

      CREATE TABLE `TableB`
          `Id` int NOT NULL PRIMARY KEY
      ) ENGINE = InnoDB;
      CREATE TABLE `TableA`
          `Id`           int          NOT NULL,
          `TableBId`     int          NOT NULL,
          `Name`         varchar(255) CHARSET utf8 NOT NULL,
          `ValidTo`      datetime     NOT NULL,
          `ActiveOrNull` TINYINT(1) AS (CASE WHEN `ValidTo` < '2099-01-01' THEN NULL ELSE 1 END) VIRTUAL,
          PRIMARY KEY (`Id`),
          UNIQUE KEY `uk_a_TableA_TableBId` (`TableBId`, `ActiveOrNull`),
          CONSTRAINT `fk_TableA_TableB` FOREIGN KEY (`TableBId`) REFERENCES `TableB` (`Id`)
      ) ENGINE = InnoDB;
      ALTER TABLE `TableA`
          MODIFY `Name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_german2_ci NOT NULL;
      INSERT INTO TableB (Id) VALUES (54321);
      INSERT INTO TableA (Id, TableBId, Name, ValidTo) VALUES (12345, 54321, 'SomeName', '2099-12-31');
      DELETE FROM TableA WHERE Id = 12345;
      DELETE FROM TableB WHERE Id = 54321;

      At this point, the following error is returned:

      Cannot delete or update a parent row: a foreign key constraint fails (`buergerkonto`.`TableA`, CONSTRAINT `fk_TableA_TableB` FOREIGN KEY (`TableBId`) REFERENCES `TableB` (`Id`))

      This is unexpected - didn't we just delete the only row which might have referred to that one? Let's roll back and look at the data:

      SELECT * FROM TableA WHERE TableBId = 54321;

      | Id    | TableBId | Name     | ValidTo             | ActiveOrNull |
      | 12345 |    54321 | SomeName | 2099-12-31 00:00:00 |            1 |
      | 12345 |    54321 | SomeName | 2099-12-31 00:00:00 |            1 |
      2 rows in set (0.001 sec)

      Whoops, we're getting two rows with the same primary key! From what I can make out, this looks like a corruption in the uk_a_TableA_TableBId index, because querying the table via the primary key looks fine:

      SELECT * FROM TableA;
      | Id    | TableBId | Name     | ValidTo             | ActiveOrNull |
      | 12345 |    54321 | SomeName | 2099-12-31 00:00:00 |            1 |
      1 row in set (0.001 sec)

      I'm not sure how best to work around this issue since I don't really undestand what is going on. Removing the ALTER TABLE makes the problem disappear. Turning the VIRTUAL column into a PERSISTENT one also makes the problem disappear, and is probably what I'll do for now. I'd feel safer knowing that it's an actual solution though.


          Issue Links



              bar Alexander Barkov
              medo Simeon Maxein
              0 Vote for this issue
              3 Start watching this issue