Bug #109923 Writeset-based Dependency Tracking will cause error HA_ERR_NO_REFERENCED_ROW.

Description:
The parent table has no primary key, unique key, only secondary index.
The child table has a primary key, and the foreign key reference to the secondary index of the parent table.
When inserting rows, the last_committed of the child table's row will be smaller than it should be, causing SQL thread aborted.

How to repeat:
=============================================================================
1. SQL
=============================================================================

SET GLOBAL binlog_transaction_dependency_tracking = COMMIT_ORDER;
SET GLOBAL transaction_write_set_extraction = XXHASH64;
SET SESSION transaction_write_set_extraction = XXHASH64;
SET GLOBAL binlog_transaction_dependency_tracking = WRITESET;

create database test;
use test;

CREATE TABLE parent (
    did INT,
    KEY (did)
) ENGINE=INNODB;

CREATE TABLE child (
    id INT PRIMARY KEY,
    parent_id INT,
    INDEX par_ind (parent_id),
    FOREIGN KEY (parent_id)
        REFERENCES parent(did)
) ENGINE=INNODB;

RESET MASTER;

INSERT INTO parent VALUES (1);

INSERT INTO child VALUES (1, 1);

INSERT INTO parent VALUES (2);

INSERT INTO child VALUES (2, 2);

=============================================================================
2. Binary log
=============================================================================

#230203 22:04:30 server id 1  end_log_pos 236 CRC32 0x597571ab 	GTID	last_committed=0	sequence_number=1	rbr_only=yes	original_committed_timestamp=1675433070628397	immediate_commit_timestamp=1675433070628397	transaction_length=279
#230203 22:04:30 server id 1  end_log_pos 515 CRC32 0x26d1be0b 	GTID	last_committed=1	sequence_number=2	rbr_only=yes	original_committed_timestamp=1675433070636099	immediate_commit_timestamp=1675433070636099	transaction_length=281
#230203 22:04:30 server id 1  end_log_pos 796 CRC32 0x6a2d870c 	GTID	last_committed=2	sequence_number=3	rbr_only=yes	original_committed_timestamp=1675433070638930	immediate_commit_timestamp=1675433070638930	transaction_length=279
#230203 22:04:30 server id 1  end_log_pos 1075 CRC32 0xdff67731 	GTID	last_committed=1	sequence_number=4	rbr_only=yes	original_committed_timestamp=1675433070882021	immediate_commit_timestamp=1675433070882021	transaction_length=281

=============================================================================
3. Analysis
=============================================================================

'INSERT INTO child VALUES (2, 2);' Its 'last_committed' is equal to 1 which should greater than 3.

=============================================================================
4. SQL thread Error

Note: This error requires inserting multiple rows quickly.
=============================================================================

Last_SQL_Errno: 1452
Last_SQL_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 2 failed executing transaction '029dde77-7630-11ed-bfda-b8599f2ff098:6' at master log mysql-bin.000001, end_log_pos 1811. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.

================
master-error.log
================

[ERROR] [MY-010584] [Repl] Slave SQL for channel '': Worker 2 failed executing transaction '029dde77-7630-11ed-bfda-b8599f2ff098:6' at master log mysql-bin.000001, end_log_pos 1811; Could not execute Write_rows event on table test.child; Cannot add or update a child row: a foreign key constraint fails (`test`.`child`, CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENC
ES `parent` (`did`)), Error_code: 1452; handler error HA_ERR_NO_REFERENCED_ROW; the event's master log mysql-bin.000001, end_log_pos 1811
, Error_code: MY-001452

Suggested fix:
diff --git a/sql/rpl_write_set_handler.cc b/sql/rpl_write_set_handler.cc
index 33a9049414e..e3656f0f65a 100644
--- a/sql/rpl_write_set_handler.cc
+++ b/sql/rpl_write_set_handler.cc
@@ -776,14 +776,14 @@ bool add_pke(TABLE *table, THD *thd, const uchar *record) {
       }
     }
 
-    if (table->s->foreign_key_parents > 0)
-      ws_ctx->set_has_related_foreign_keys();
-
 #ifndef NDEBUG
     debug_check_for_write_sets(write_sets, hash_list);
 #endif
   }
 
+  if (table->s->foreign_key_parents > 0)
+    ws_ctx->set_has_related_foreign_keys();
+
   if (!writeset_hashes_added) ws_ctx->set_has_missing_keys();
   return false;
 }

Hi,

Thank you for the case and the patch. I think this might be a duplicate of Bug #97836 but I am not 100% sure. You can try the workaround from there.

Thank you very much for your patch contribution, we appreciate it!

In order for us to continue the process of reviewing your contribution to MySQL, please send us a signed copy of the Oracle Contributor Agreement (OCA) as outlined in https://oca.opensource.oracle.com

Signing an OCA needs to be done only once and it's valid for all other Oracle governed Open Source projects as well.

Getting a signed/approved OCA on file will help us facilitate your contribution - this one, and others in the future.  

Please let me know, if you have any questions.

Thank you for your interest in MySQL.

Hi!

This case is different with Bug #97836.

This case's parent table has no primary key, unique key while Bug #97836 's parent table has which means the solution is different.

[Bugfix] BUG#109923 WRITESET tracking for parent table without PK

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

Contribution: Bugfix-BUG-109923-WRITESET-tracking-for-parent-table.patch (application/octet-stream, text), 7.72 KiB.