MySQL -> Percona-5 binlogging problem?

From here but I thought I’d pick your brains also )

Hi all, I have what seems to be a binlogging problem with Percona-5:

It may be a peculiarity of my particular setup, but we have a system where in production we have a pair of MySQL machines (actually two sets, one 4.1.x and one 5.0.x, but lets make this simple).

We are installing two new machines, again in a master/slave relationship, but these are running Percona-5 (Percona-SQL-50-5.0.91-b22).

The replication chain is like this:

A->B
A->C
C->D

A = production mysql master
B = production mysql slave
C = new percona-5 master
D = new percona-5 slave

Replication between A and C seems to be working fine. The only problem is where D needs to receive an update from C.

C does not appear to be binlogging anything relevant (and it has the correct options in its my.cnf, log_bin, log_slave_updates, etc.).

If I insert something directly into a table on C that is replicated by D, I get the following error:
“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 are the contents of the relevant relay log on server D:
/!40019 SET @@session.max_insert_delayed_threads=0/;
/!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/;
DELIMITER /!/;

at 4

#100913 11:06:33 server id 77702 end_log_pos 98 Start: binlog v 4, server v 5.0.91-log created 100913 11:06:33

at 98

#700101 1:00:00 server id 77701 end_log_pos 0 Rotate to servername-bin.000001 pos: 4

at 147

#100913 10:58:15 server id 77701 end_log_pos 98 Start: binlog v 4, server v 5.0.91-log created 100913 10:58:15 at startup
ROLLBACK/!/;
ERROR: Error in Log_event::read_log_event(): ‘Found invalid event in binary log’, data_len: 88, event_type: 2
Could not read entry at offset 241:Error in log format or read error
DELIMITER ;

End of log file

ROLLBACK /* added by mysqlbinlog /;
/
!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

Here are the contents of the binlog on server C:
/!40019 SET @@session.max_insert_delayed_threads=0/;
/!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/;
DELIMITER /!/;

at 4

#100913 10:58:15 server id 77701 end_log_pos 98 Start: binlog v 4, server v 5.0.91-log created 100913 10:58:15 at startup

Warning: this binlog is either in use or was not closed properly.

ROLLBACK/!/;
ERROR: Error in Log_event::read_log_event(): ‘Found invalid event in binary log’, data_len: 88, event_type: 2
DELIMITER ;

End of log file

ROLLBACK /* added by mysqlbinlog /;
/
!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

Bear in mind that the server C is a slave to A and is receiving updates fine (writes to the relay log, applies them), but for some reason the then writing these out to the binlog isn’t working.

Don’t think its anything to do with the MySQL versions on servers A and B, as we have this setup twice with both versions 4.1.x and 5.0.x of MySQL with the same happening on the Percona side.

Any advice would be helpful, as we’re stumped and don’t want to have to go back to using vanilla MySQL on this particular system.

Cheers,
Mike.

Has the server crashed or MySQL on server C been shut down inappropriately?

Have you tried to just remove the binlog file and let MySQL recreate it? Since it looks like it has been corrupted for some reason.

Hi,

MySQL has been shut down cleanly. I have gone as far as to completely remove the master aspect (i.e. remove log-bin and log-slave-updates), delete all binlogs & index, restart the server so that show master status is blank, take the server down, put the master directives back in and bring the server back up again.

It’s writing to its relaylog fine as its replicating successfully from a master, but it won’t then take those successfully applied updates and write them out to a binlog properly.

Mike

If you don’t have the log_slave_updates enabled and issue queries to the server, are these queries placed in the binlog properly?

Just to see if it’s a generic bin-log problem or if it’s only related to log_slave_updates.

This really looks to me like a hardware problem on C or D.