From 2fbcddbeafb558720f4b74b0a8f68c18b48d9f2e Mon Sep 17 00:00:00 2001 From: sjaakola Date: Mon, 9 Nov 2020 12:41:52 +0200 Subject: MDEV-24119 MDL BF-BF Conflict caused by TRUNCATE TABLE MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit A follow-up fix, for original fix for MDEV-21577, which did not handle well temporary tables. OPTIMIZE and REPAIR TABLE statements can take a list of tables as argument, and some of the tables may be temporary. Proper handling of temporary tables is to skip them and continue working on the real tables. The bad version, skipped all tables, if a single temporary table was in the argument list. And this resulted so that FK parent tables were not scnanned for the remaining real tables. Some mtr tests, using OPTIMIZE or REPAIR for temporary tables caused regressions bacause of this, e.g. galera.galera_optimize_analyze_multi The fix in this PR opens temporary and real tables first, and in the table handling loop skips temporary tables, FK parent scanning is done only for real tables. The test has new scenario for OPTIMIZE and REPAIR issued for two tables of which one is temporary table. Reviewed-by: Jan Lindström --- .../suite/galera/r/galera_ddl_fk_conflict.result | 106 +++++++++++++++++++++ .../suite/galera/t/galera_ddl_fk_conflict.inc | 20 ++-- .../suite/galera/t/galera_ddl_fk_conflict.test | 2 + .../galera/t/galera_ddl_fk_conflict_with_tmp.inc | 69 ++++++++++++++ sql/wsrep_mysqld.cc | 35 ++++--- 5 files changed, 207 insertions(+), 25 deletions(-) create mode 100644 mysql-test/suite/galera/t/galera_ddl_fk_conflict_with_tmp.inc diff --git a/mysql-test/suite/galera/r/galera_ddl_fk_conflict.result b/mysql-test/suite/galera/r/galera_ddl_fk_conflict.result index f0d0d61e58f..5f09345b79b 100644 --- a/mysql-test/suite/galera/r/galera_ddl_fk_conflict.result +++ b/mysql-test/suite/galera/r/galera_ddl_fk_conflict.result @@ -104,6 +104,7 @@ connection node_1; BEGIN; connection node_1b; BEGIN; +connection node_1a; SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync'; connection node_2; OPTIMIZE TABLE c2 ; @@ -148,6 +149,58 @@ EXPECT_2 DROP TABLE c1, c2; DROP TABLE p1, p2; ###################################################################### +# Test for OPTIMIZE +###################################################################### +connection node_1; +SET SESSION wsrep_sync_wait=0; +CREATE TABLE p1 (pk INTEGER PRIMARY KEY, f2 CHAR(30)); +INSERT INTO p1 VALUES (1, 'INITIAL VALUE'); +CREATE TABLE c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk)); +INSERT INTO c1 VALUES (1,1); +###################################################################### +# +# Scenario #4: DML working on FK parent table tries to replicate, but +# fails in certification for earlier DDL on child table +# and another temporary table. TMP table should be skipped +# but FK child table should be replicated with proper keys +# +###################################################################### +connection node_1; +BEGIN; +SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync'; +connection node_2; +CREATE TEMPORARY TABLE tmp (i int); +OPTIMIZE TABLE c1, tmp ; +Table Op Msg_type Msg_text +test.c1 optimize note Table does not support optimize, doing recreate + analyze instead +test.c1 optimize status OK +test.tmp optimize note Table does not support optimize, doing recreate + analyze instead +test.tmp optimize status OK +DROP TABLE tmp; +connection node_1a; +SET SESSION wsrep_on = 0; +SET SESSION wsrep_on = 1; +SET GLOBAL wsrep_provider_options = 'dbug='; +connection node_1; +UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1; +COMMIT; +connection node_1a; +SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync'; +connection node_1; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +SELECT 'I deadlocked'; +I deadlocked +I deadlocked +SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE'; +EXPECT_1 +1 +connection node_2; +SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE'; +EXPECT_1 +1 +DROP TABLE c1; +DROP TABLE p1; +###################################################################### # Test for REPAIR ###################################################################### ###################################################################### @@ -243,6 +296,7 @@ connection node_1; BEGIN; connection node_1b; BEGIN; +connection node_1a; SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync'; connection node_2; REPAIR TABLE c2 ; @@ -286,6 +340,56 @@ EXPECT_2 DROP TABLE c1, c2; DROP TABLE p1, p2; ###################################################################### +# Test for REPAIR +###################################################################### +connection node_1; +SET SESSION wsrep_sync_wait=0; +CREATE TABLE p1 (pk INTEGER PRIMARY KEY, f2 CHAR(30)); +INSERT INTO p1 VALUES (1, 'INITIAL VALUE'); +CREATE TABLE c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk)); +INSERT INTO c1 VALUES (1,1); +###################################################################### +# +# Scenario #4: DML working on FK parent table tries to replicate, but +# fails in certification for earlier DDL on child table +# and another temporary table. TMP table should be skipped +# but FK child table should be replicated with proper keys +# +###################################################################### +connection node_1; +BEGIN; +SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync'; +connection node_2; +CREATE TEMPORARY TABLE tmp (i int); +REPAIR TABLE c1, tmp ; +Table Op Msg_type Msg_text +test.c1 repair note The storage engine for the table doesn't support repair +test.tmp repair note The storage engine for the table doesn't support repair +DROP TABLE tmp; +connection node_1a; +SET SESSION wsrep_on = 0; +SET SESSION wsrep_on = 1; +SET GLOBAL wsrep_provider_options = 'dbug='; +connection node_1; +UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1; +COMMIT; +connection node_1a; +SET GLOBAL wsrep_provider_options = 'signal=apply_monitor_slave_enter_sync'; +connection node_1; +ERROR 40001: Deadlock found when trying to get lock; try restarting transaction +SELECT 'I deadlocked'; +I deadlocked +I deadlocked +SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE'; +EXPECT_1 +1 +connection node_2; +SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE'; +EXPECT_1 +1 +DROP TABLE c1; +DROP TABLE p1; +###################################################################### # Test for ALTER ENGINE=INNODB ###################################################################### ###################################################################### @@ -377,6 +481,7 @@ connection node_1; BEGIN; connection node_1b; BEGIN; +connection node_1a; SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync'; connection node_2; ALTER TABLE c2 ENGINE=INNODB; @@ -509,6 +614,7 @@ connection node_1; BEGIN; connection node_1b; BEGIN; +connection node_1a; SET GLOBAL wsrep_provider_options = 'dbug=d,apply_monitor_slave_enter_sync'; connection node_2; TRUNCATE TABLE c2 ; diff --git a/mysql-test/suite/galera/t/galera_ddl_fk_conflict.inc b/mysql-test/suite/galera/t/galera_ddl_fk_conflict.inc index 4a473d3776c..06b7bbe41c4 100644 --- a/mysql-test/suite/galera/t/galera_ddl_fk_conflict.inc +++ b/mysql-test/suite/galera/t/galera_ddl_fk_conflict.inc @@ -62,6 +62,13 @@ UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1; --connection node_2 SET SESSION wsrep_sync_wait=0; +# wait for tables to be created in node 2 and all rows inserted as well +--let $wait_condition = SELECT COUNT(*) = 2 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/c%' +--source include/wait_condition.inc +--let $wait_condition = SELECT COUNT(*) = 2 FROM c2 +--source include/wait_condition.inc + +# replicate the DDL to be tested --eval $table_admin_command TABLE c1 $table_admin_command_end --connection node_1 @@ -102,12 +109,12 @@ UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1; --send COMMIT --connection node_1a ---let $galera_sync_point = apply_monitor_slave_enter_sync ---source include/galera_signal_sync_point.inc - --let $wait_condition = SELECT VARIABLE_VALUE = $expected_cert_failures FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_cert_failures' --source include/wait_condition.inc +--let $galera_sync_point = apply_monitor_slave_enter_sync +--source include/galera_signal_sync_point.inc + --connection node_1 --error ER_LOCK_DEADLOCK --reap @@ -136,6 +143,7 @@ BEGIN; --connection node_1b BEGIN; +--connection node_1a # Block the applier on node #1 and issue DDL on node 2 --let $galera_sync_point = apply_monitor_slave_enter_sync --source include/galera_set_sync_point.inc @@ -157,12 +165,12 @@ UPDATE p2 SET f2 = 'TO DEADLOCK' WHERE pk = 2; --send COMMIT --connection node_1a ---let $galera_sync_point = apply_monitor_slave_enter_sync ---source include/galera_signal_sync_point.inc - --let $wait_condition = SELECT VARIABLE_VALUE = $expected_cert_failures FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_cert_failures' --source include/wait_condition.inc +--let $galera_sync_point = apply_monitor_slave_enter_sync +--source include/galera_signal_sync_point.inc + --connection node_1 --error ER_LOCK_DEADLOCK --reap diff --git a/mysql-test/suite/galera/t/galera_ddl_fk_conflict.test b/mysql-test/suite/galera/t/galera_ddl_fk_conflict.test index c6d20e3bffd..88837933e5a 100644 --- a/mysql-test/suite/galera/t/galera_ddl_fk_conflict.test +++ b/mysql-test/suite/galera/t/galera_ddl_fk_conflict.test @@ -19,9 +19,11 @@ SET SESSION wsrep_sync_wait=0; --let $table_admin_command = OPTIMIZE --source galera_ddl_fk_conflict.inc +--source galera_ddl_fk_conflict_with_tmp.inc --let $table_admin_command = REPAIR --source galera_ddl_fk_conflict.inc +--source galera_ddl_fk_conflict_with_tmp.inc --let $table_admin_command = ALTER --let $table_admin_command_end = ENGINE=INNODB diff --git a/mysql-test/suite/galera/t/galera_ddl_fk_conflict_with_tmp.inc b/mysql-test/suite/galera/t/galera_ddl_fk_conflict_with_tmp.inc new file mode 100644 index 00000000000..acf3c54180b --- /dev/null +++ b/mysql-test/suite/galera/t/galera_ddl_fk_conflict_with_tmp.inc @@ -0,0 +1,69 @@ +--echo ###################################################################### +--echo # Test for $table_admin_command $table_admin_command_end +--echo ###################################################################### + + +--connection node_1 +SET SESSION wsrep_sync_wait=0; + +CREATE TABLE p1 (pk INTEGER PRIMARY KEY, f2 CHAR(30)); +INSERT INTO p1 VALUES (1, 'INITIAL VALUE'); + + +CREATE TABLE c1 (pk INTEGER PRIMARY KEY, fk INTEGER, FOREIGN KEY (fk) REFERENCES p1(pk)); +INSERT INTO c1 VALUES (1,1); + +--echo ###################################################################### +--echo # +--echo # Scenario #4: DML working on FK parent table tries to replicate, but +--echo # fails in certification for earlier DDL on child table +--echo # and another temporary table. TMP table should be skipped +--echo # but FK child table should be replicated with proper keys +--echo # +--echo ###################################################################### + +--connection node_1 +BEGIN; + +# Block the applier on node #1 and issue DDL on node 2 +--let $galera_sync_point = apply_monitor_slave_enter_sync +--source include/galera_set_sync_point.inc + +--connection node_2 +# wait for tables to be created in node 2 and all rows inserted as well +--let $wait_condition = SELECT COUNT(*) = 1 FROM INFORMATION_SCHEMA.INNODB_SYS_TABLES WHERE NAME LIKE 'test/c1' +--source include/wait_condition.inc +--let $wait_condition = SELECT COUNT(*) = 1 FROM c1 +--source include/wait_condition.inc +CREATE TEMPORARY TABLE tmp (i int); +--eval $table_admin_command TABLE c1, tmp $table_admin_command_end +DROP TABLE tmp; + +--connection node_1a +--source include/galera_wait_sync_point.inc +--source include/galera_clear_sync_point.inc +--let $expected_cert_failures = `SELECT VARIABLE_VALUE+1 FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_cert_failures'` + +--connection node_1 +UPDATE p1 SET f2 = 'TO DEADLOCK' WHERE pk = 1; +--send COMMIT + +--connection node_1a +--let $wait_condition = SELECT VARIABLE_VALUE = $expected_cert_failures FROM information_schema.global_status WHERE VARIABLE_NAME = 'wsrep_local_cert_failures' +--source include/wait_condition.inc + +--let $galera_sync_point = apply_monitor_slave_enter_sync +--source include/galera_signal_sync_point.inc + +--connection node_1 +--error ER_LOCK_DEADLOCK +--reap + +SELECT 'I deadlocked'; +SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE'; + +--connection node_2 +SELECT COUNT(*) AS EXPECT_1 FROM p1 WHERE f2 = 'INITIAL VALUE'; + +DROP TABLE c1; +DROP TABLE p1; diff --git a/sql/wsrep_mysqld.cc b/sql/wsrep_mysqld.cc index 304da7ec979..99a8105efd9 100644 --- a/sql/wsrep_mysqld.cc +++ b/sql/wsrep_mysqld.cc @@ -1191,30 +1191,27 @@ wsrep_append_fk_parent_table(THD* thd, TABLE_LIST* tables, wsrep::key_array* key uint counter; MDL_savepoint mdl_savepoint= thd->mdl_context.mdl_savepoint(); - bool open_error= - open_tables(thd, &tables, &counter, MYSQL_OPEN_FORCE_SHARED_HIGH_PRIO_MDL); - if (unlikely(open_error && (thd->killed || thd->is_error()))) + if (thd->open_temporary_tables(tables) || + open_tables(thd, &tables, &counter, MYSQL_OPEN_FORCE_SHARED_HIGH_PRIO_MDL)) { - WSREP_WARN("unable to open table for FK checks in OPTIMIZE/REPAIR/ALTER processing"); + WSREP_DEBUG("unable to open table for FK checks for %s", thd->query()); } - else + + for (table= tables; table; table= table->next_local) { - for (table= tables; table; table= table->next_local) + if (!is_temporary_table(table) && table->table) { - if (table->table) + FOREIGN_KEY_INFO *f_key_info; + List f_key_list; + + table->table->file->get_foreign_key_list(thd, &f_key_list); + List_iterator_fast it(f_key_list); + while ((f_key_info=it++)) { - FOREIGN_KEY_INFO *f_key_info; - List f_key_list; - - table->table->file->get_foreign_key_list(thd, &f_key_list); - List_iterator_fast it(f_key_list); - while ((f_key_info=it++)) - { - WSREP_DEBUG("appended fkey %s", f_key_info->referenced_table->str); - keys->push_back(wsrep_prepare_key_for_toi(f_key_info->referenced_db->str, - f_key_info->referenced_table->str, - wsrep::key::shared)); - } + WSREP_DEBUG("appended fkey %s", f_key_info->referenced_table->str); + keys->push_back(wsrep_prepare_key_for_toi(f_key_info->referenced_db->str, + f_key_info->referenced_table->str, + wsrep::key::shared)); } } } -- cgit v1.2.1