Call us Toll-Free:
1-800-218-1525
Email us

 Sponsors

How to recover from MySQL replication Event too small corruption

Dawn Rossi, 09-04-2009
If you're using MySQL InnoDB tables with these two lines in your /etc/my.cnf, for the most part, your database should gracefully handle unexpected shutdowns without data corruption:

innodb_flush_log_at_trx_commit=1
sync_binlog=1

I was working on such a database today, that experienced a power outage.

While all InnoDB data remained intact (I verified the checksum to ensure database is in sync), replication relay logs got corrupted.

Trying to SLAVE START failed with this nasty error message:

Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

Here's how to recover from such an error -

Step 1: Identify the corrupted file

Issue a SHOW SLAVE STATUS followed by a SHOW MASTER STATUS and record the relay log filenames and positions.

In my case those were: mysql-bin.000313 position 1037717439 and mysql-bin.000249 position 326501604

Running mysqlbinlog to read the first block in each of these files, starting at the last position, immediately reveals the problematic file:

/usr/local/mysql/bin/mysqlbinlog -Hvv mysql-bin.000313 --start-position=1037717439 | more

ERROR: Error in Log_event::read_log_event(): 'Event too small', data_len: 0, event_type: 0
ERROR: Could not read entry at offset 326501604: Error in log format or read error.
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;

Notice the ERROR on the first line. This tells us we've found the corrupted replication file

Step 2 - Skip the bad block

This involves some trial and error, but what we want to do is use mysqlbinlog to manually parse MySQL replication log file to the end, bypassing the 'Event too small' bad block.

Once done, we have to update relay-log.info, so that MySQL doesn't try to process this file again.

Replaced this:

cat relay-log.info

./mysql-bin.000313
1037717439
mysql-bin.000249
113612

With:

./mysql-bin.000314
0
mysql-bin.000249
113612

(Telling MySQL to advance to the next file mysql-bin.000314)

Step 3 - Restart database

While this may be a bit of an overkill, I prefer restarting the entire database, before issuing a SLAVE START

mysqladmin shutdown
mysqld_safe &

anilroyal, 09-16-2010
There's another way of resolving this issue. In most cases, this happens when the relay log gets corrupt during network transmission. So all you need to do is the following:

1. STOP SLAVE
2. SHOW SLAVE STATUS
3. Note down 'Relay_Master_Log_File' and 'Exec_Master_Log_Pos' entries.
4. RESET SLAVE
5. CHANGE MASTER TO ..... (use MASTER_LOG_FILE=relay_master_log_file and MASTER_LOG_POS=exec_master_log_pos from Step 3)
6. START SLAVE

This basically removes all relay logs from the slave (including the one that got corrupt), and starts replicating from exactly where it stopped by requesting a fresh binlog from the master.

Voila!

pdwalker, 01-26-2011
anilroyal,

That did the trick.

Niels, 02-28-2012
@anilroyal: thank you so much! Worked like a charm!

srm80, 12-21-2012
Thank you anilroyal! That really helped!

d9k, 02-10-2017
@anilroyal: I had Could not execute Write_rows_v1 event on table ....; Duplicate entry .... for key 'PRIMARY'
Enjoyed this post?

Subscribe Now to receive new posts via Email as soon as they come out.

 Comments
Post your comments












Note: No link spamming! If your message contains link/s, it will NOT be published on the site before manually approved by one of our moderators.



About Us  |  Contact us  |  Privacy Policy  |  Terms & Conditions