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;
}
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; }