Mysql row-based binary logging behaving different in some versions of thesql (rows grouping)

amazon-rdsenvironment-variablesMySQLmysql-5.7mysqlbinlog

Mysql row-based binary logging seems to have a different behaviour when grouping rows changes in some versions of mysql.

Let's assume the following statement update three rows:

UPDATE table_name SET a=1 WHERE id IN (1, 2, 3);

In mysql 5.7.21: The binary log receive only one log containing the 3 rows changes. (Which is my desired behaviour)

However, when tested in mysql 5.7.11 and mysql 5.7.26, the same statement produces three separated logs. Each one of them containing each changed row.

I have checked the system variables and, at least, the ones who are related to the binary log, seem to be exactly the same on 5.7.21 and 5.7.26.

We have checked the startup variable --binlog-row-event-max-size, which could be causing the problem. Since the rows in our tables could be bigger than the default 8KB. But, we are not able to set this variable. Since we are working in Amazon RDS. (How can we do it?)

I need that the rows that are changed on the same statement are always batched in the binary log. Is there some variable which could be causing this behaviour?

Notes: There is no replication involved in our setup.

Best Answer

The setting is a "soft" max. If a row exceeds the given value, the entry will be bigger than the max. That is, "no problem".

If your desire is to avoid them ever being batched, you probably need the value to be less than 8K.

From the 8.0.14 Changelog:

The startup option --binlog-row-event-max-size now has a corresponding system variable binlog_row_event_max_size. The startup option and system variable set a soft limit on the maximum size of a row-based binary log event, with a default setting of 8192 bytes. Where possible, rows stored in the binary log are grouped into events with a size not exceeding the value of this setting. If an event cannot be split, the maximum size can be exceeded.

The binlog_row_event_max_size global system variable is read-only and can be set only at server startup.

As for whether, or when, Amazon will provide a way to set it, I don't know. File a bug report with them.

I would suggest that the value of the variable is only a minor optimization.

----- 2017-04-10 8.0.1 Development Milestone & 2017-04-10 5.7.18 General Availability -- Bugs Fixed -- Replication -----

Binlog_sender, which writes events from the binary log to a packet buffer and then sends the packet to the slave, did not reduce the size of the send buffer as expected. (Bug #24643036)

Related Question