MySQL Replication is a powerful tool and it’s hard to find a production system not using it. On the other hand debugging replication issues can be very hard and time consuming. Especially if your replication setup is not straightforward and you are using filtering of some kind.

Recently we got an alert from our monitoring system that replication stopped on production slave with the following error:

Can't find record in 'some_table', Error_code: 1032;
handler error HA_ERR_KEY_NOT_FOUND;
the event's master log binlog.000011, end_log_pos 735766642

This means that a ROW-based replication event was going to be applied on slave, but could not find the row it was supposed to be applied to. This is something I like about ROW format — it allows you to catch such data synchronization issues right away. In this particular case MIXED format was used, but if this event was written in STATEMENT format, slave would just apply it …

I’ll keep this short. Let’s say you run “mysql> show slave status\G” and you see the following error:

Last_Error: Error 'Unknown or incorrect time zone: 'Etc/UTC'' on query. Default database: 'db_name'. Query: 'INSERT INTO table SET column = CONVERT_TZ('2009-01-24', '-8:00', '+00:00')'

It’s a simple fix. Exit out of MySQL and run the following command - this works on Redhat, I’ve not tried it on other flavors.

shell> mysql_tzinfo_to_sql /usr/share/zoneinfo|mysql -u root -p

Log back into MySQL and run the following.

mysql> stop slave;
mysql> start slave;
mysql> show slave status\G;

The error should be gone and the slave should be catching up to the master now. Problem solved.