Bug #103672 Binlog compression Transaction_payload event exceeds max allowed packet
Submitted: 12 May 2021 16:50 Modified: 3 Jun 2021 13:59
Reporter: Peter Kan Email Updates:
Status: Closed Impact on me:
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 2021 16:50] Peter Kan
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 2021 12:14] MySQL Verification Team

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
[3 Jun 2021 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 " 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 2021 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.
[30 Aug 2021 12:06] MySQL Verification Team
Bug#104765 is marked as duplicate of this one
[30 Aug 2021 13:54] Sergiu Hlihor
Documenting the issue is not enough and not truthful, as it does not state that replication cannot be resumed and replicas have to be rebuilt from scratch. I therefore propose two alternatives: 
1) Implement flag that would check for compressed transaction size and immediately reject the transaction if compressed size > 1GB with the appropiate error message that would allow a developer to take actions and redesign the transaction strategy. This allows database replication consistency to be kept at a cost of a transaction being rejected.  
  2) Implement mixed binary logs concept where, if compressed size > 1GB, the old uncompressed mechanism is used, that is capable of replicating transactions greater than MAX_ALLOWED_PACKET. This would be most desirable since old mechanism is known to be working, thus accepting a one time uncompressed transaction while keeping most other transactions compressed. Given that such large updates are typical for database migrations, this would have the advantage of fixing the problem while still getting the benefit of compression for 99.99% of time.