diff options
author | sjaakola <seppo.jaakola@iki.fi> | 2021-12-09 18:12:20 +0200 |
---|---|---|
committer | Jan Lindström <jan.lindstrom@mariadb.com> | 2021-12-17 09:38:23 +0200 |
commit | c1846c4fcfb04cb628c73b95516deefeb8ee0e19 (patch) | |
tree | 80e1f43a6e7becd22cc28b43b3ac6d603c9bf630 | |
parent | 20f22dfa2f2d5bb5a1511d42a5bb18fbf469e812 (diff) | |
download | mariadb-git-c1846c4fcfb04cb628c73b95516deefeb8ee0e19.tar.gz |
MDEV-26803 PA unsafety with FK cascade delete operation
This commit has a mtr test where two two transactions delete a row from
two separate tables, which will cascade a FK delete for the same row in
a third table. Second replica node is configured with 2 applier threads,
and the test will fail if these two transactions are applied in parallel.
The actual fix, in this commit, is to mark a transaction as unsafe for
parallel applying when it traverses into cascade delete operation.
Reviewed-by: Jan Lindström <jan.lindstrom@mariadb.com>
-rw-r--r-- | include/mysql/service_wsrep.h | 4 | ||||
-rw-r--r-- | mysql-test/suite/galera/r/galera_fk_cascade_delete.result | 110 | ||||
-rw-r--r-- | mysql-test/suite/galera/t/galera_fk_cascade_delete.test | 186 | ||||
-rw-r--r-- | sql/service_wsrep.cc | 8 | ||||
-rw-r--r-- | sql/sql_plugin_services.ic | 3 | ||||
-rw-r--r-- | sql/wsrep_dummy.cc | 4 | ||||
-rw-r--r-- | sql/wsrep_thd.h | 2 | ||||
-rw-r--r-- | storage/innobase/handler/ha_innodb.cc | 29 | ||||
-rw-r--r-- | storage/innobase/row/row0ins.cc | 39 |
9 files changed, 354 insertions, 31 deletions
diff --git a/include/mysql/service_wsrep.h b/include/mysql/service_wsrep.h index 10c70c790e9..1e6aaa1b9b9 100644 --- a/include/mysql/service_wsrep.h +++ b/include/mysql/service_wsrep.h @@ -88,6 +88,7 @@ extern struct wsrep_service_st { unsigned long long trx_id); void (*wsrep_thd_kill_LOCK_func)(const MYSQL_THD thd); void (*wsrep_thd_kill_UNLOCK_func)(const MYSQL_THD thd); + void (*wsrep_thd_set_wsrep_PA_unsafe_func)(MYSQL_THD thd); } *wsrep_service; #define MYSQL_SERVICE_WSREP_INCLUDED @@ -131,6 +132,7 @@ extern struct wsrep_service_st { #define wsrep_thd_is_applying(T) wsrep_service->wsrep_thd_is_applying_func(T) #define wsrep_thd_set_wsrep_aborter(T) wsrep_service->wsrep_thd_set_wsrep_aborter_func(T1, T2) #define wsrep_report_bf_lock_wait(T,I) wsrep_service->wsrep_report_bf_lock_wait(T,I) +#define wsrep_thd_set_PA_unsafe(T) wsrep_service->wsrep_thd_set_PA_unsafe_func(T) #else #define MYSQL_SERVICE_WSREP_STATIC_INCLUDED @@ -229,5 +231,7 @@ extern "C" my_bool wsrep_thd_is_applying(const MYSQL_THD thd); extern "C" bool wsrep_thd_set_wsrep_aborter(MYSQL_THD bf_thd, MYSQL_THD victim_thd); extern "C" void wsrep_report_bf_lock_wait(const THD *thd, unsigned long long trx_id); +/* declare parallel applying unsafety for the THD */ +extern "C" void wsrep_thd_set_PA_unsafe(MYSQL_THD thd); #endif #endif /* MYSQL_SERVICE_WSREP_INCLUDED */ diff --git a/mysql-test/suite/galera/r/galera_fk_cascade_delete.result b/mysql-test/suite/galera/r/galera_fk_cascade_delete.result index 808e32b8cb2..9bb004d0ed8 100644 --- a/mysql-test/suite/galera/r/galera_fk_cascade_delete.result +++ b/mysql-test/suite/galera/r/galera_fk_cascade_delete.result @@ -48,3 +48,113 @@ id parent_id DROP TABLE child; DROP TABLE parent; DROP TABLE grandparent; + +Scenario 2, testing PA applying with FK cascade delete + +CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB; +CREATE TABLE p2 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB; +CREATE TABLE c (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER, +f2 INTEGER, +CONSTRAINT fk_1 FOREIGN KEY (p1_id) REFERENCES p1 (f1) +ON DELETE CASCADE, +CONSTRAINT fk_2 FOREIGN KEY (p2_id) REFERENCES p2 (f1) +ON DELETE CASCADE); +connection node_2; +set global wsrep_slave_threads=DEFAULT; +SELECT * FROM p1; +f1 f2 +SELECT * FROM p2; +f1 f2 +SELECT * FROM c; +f1 p1_id p2_id f2 +connection node_1; +DROP TABLE c; +DROP TABLE p1,p2; + +Scenario 4, testing PA applying with FK cascade delete on +more than one level + +CREATE TABLE gp1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB; +CREATE TABLE gp2 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB; +CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER, +f2 INTEGER, +CONSTRAINT pfk_3 FOREIGN KEY (p1_id) REFERENCES gp1 (f1) +ON DELETE CASCADE +) ENGINE=INNODB; +CREATE TABLE p2 (f1 INTEGER PRIMARY KEY,p1_id INTEGER, p2_id INTEGER, +f2 INTEGER, +CONSTRAINT pfk_4 FOREIGN KEY (p1_id) REFERENCES gp2 (f1) +ON DELETE CASCADE +) ENGINE=INNODB; +CREATE TABLE c (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER, +f2 INTEGER, +CONSTRAINT fk_1 FOREIGN KEY (p1_id) REFERENCES p1 (f1) +ON DELETE CASCADE, +CONSTRAINT fk_2 FOREIGN KEY (p2_id) REFERENCES p2 (f1) +ON DELETE CASCADE) ENGINE=INNODB; +connection node_2; +set global wsrep_slave_threads=DEFAULT; +SELECT * FROM gp1; +f1 f2 +SELECT * FROM gp2; +f1 f2 +SELECT * FROM p1; +f1 p1_id p2_id f2 +SELECT * FROM p2; +f1 p1_id p2_id f2 +SELECT * FROM c; +f1 p1_id p2_id f2 +connection node_1; +DROP TABLE c; +DROP TABLE p1,p2; +DROP TABLE gp1,gp2; + +Scenario 3, testing PA applying with FK cascade delete on +more than one level in a diamond topology + +CREATE TABLE ggp1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB; +CREATE TABLE gp1 (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER, +f2 INTEGER, +CONSTRAINT pfk_6 FOREIGN KEY (p1_id) REFERENCES ggp1 (f1) +ON DELETE CASCADE +) ENGINE=INNODB; +CREATE TABLE gp2 (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER, +f2 INTEGER, +CONSTRAINT pfk_5 FOREIGN KEY (p1_id) REFERENCES ggp1 (f1) +ON DELETE CASCADE +) ENGINE=INNODB; +CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER, +f2 INTEGER, +CONSTRAINT pfk_3 FOREIGN KEY (p1_id) REFERENCES gp1 (f1) +ON DELETE CASCADE +) ENGINE=INNODB; +CREATE TABLE p2 (f1 INTEGER PRIMARY KEY,p1_id INTEGER, p2_id INTEGER, +f2 INTEGER, +CONSTRAINT pfk_4 FOREIGN KEY (p1_id) REFERENCES gp2 (f1) +ON DELETE CASCADE +) ENGINE=INNODB; +CREATE TABLE c (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER, +f2 INTEGER, +CONSTRAINT fk_1 FOREIGN KEY (p1_id) REFERENCES p1 (f1) +ON DELETE CASCADE, +CONSTRAINT fk_2 FOREIGN KEY (p2_id) REFERENCES p2 (f1) +ON DELETE CASCADE) ENGINE=INNODB; +connection node_2; +set global wsrep_slave_threads=DEFAULT; +SELECT * FROM ggp1; +f1 f2 +SELECT * FROM gp2; +f1 p1_id p2_id f2 +SELECT * FROM gp1; +f1 p1_id p2_id f2 +SELECT * FROM p1; +f1 p1_id p2_id f2 +SELECT * FROM p2; +f1 p1_id p2_id f2 +SELECT * FROM c; +f1 p1_id p2_id f2 +connection node_1; +DROP TABLE c; +DROP TABLE p1,p2; +DROP TABLE gp1,gp2; +DROP TABLE ggp1; diff --git a/mysql-test/suite/galera/t/galera_fk_cascade_delete.test b/mysql-test/suite/galera/t/galera_fk_cascade_delete.test index 49b54f0f7f0..901fc1fc6d1 100644 --- a/mysql-test/suite/galera/t/galera_fk_cascade_delete.test +++ b/mysql-test/suite/galera/t/galera_fk_cascade_delete.test @@ -68,3 +68,189 @@ SELECT * FROM child; DROP TABLE child; DROP TABLE parent; DROP TABLE grandparent; + +--echo +--echo Scenario 2, testing PA applying with FK cascade delete +--echo + +CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB; +CREATE TABLE p2 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB; +CREATE TABLE c (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER, + f2 INTEGER, + CONSTRAINT fk_1 FOREIGN KEY (p1_id) REFERENCES p1 (f1) + ON DELETE CASCADE, + CONSTRAINT fk_2 FOREIGN KEY (p2_id) REFERENCES p2 (f1) + ON DELETE CASCADE); + +--let $count = 100 +--disable_query_log +while ($count) +{ + --eval INSERT INTO p1 VALUES ($count, 0); + --eval INSERT INTO p2 VALUES ($count, 0); + --eval INSERT INTO c VALUES ($count, $count, $count, 0); + --dec $count +} + +--connection node_2 +set global wsrep_slave_threads=2; + +--connection node_1 +--let $count = 100 +while ($count) +{ + --eval DELETE FROM p2 WHERE f1=$count; + --eval DELETE FROM p1 WHERE f1=$count; + +--dec $count +} +--enable_query_log + +--connection node_2 +set global wsrep_slave_threads=DEFAULT; + + +SELECT * FROM p1; +SELECT * FROM p2; +SELECT * FROM c; + +--connection node_1 +DROP TABLE c; +DROP TABLE p1,p2; + +--echo +--echo Scenario 4, testing PA applying with FK cascade delete on +--echo more than one level +--echo +CREATE TABLE gp1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB; +CREATE TABLE gp2 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB; +CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER, + f2 INTEGER, + CONSTRAINT pfk_3 FOREIGN KEY (p1_id) REFERENCES gp1 (f1) + ON DELETE CASCADE + ) ENGINE=INNODB; +CREATE TABLE p2 (f1 INTEGER PRIMARY KEY,p1_id INTEGER, p2_id INTEGER, + f2 INTEGER, + CONSTRAINT pfk_4 FOREIGN KEY (p1_id) REFERENCES gp2 (f1) + ON DELETE CASCADE + ) ENGINE=INNODB; +CREATE TABLE c (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER, + f2 INTEGER, + CONSTRAINT fk_1 FOREIGN KEY (p1_id) REFERENCES p1 (f1) + ON DELETE CASCADE, + CONSTRAINT fk_2 FOREIGN KEY (p2_id) REFERENCES p2 (f1) + ON DELETE CASCADE) ENGINE=INNODB; + +--let $count = 100 +--disable_query_log +while ($count) +{ + --eval INSERT INTO gp1 VALUES ($count, 0); + --eval INSERT INTO gp2 VALUES ($count, 0); + --eval INSERT INTO p1 VALUES ($count, $count, $count, 0); + --eval INSERT INTO p2 VALUES ($count, $count, $count, 0); + --eval INSERT INTO c VALUES ($count, $count, $count, 0); + --dec $count +} + +--connection node_2 +set global wsrep_slave_threads=2; + +--connection node_1 +--let $count = 100 +while ($count) +{ + --eval DELETE FROM gp1 WHERE f1=$count; + --eval DELETE FROM gp2 WHERE f1=$count; + +--dec $count +} +--enable_query_log + +--connection node_2 +set global wsrep_slave_threads=DEFAULT; + +SELECT * FROM gp1; +SELECT * FROM gp2; +SELECT * FROM p1; +SELECT * FROM p2; +SELECT * FROM c; + +--connection node_1 +DROP TABLE c; +DROP TABLE p1,p2; +DROP TABLE gp1,gp2; + +--echo +--echo Scenario 3, testing PA applying with FK cascade delete on +--echo more than one level in a diamond topology +--echo +CREATE TABLE ggp1 (f1 INTEGER PRIMARY KEY, f2 INTEGER) ENGINE=INNODB; +CREATE TABLE gp1 (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER, + f2 INTEGER, + CONSTRAINT pfk_6 FOREIGN KEY (p1_id) REFERENCES ggp1 (f1) + ON DELETE CASCADE + ) ENGINE=INNODB; +CREATE TABLE gp2 (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER, + f2 INTEGER, + CONSTRAINT pfk_5 FOREIGN KEY (p1_id) REFERENCES ggp1 (f1) + ON DELETE CASCADE + ) ENGINE=INNODB; +CREATE TABLE p1 (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER, + f2 INTEGER, + CONSTRAINT pfk_3 FOREIGN KEY (p1_id) REFERENCES gp1 (f1) + ON DELETE CASCADE + ) ENGINE=INNODB; +CREATE TABLE p2 (f1 INTEGER PRIMARY KEY,p1_id INTEGER, p2_id INTEGER, + f2 INTEGER, + CONSTRAINT pfk_4 FOREIGN KEY (p1_id) REFERENCES gp2 (f1) + ON DELETE CASCADE + ) ENGINE=INNODB; +CREATE TABLE c (f1 INTEGER PRIMARY KEY, p1_id INTEGER, p2_id INTEGER, + f2 INTEGER, + CONSTRAINT fk_1 FOREIGN KEY (p1_id) REFERENCES p1 (f1) + ON DELETE CASCADE, + CONSTRAINT fk_2 FOREIGN KEY (p2_id) REFERENCES p2 (f1) + ON DELETE CASCADE) ENGINE=INNODB; + +--let $count = 100 +--disable_query_log +while ($count) +{ + --eval INSERT INTO ggp1 VALUES ($count, 0); + --eval INSERT INTO gp1 VALUES ($count, $count, $count, 0); + --eval INSERT INTO gp2 VALUES ($count, $count, $count, 0); + --eval INSERT INTO p1 VALUES ($count, $count, $count, 0); + --eval INSERT INTO p2 VALUES ($count, $count, $count, 0); + --eval INSERT INTO c VALUES ($count, $count, $count, 0); + --dec $count +} + +--connection node_2 +set global wsrep_slave_threads=2; + +--connection node_1 +--let $count = 100 +while ($count) +{ + --eval DELETE FROM ggp1 WHERE f1=$count; + +--dec $count +} +--enable_query_log + +--connection node_2 +set global wsrep_slave_threads=DEFAULT; + +SELECT * FROM ggp1; +SELECT * FROM gp2; +SELECT * FROM gp1; +SELECT * FROM p1; +SELECT * FROM p2; +SELECT * FROM c; + +--connection node_1 +DROP TABLE c; +DROP TABLE p1,p2; +DROP TABLE gp1,gp2; +DROP TABLE ggp1; diff --git a/sql/service_wsrep.cc b/sql/service_wsrep.cc index 18067a4d0ec..fd2a5c301f2 100644 --- a/sql/service_wsrep.cc +++ b/sql/service_wsrep.cc @@ -381,3 +381,11 @@ extern "C" void wsrep_report_bf_lock_wait(const THD *thd, wsrep_thd_query(thd)); } } + +extern "C" void wsrep_thd_set_PA_unsafe(THD *thd) +{ + if (thd && thd->wsrep_cs().mark_transaction_pa_unsafe()) + { + WSREP_DEBUG("session does not have active transaction, can not mark as PA unsafe"); + } +} diff --git a/sql/sql_plugin_services.ic b/sql/sql_plugin_services.ic index 740569dc76e..60ba38eae61 100644 --- a/sql/sql_plugin_services.ic +++ b/sql/sql_plugin_services.ic @@ -176,7 +176,8 @@ static struct wsrep_service_st wsrep_handler = { wsrep_thd_set_wsrep_aborter, wsrep_report_bf_lock_wait, wsrep_thd_kill_LOCK, - wsrep_thd_kill_UNLOCK + wsrep_thd_kill_UNLOCK, + wsrep_thd_set_PA_unsafe }; static struct thd_specifics_service_st thd_specifics_handler= diff --git a/sql/wsrep_dummy.cc b/sql/wsrep_dummy.cc index 129df8e1577..83bf4778d10 100644 --- a/sql/wsrep_dummy.cc +++ b/sql/wsrep_dummy.cc @@ -147,3 +147,7 @@ bool wsrep_thd_set_wsrep_aborter(THD*, THD*) void wsrep_report_bf_lock_wait(const THD*, unsigned long long) {} + +void wsrep_thd_set_PA_unsafe(THD*) +{} + diff --git a/sql/wsrep_thd.h b/sql/wsrep_thd.h index 560dbbdab44..40e5ed98e9b 100644 --- a/sql/wsrep_thd.h +++ b/sql/wsrep_thd.h @@ -90,8 +90,6 @@ void wsrep_create_rollbacker(); bool wsrep_bf_abort(THD* bf_thd, THD* victim_thd); int wsrep_abort_thd(THD *bf_thd_ptr, THD *victim_thd_ptr, my_bool signal); -extern void wsrep_thd_set_PA_safe(void *thd_ptr, my_bool safe); - /* Helper methods to deal with thread local storage. The purpose of these methods is to hide the details of thread diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc index 23df38c64ab..257074ae56d 100644 --- a/storage/innobase/handler/ha_innodb.cc +++ b/storage/innobase/handler/ha_innodb.cc @@ -10220,12 +10220,20 @@ wsrep_append_foreign_key( dict_foreign_t* foreign, /*!< in: foreign key constraint */ const rec_t* rec, /*!<in: clustered index record */ dict_index_t* index, /*!<in: clustered index */ - ibool referenced, /*!<in: is check for referenced table */ + bool referenced, /*!<in: is check for + referenced table */ + upd_node_t* upd_node, /*<!in: update node */ + bool pa_disable, /*<!in: disable parallel apply ?*/ Wsrep_service_key_type key_type) /*!< in: access type of this key (shared, exclusive, reference...) */ { - if (!trx->is_wsrep() || !wsrep_thd_is_local(trx->mysql_thd)) { + ut_ad(trx->is_wsrep()); + + if (!wsrep_thd_is_local(trx->mysql_thd)) return DB_SUCCESS; + + if (upd_node && wsrep_protocol_version < 4) { + key_type = WSREP_SERVICE_KEY_SHARED; } THD* thd = trx->mysql_thd; @@ -10286,8 +10294,7 @@ wsrep_append_foreign_key( WSREP_WARN("FK: %s missing in query: %s", (!foreign->referenced_table) ? "referenced table" : "foreign table", - (wsrep_thd_query(thd)) ? - wsrep_thd_query(thd) : "void"); + wsrep_thd_query(thd)); return DB_ERROR; } @@ -10365,20 +10372,24 @@ wsrep_append_foreign_key( wkey_part, (size_t*)&wkey.key_parts_num)) { WSREP_WARN("key prepare failed for cascaded FK: %s", - (wsrep_thd_query(thd)) ? - wsrep_thd_query(thd) : "void"); + wsrep_thd_query(thd)); return DB_ERROR; } + rcode = wsrep_thd_append_key(thd, &wkey, 1, key_type); + if (rcode) { - DBUG_PRINT("wsrep", ("row key failed: " ULINTPF, rcode)); WSREP_ERROR("Appending cascaded fk row key failed: %s, " ULINTPF, - (wsrep_thd_query(thd)) ? - wsrep_thd_query(thd) : "void", rcode); + wsrep_thd_query(thd), + rcode); return DB_ERROR; } + if (pa_disable) { + wsrep_thd_set_PA_unsafe(trx->mysql_thd); + } + return DB_SUCCESS; } diff --git a/storage/innobase/row/row0ins.cc b/storage/innobase/row/row0ins.cc index a63dd90e9c2..a1f5e55d8f4 100644 --- a/storage/innobase/row/row0ins.cc +++ b/storage/innobase/row/row0ins.cc @@ -970,7 +970,9 @@ dberr_t wsrep_append_foreign_key(trx_t *trx, dict_foreign_t* foreign, const rec_t* clust_rec, dict_index_t* clust_index, - ibool referenced, + bool referenced, + upd_node_t* upd_node, + bool pa_disable, Wsrep_service_key_type key_type); #endif /* WITH_WSREP */ @@ -1322,11 +1324,13 @@ row_ins_foreign_check_on_constraint( } #ifdef WITH_WSREP - err = wsrep_append_foreign_key(trx, foreign, clust_rec, clust_index, - FALSE, WSREP_SERVICE_KEY_EXCLUSIVE); - if (err != DB_SUCCESS) { - ib::info() << "WSREP: foreign key append failed: " << err; - goto nonstandard_exit_func; + if (trx->is_wsrep()) { + err = wsrep_append_foreign_key(trx, foreign, clust_rec, clust_index, + false, NULL, true, + WSREP_SERVICE_KEY_EXCLUSIVE); + if (err != DB_SUCCESS) { + goto nonstandard_exit_func; + } } #endif /* WITH_WSREP */ mtr_commit(mtr); @@ -1722,19 +1726,16 @@ row_ins_check_foreign_constraint( if (check_ref) { err = DB_SUCCESS; #ifdef WITH_WSREP - err = wsrep_append_foreign_key( - thr_get_trx(thr), - foreign, - rec, - check_index, - check_ref, - (upd_node != NULL - && wsrep_protocol_version < 4) - ? WSREP_SERVICE_KEY_SHARED - : WSREP_SERVICE_KEY_REFERENCE); - if (err != DB_SUCCESS) { - fprintf(stderr, - "WSREP: foreign key append failed: %d\n", err); + if (trx->is_wsrep()) { + err = wsrep_append_foreign_key( + thr_get_trx(thr), + foreign, + rec, + check_index, + check_ref, + upd_node, + false, + WSREP_SERVICE_KEY_REFERENCE); } #endif /* WITH_WSREP */ goto end_scan; |