MySQL Bugs: #107366: Contribution by Tencent: Invalid JSON value, Error

Bug #107366	Contribution by Tencent: Invalid JSON value, Error_code: 3903
Submitted:	23 May 2022 1:53	Modified:	3 Jan 2023 13:09
Reporter:	zhang simon (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S2 (Serious)
Version:	8.0.18 and prior, 8.0.29, 8.0.30, 8.0.31, 8.0.32	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	replication json

Description:
replica fails with Error_code: 3903

Last_SQL_Error  Could not execute Update_rows event on table test.t1; Invalid JSON value for CAST for functional index 'comp'., Error_code: 3903; handler error No Error!; the event's master log master-bin.000001, end_log_pos 1033

How to repeat:
--source include/master-slave.inc
--source include/have_binlog_format_row.inc

--connection master

CREATE TABLE `t1` (
  `id` int,
  `c1` json NOT NULL,
   c2 int,
  KEY `comp` ((cast(`c1` as unsigned array)))
) ENGINE=InnoDB;

insert into t1 values(1,'[1046, 2995]',1);

update t1 set c2=2;

select * from t1;

--source include/sync_slave_sql_with_master.inc

select * from t1;

--source include/rpl_end.inc

Hello zhang simon,

Thank you for the report and test case.

regards,
Umesh

REPLICATION ERROR WITH HASH_SCAN ROW SEARCH ALGORITHM FOR FUNCTIONAL INDEXES (*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: bugfix-107366-REPLICATION-ERROR-WITH-HASH_SC.patch (application/octet-stream, text), 3.81 KiB.

In the last comment, we submitted a patch to fix this bug.

Description
-----------
HASH_SCAN builds a hash of changes, scans the target table or
index, and applies any matching change for current entry. In the
build phase, it uses only the before-image, and skips any
after-image to avoid loose ends. However, previous implementation
(since WL#1075) computed generated columns for the skipped
after-image, thus causing replication error in some cases.

Analysis
--------
Rows_log_event::unpack_current_row() is used to unpack or skip a
row event. In the latter case, computing generated columns is
meaningless and sometimes harmful.

Fix
---
Never compute any generated columns for only-seek calls.

update Synopsis

update Synopsis

update Synopsis

update Synopsis

update Synopsis

update Synopsis

Documented fix as follows in the MySQL 8.0.33 changelog:

    A hash scan builds a hash of changes, scans the target table or
    index, and applies any matching change for the current entry. In
    the build phase, it uses only the before image, and skips any
    after image. Problems arose in some cases because generated
    columns were computed for the (skipped) after image, leading to
    replication errors. This is fixed by not computing generated
    columns any longer for seek-only calls such as hash scans.

    Our thanks to dc huang for the contribution.

Closed.