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

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