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

Data corruption after partition maintenance for table using legacy DATETIME storage.

    XMLWordPrintable

    Details

      Description

      After upgrading to 10.4 we saw data corruption in some of our partitioned tables. Turns out they were using the older DATETIME storage, and adding or removing partitions from them corrupted the existing datetime data, and caused other columns to be corrupted after additional inserts/updates.

      I think this is due to the fact that the ALTER TABLE ADD/DROP PARTITION statement triggers the automatic upgrade in 10.4 for the datetime columns. However since it is targeted at the partition that is being dropped/added, it does not actually perform the data upgrade on the datetime columns. Instead it might be trying to use the old data as if it were upgraded.

      The following script creates a table using the old formats by setting the global to disable the newer storage format. It then adds some data and adds a partition. In my tests the dates are corrupted directly after the partition is added. After the insert, even the varchar column has become corrupted.

      SET GLOBAL mysql56_temporal_format=off;
       
      CREATE OR REPLACE TABLE `t` (
          `pid` INT(5) NOT NULL,
          `createdAt` DATETIME NOT NULL DEFAULT NOW(),
          `comment` VARCHAR(20)
      ) ENGINE=INNODB DEFAULT CHARSET=latin1 ROW_FORMAT=DYNAMIC
      PARTITION BY LIST(pid)
      (PARTITION p1 VALUES IN (1), PARTITION p2 VALUES IN (2));
       
      INSERT INTO `t` (`pid`, `comment`)
      VALUES (1, 'First'), (2, 'Second');
       
      SELECT * FROM `t`;
       
      SET GLOBAL mysql56_temporal_format=on;
       
      ALTER TABLE `t` ADD PARTITION (PARTITION p3 VALUES IN (3));
       
      SELECT * FROM `t`;
       
      INSERT INTO `t` (`pid`, `comment`)
      VALUES (1, 'First'), (2, 'Second');
       
      SELECT * FROM `t`;
      

      OUTPUT

      pid	createdAt	comment
      1	2019-11-01 04:01:17	First
      2	2019-11-01 04:01:17	Second
      pid	createdAt	comment
      1	9271-10-19 17:45:29	First
      2	9271-10-19 17:45:29	Second
      pid	createdAt	comment
      1	9271-10-19 17:45:29	First
      1	1070-04-28 22:25:28	st\0\0\0
      2	9271-10-19 17:45:29	Second
      2	2253-01-22 06:25:28	ondond
      

        Attachments

          Issue Links

            Activity

              People

              Assignee:
              bar Alexander Barkov
              Reporter:
              jacob.williams Jacob Williams
              Votes:
              0 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

                Created:
                Updated: