0

I am currently running 2 instances of 8.0.31 MySQL Community Server on two virtual machines. These are set up with one way replication. The replication runs fine once set up, but at some point it fails with the following error:

Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000020, end_log_pos 767417434. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

looking into the logs I notice that this is the underlying error:

2022-11-15T18:03:55.351308Z 72 [ERROR] [MY-010584] [Repl] Slave SQL for channel '': Worker 1 failed executing transaction 'ANONYMOUS' at master log mysql-bin.000020, end_log_pos 767417434; Could not execute Delete_rows event on table rapreport.events; Can't find record in 'events', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000020, end_log_pos 767417434, Error_code: MY-001032

And the timestamp seems to be right about where the mysql-bin.000020 became mysql-bin.000021.

I understand that the mysql-bin log file is capped in content and once it reaches that limit, it closes the current n version and opens n+1.

My replication was set up with the following command:

CHANGE REPLICATION SOURCE TO
SOURCE_HOST='master_ip',
SOURCE_USER='replica_user',
SOURCE_PASSWORD='master_password',
SOURCE_LOG_FILE='mysql-bin.000020',
SOURCE_LOG_POS=99999;

START REPLICA;

the values are obviously bogus.

So, my questions are as follows: how do I set up my replica database to not lose sync when the source database increments it's log file?

Am I even correct in my associating the log incrementation with my replication failure?

This database is fairly simple, although it gets a lot of transactions. there is one table 'events' that matters. 6 hosts are running an application that send inserts into the DB. Twice daily, a reporting server makes a select from the DB, only retrieving from the events table. Once a week the DB is truncated. Rinse and repeat.

3
  • Initial setup should not set the POS; restarting replication normally should not set the FILE or POS.
    – Rick James
    Commented Nov 15, 2022 at 21:54
  • @RickJames So I should modify my change replication source command to remove the POS and the FILE? thank you. I will try try this
    – M.Barbara
    Commented Nov 16, 2022 at 7:04
  • 1
    When you are initializing replication, it is not needed. When replication resumes after an outage, it know where it left off. Using FILE and POS implies that "you know better", but could skip or duplicate queries!
    – Rick James
    Commented Nov 16, 2022 at 20:57

2 Answers 2

0

Basically, your data is not consistent between primary and replica; and this could be hard to track down.

See the error you shared:

Could not execute Delete_rows event on table rapreport.events; Can't find record in 'events', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin.000020, end_log_pos 767417434, Error_code: MY-001032

This means that your replication tried to replicate a DELETE that happened in your primary node, but that record doesn't exist on the replica.

If you still have the data as is, you could check the DELETE statement using something like this:

mysqlbinlog --read-from-remote-server -h <master_ip> mysql-bin.000020 --verbose --base64-output=DECODE-ROWS|grep -A10 -B10 767417434

mysql-bin.000020 from your error.

The number 767417434 in the grep, is the end_log_pos from the error as well.

You can play around with the "-A -B" options in the grep to check further (As is, that's 10 lines After and 10 lines Before the line where the text was found).

Once you have the DELETE statement, you could check with a SELECT in your replica and you'll see the record doesn't exist, that's why it can't delete something that's not there, hence the error.

That's what you should fix; probably the best way should be reseed your replica and ensure you configure the replication properly.

As for you question, on a normal replication scenario, you don't need to manually modify your replication settings, the replica will move from one binlog to the next whenever it's required, without manual intervention.

As final note, I would recommend using GTIDs (if not already), with this is easier to track your replication status and issues.

1
  • Thank you for your comment. This is brilliant. You are a gentleman and a scholar!
    – M.Barbara
    Commented Nov 17, 2022 at 7:49
0

I encountered a similar issue and attempted to resolve it using two different methods.

ERROR:

Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 1 failed executing transaction '92f5b467-0b7f-11ee-8ab4-00505601024f:4' at master log binlog.000001, end_log_pos 69389. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

Solution 1:

mysql> stop slave;
mysql> set sql_log_bin = off;
mysql> set gtid_next='bebd7b80-9904-11ec-99e9-525400adb547:116979494';
mysql> begin;commit;
mysql> set gtid_next='automatic';
mysql> set sql_log_bin = on;
mysql> start slave;

Solution 2:

Add this parameter slave-skip-errors=1032 in /etc/my.cnf.d/mysql-server.cnf

[root@db1-dr my.cnf.d]# cat mysql-server.cnf
[mysqld]
bind-address=192.168.0.10
server_id = 2
datadir=/mysql_db/data
socket=/var/lib/mysql/mysql.sock
log-error=/var/log/mysql/mysqld.log
pid-file=/run/mysqld/mysqld.pid
slave-skip-errors=1032,1062

[root@db1-dr my.cnf.d]# systemctl stop mysqld
[root@db1-dr my.cnf.d]# systemctl start mysqld

mysql>  show slave status\G
*************************** 1. row ***************************
         
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

Hope it helped !!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.