MySQL Replication Error 1236

MySQL error 1236: Client requested master to start replication from impossible position

Symptoms:
[1236] Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position'

110902 16:47:08 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236)
110902 16:47:08 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position', Error_code: 1236
110902 16:47:08 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000033', position 4621679

on the master
root@dimko:/var/lib/mysql# ls -la mysql-bin.000033
-rw-rw---- 1 mysql mysql 4620018 2011-09-01 13:45 mysql-bin.000033

4620018 is less than 4621679, therefore it's an invalid position.


Causes:
Master server has crashed and the binlog cache has not been flushed to disk. Slave has recieved a new position, did not recieve data, and data gets lost in a crash (however it might have been written to table, but not in binlog).

Solution:
Use this CHANGE MASTER statement on the slave.
CHANGE MASTER TO MASTER_LOG_FILE=[NEXT FILE], MASTER_LOG_POS=4;
SLAVE START;

in my case that would be

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000034', MASTER_LOG_POS=4;
SLAVE START;

I don't know why the master log position needs to be 4 for the new file.

What happens:
When the master server restarts it logs binary changes to a new binlog file, so that we minimize data loss by skipping to the next file (everything from the previous file was written already).

Prevention:
Add this line to my.cnf:

sync_binlog = 1

 With this setting the master server flushes cache in the binlog after every write, so that in case of a crash you can lose one statement at most.