Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We are using MySQL DB with one master and one slave. We are using this setup for months now. The slave sync stopped today and the error we got was:

Last_IO_Errno: 1236
            Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master'
           Last_SQL_Errno: 1594
           Last_SQL_Error: 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.

We tried restarting slave's mysql service and start and stop the slave replication with no help. After further investigation we found that there was an issue with particularly big size of query. In the error log of the master server we got error log like below:

[ERROR] Error in Log_event::read_log_event(): 'Event too big', data_len: 1936941420, event_type: 109

We have identified the query that caused this issue. We can ignore the update from that query. This error is logged constantly in the mysql error log.

The issue is, the master is not able to read the from the relay log after that perticular log position. We just want to remove that particular relay log number from the binary log of the master server. How can we remove a particular relay log entry from the binary log file? And yes, we have a critical situation with the application due to this.:(

share|improve this question

2 Answers 2

up vote 3 down vote accepted

A possible solution that comes to mind is to set the master_log_position to the next one:

change master to master_log_position = <next_pos>;
start slave;

Or just use sql_slave_skip_counter = 1. If that doesn't work, you may try to parse the binlog (if mysqlbinlog is able to parse it ...):

mysqlbinlog filename > script.sql

Edit the script and remove the part up to and including the long statement. Run the modified script manually on the slave and set the master_logfile to the next binlog:

change master to master_log_file ...;
start slave;

You may also check, just in case, if both master and slave use the same *max_allowed_packet*.

share|improve this answer
    
Thanks Dimitre! You are life saver. Used the binlog parse loginc. –  Ruchit Rami Jan 8 at 17:16

Quite some time ago we had an corrupted binary log on the master due to some severe issues with the SAN, which did things that a SAN really really really should not do like writing blocks of zeros instead of the actual data, which we learned afterwards.

My solution was to skip the broken blocks / log entries by setting the master_log_position on the slaves to the next valid statement in the master bin log and hope that the skipped / missing statements did not do break anything on the logical level aka slave complaining about duplicate key and such things afterwards.

Problem is to find the next valid position within the binary log in this case.

So I took a look into the developer documentation of mysql to know the internal structure of the binary log and looked into my broken master binary log with a hex editor. The position of the last valid entry can be seen on the slave, start from there.

As the binary log contained the recorded sql statements in almost text form so I was able the identify the position of next INSERT / UPDATE / something statements. Subtract the size of the binary log header fields before the actual statement (see developer documentation), set the master_log_position to that value and the restart the slave. If you hit a valid position it will continue, if not it will complain. You can use mysqlbinlog to dump the statement at that position as well.

This is nothing for the faint of heart and I can not give you a more detailed step by step instruction.

If your binary log is really corrupted (and to me it looks like this, date_len is pulled from a field in the bin log and this is not a valid value, that's why the slave is complaining) the by the book solution is to rebuild the slave by restoring a fresh backup from the master into the slave (etc., like setting up a new slave) as you can not tell afterwards if the data is really in sync with your master anymore because you skipped some statements.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.