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
• Status: Verified
• Category: MySQL Server: Row Based Replication ( RBR )
• Severity: S5 (Performance)
• Version: 5.6, 5.6.34
• OS: Any
• CPU Architecture: Any
• Tags: binlog, binlog_row_image, minimal, replication

[18 Oct 2016 0:46] Abhinav Sharma

Description:
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:
Example:

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.

Thanks,
Umesh

[18 Oct 2016 8:50] MySQL Verification Team

test results

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