MySQL replication - rapidly growing relay bin logs

Question

Morning all,

I've got a really strange situation here this morning much like a reportedly fixed MySQL bug.

http://bugs.mysql.com/bug.php?id=28421

My relay bin logs are rapidly filling with an infinite loop of junk made of this sort of thing.

#121018  5:40:04 server id 101  end_log_pos 15598207
#Append_block: file_id: 2244  block_len: 8192
# at 15598352
#121018  5:40:04 server id 101  end_log_pos 15606422
#Append_block: file_id: 2244  block_len: 8192
# at 15606567

...

# at 7163731
#121018  5:38:39 server id 101  end_log_pos 7171801
#Append_block: file_id: 2243  block_len: 8192
WARNING: Ignoring Append_block as there is no Create_file event for file_id: 2243
# at 7171946
#121018  5:38:39 server id 101  end_log_pos 7180016
#Append_block: file_id: 2243  block_len: 8192
WARNING: Ignoring Append_block as there is no Create_file event for file_id: 2243

These log files grow to 1Gb within about a minute before rotating and starting again.

These big files are interspersed with 1 or 2 smaller files with just this in

/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#121023  9:43:05 server id 100  end_log_pos 106         Start: binlog v 4, server v 5.1.61-log created 121023  9:43:05
BINLOG '
mViGUA9kAAAAZgAAAGoAAAAAAAQANS4xLjYxLWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAUwAEGggAAAAICAgC
'/*!*/;
# at 106
#121023  9:43:05 server id 100  end_log_pos 156         Rotate to mysqld-relay-bin.000003  pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;

We're running a master-master replication setup with the problematic server running mysql 5.1.61. The other server which is, for the moment, stable is running 5.1.58.

Has anyone got any ideas what the solution is to this and moreover, what might have caused this?

Answer 1

After a good couple of hours of frantic searching, purging, resetting, etc... It seems that a good old fashioned turn it off and back on again is a solution. Not quite a power cycle but a complete reset of slaves + masters.

slave stop;
reset master;
reset slave;
slave start;

I did this on each of the master/slaves such that all slaves were stopped before each of the masters were reset before restarting each of the slaves.

I hope that this helps someone else, there's not much out there on this situation.