slave_max_allowed_packet is not big enough when binlog is row format

The global variable max_allowed_packet limit the client packet and slave_max_allowed_packet limit the packet from master. The max value of both vairiables is 1G.

The problem is when binlog is row format, the update statement data size will be doubled in slave node. so if max_allowed_packet is same as slave_max_allowed_packet the slave will get error no 1594.

Here is example, the binlog is row format.

CREATE TABLE `t_rf_compact` (

  `f_id` int(11) NOT NULL AUTO_INCREMENT,

  `f_text` longtext NOT NULL,

  PRIMARY KEY (`f_id`)

) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;

First create a table and set the global variables both in master and slave.

MariaDB [test]> show global variables like "%packet%";

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

| Variable_name            | Value    |

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

| max_allowed_packet       | 16777216 |

| slave_max_allowed_packet | 16777216 |

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

MariaDB [test]> insert into t_rf_compact(f_text) values (repeat("D", 10000000));

Query OK, 1 row affected (2.121 sec)

MariaDB [test]> update t_rf_compact set f_text=repeat("d", 10000000) where f_id=1;

Query OK, 1 row affected (2.337 sec)

Rows matched: 1  Changed: 1  Warnings: 0

In this example, we update a row and need to sent 10000000 Byte from client to master, but master need to sent 20000000 Byte to slave. 10000000 < max_allowed_packet and slave_max_allowed_packet < 20000000. so the slave IO thread will exit will error

Last_IO_Errno: 1153

Last_IO_Error: Got a packet bigger than 'slave_max_allowed_packet' bytes

Fix:
we need to keep max_allowed_packet < slave_max_allowed_packet / 2. First the max value of max_allowed_packet should be less than half of slave_max_allowed_packet's max value.