Bug #83418 When binlog_row_image is MINIMAL, some updates queries logs redundant columns
Submitted: 18 Oct 2016 0:46 Modified: 18 Oct 2016 8:48
Reporter: Abhinav Sharma Email Updates:
Status: Verified Impact on me:
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S5 (Performance)
Version:5.6, 5.6.34 OS:Any
Assigned to: CPU Architecture:Any
Tags: binlog, binlog_row_image, minimal, replication

[18 Oct 2016 0:46] Abhinav Sharma
For some "update" and "insert into ... on duplicate key update" queries columns which are not changed are logged in both the before and after image. Not logging these columns can reduce the binlog space.

How to repeat:

create table t (a int primary key, b int, c int);
insert into t values(1, 1, 1);
# b will be present in the after image even though it's value is unchanged
update t set a = 2, b = 1 where c = 1;
#c will be present in the after image even though it's value is unchanged
insert into t values(2, 1, 1) on duplicate key update b = b + 1, c = values(c);

Suggested fix:
THD::binlog_prepare_row_images can parse the AI and BI to check for fields that are unchanged and remove them from the write_set bitmap. Since this method is called just before pack_row, unchanged columns will not be logged.
[18 Oct 2016 0:50] Abhinav Sharma
Sorry, the description is misleading. Unchanged column values are only present in the after image.
[18 Oct 2016 8:48] MySQL Verification Team
Hello Abhinav Sharma,

Thank you for the report and test case.
Observed this with 5.6.34 build.

[18 Oct 2016 8:50] MySQL Verification Team
test results

Attachment: 83418_5.6.34.results (application/octet-stream, text), 5.37 KiB.