Max Allowed Packet Error in MySQL Replication

 Last week one of my friend called and Ask for help. He told me he is getting below error in his MySQL setup.

 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; the first event 'mysql_binary_log.000050' at 658752953, the last event read from '/mysql/logs/binary/mysql_binary_log.000050' at 658752953, the last byte read from '/mysql/logs/binary/mysql_binary_log.000050' at 658752972.'

The first solution of it to increase size of max_allowed_packet to start replication.   To configure this variable you need to execute below command.

Check the current Size.

mysql> show variables like 'max%allowed%';

+--------------------+---------+

| Variable_name      | Value   |

+--------------------+---------+

| max_allowed_packet | 4194304 |

+--------------------+---------+

1 row in set (0.08 sec)

mysql>

To increase size execute below commands.

mysql>

mysql> set global max_allowed_packet=41943040

    -> ;

Query OK, 0 rows affected (0.04 sec)

mysql>

Check size by login in another MySQL session.

mysql> show variables like '%allowed%'

    -> ;

+--------------------------+------------+

| Variable_name            | Value      |

+--------------------------+------------+

| max_allowed_packet       | 41943040   |

| slave_max_allowed_packet | 1073741824 |

+--------------------------+------------+

2 rows in set (0.00 sec)

mysql>

It was a simple solution to start work.  Still I was keen to know details /possible reason of this.  I asked  for some more details from him and found current bin log format is configured to “row”. In case of row based binary log size of binary log is more than from mixed/statement based logging.

In his scenario it was due to a big transection which generated 2GB log and replication stopped. Set your binlog format to mixed to avoid these situations.