Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Won't Fix
-
None
-
10.4.0-1
Description
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.
Attachments
Issue Links
- relates to
-
MDEV-10963 mysqlbinlog can produce events larger than max_allowed_packet for mysql
-
- Closed
-
- links to
Again, I wrote above and as bug#77817 says 2x doesn't help you can do many times more than max_allowed_packet.
But note that by default slave_max_allowed_packet is not max_allowed_packet.
MariaDB [test]> select variable_name,default_value
from information_schema.system_variables
where variable_name like '%max_allowed_packet';
+--------------------------+---------------+
| variable_name | default_value |
+--------------------------+---------------+
| SLAVE_MAX_ALLOWED_PACKET | 1073741824 |
| MAX_ALLOWED_PACKET | 16777216 |
+--------------------------+---------------+
2 rows in set (0.003 sec)
That is, the slave_max_allowed_packet by default is set to max possible value for a packet size. We can increase that. But I don't see a reason to decrease it.