Description:
The documentation https://dev.mysql.com/doc/refman/8.0/en/replication-rbr-safe-unsafe.html mentions some of the nondeterministic functions as safe, including the CURRENT_TIMESTAMP. Hence, when MIXED binlog_format is used, statements using this function are not logged using RBR format.
I have a scenario where this does cause data inconsistency between source and replica.
How to repeat:
source: root@localhost (db2) > select @@system_time_zone,@@time_zone,@@binlog_format;
+--------------------+-------------+-----------------+
| @@system_time_zone | @@time_zone | @@binlog_format |
+--------------------+-------------+-----------------+
| BST | SYSTEM | MIXED |
+--------------------+-------------+-----------------+
1 row in set (0.00 sec)
CREATE TABLE `test1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`d` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
`a` varchar(30) NOT NULL,
`name` varchar(25) DEFAULT NULL,
PRIMARY KEY (`a`),
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB;
insert into test1 values (null,now(),"test1",0);
insert into test1 values (null,now(),"test2",0);
insert into test1 values (null,now(),"test3",0);
select d from test1 where id=1 into @d;
UPDATE test1 SET name = 'foobar', d = CURRENT_TIMESTAMP WHERE a = 'test1' AND d = @d;
source: root@localhost (db2) > select * from test1;
+----+---------------------+-------+--------+
| id | d | a | name |
+----+---------------------+-------+--------+
| 1 | 2022-05-15 14:20:30 | test1 | foobar |
| 2 | 2022-05-15 14:18:00 | test2 | 0 |
| 3 | 2022-05-15 14:18:01 | test3 | 0 |
+----+---------------------+-------+--------+
3 rows in set (0.00 sec)
replica: root@localhost (db2) > select @@system_time_zone,@@time_zone;
+--------------------+-------------+
| @@system_time_zone | @@time_zone |
+--------------------+-------------+
| UTC | SYSTEM |
+--------------------+-------------+
1 row in set (0.00 sec)
replica: root@localhost (db2) > select * from test1;
+----+---------------------+-------+------+
| id | d | a | name |
+----+---------------------+-------+------+
| 1 | 2022-05-15 13:18:00 | test1 | 0 |
| 2 | 2022-05-15 13:18:00 | test2 | 0 |
| 3 | 2022-05-15 13:18:01 | test3 | 0 |
+----+---------------------+-------+------+
3 rows in set (0.00 sec)
Suggested fix:
As seen above, the replica did not update the corresponding row. This should not happen.
When the time_zone offset only is different, no similar issue is happening, but when the OS runs with a different TZ, the replica gets inconsistent.
Any reason for not switching to RBR when these nondeterministic functions are used in MIXED mode?
