I am running MySQL on AWS RDS.. Slave replication is getting stuck because of following error. It says that it is not able to find a record in table of slave server... Is there a way to know which record ? Or the SQL which is failing !
Read Replica Replication Error - SQLError: 1032, reason: Could not execute Update_rows_v1 event on table customers.visitor; Can't find record in 'visitor', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log mysql-bin-changelog.206572, end_log_pos 12627388
Use mysqlbinlog to read the log.
mysqlbinlog --verbose --base64-output=decode-rows mysql-bin-changelog.206572 \
| awk '/end_log_pos/{flag=0}/end_log_pos\ 12627388\ /{flag=1}flag'
From the error message, this looks like an event that was logged in row-format, so mysqlbinlog will decode it as pseudo-sql, showing each column with its ordinal position, something like this:
### UPDATE `my_db_name`.`my_table_name`
### WHERE
### @1=70
### @2=1476602910
### @3=NULL
### @4=NULL
### @5=NULL
### @6=NULL
### @7=NULL
### SET
### @1=70
### @2=1476602910
### @3=1476602926
### @4=000000016.283000000
### @5=NULL
### @6='0'
### @7=NULL
The @1, @2, etc., in the output is column 1, column 2, etc. The binlog does not contain the actual names of the columns, since they are assumed to be the same on master and slave and they are therefore redundant... so mysqlbinlog creates this pseudo-sql to illustrate how it is interpreting the logged change event. The WHERE is the version of the row as it appeared on the master before the update (this is going to be the row that is not found on your slave), and SET is the value of the row after the update on the master (what the slave should have changed the row to look like, if it had existed).
This doesn't identify your original query, but it will show you the actual row that was present on the master but is missing on the slave.
If you don't already have the log, you can fetch it from the master, first, with this:
mysqlbinlog --host=xxxx.rds.aws-regi-on.amazonaws.com --user 'rds-master-user' \
--password='rds-master-password' --read-from-remote-server \
--raw mysql-bin-changelog.206572
The log has to be fetched from the master. It can't be fetched from the slave in this case. (If this weren't RDS, you could connect to the slave by logging in to a shell over SSH and read the relay log directly, but those are not accessible externally, which is why you have to go to the master for the log, in RDS.)
Unless you've already configured RDS to retain your binlogs longer than they are technically needed, the log may no longer be available on the master, so you may be somewhat out of luck until next time. You can configure binlog retention on RDS with a custom procedure included on all RDS instances for MySQL 5.6 and higher. To set the log retention to 24 hours, for example:
CALL mysql.rds_set_configuration('binlog retention hours', 24);
See also http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_LogAccess.Concepts.MySQL.html
Credit to the source of the inspiration for using awk to find lines between two patterns.
Note that after the command above matches and prints out the event where the error is occurring, it may appear to hang, since awk continues scanning to the end of the file. Control-C to terminate.