Bug #93917 Wrong binlog entry for BLOB on a blackhole intermediary master

• Submitted: 14 Jan 2019 13:47
• Modified: 27 Jan 2019 8:52
• Reporter: Sveta Smirnova (OCA)
• Status: Verified
• Category: MySQL Server: Row Based Replication ( RBR )
• Severity: S3 (Non-critical)
• Version: 5.7.24,8.0.12
• OS: Any
• CPU Architecture: Any

[14 Jan 2019 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 2019 13:48] Sveta Smirnova

test case for MTR

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

[14 Jan 2019 13:48] Sveta Smirnova

option file

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

[14 Jan 2019 14:10] MySQL Verification Team

Hello Sveta,

Thank you for the report and test case.

regards,
Umesh

[17 Jan 2019 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 2019 8:52] Sveta Smirnova

Workaround: use VARCHAR or VARBINARY type.

[23 Apr 2019 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 2019 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 2019 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 2019 12:30] MySQL Verification Team

Thank you for the contribution.

regards,
Umesh