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

Question

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='<ipaddress>', 
MASTER_USER='<username>', MASTER_PASSWORD='<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

ERROR: Error in Log_event::read_log_event(): 'Sanity check failed', data_len: 38916267, event_type: 69

ERROR: Could not read entry at offset 253: Error in log format or read error.

I checked the variables and the changes worked however

mysql> show variables LIKE '%max_allowed_packet%';

on the new slave showed max_allowed_packet AND slave_max_allowed_packet where as on the master it only has max_allowed_packet

so I did a version check on the master:

mysql> show variables LIKE '%version%';
+-------------------------+--------------------------------------+
| Variable_name           | Value                                |
+-------------------------+--------------------------------------+
| innodb_version          | 1.1.6                                |
| protocol_version        | 10                                   |
| slave_type_conversions  |                                      |
| version                 | 5.5.11-log                           |
| version_comment         | MySQL Community Server (GPL) by Remi |
| version_compile_machine | x86_64                               |
| version_compile_os      | Linux                                |
+-------------------------+--------------------------------------+

and on the new slave

mysql> show variables LIKE '%version%';
+-------------------------+--------------------------------------+
| Variable_name           | Value                                |
+-------------------------+--------------------------------------+
| innodb_version          | 5.5.32                               |
| protocol_version        | 10                                   |
| slave_type_conversions  |                                      |
| version                 | 5.5.32-log                           |
| version_comment         | MySQL Community Server (GPL) by Remi |
| version_compile_machine | x86_64                               |
| version_compile_os      | Linux                                |
+-------------------------+--------------------------------------+

Are these 2 versions too far apart?

Answer 1

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 !!!

Answer 2

Rather embarrassingly the problem was incorrect filenames for logs, causing weird results, reimported with the correct filenames and everything was fine hangs head in shame

Answer 3

If you see binlog on the master, binlog positions are wrong. Sometimes error is not what it seems to be. Do not change max packet value. We need to do a little work through here. So, my suggestion, you may need to reset slave replication coordinates. Use mysqlbinlog on the master to identify where the transactions begin, and look for the adjacent transactions. Reset as explained below.

STOP SLAVE;
RESET SLAVE;
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000851', MASTER_LOG_POS=15824150, MASTER_HOST='master-server', MASTER_USER='master-user', MASTER_PASSWORD='master-password';

You can find more details on exactly what to do in this mysql troubleshooting article. Hope this will help.