One month ago I wrote about how a big read_buffer_size could break the replication. The bug is not solved but now there is an official workaround to ease this problem using a new configuration variable:
This new variable will be available in 5.1.64, 5.5.26, and 5.6.6 and can establish a different limit on the max_allowed_packet for the slave servers (IO Thread). Now on a slave server the maximum size of a packet is checked against this variable and not max_allowed_packet.
The default value is 1GB and that means if we don’t tune the variable our I/O thread can read up to that amount of data. This is important if we use the binary logs for PITR. This variable solves only one part of the problem, the slave doesn’t stop working but binary log events can still be bigger than max_allowed_packet. During the recovery process the slave_max_allowed_packet is not going to help us and our recovery process could fail.
Conclusion
Now we have two workarounds for the same problem. First, use a small value for your read_buffer_size (less than max_allowed_packet) and the second one, upgrade to a version that has slave_max_allowed_packet.
Training
In September I’m going to deliver MySQL Training (Sep 3-6) in Madrid. This is the first time we are going to deliver the training in Spanish 🙂 If you’re interested just click on the previous link to get more information.
En Septiembre voy a impartir Formación de MySQL en Madrid (Sep 3-6). Está será la primera vez que se dará la formación en Español 🙂 Si estás interesado haz click en el enlace anterior para tener más información.
I just got the update today, and I was surprise to see my replication broken with this error :
Got a packet bigger than ‘slave_max_allowed_packet’ bytes
And my slave_max_allowed_packet is already set to the maximum (1G) . I have no idea how to override this setting.
Seems to be a bug, if you have binlog_format=STATEMENT (and probably MIXED as well) MySQL still looking to max_allowed_packet but it report on slave_max_allowed_packet http://bugs.mysql.com/bug.php?id=69104