Bug #93917 Wrong binlog entry for BLOB on a blackhole intermediary master
Submitted: 14 Jan 13:47 Modified: 27 Jan 8:52
Reporter: Sveta Smirnova (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S3 (Non-critical)
Version:5.7.24,8.0.12 OS:Any
Assigned to: CPU Architecture:Any

[14 Jan 13:47] Sveta Smirnova
Description:
Bug #56007  seems to be silently fixed. However, the same issue exists for a BLOB column.

How to repeat:
Run the attached test case, then check binary logs on the slave.

Test column changes recorded correctly.

Original update:

UPDATE BLACK SET TEXTCOL='teststring_update' where ID=1;

Binlog entry:

### UPDATE `test`.`BLACK`
### WHERE
###   @1=1 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @2='teststring' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
###   @3='TESTBLOB' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### SET
###   @1=1 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @2='teststring_update' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
###   @3='TESTBLOB' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */

Update for a BLOB column not.

Original update:

UPDATE BLACK SET BLOBCOL='testblob_update' where ID=2;

Binlog entry:

### UPDATE `test`.`BLACK`
### WHERE
###   @1=2 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @2='TESTSTRING_2' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
###   @3='testblob_u' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### SET
###   @1=2 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @2='TESTSTRING_2' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
###   @3='testblob_update' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
[14 Jan 13:48] Sveta Smirnova
test case for MTR

Attachment: rpl_bug93917.test (application/octet-stream, text), 797 bytes.

[14 Jan 13:48] Sveta Smirnova
option file

Attachment: rpl_bug93917-slave.opt (application/octet-stream, text), 22 bytes.

[14 Jan 14:10] Umesh Shastry
Hello Sveta,

Thank you for the report and test case.

regards,
Umesh
[17 Jan 14:52] Sveta Smirnova
Actually for the TEXT column binary log entry is also wrong.

Expected:

### UPDATE `test`.`BLACK`
### WHERE
###   @1=1 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @2='TESTSTRING' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
###   @3='TESTBLOB' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### SET
###   @1=1 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @2='teststring_update' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
###   @3='TESTBLOB' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */

Actual:

### UPDATE `test`.`BLACK`
### WHERE
###   @1=1 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @2='teststring' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
###   @3='TESTBLOB' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
### SET
###   @1=1 /* LONGINT meta=0 nullable=1 is_null=0 */
###   @2='teststring_update' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
###   @3='TESTBLOB' /* BLOB/TEXT meta=2 nullable=1 is_null=0 */
[27 Jan 8:52] Sveta Smirnova
Workaround: use VARCHAR or VARBINARY type.
[23 Apr 12:26] Yura Sorokin
5.6 patch

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: mysql_bug93917_5_6.diff (application/octet-stream, text), 15.24 KiB.

[23 Apr 12:27] Yura Sorokin
5.7 patch

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: mysql_bug93917_5_7.diff (application/octet-stream, text), 16.80 KiB.

[23 Apr 12:27] Yura Sorokin
8.0 patch

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: mysql_bug93917_8_0.diff (application/octet-stream, text), 17.22 KiB.

[23 Apr 12:30] Umesh Shastry
Thank you for the contribution.

regards,
Umesh