In this example you update a row and send strlen("update t_rf_compact set f_text=repeat("d", 10000000) where f_id=1;")=66 bytes from client to master (a bit more, because of the protocol overhead).

The point is, no matter how large the slave_max_allowed_packet will be, you can still generate a larger row event with just a few bytes sent from the client to the master. The shortest is, something like

do f();

And it can generate an arbitrary large event.
Also, note, there can be many blobs on the table, for example

create table t1 (b1 blob, b2 blob, b3 blob, ... );

...

update set b1=repeat(...), b2=repeat(...), ...

this way even if every individual repeat is below the limit, the row packet will be many times above it. Even for insert, not only for update.

Yes, you are right. That would be less problem if slave_max_allowed_packet is far bigger than max_allowed_packet in production environment. But can not solve it all.

I just wonder if we can remove the limit of slave_max_allowed_packet by cutting a big event into some pieces. Because as users ( for example cloud DB users), they can execute a statement in master but will fail in the slave, that's make no sense to them : )

I don't know... I suspect slave_max_allowed_packet was added for a good reason, to limit the packet size. And you suggest to introduce a loophole to bypass it, basically, and send larger packets.

May be a fix should be different — you say "users <...> can execute a statement in master but will fail in the slave, that's make no sense to them", so perhaps the statement should fail on the master? If the generated row event is going to be larger than slave_max_allowed_packet the statement is aborted with an error? Do you think your users would prefer that instead of failed replication?

I think make the statement fail on the master is better than fail on slave's IO thread.. But sometimes users even do not know their master has a slave. The slave in our environment is just a backup and transparent to users. so, I guess they prefer a failed replication... (we prefer a failed master statement : )

ZhangJian> I think make the statement fail on the master is better than fail on slave's IO thread

True, as the sooner the better. But Sergei has pointed to MDEV-10963 that guarantees no failure on the master either.

I think we can close this one as a duplicate. Objections, Sergei, Zhang Jian?

Yes，Thanks for Andrei's comment I get to know why we have a variable slave_max_allowed_packet, just one last question, can we make the max value of slave_max_allowed_packet bigger( maybe twice or times bigger) than the max value of max_allowed_packet. It's seems safer .

By the way , here is a same issue and the suggestion fix is to ignore max allow packet completely. https://bugs.mysql.com/bug.php?id=77817

Elkin:

Sergei has pointed to MDEV-10963 that guarantees no failure on the master either. I think we can close this one as a duplicate.

No, it's not a duplicate of MDEV-10963. In MDEV-10963 there is no slave_max_allowed_packet, it's about BINLOG command. A packet less than max_allowed_packet in binary, can be larger than max_allowed_packet in base64.

In this issue there is no base64. It's different.

> MDEV-10963 ... about BINLOG command.

True, my bad. It's a similar to that one's fragmentation idea that apparently could be exploited.
Thanks, Zhang Jian for the reference. This issue actually duplicates bug#77817

Well, as I wrote above, slave_max_allowed_packet is a safety measure to limit the max packet size and, thus, the memory consumption on the slave. It's currently set to 1GB. Perhaps we can increase it, but I doubt it would be desirable to remove the limit completely. Getting out-of-memory error on the slave or having the slave killed by an OOM killer is not really better than rejecting a huge event up front.

After thinking over the issue I have gotten the following outline.

Just for the sake of clearness let's consider
M & S master and slave each configured with the same value of
@@global.max_allowed_packet (to shortcut as A_max)

M.A_max == S.A_max == A_max

A_max stands for two control items, both dealing with memory
consumption: the size of the input buffer on a connection on the
server side, and the max size of

any generated/intermediate string,

including parts of a row-based event.
Doubtlessly from the user pov this configuration must provide working replication.

Technically though in order to let a 2 * M.A_max maximum-size row-based event
produced by M be accepted by the slave receiver its S@@session.max_allowed_packet needs
to be set to this value (rather than remain as S.A_max) or greater.
Notice that 2 * M.A_max memory consumption by the receiver
is about the same as at the original query and its replication event processing, so it must be
acceptable by the memory consumption requirement.

Notice in such A_max-equal replication configuration slave_max_allowed_packet is
indeed extraneous.

Let's turn from the equal to a general A_max distribution.
It's clear that only

[*] S.A_max >= M.A_max

satisfies replication from the event applying perspective.
The reverse strict inequality would led to an error at slave applying.

From above we learn that for successful acceptance by the slave receiver the following must hold:

slave_io_thread@@session.max_allowed_packet == slave_max_allowed_packet >= 2 * master@@global.max_allowed_packet

Hence from [*] the slave receive buffer max size can be set to

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

that is to a value computed from the slave's global max_allowed_packet.

The general case also optimizes slave_io_thread@@session.max_allowed_packet away.

Now practically as the current ticket resolution we would deprecate the "manual" control of
slave_max_allowed_packet to favor setting of the slave receiver's
@@session.max_allowed_packet to 2*@@global.max_allowed_packet.

We will also make the slave receiver thread to verify [*] at its
connecting time and issue a warning if the safety condition does not
hold.

We won't be tacking a case when M.A_max was/is changing:
if before S has connected to M the latter had A_max of
a bigger value and produced respectively-sized events the
by-default-configured slave receiver would routinely stop with an error.
It will be the user's burden to memorize an old M.A_max and set S.A_max accordingly before starting replication. Similar manual steps are reserved for the case of M.A_max grown while replication is running.

Separately, as max_allowed_packet itself is under 1GB limit, row-based events over the limit can be transmitted
only by fragments. I am reporting a ticket for this task.

If there will be agreement on slave_io_thread@@session.max_allowed_packet := 2 * slave@@global.max_allowed_packet etc fixes, what version we should target?

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.

> 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.

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.

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.