Extension of timestamp range can cause OM=>NS replication discrepancy and failure

There can be a variety of cases when a binlog event executed on an new replica (which has extended timestamps introduced in MDEV-32188) will end up with a different data comparing to the old primary which doesn't have the functionality. A discrepancy in data sooner or later causes replication abort.

The scenarios will most likely involve a non-strict mode, either explicitly (via IGNORE modifier) or implicitly by using default sql_mode which is non-strict for non-transactional tables, or another value which doesn't involve strict mode (e.g. sql_mode=ORACLE). It is however possible that there are cases when even the strict mode doesn't prevent it from happening.

I doubt that it can be easily fixed, or even that it is important enough to necessarily fix it, but I think it's better to have it filed, so it can be addressed later if the need arises.

Below are a few examples. They can be simplified further, but are intentionally left the way they are, to keep them somewhat realistic.

--source include/have_binlog_format_mixed.inc

CREATE TABLE t1 (a DATETIME);

INSERT INTO t1 VALUES ('2000-01-01'),('2100-01-01');

CREATE TABLE t2 (a TIMESTAMP) ENGINE=MyISAM;

INSERT INTO t2 VALUES (NOW()),('2100-01-01');

SHOW BINLOG EVENTS;

SELECT * FROM t2;

DROP TABLE t1, t2;

On an old server INSERT INTO t2 will truncate the 2nd value to 0000-00-00, with a warning, and the statement will be binlogged as is. On a new server (including a new replica), it will insert 2100-01-01. Further replication on diverged tables will break easily.

--source include/have_innodb.inc

--source include/have_binlog_format_mixed.inc

SET sql_mode=ORACLE;

CREATE TABLE t (a TIMESTAMP) ENGINE=InnoDB;

INSERT INTO t VALUES (NOW()),('2100-01-01');

SHOW BINLOG EVENTS;

SELECT * FROM t;

DROP TABLE t;

sql_mode=ORACLE unsets STRICT_TRANS_TABLES (which may be not obvious), further result is the same as in the 1st example, with 0000-00-00 inserted on the old server/primary, and 2100-01-01 on the new server/replica.

--source include/have_binlog_format_mixed.inc

CREATE TABLE t1 (a DATETIME);

INSERT INTO t1 VALUES ('2000-01-01'),('2100-01-01');

ALTER IGNORE TABLE t1 MODIFY a TIMESTAMP;

SELECT * FROM t1;

SHOW BINLOG EVENTS;

DROP TABLE t1;

The outcome is the same.

--source include/have_partition.inc

--source include/have_binlog_format_mixed.inc

CREATE TABLE t1 (a TIMESTAMP) ENGINE=MyISAM PARTITION BY RANGE (UNIX_TIMESTAMP(a)) PARTITION pn VALUES LESS THAN (2147483647));

INSERT INTO t1 VALUES ('2000-01-01'),('2100-01-01');

SHOW BINLOG EVENTS;

DROP TABLE t1;

This is a variation of the first example, but here INSERT will cause replication abort right away – on an old primary it will truncate the second value (with a warning), insert the truncated value into the partition, and write INSERT into the binlog. On a new replica, there will be no truncation, so it will attempt to insert 2100-01-01, and will fail because there is no partition for it.