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

Details

    • 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

            serg Sergei Golubchik added a comment - - edited

            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.

            serg Sergei Golubchik added a comment - - edited 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.
            Elkin Andrei Elkin added a comment - - edited

            > bug#77817 says 2x doesn't help you can do many times more than max_allowed_packet.

            You mean 2 * master@@global.max_allowed_packet > 1GB ? This can be only addressed with fragmentation layer which I am about to 'separate' from this ticket.

            As to the slave_max_allowed_packet by default is set to max possible, I should've checked the default value, thanks! Indeed
            slave_io_thread@@session.max_allowed_packet in [**] may be left with this 1GB default value (to comply with your no reason to decrease).
            Τhen [**] turns into an inequality check

            [***] slave@slave_io_thread@@session.max_allowed_packet >= 2 * slave@@global.max_allowed_packet

            which would be conducted at time of either @@globals changes its value.
            I would prefer to constrain the valid range as
            SLAVE_MAX_ALLOWED_PACKET >= 2*@@global.max_allowed_packet instead of a warning.
            Or even better SLAVE_MAX_ALLOWED_PACKET deprecation remains as an option for a single reason of being a redundant parameter which valid range watching is required.

            Elkin Andrei Elkin added a comment - - edited > bug#77817 says 2x doesn't help you can do many times more than max_allowed_packet. You mean 2 * master@@global.max_allowed_packet > 1GB ? This can be only addressed with fragmentation layer which I am about to 'separate' from this ticket. As to the slave_max_allowed_packet by default is set to max possible , I should've checked the default value, thanks! Indeed slave_io_thread@@session.max_allowed_packet in [**] may be left with this 1GB default value (to comply with your no reason to decrease ). Τhen [**] turns into an inequality check [***] slave@slave_io_thread@@session.max_allowed_packet >= 2 * slave@@global.max_allowed_packet which would be conducted at time of either @@globals changes its value. I would prefer to constrain the valid range as SLAVE_MAX_ALLOWED_PACKET >= 2*@@global.max_allowed_packet instead of a warning. Or even better SLAVE_MAX_ALLOWED_PACKET deprecation remains as an option for a single reason of being a redundant parameter which valid range watching is required.
            Elkin Andrei Elkin added a comment - - edited

            After some more consideration I have committed myself to implement the following two steps that cover two existing issues
            around SLAVE_MAX_ALLOWED_PACKET. These are

            • lack of automatic control over SLAVE_MAX_ALLOWED_PACKET which would proactively
              warn the DBA on possible future errors. In order DBA not be surprised by

                Last_IO_Errno: 1153
                Last_IO_Error: Got a packet bigger than 'slave_max_allowed_packet' bytes
                

              deploy

               SELECT Slave@@global.SLAVE_MAX_ALLOWED_PACKET > 2 * Master@@global.MAX_ALLOWED_PACKET
              

              check at slave handshake with master. I think this sort of grace could
              be done on GA versions as well.

            • Support for 1GB+ events (aka bug#77817).
              Continue to regard SLAVE_MAX_ALLOWED_PACKET as it is and
              introduce a new CHANGE-MASTER parameter as a flag requesting from the master
              to fragment events exceeding SLAVE_MAX_ALLOWED_PACKET.
              For backward compatibility the flag stays down so a an event over this size triggers the above error.
              Otherwise the big event is transferred via
              fragmentation mechanism which bases on a new
              replication event type (tentatively Transport_event) with its subheader describing
              the fragment that is carried as the event payload.

            Fragmentation and the transport_event creation is
            done by Dump thread, the assembly by the slave IO.

            serg, anybody could you please rate the idea. Thanks.

            Elkin Andrei Elkin added a comment - - edited After some more consideration I have committed myself to implement the following two steps that cover two existing issues around SLAVE_MAX_ALLOWED_PACKET . These are lack of automatic control over SLAVE_MAX_ALLOWED_PACKET which would proactively warn the DBA on possible future errors. In order DBA not be surprised by Last_IO_Errno: 1153 Last_IO_Error: Got a packet bigger than 'slave_max_allowed_packet' bytes deploy SELECT Slave@ @global .SLAVE_MAX_ALLOWED_PACKET > 2 * Master@ @global .MAX_ALLOWED_PACKET check at slave handshake with master. I think this sort of grace could be done on GA versions as well. Support for 1GB+ events (aka bug#77817). Continue to regard SLAVE_MAX_ALLOWED_PACKET as it is and introduce a new CHANGE-MASTER parameter as a flag requesting from the master to fragment events exceeding SLAVE_MAX_ALLOWED_PACKET . For backward compatibility the flag stays down so a an event over this size triggers the above error. Otherwise the big event is transferred via fragmentation mechanism which bases on a new replication event type (tentatively Transport_event) with its subheader describing the fragment that is carried as the event payload. Fragmentation and the transport_event creation is done by Dump thread, the assembly by the slave IO. serg , anybody could you please rate the idea. Thanks.

            There is no need for a automatic control of SLAVE_MAX_ALLOWED_PACKET. Because it is already set to is maximal possible value. You cannot increase it further automatic or not. You can decrease it, but it won't help to avoid these "packet too large" errors, will it?

            And, again, SLAVE_MAX_ALLOWED_PACKET is a safety measure. So, no fragmentation should be able to produce packets larger than that.

            The only thing we can do is to increase SLAVE_MAX_ALLOWED_PACKET. 1GB is too little? Let's try if 2GB or 4GB will work.

            serg Sergei Golubchik added a comment - There is no need for a automatic control of SLAVE_MAX_ALLOWED_PACKET. Because it is already set to is maximal possible value . You cannot increase it further automatic or not. You can decrease it, but it won't help to avoid these "packet too large" errors, will it? And, again, SLAVE_MAX_ALLOWED_PACKET is a safety measure . So, no fragmentation should be able to produce packets larger than that. The only thing we can do is to increase SLAVE_MAX_ALLOWED_PACKET. 1GB is too little? Let's try if 2GB or 4GB will work.

            So, shall we try a larger SLAVE_MAX_ALLOWED_PACKET ? Like 2GB? An alternative would be to close this issue, because it's not a bug and it cannot be fixed.

            serg Sergei Golubchik added a comment - So, shall we try a larger SLAVE_MAX_ALLOWED_PACKET ? Like 2GB? An alternative would be to close this issue, because it's not a bug and it cannot be fixed.

            People

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

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.