What fix could we possibly do here?

As suggested in upstream bug report:

Take encoding overhead of mysqlbinlog into account somehow while deciding on maximum possible row-based even size for the given max_allowed_packet.

In the meantime, please, describe "safe" setting of max_allowed_packet in case of row-based replication in KB clearly, as well as any workarounds for the case when max_allowed_packet was 1G on the server that produced binary long with huge row based event that one needs to restore now.

As far as I understand, the row event up to max_allowed_packet can be replicated just fine. It's only when mysqlbinlog converts it to a BINLOG statement, that it becomes larger than max_allowed_packet.

Meaning, always taking mysqlbinlog overhead into account will prevent replication of perfectly valid row events.

First step is to estimate/compute the encoding overhead. Then we can document the "absolutely safe" max_allowed_packet value in KB article maybe.

I'd also suggest to add new option/server variable (off by default) for taking overhead into account while writing the binary log events, so that we do not write something that can not be decoded by mysqlbinlog.

Hello.

After reading through the description and discussion the following can be made.

As it was already referred the issue is essentially a follow-up of under-fixed Bug#60926 for a use case of mysqlbinlog as the feeder to point-in-time-recovered server ("slave").
It is also complicated by a fact that the 1GB "hard" max-allowed-packet max events can't be handled merely
with @@global.max_allowed_packet increase. Given specifics of 4/3 base64-encode overhead it just can't be set to 4/3*1GB.

We can fix the issue with "pointed" fragmentation layer arrangement so Log_event::print_base64() would
create a number of BINLOG pseudo-queries not just one as currently.

Each BINLOG fragment to be fit within mysqlbinlog.max_allowed_packet. The fragments will be decoded while they are emerging into server.mysql_client_binlog_statement() and each decoded one appended to already
collected total. While it's not the whole execution is deferred. The last fragment will trigger eventual execution of the totally decoded and assembled one.

Changes are deemed to mysqlbinlog and the server. The server will remain backward-compatible mode. mysqlbinlog could be made the same with a new option.

I fancy we could go on and replicate 1GB+ rows-events (e.g 2GB Update-rows-log-event where BI, AI are of 1GB each; note it's an unresolved case as of current) to use this method through initial converting the big Rows_log_events into BINLOG pseudo-queries (perhaps by the dump thread?!).

Less ambitious would be to come up with a warning printed when a created event size exceeds "about" 3/4*@@global.max_allow_packet.

I would be glad to hear any feedback on the proposals.

Andrei.

Sergei, maybe I am steeling the idea from you, that's why I apply to you early .
If it's really sane enough, do you we can go to fix in already in GA:s?

Thank you.

Andrei

Fragmentation sounds too complex.
A warning when an event is created makes little sense — it's too late to warn.

Here a couple of other ideas:

• a warning when max_allow_packet is set to a value larger than 3/4*1GB. Like "careful, this will allow to create binlog events that cannot be replayed with mysqlbinlog|mysql"
• a new statement, for example BINLOG RAW <expression>. mysqlbinlog, when detecting events that cannot be base64-ed, will do, like

  SET @val1=UNHEX(...);

  SET @val2=UNHEX(...);

  BINHEX RAW CONCAT(@val1, @val2);

  here everything is below max_allowed_packet, and BINHEX RAW will simply skip base64 decoding step. May be not RAW but, say, BINARY or some other, more appropriate, keyword.

Sergei, I like the idea very much!

Your `@val`:s are pretty much the same fragments.
And they could be base64-encoded strings as well for consistency with the old "static" BINLOG.
CONCAT of

BINLOG RAW CONCAT(from_base64(@frag1), from_base64(@frag2), ... , from_base64(@fragN))

would create the raw bytes string of the size satisfying max_allow_packet.
Maybe we won't need 'RAW' keyword actually to favor

BINLOG @frag1,@frag2,.., @fragN

where the @frag list expands into CONCAT(from_base64(),...).

How such elaboration looks like?

Andrei

Replied to Sergei to explain difficulty in implementing a suggested
syntax. Perhaps the latest patch method is the only feasible one.

All notes of previous review round are addressed in a new patch d282f5c5560.

Thanks for checking and insightful comments as usual!
I am publishing an updated patch c3ef531aaff to cover all of them.

A updated patch b58448b10ed is out to address yesterday review notes and questions.

ok to push

Pushed 5d48ea7d07b.