MySQL Bugs: #28461: Got fatal error 1236: 'bogus data in log event' from master

Bug #28461	Got fatal error 1236: 'bogus data in log event' from master
Submitted:	16 May 2007 5:04	Modified:	12 Apr 2008 17:18
Reporter:	Rustam Valiev	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0.22	OS:	FreeBSD (6.1)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	bogus, master, replication, shutdown

Description:
Hi. I don't speak English very well, so sorry for mistakes.
So. I have replication from Master 4.1.27 to Slave 5.0.22
InnoDB and MyISAM engines.
All work good, but some times master suddenly restart (elctrical problem).
After this slave can't continue replication.
In log file written:
----------------------------------------------------
070515 15:07:45 [ERROR] Error reading packet from server: bogus data in log event ( server_errno=1236)
070515 15:07:45 [ERROR] Got fatal error 1236: 'bogus data in log event' from master when reading data from binary log
070515 15:07:45 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000047', position 814960501
----------------------------------------------------

In mysql-bin.000047 at this  position written 
----------------------------------------------------
....a02b03a1.UPDATE `cash_refpage` SET `count`= '167'  where `pid`='579' and `data`='20070515' and type
='170'.......................................................................................................................
.............................................................................................................................
.............................................................................................................................
.............................................................................................................................
.............................................................................................................................
.............................................................................................................................
.............................................................................................................................
.............................................................................................................................
.............................................................................................................................
.............................................................................................................................
.............................................................................................................................
.............................................................................................................................
.............................................................................................................................
........................................................................................
----------------------------------------------------------

How to repeat:
It's can repeat when master suddenly restarting (I mean master's OS, not only MySQL server)

Suggested fix:
So, may be you can create some utilite, for fix errors in binlog files?

There is nothing MySQL can do about this, IMHO. If the master
crashes while writing the binlog (as you are experiencing power outages),
i am not sure if it is safe to assume that the last statement in
the binlog has *not* yet been executed on the master (which is
what mysqlbinlog implicitly assumes when you feed it an incomplete
binlog - it appends "ROLLBACK").

So, IMHO, it's up to you to decide whether you want the slave to
get the statement the replication broke at. Here's what to do:

* view the binlog on the master

  mysqlbinlog <LOG>

* find the statement:

# at <POSITION>

* Either execute that statement on the slave or not, depending
  on whether you believe it was executed on the master or not

* Get the next binlog and reset your slave to it:

  mysqlbinlog <NEXT LOG>

  Get the first statement pos (usually at 4)

  on the slave:

  STOP SLAVE;RESET SLAVE;
  CHANGE MASTER TO ... Master_Log_File="<NEXT LOG>",Master_Log_pos=<POS>

Or, to be absolutely sure that the slave has consitent data,
set it up again.

Please, try to set sync_binlog=1 and check if this problem is repeatable after that. Read http://dev.mysql.com/doc/refman/5.0/en/replication-features-mastercrash.html for the details.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Rustam,

did the hints that Valeriy gave here solve your problem? Can we close this bug?

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".