Mysql slave not synching with master

Got this case from "Duke University Health System, Inc"

Sysmptom/Issue

Last_Error: Could not execute Update_rows event on table redcap.redcap_data; Can't find record in 'redcap_data', Error_code: 1032; Can't find record in 'redcap_data', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.001256, end_log_pos 532444802

*************************** 1. row ***************************
              Slave_IO_State: Waiting for master to send event
                 Master_Host: vlp-rcproddb01.dhe.duke.edu
                 Master_User: repl
                 Master_Port: 3306
               Connect_Retry: 60
             Master_Log_File: mysql-bin.001375
         Read_Master_Log_Pos: 961367292
              Relay_Log_File: vls-rcstandby01-relay-bin.000475
               Relay_Log_Pos: 532444658
       Relay_Master_Log_File: mysql-bin.001256
            Slave_IO_Running: Yes
           Slave_SQL_Running: No
             Replicate_Do_DB:
         Replicate_Ignore_DB:
          Replicate_Do_Table:
      Replicate_Ignore_Table:
     Replicate_Wild_Do_Table:
 Replicate_Wild_Ignore_Table:
                  Last_Errno: 1032
                  Last_Error: Could not execute Update_rows event on table redcap.redcap_data; Can't find record in 'redcap_data', Error_code: 1032; Can't find record in 'redcap_data', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.001256, end_log_pos 532444802
                Skip_Counter: 0
         Exec_Master_Log_Pos: 532444443
             Relay_Log_Space: 126669963642
             Until_Condition: None
              Until_Log_File:
               Until_Log_Pos: 0
          Master_SSL_Allowed: No
          Master_SSL_CA_File:
          Master_SSL_CA_Path:
             Master_SSL_Cert:
           Master_SSL_Cipher:
              Master_SSL_Key:
       Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
               Last_IO_Errno: 0
               Last_IO_Error:
              Last_SQL_Errno: 1032

Analysis:

Looking at the error message:

                  Last_Error: Could not execute Update_rows event on table redcap.redcap_data; Can't find record in 'redcap_data', Error_code: 1032; Can't find record in 'redcap_data', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin.001256, end_log_pos 532444802

This usually indicates a difference in the data between the source and the replica. There are two common causes for this to happen:

1. There are no primary keys on the table and the data is "lost" in comparison. You can check if there are any tables missing primary keys using the query:

   mysql> SELECT t.TABLE_SCHEMA, t.TABLE_NAME, t.ENGINE
   FROM information_schema.tables AS t
   LEFT JOIN information_schema.TABLE_CONSTRAINTS AS tc
   ON tc.TABLE_SCHEMA = t.TABLE_SCHEMA
   AND tc.TABLE_NAME = t.TABLE_NAME
   AND tc.CONSTRAINT_TYPE = 'PRIMARY KEY'
   WHERE t.TABLE_TYPE = 'BASE TABLE'
   AND tc.CONSTRAINT_TYPE IS NULL
   AND t.TABLE_SCHEMA NOT IN ('mysql','information_schema', 'performance_schema');

2. The replica instance is being updated manually by someone other than the replication user internally via the replication threads.

Otherwise there is the possibility of a bug, but these are the first points to check.

Can I get you to follow these steps please:

1. Look at the `Relay_Source_Log_File` and `Relay_Source_Log_File` in SHOW SLAVE STATUS output.

Example:

       Relay_Source_Log_File: mysql-bin.000066
         Exec_Source_Log_Pos: 727047063

2. Get the `mysqlbinlog` output for this file and position:

Example:

SHELL>  mysqlbinlog -vv /var/lib/mysql/mysql-bin.000066 --start-position=727047063

### DELETE FROM <DB>.<TABLE1>
### WHERE
###   @1=57174 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @2=525237253 /* LONGINT meta=0 nullable=0 is_null=0 */
###   @3='16912348241343403438768' /* VARSTRING(150) meta=150 nullable=0 is_null=0 */
# at 727047278
#120804  5:30:37 server id 112225  end_log_pos 727047305        Xid = 2026251694

Once you have the above details, you can query the slave database directly for the information required and see if there is any discrepancy.

Result & Solution：

t is highly recommended that you do fix the issue on the master and then create the backup image to restore to the slave.
This will allow for identical data stores for each instance and avoid future missing key errors like you are getting now.