1

We run a RDS MySQL master-slave replication setup and quite regularly add another replica (replicating from the slave) for maintenance, testing, analytics, etc.

However, I currently cannot create a new instance that replicates from the slave. When it starts replicating, error 1032 occurs:

Could not execute Update_rows event on table x.y; Can't find record in 'y', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event's master log mysql-bin-changelog.083465, end_log_pos 260523

When I create a new instance that replicates from the master, everything works as usual.

During recent maintenance we created a new table y_temp, filled it with a subset of rows from y, and then renamed y to y_old and y_temp to y. It seems very likely that this has caused the state we're seeing, but I don't understand how. During the maintenance, slave was replicating from master as usual and it now has the same rows in y as master.

I have not been able to find any reports that match our situation. I would prefer to have new instances replicate from slave instead of master. Should we recreate the slave instance?

This is RDS MySQL 5.6.35 and I use the RDS console to add the read replica.

  • Questions : Q1) When you made y_temp, did you use CREATE TABLE or CREATE TEMPORARY TABLE ? Q2) Did you use RENAME TABLE y TO y_old; or ALTER TABLE y RENAME y_old; ? Q3) Did you execute the rename command as one command (RENAME TABLE y TO y_old,y_temp TO y;) or as two commands (RENAME TABLE y_old TO y_old; RENAME TABLE y_temp TO y;) ? – RolandoMySQLDBA Aug 2 '17 at 0:13
  • Possible workaround: create a manual snapshot of the slave. After that is finished, wait about 10 minutes, then create a new replica from the slave. You won't be using that snapshot for anything, so you can delete it later, but it's a way (theoretically) to convince RDS to create the new replica from a later baseline. Would the table in question happen to be a MyISAM table? – Michael - sqlbot Aug 2 '17 at 0:16
  • @RolandoMySQLDBA It was a regular CREATE TABLE. We used RENAME TABLE y TO y_old; and then RENAME TABLE y_temp TO y;. No other operations were sent to the database during that time (or should have been), application was in maintenance mode. – Martijn Vermaat Aug 2 '17 at 7:29
  • @Michael-sqlbot I also thought of that later yesterday and I'll try that. The table is InnoDB. – Martijn Vermaat Aug 2 '17 at 7:30
  • Creating a manual snapshot of the slave and trying again gave the same error. – Martijn Vermaat Aug 2 '17 at 10:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.