Bug #102971 | slave-db losts original commit time of master_db | ||
---|---|---|---|
Submitted: | 15 Mar 2021 7:06 | Modified: | 20 Apr 2021 10:04 |
Reporter: | Brian Yue (OCA) | Email Updates: | |
Status: | Verified | Impact on me: | |
Category: | MySQL Server: Replication | Severity: | S3 (Non-critical) |
Version: | MySQL8.0.22, 8.0.23 | OS: | Any (rhel-7.4) |
Assigned to: | CPU Architecture: | Any (intel x86-64) |
[15 Mar 2021 7:06]
Brian Yue
[1 Apr 2021 7:34]
MySQL Verification Team
Hello Brian Yue, Thank you for the report and feedback. Are there time difference in source and replica instances? I tried to follow your steps but not seeing any difference as reported. Please let me know, lso could you please share configuration file from both source and replica(if not on default)? Thank you. regards, Umesh
[1 Apr 2021 14:02]
Brian Yue
Hello Umesh, Here is some related configurations of mysqld, I wish it's helpful: [general] instance_num=1 [mysqld] # generic configuration options sql_mode = STRICT_TRANS_TABLES sync_binlog=1 binlog_expire_logs_seconds=259200 open_files_limit = 65535 transaction-isolation = REPEATABLE-READ lower_case_table_names = 1 performance_schema = ON max_binlog_size = 10485760 binlog_format=ROW slave_parallel_workers = 32 slave-parallel-type = LOGICAL_CLOCK slave_preserve_commit_order = 1 relay_log_recovery = 0 mysqlx=0 default_authentication_plugin=mysql_native_password log-bin=../binlog/mysql-bin relay-log=../relaylog/relay-bin secure-file-priv= gtid_mode = on enforce_gtid_consistency=on default-time-zone='+8:00' log_timestamps=SYSTEM master-info-repository=TABLE relay-log-info-repository=TABLE character_set_server = utf8mb4 collation_server = utf8mb4_bin
[20 Apr 2021 10:04]
MySQL Verification Team
Observed this finally. Thank you! regards, Umesh
[6 Jun 2022 8:44]
Sven Sandberg
Posted by developer: If I understand right, the problem can be phrased as follows: - There are three kinds of timestamp: per-event time, per-transaction original_commit_timestamp, per-transaction immediate_commit_timestamp. - There is no observed problem with original_commit_timestamp or immediate_commit_timestamp. - The observation is that the per-event time for XID_event in a replica binary log is always equal to that of the previous event in the same binary log. - This differs from row events and query_log_events, since the per-event time for them is preserved from the source. - This is perceived as a problem, and the suggestion is to instead make it equal to the per-event time for the corresponding XID_event in the primary. The suggested change is reasonable, as it would make the timestamps more understandable. But it would be good to understand the damage from this defect, since: - AFAIU, it has always worked like the current behavior, so there is no regression. - The per-event timestamp is a bit obsolete, since it has very low precision (second) and does not work well with timezone differences. - For measuring lag it is recommended to use the per-transaction timestamps. In what way does the current behavior cause problems for the application?