From d4bcc0f7c046d3a0532aaddf0c0c128cf9455517 Mon Sep 17 00:00:00 2001 From: "yishun.ys" Date: Mon, 3 Apr 2023 15:57:56 +0800 Subject: [PATCH] [Bugfix] BUG#109923 WRITESET tracking for parent table without PK Description =========== A foreign key relationship involves a parent table and a child table. If the parent table doesn't have primary key or unique key and its secondary index is referenced by child table, the last_committed of the child table's row would be smaller than it should be, causing SQL thread aborted when inserting rows based on WRITESET transaction dependency tracking. Analysis ======== Before a row is added to the writeset, it is first checked if there is a primary or unique key. If there is only non-unique key in parent table which is reference by child, MySQL will not check foreign key constraints. This leads to problems. Fix === After checking whether the table has a primary key, check the foreign key constraints separately in add_pke(). If the table has been referenced by child table, set 'm_has_related_foreign_keys' as true which will clean the writeset history in Writeset_trx_dependency_tracker::get_dependency(). --- ...racking_for_parent_table_without_pk.result | 47 +++++++++ ..._tracking_for_parent_table_without_pk.test | 97 +++++++++++++++++++ sql/rpl_write_set_handler.cc | 6 +- 3 files changed, 147 insertions(+), 3 deletions(-) create mode 100644 mysql-test/suite/rpl/r/rpl_writeset_tracking_for_parent_table_without_pk.result create mode 100644 mysql-test/suite/rpl/t/rpl_writeset_tracking_for_parent_table_without_pk.test diff --git a/mysql-test/suite/rpl/r/rpl_writeset_tracking_for_parent_table_without_pk.result b/mysql-test/suite/rpl/r/rpl_writeset_tracking_for_parent_table_without_pk.result new file mode 100644 index 00000000000..fbe672dc59b --- /dev/null +++ b/mysql-test/suite/rpl/r/rpl_writeset_tracking_for_parent_table_without_pk.result @@ -0,0 +1,47 @@ +include/master-slave.inc +[connection master] +# +############ 1. INITIALIZE ############ +# +SET @save_binlog_transaction_dependency_tracking = @@GLOBAL.binlog_transaction_dependency_tracking; +SET @save_transaction_write_set_extraction = @@SESSION.transaction_write_set_extraction; +SET SESSION transaction_write_set_extraction = XXHASH64; +SET GLOBAL binlog_transaction_dependency_tracking = WRITESET; +SHOW VARIABLES LIKE "%binlog_transaction_dependency_tracking%"; +Variable_name Value +binlog_transaction_dependency_tracking WRITESET +SHOW VARIABLES LIKE "%transaction_write_set_extraction%"; +Variable_name Value +transaction_write_set_extraction XXHASH64 +# +############ 2. CREATE TABLE and INSERT rows ############ +# +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; +INSERT INTO parent VALUES (1); +include/include/assert_logical_timestamps.inc [2 3] +INSERT INTO child VALUES (1, 1); +include/include/assert_logical_timestamps.inc [3 4] +include/rpl_sync.inc +INSERT INTO parent VALUES (2); +include/include/assert_logical_timestamps.inc [4 5] +INSERT INTO child VALUES (2, 2); +include/include/assert_logical_timestamps.inc [5 6] +include/rpl_sync.inc +# +############ CLEAN UP ############ +# +SET @@GLOBAL.binlog_transaction_dependency_tracking= @save_binlog_transaction_dependency_tracking; +SET @@SESSION.transaction_write_set_extraction = @save_transaction_write_set_extraction; +DROP TABLE child; +DROP TABLE parent; +include/rpl_end.inc diff --git a/mysql-test/suite/rpl/t/rpl_writeset_tracking_for_parent_table_without_pk.test b/mysql-test/suite/rpl/t/rpl_writeset_tracking_for_parent_table_without_pk.test new file mode 100644 index 00000000000..f8f917eabd4 --- /dev/null +++ b/mysql-test/suite/rpl/t/rpl_writeset_tracking_for_parent_table_without_pk.test @@ -0,0 +1,97 @@ +################################################################################ +# [Bugfix] BUG#109923 WRITESET tracking for parent table without PK +# +# Description +# =========== +# A foreign key relationship involves a parent table and a child table. +# If the parent table doesn't have primary key or unique key and its secondary +# index is referenced by child table, the last_committed of the child table's +# row would be smaller than it should be, causing SQL thread aborted when +# inserting rows based on WRITESET transaction dependency tracking. +# +# Analysis +# ======== +# Before a row is added to the writeset, it is first checked if there is a +# primary or unique key. If there is only non-unique key in parent table which +# is reference by child, MySQL will not check foreign key constraints. This +# leads to problems. +# +# Fix +# === +# After checking whether the table has a primary key, check the foreign key +# constraints separately in add_pke(). If the table has been referenced by +# child table, set 'm_has_related_foreign_keys' as true which will clean the +# writeset history in Writeset_trx_dependency_tracker::get_dependency(). +################################################################################ + +--disable_warnings +--source include/have_binlog_format_row.inc +--source include/master-slave.inc + +--echo # +--echo ############ 1. INITIALIZE ############ +--echo # + +SET @save_binlog_transaction_dependency_tracking = @@GLOBAL.binlog_transaction_dependency_tracking; +SET @save_transaction_write_set_extraction = @@SESSION.transaction_write_set_extraction; +SET SESSION transaction_write_set_extraction = XXHASH64; +SET GLOBAL binlog_transaction_dependency_tracking = WRITESET; +--enable_warnings + +SHOW VARIABLES LIKE "%binlog_transaction_dependency_tracking%"; +SHOW VARIABLES LIKE "%transaction_write_set_extraction%"; + +--echo # +--echo ############ 2. CREATE TABLE and INSERT rows ############ +--echo # + +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; + +INSERT INTO parent VALUES (1); +--let $binlog_file= master-bin.000001 +--let $logical_timestamps= 2 3 +--source include/assert_logical_timestamps.inc + +INSERT INTO child VALUES (1, 1); +--let $binlog_file= master-bin.000001 +--let $logical_timestamps= 3 4 +--source include/assert_logical_timestamps.inc +--source include/rpl_sync.inc + +--connection master +INSERT INTO parent VALUES (2); +--let $binlog_file= master-bin.000001 +--let $logical_timestamps= 4 5 +--source include/assert_logical_timestamps.inc + +INSERT INTO child VALUES (2, 2); +--let $binlog_file= master-bin.000001 +--let $logical_timestamps= 5 6 +--source include/assert_logical_timestamps.inc +--source include/rpl_sync.inc + +--echo # +--echo ############ CLEAN UP ############ +--echo # + +--connection master +--disable_warnings +SET @@GLOBAL.binlog_transaction_dependency_tracking= @save_binlog_transaction_dependency_tracking; +SET @@SESSION.transaction_write_set_extraction = @save_transaction_write_set_extraction; +--enable_warnings + +DROP TABLE child; +DROP TABLE parent; + +--source include/rpl_end.inc 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; } -- 2.19.1.6.gb485710b