Silent corruption in mixed-mode replication of TIMESTAMP when @@system_time_zone differs between slave and master

The problem occurs when @@time_zone is set to SYSTEM but @@system_time_zone differs between master and slave:

TZ=Asia/Tokyo sql/mysqld --server-id=1 ...

TZ=CET sql/mysqld --server-id=2 ...

Query_log_event includes the @@time_zone=SYSTEM value, but since the system time zone differs on the slave, time values in the query are still converted incorrectly to internal UTC representation of TIMESTAMP during replication:

# Master:

MariaDB [test]> CREATE TABLE t1 (a INT PRIMARY KEY, b TIMESTAMP) ENGINE=InnoDB;

MariaDB [test]> set binlog_format=mixed;

MariaDB [test]> INSERT INTO t1 VALUES (10, '2024-03-31 03:00:02.0');

MariaDB [test]> SET STATEMENT time_zone='+00:00' for INSERT INTO t1 VALUES (11,

MariaDB [test]> set binlog_format=row;

MariaDB [test]> INSERT INTO t1 VALUES (12, '2024-03-31 03:00:02.0');

MariaDB [test]> SET STATEMENT time_zone='+00:00' for INSERT INTO t1 VALUES (13,

MariaDB [test]> SET STATEMENT time_zone='+00:00' FOR select * from t1;

+----+---------------------+

| a  | b                   |

+----+---------------------+

| 10 | 2024-03-30 18:00:02 |

| 11 | 2024-03-31 03:00:02 |

| 12 | 2024-03-30 18:00:02 |

| 13 | 2024-03-31 03:00:02 |

+----+---------------------+

# Slave:

MariaDB [test]> SET STATEMENT time_zone='+00:00' FOR select * from t1;

+----+---------------------+

| a  | b                   |

+----+---------------------+

| 10 | 2024-03-31 01:00:02 |

| 11 | 2024-03-31 03:00:02 |

| 12 | 2024-03-30 18:00:02 |

| 13 | 2024-03-31 03:00:02 |

+----+---------------------+

The inserted TIMESTAMP value is incorrect when using MIXED (or STATEMENT) binlogging and default time_zone=SYSTEM. It is replicated correctly when using ROW mode or explicit (non-SYSTEM) time_zone.

This behaviour is quite bad, as it silently corrupts data in replication. Note that MIXED mode and time_zone=SYSTEM are default settings.

It is also possible to get an error from this when a date from one time-zone is invalid in the other time zone (eg. daylight saving):

[ERROR] Slave SQL: Error 'Incorrect datetime value: '2024-03-31 02:00:02.0' for column `test`.`t1`.`b` at row 1' on query. Default database: 'test'. Query: 'INSERT INTO t1 VALUES (3, '2024-03-31 02:00:02.0')', Gtid 0-1-2, Internal MariaDB error code: 1292

One possible fix would be that when time_zone=SYSTEM, the master would also include the value of @@system_time_zone in the statement-based event. And the slave would then use this value instead of the normal global system time zone in effect on the slave. (The binlogging on the slave would then need to recursively binlog the master's original @@system_time_zone if --log-slave-updates).

A simpler fix might be to mark as unsafe for statement-based binlogging those queries that do a conversion of a litteral time/date value into a TIMESTAMP value during query parsing. Assuming such conversion can be detected easily in the binlogging code.

Alternatively, if it is a strict requirement for replication to work that system time zones are configured correctly, the server should check that the system time zone is set identical on master and slave, and give an error or at least a warning if not.

Work-around is to set the system time zone identical on all servers in a replication setup.