Bug #107293 CURRENT_TIMESTAMP not safe after all?
Submitted: 15 May 13:34 Modified: 21 May 20:52
Reporter: Przemyslaw Malkowski Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:5.7.37, 8.0.28 OS:Any
Assigned to: CPU Architecture:Any
Tags: current_timestamp, mixed

[15 May 13:34] Przemyslaw Malkowski
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?
[18 May 7:24] MySQL Verification Team
Hello Przemyslaw,

Thank you for the report. 

Thanks,
Umesh
[21 May 20:52] Przemyslaw Malkowski
Applies to MySQL 8.0 as well. Tested with 8.0.28.