Mysql 8.0 point in time recovery error - sequence number is inconsistent

MySQL & MariaDB Percona Server for MySQL 8.0
1

Using relay logs to speed up the process of point in time recovery I run into an error.
I’m testing this method to recover binlogs.

But for each relay binlog being recovered I will get one error:
ERROR] [MY-010411] [Repl] Transaction’s sequence number is inconsistent with that of a preceding one: sequence_number (1) <= previous sequence_number (xxxxxxx)
I thought it might be that slave_preserve_commit_order was not enabled, but even with it enabled I still encounter that error.
Note: binlog_order_commits set is not set to ON for the Master.
Thanks for the help.

2

Does this error always happen at the same place? (ie: always at the beginning of each log, or always at the end?) Have you dumped the verbose contents of the log and examined the sequence number manually to see if it really is out of order?

3

Hi Matthew,
I’ve dumped the verbose contents of bin-log.006410 but I’m not sure how to find the offset position in the binlog that it’s referring to? I assume the error is referring to the Read_Master_Log not the Relay_Log?
I think I take the Read_Master_Log_Pos from the replica status and calculate from there, but where is the sequence_number (1) coming from?

Here is my most recent test case error.
Transaction’s sequence number is inconsistent with that of a preceding one: sequence_number (1) <= previous sequence_number (1929667)

Here is information from replica status and master status.
Master_Log_File: bin-log.006410
Read_Master_Log_Pos: 708184140
Relay_Log_File: bin-log.000002
Relay_Log_Pos: 1073742162
Exec_Master_Log_Pos: 1073742162

Master status
File: bin-log.006410
Position: 708184140

The start of the master binlog has this position info.
at 4
#240326 10:46:13 server id 12345678 end_log_pos 126 CRC32 0x7e689634 Start: binlog v 4, server v 8.0.32-24 created 240326 10:46:13 at startup
Warning: this binlog is either in use or was not closed properly.
ROLLBACK/!/;
at 126

The end has this info.
at 708184051
#240323 21:34:38 server id 12345678 end_log_pos 708184140 CRC32 0x72090bb8 Query thread_id=2369892 exec_time=221900 error_code=0
SET TIMESTAMP=1711254878/!/;
COMMIT

Thanks!