Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-16216

slave_max_allowed_packet is not big enough when binlog is row format

    XMLWordPrintable

    Details

    • Sprint:
      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

            Activity

              People

              Assignee:
              serg Sergei Golubchik
              Reporter:
              zhang xiaojian zhang jian
              Votes:
              0 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: