21

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?

1
  • There is some thing interesting here. Hope this would be helpful.
    – Sathish D
    Jul 4, 2013 at 5:47

3 Answers 3

24

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

Understanding MySQL Internals

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

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

8
  • thanks, that's great that it's safe; but I still don't get why I need to change it at all when the current value matches the master, and the other slave which is working perfectly?
    – CodeMonkey
    Jul 4, 2013 at 8:08
  • something else is going on, I have added more details
    – CodeMonkey
    Jul 4, 2013 at 8:52
  • 2
    Just for your information: This occured me when I reseted the replication process and accidentally entered wrong MASTER_LOG_FILE name. E.g. used mysql-bin.000001 when I should have used mysql-bin.000003 from SHOW MASTER STATUS in CHANGE MASTER TO. May 23, 2014 at 11:00
  • Thanks @RolandoMySQLDBA ! Your suggestion worked for me! My replication stopped with an error "increase max_allowed_packet" but it didn't want to start after increasing and only your advice helped!
    – Stalinko
    Oct 29, 2020 at 7:33
  • Are your suggestions to set it to the maximum also applies to mariadb ?
    – Freedo
    Jan 22, 2021 at 16:05
14

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

2
  • Thanks a lot! You were not the only one doing this mistake. May 23, 2014 at 11:02
  • 9
    It's always worth posting the answer even if it's silly. Everyone makes silly mistakes as we're all human. Apart from those of us that are robots.
    – John Hunt
    Jan 16, 2018 at 11:47
1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.