MySQL replication problem

My MySQL replication slave stopped replicating the master. Checked the log file it has this error:

Error reading packet from server: log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master (server_errno=1236)

Got fatal error 1236: ‘log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master’ from master when reading data from binary log

Naturally I started increasing the max_allowed_packet in my.cnf. No help. So I looked up the web and found this bug documented. Bingo I thought…oh wait, the bug was discovered a few years back and my version of MySQL should have included the fix already. Ok back to square one.

Just to poke around I ran “show master status” on the master server and compare bin file name and position with what’s in the logs on slave server. The files do not match. It may or may not cause the error 1236 but it’s definitely not right. So I went ahead ran the following command to re-sync:

On the slave:

mysql -u root -pxxxxx

SLAVE STOP

LOAD DATA FROM MASTER

CHANGE MASTER TO MASTER_LOG_FILE=’the-right-bin.xxx’, MASTER_LOG_POS=the-right-position;

SLAVE START

Now step two to load data from mater created a lot of error logs since my slave DB had had data so I really should have clean up the DB before run the commands.

So the replication is back to work now. Not sure what had really caused error 1236. Get my fingers crossed.

This entry was posted in mysql. Bookmark the permalink.