What max_allowed_packet is big enough, and why do I need to change it?

I have MySQL (5.5) in master-slave setup and created another slave server.

I stopped the original slave, dumped the data, copied and reimported and it worked fine. I noted the master_log pos of the original slave and used these commands to set it on the new slave

CHANGE MASTER TO MASTER_HOST='', 
MASTER_USER='', MASTER_PASSWORD='', 
MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000851', 
MASTER_LOG_POS=15824150, 
MASTER_CONNECT_RETRY=10;

When I started the new slave I got

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

However when I started the original slave, it caught up just fine, and is now in sync.

So the questions:

-
the current value is 16M, how do I know how big to go? (I would rather avoid trial and error with a production server).

-
why do I need to increase the value on the master when the original slave coped just fine, could the problem really be with the new slave?

update

I increased the max_allowed_packet to 1073741824 as Rolando suggested on the master, old slave and new slave, and restarted them (SET GLOBAL max_allowed_packet = 1073741824; for some reason didn't seem to take)

now the last IO error is the same as before, but now I see

Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.

If I do a mysqlbinlog on the master's file, it scrolls past with commands quite happily for ages - the file is 722M - if I do that for the slave relay log I get

E

It's OK to max out the max_allowed_packet to 1G. Whenever a MySQL Packet is constructed, it will not jump to 1G from the start. Why?

First you need to know what a MySQL Packet. Page 99 of the Book

explains it in paragraphs 1-3 as follows:


MySQL network communication code was
written under the assumption that
queries are always reasonably short,
and therefore can be sent to and
processed by the server in one chunk,
which is called a packet in MySQL
terminology. The server allocates the
memory for a temporary buffer to store
the packet, and it requests enough to
fit it entirely. This architecture
requires a precaution to avoid having
the server run out of memory---a cap
on the size of the packet, which this
option accomplishes.


The code of interest in relation to
this option is found in
sql/net_serv.cc. Take a look at my_net_read(), then follow the call to my_real_read() and pay
particular attention to
net_realloc().


This variable also limits the length
of a result of many string functons.
See sql/field.cc and
sql/intem_strfunc.cc for details.

Compare that with the MySQL Documentation on max_allowed_packet:


The maximum size of one packet or any generated/intermediate string,
or any parameter sent by the mysql_stmt_send_long_data() C API
function. The default is 4MB as of MySQL 5.6.6, 1MB before that.


The packet message buffer is initialized to net_buffer_length bytes,
but can grow up to max_allowed_packet bytes when needed. This value by
default is small, to catch large (possibly incorrect) packets.


You must increase this value if you are using large BLOB columns or
long strings. It should be as big as the largest BLOB you want to use.
The protocol limit for max_allowed_packet is 1GB. The value should be
a multiple of 1024; nonmultiples are rounded down to the nearest
multiple.


When you change the message buffer size by changing the value of the
max_allowed_packet variable, you should also change the buffer size on
the client side if your client program permits it. On the client side,
max_allowed_packet has a default of 1GB. Some programs such as mysql
and mysqldump enable you to change the client-side value by setting
max_allowed_packet on the command line or in an option file.

Given this information, you should be glad MySQL will expand and contract the MySQL Packet as needed. Therefore, go ahead and

• set max_allowed_packet to 1G on both Master and Slave

• set net_buffer_length to its max value of 1M on both Master and Slave

Master and Slave should match in terms of who they transmit data, especially BLOB data.

UPDATE 2013-07-04 07:03 EDT

From your messages concerning the relay log, it looks like you have the following

• a corrupt relay log

• a good master log

SUGGESTION

SHOW SLAVE STATUS\G
STOP SLAVE;
CHANGE MASTER TO
MASTER_LOG_FILE='(Relay_Master_Log_File from SHOW SLAVE STATUS\G)',
MASTER_LOG_POS=(Exec_Master_Log_Pos from SHOW SLAVE STATUS\G);
START SLAVE;

Running CHANGE MASTER TO clears all relay logs and starts with a new one. You will be replicating from the Last Master BinLog Event (BinLog,Position) that executed on the Slave.

Give it a Try !!!

StackExchange Database Administrators Q#45665, answer score: 24