I’m experiencing an issue with AWS MySQL RDS Read Replica syncing data with the primary instance. I have a stored procedure that inserts data into a table. While the INSERT operation appears to work and the data is properly inserted, the binary log seems to record the INSERT statement incorrectly.

Below is the stored procedure. It takes a JSON string containing an array of items as the first parameter and an order object as the second parameter. The items are inserted into the “Items” table, while the order object is inserted into the “Orders” table.

CREATE PROCEDURE `InsertOrder`(items_array LONGTEXT, ord_obj TEXT)
BEGIN
    DECLARE items, item LONGTEXT;
    DECLARE i INT DEFAULT 0;
    DECLARE rowCount INT DEFAULT 0;

    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        GET DIAGNOSTICS CONDITION 1
        @p1 = RETURNED_SQLSTATE, @p2 = MESSAGE_TEXT;
        SELECT @p1 AS ecode, @p2 AS message;
        ROLLBACK;
    END;

    START TRANSACTION;

    SELECT items_array INTO items;

    WHILE i < JSON_LENGTH(items) DO
        SELECT JSON_EXTRACT(items, CONCAT('$[', i, ']')) INTO item;

        INSERT INTO Item(ID, SnapShot, Price, Name, OrderID) 
        VALUES (
            item->>"$.ID",
            NULLIF(item->>"$.SnapShot", ''),
            item->>"$.Price",
            item->>"$.Name",
            item->>"$.OrderID"
        );

        SELECT i + 1 INTO i;
    END WHILE;

    INSERT INTO Orders(ID, CreatedDate, IsActive, TotalPrice, Coupons) 
    VALUES (
        ord_obj->>"$.ID",
        ord_obj->>"$.CreatedDate",
        ord_obj->>"$.IsActive",
        ord_obj->>"$.TotalPrice",
        ord_obj->>"$.Coupons"
    );

    COMMIT;

    SELECT rowCount;

END

However, the binary log shows the following incorrect INSERT statement:

INSERT INTO Item(ID, SnapShot, Price, Name, OrderID) 
VALUES (NAME_CONST('item', _utf8mb4'{"ID": "1818"}' COLLATE 'utf8mb4_unicode_520_ci')->>"$.ID", NULLIF(...))

It seems like the statement is treating each column’s value as a separate row, which is causing the replication server to stop syncing data. The replication server shows error 1064: “You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘->>”$.ID” at line 1.”

The binary log format is set to Mixed.

Could anyone identify the potential issues in my Stored Procedure or suggest troubleshooting steps to ensure the binary log records the INSERT statement properly?

Any help would be appreciated. Thanks in advance!