Bug #52226 Relay logs rotate inappropriately
Submitted: 19 Mar 2010 16:27
Reporter: Shawn Green Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S3 (Non-critical)
Version:5.1.41 OS:Any
Assigned to: Assigned Account CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[19 Mar 2010 16:27] Shawn Green
Description:
The relay logs are rotating inappropriately. Here is a list of binary logs and their sizes:

52,803,053 CCCCCC-bin.000001
56,102,876 CCCCCC-bin.000002
56,102,940 CCCCCC-bin.000003
56,102,940 CCCCCC-bin.000004
56,102,940 CCCCCC-bin.000005

And the relay logs generated by the slave

        156 CCCCC-relay-bin.000001
 52,429,178 CCCCC-relay-bin.000002
    374,285 CCCCC-relay-bin.000003
        200 CCCCC-relay-bin.000004
 52,429,573 CCCCC-relay-bin.000005
  3,673,713 CCCCC-relay-bin.000006
        200 CCCCC-relay-bin.000007
 52,428,889 CCCCC-relay-bin.000008
  3,674,461 CCCCC-relay-bin.000009
        200 CCCCC-relay-bin.000010
 52,428,889 CCCCC-relay-bin.000011
  3,674,461 CCCCC-relay-bin.000012
        200 CCCCC-relay-bin.000013
 52,428,889 CCCCC-relay-bin.000014
  3,674,461 CCCCC-relay-bin.000015
        200 CCCCC-relay-bin.000016

How to repeat:
Replicate between two 5.1.41 instances. You don't need to actually process any of the relay logs, all you need to activate is the IO_THREAD and stream the binary logs to the slave.  

I will attach some overlapping parts of the mysqlbinlog outputs from the first binary log and the first two relay logs. Please note that relay-bin.000002 rotates backwards to relay-bin.000001 before continuing with the next statement from the actual binary log. 

The --max-relay-log-size setting is about double the value for --max-binlog-size

See the attached files:
bin1.txt - an extract from the head of the first binary log
relay1.txt - the entire first relay log
relay2.txt - an extract from the head of the second relay log

Suggested fix:
Fix the "rotate relay log" logic to behave more predictably.
[19 Mar 2010 16:28] Shawn Green
The opening segments of the mysqldump readthrough of the first binary log

Attachment: bin1.txt (text/plain), 3.16 KiB.

[19 Mar 2010 16:28] Shawn Green
The entire mysqlbinlog ouput for the first relay log

Attachment: relay1.txt (text/plain), 1.58 KiB.

[19 Mar 2010 16:29] Shawn Green
The opening sections of the mysqlbinlog output of the second relay log

Attachment: relay2.txt (text/plain), 4.39 KiB.