MySQL 5.7 error reporting from database 1032 [How to Solve]

1. Introduction:

During MySQL 5.7 multi-source replication and master-slave synchronization, the master database updates a piece of data, and the slave database has been deleted, resulting in the master-slave disconnection

Last_Errno: 1032

The 1032 error of MySQL master-slave synchronization generally means that the data to be changed does not exist and SQL_The log extracted by thread cannot be applied, so an error is reported, resulting in synchronization failure

(update, delete, insert a piece of deleted data). The 1032 error itself has no impact on data consistency, and the biggest impact is the synchronization failure

2. Solution:

MySQL version 5.7.15-log, binlog mode is row

Show slave status \ g, you can see the following error reports:

 1  Last_Errno: 1032
 2                    Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 3 failed executing transaction '1699
 3 7e5e-5707-11e7-a702-94188201787c:161982297' at master log mysql-bin.000309, end_log_pos 774308000. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.                 Skip_Counter: 0
 4           Exec_Master_Log_Pos: 774271552
 5               Relay_Log_Space: 1266940262
 6               Until_Condition: None
 7                Until_Log_File: 
 8                 Until_Log_Pos: 0
 9            Master_SSL_Allowed: No
10            Master_SSL_CA_File: 
11            Master_SSL_CA_Path: 
12               Master_SSL_Cert: 
13             Master_SSL_Cipher: 
14                Master_SSL_Key: 
15         Seconds_Behind_Master: NULL
16 Master_SSL_Verify_Server_Cert: No
17                 Last_IO_Errno: 0
18                 Last_IO_Error: 
19                Last_SQL_Errno: 1032
20                Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 3 failed executing transaction '1699
21 7e5e-5707-11e7-a702-94188201787c:161982297' at master log mysql-bin.000309, end_log_pos 774308000. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

 

See error log:

vim error.log

2018-10-26T01:55:00.133136+08:00 16179 [ERROR] Slave SQL for channel 'master_6': Worker 3 failed executing transaction '16997e5e-5707-11e7-a702-94188201787c:161982297' at 
master log mysql-bin.000309, end_log_pos 774308000; Could not execute Update_rows event on table test.test_log; Can't find record in 'test_log', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.000309, end_log_pos 774308000, Error_code: 1032

Method 1: skip errors

1) Skip this error first and let the master-slave synchronization return to normal（ Or n events (skip one by one)

set global sql_slave_skip_counter=1;

start slave sql_thread for channel ‘master_6’;

After the slave is up, recover the data and re insert it from the database

2）The most recent failure being: Worker 3 failed executing transaction ‘169921 7e5e-5707-11e7-a702-94188201787c:161982297′

In gtid mode, you can also:

set gtid_next=’169921 7e5e-5707-11e7-a702-94188201787c:161982297′;

begin;

commit;

set gtid_next=’automatic’;

stop slave   sql_thread for channel ‘master_6’;

 

start slave sql_thread for channel ‘master_6’;

Method 2: restore the deleted data

According to the error prompt, find the SQL of this data through mysqlbinglog. The location of this event in the master binlog of the master server is mysql-bin.000309, end_log_pos77430800

 1 [root@xxxxx binlog]# mysqlbinlog  --no-defaults --stop-position=774308000 mysql-bin.000309 -vv |grep -A 20 774308000
 2 #181026  1:55:00 server id 3663306  end_log_pos 774308000 CRC32 0x2969563a     Update_rows: table id 52745 flags: STMT_END_F
 3 
 4 BINLOG '
 5 dAPSWxPK5TcAWgAAAIr+Ji4AAAnOAAAAAAEAB21vbml0b3IAEWpvYl9leGVjdXRpb25fbG9nAAsP
 6 Dw8PDwMPDwMRERCgAJAB/AP8A8gAUACAPgAAgAbDz1ww
 7 dAPSWx/K5TcAFgIAAKAAJy4AAAnOAAAAAAEAAgAL/////4D8JDZlOGJjNGZlLTlmYzktNDllYS1h
 8 NjliLWE4YmNjMWZjOGJlZiQAb21zVXBkYXRlV2FyZWhvdXNlT3JkZXJUYXNrU3RhdHVzSm9iZgBv
 9 bXNVcGRhdGVXYXJlaG91c2VPcmRlclRhc2tTdGF0dXNKb2JALUAwQC1AUkVBRFlALUAxMC4xNTcu
10 MzIuMzlALUA2NTYyYzQ5YS0zMjQyLTQyNWQtODdhZC1lZTA4N2M0NmYzN2UXAGRwcHZkb2NzZXIx
11 OC5zZXBob3JhLmNuDDEwLjE1Ny4zMi4zOQAAAAAOTk9STUFMX1RSSUdHRVIAAAAAW9IDdID4JDZl
12 OGJjNGZlLTlmYzktNDllYS1hNjliLWE4YmNjMWZjOGJlZiQAb21zVXBkYXRlV2FyZWhvdXNlT3Jk
13 ZXJUYXNrU3RhdHVzSm9iZgBvbXNVcGRhdGVXYXJlaG91c2VPcmRlclRhc2tTdGF0dXNKb2JALUAw
14 QC1AUkVBRFlALUAxMC4xNTcuMzIuMzlALUA2NTYyYzQ5YS0zMjQyLTQyNWQtODdhZC1lZTA4N2M0
15 NmYzN2UXAGRwcHZkb2NzZXIxOC5zZXBob3JhLmNuDDEwLjE1Ny4zMi4zOQAAAAAOTk9STUFMX1RS
16 SUdHRVIBAAAAW9IDdFvSA3Q6Vmkp
17 '/*!*/;
18 ### UPDATE `test`.`test_log`
19 ### WHERE
20 ###   @1='6e8bc4fe-9fc9-49ea-a69b-a8bcc1fc8bef' /* VARSTRING(160) meta=160 nullable=0 is_null=0 */
21 ###   @2='omsUpdateWarehouseOrderTaskStatusJob' /* VARSTRING(400) meta=400 nullable=0 is_null=0 */
22 ###   @3='omsUpdateWarehouseOrderTaskStatusJob@-@0@-@READY@-@1@-@6562c49a-3242-425d-87ad-ee087c46f37e' /* VARSTRING(1020) meta=1020 nullable=0 is_null=0 */

 

The statement for the transaction is: UPDATE `test`. `test_log` where @1=”, @2=” (@1 is the first field, @2 is the second field)

To be missing this data from the library, change the update in the above binglog to insert

Do not log binlog.

set sql_log_bin=0;

insert  into `test`.`test_log`   values() ;

set sql_log_bin=1;

start slave sql_thread for channel ‘master_6’;

Similar Posts: