Bug #103672 Binlog compression Transaction_payload event exceeds max allowed packet
Submitted: 12 May 16:50 Modified: 3 Jun 13:59
Reporter: Peter Kan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S2 (Serious)
Version:8.0.23 OS:Any
Assigned to: CPU Architecture:Any
Tags: Binlog Compression, Binlog Replication, Transaction_payload

[12 May 16:50] Peter Kan
Description:
I turned on binlog_transaction_compression and ran a large transaction. The Transaction_payload event exceeds the max event log size, which is 64MB by default. I can see the following error from SHOW BINLOG EVENTS: 

mysql> show binlog events in 'mysql-binlog.000001';
ERROR 1220 (HY000): Error when executing command SHOW BINLOG EVENTS: Event too big

If the Transaction_payload event is larger than slave_max_allowed_packet, it breaks the binlog replication

Last_IO_Errno: 13114
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on master; 

How to repeat:
1. set binlog_transaction_compression=ON, zstd level is the default 3
2. create table t1 (c text);
3. START TRANSACTION; Insert 5 million rows of data into t1 values(repeat(md5(rand()),5)); COMMIT; 
4. Then you will have a huge Transaction_payload event
5. SHOW BINLOG EVENTS or set up a binlog replication, you will notice the above errors. 

Suggested fix:
1. Disable binlog compression if the uncompressed cache size exceeds max_allowed_packet

2. Design a new version of Transaction_payload event. It can split the cache data into smaller pieces based on max_allowed_packet and compress each piece into a log event. All these smaller events could contain information to indicate that they are from the same transaction.
[13 May 12:14] MySQL Verification Team
Hi,

Thanks for the report, we are aware of this problem and are in process of fixing it. I don't think it is already fully solved with 8.0.25 but you should upgrade to 8.0.25 as it solves number of issues that also cause this problem with binlog compression.

kind regards
Bogdan
[3 Jun 13:59] Margaret Fisher
Posted by developer:
 
Thanks for your comment! This issue is documented in https://dev.mysql.com/doc/refman/8.0/en/binary-log-transaction-compression.html in the section "5.4.4.5.1 Behaviors When Binary Log Transaction Compression is Enabled". I have turned the most relevant part into an Important note to give it more visibility, and added a clarification that this might cause replication to stop.
[7 Jun 14:18] Sergiu Hlihor
What is the resolution of this ticket? Will this behavior be changed in future such that is consistent with scenarios when binlog compression is disabled? Currently it is physically impossible to replicate transactions where compressed size is greater than 1GB while with compression disabled, it is no problem.