Thanks for the feedback.
Alas, your reply means that I need to be less confident in saying why
the problem happens. And I need to continue to look for another cause.
(It happens to me maybe once per year per slave.)
Can anyone else proffer an explanation?
On 4/29/11 12:06 PM, a.smith@stripped wrote:
> Quoting Rick James<rjames@stripped>:
> Thanks for the reply. I don't believe that the system crashed or
> mysql was shutdown uncleanly, however I have now set "sync_binlog=1"
> so if this happens again at least I have ruled that out as a possible
> cause. My DB is low on writes so I think I can cope with this
> performance wise no problems,
> thanks Andy.
>> _Usually_ that is caused by the Master dying, and you have sync_binlog=OFF.
>> * Master sends binlog stuff to slave(s) before flushing to binlog
>> * Slave gets the stuff and writes to the relaylog, calculating where
>> it logically is in the binlog
>> * Master crashes without flushing.
>> * Slave is disconnected
>> * Master comes back up.
>> * Master starts a new binlog (as is normal for starting up)
>> * Slave reconnects and asks to pick up where it left off in the
>> binlog. But that is off the end of the unflushed binlog.
>> The Fix (on Slave) -- CHANGE MASTER TO master_file = ... master_pos
>> = 0 (or 4); the file is that new binlog mentioned above. (That is
>> what you did.)
>> Caution: whereas sync_binlog=ON may cure it in the future, it can
>> take a severe toll on I/O.
Rick James - MySQL Geek