From 88a4be75a5f3b8d59ac8f6347ff2c197813c05dc Mon Sep 17 00:00:00 2001 From: sjaakola Date: Wed, 15 Sep 2021 09:16:44 +0300 Subject: MDEV-25114 Crash: WSREP: invalid state ROLLED_BACK (FATAL) MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: 8bit This patch is the plan D variant for fixing potetial mutex locking order exercised by BF aborting and KILL command execution. In this approach, KILL command is replicated as TOI operation. This guarantees total isolation for the KILL command execution in the first node: there is no concurrent replication applying and no concurrent DDL executing. Therefore there is no risk of BF aborting to happen in parallel with KILL command execution either. Potential mutex deadlocks between the different mutex access paths with KILL command execution and BF aborting cannot therefore happen. TOI replication is used, in this approach, purely as means to provide isolated KILL command execution in the first node. KILL command should not (and must not) be applied in secondary nodes. In this patch, we make this sure by skipping KILL execution in secondary nodes, in applying phase, where we bail out if applier thread is trying to execute KILL command. This is effective, but skipping the applying of KILL command could happen much earlier as well. This patch also fixes mutex locking order and unprotected THD member accesses on bf aborting case. We try to hold THD::LOCK_thd_data during bf aborting. Only case where it is not possible is at wsrep_abort_transaction before call wsrep_innobase_kill_one_trx where we take InnoDB mutexes first and then THD::LOCK_thd_data. This will also fix possible race condition during close_connection and while wsrep is disconnecting connections. Added wsrep_bf_kill_debug test case Reviewed-by: Jan Lindström --- .../suite/galera/r/galera_UK_conflict.result | 7 + .../suite/galera/r/galera_bf_kill_debug.result | 163 ++++++++++++ .../suite/galera/r/galera_toi_ddl_fk_insert.result | 16 -- mysql-test/suite/galera/t/galera_UK_conflict.test | 4 + mysql-test/suite/galera/t/galera_bf_kill_debug.cnf | 9 + .../suite/galera/t/galera_bf_kill_debug.test | 283 +++++++++++++++++++++ .../suite/galera/t/galera_toi_ddl_fk_insert.test | 14 +- mysql-test/suite/wsrep/t/variables.test | 6 +- 8 files changed, 474 insertions(+), 28 deletions(-) create mode 100644 mysql-test/suite/galera/r/galera_bf_kill_debug.result create mode 100644 mysql-test/suite/galera/t/galera_bf_kill_debug.cnf create mode 100644 mysql-test/suite/galera/t/galera_bf_kill_debug.test (limited to 'mysql-test') diff --git a/mysql-test/suite/galera/r/galera_UK_conflict.result b/mysql-test/suite/galera/r/galera_UK_conflict.result index 76649f1b268..2795a86d6a6 100644 --- a/mysql-test/suite/galera/r/galera_UK_conflict.result +++ b/mysql-test/suite/galera/r/galera_UK_conflict.result @@ -68,6 +68,9 @@ f1 f2 f3 10 10 0 INSERT INTO t1 VALUES (7,7,7); INSERT INTO t1 VALUES (8,8,8); +SELECT COUNT(*) FROM t1; +COUNT(*) +7 SELECT * FROM t1; f1 f2 f3 1 1 0 @@ -78,6 +81,9 @@ f1 f2 f3 8 8 8 10 10 0 connection node_1; +SELECT COUNT(*) FROM t1; +COUNT(*) +7 SELECT * FROM t1; f1 f2 f3 1 1 0 @@ -85,5 +91,6 @@ f1 f2 f3 4 4 2 5 5 2 7 7 7 +8 8 8 10 10 0 DROP TABLE t1; diff --git a/mysql-test/suite/galera/r/galera_bf_kill_debug.result b/mysql-test/suite/galera/r/galera_bf_kill_debug.result new file mode 100644 index 00000000000..c20bb80131a --- /dev/null +++ b/mysql-test/suite/galera/r/galera_bf_kill_debug.result @@ -0,0 +1,163 @@ +# +# Case 1: We execute bf kill to wsrep_innobase_kill_one_trx +# function just before wsrep_thd_LOCK(thd) call. Then we +# try to kill victim transaction by KILL QUERY +# +CREATE TABLE t1(id int not null primary key, b int) engine=innodb; +INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5); +connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; +begin; +update t1 set b = b * 10 where id between 2 and 4; +connect node_1b, 127.0.0.1, root, , test, $NODE_MYPORT_1; +connect node_1c, 127.0.0.1, root, , test, $NODE_MYPORT_1; +SET DEBUG_SYNC='wsrep_before_BF_victim_lock SIGNAL bf_kill WAIT_FOR bf_continue'; +ALTER TABLE t1 ADD UNIQUE KEY b1(b);; +connection node_1; +SET DEBUG_SYNC='now WAIT_FOR bf_kill'; +connection node_1b; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) NOT NULL, + `b` int(11) DEFAULT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `b1` (`b`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +id b +1 1 +2 2 +3 3 +4 4 +5 5 +connection node_1; +SET DEBUG_SYNC= 'RESET'; +DROP TABLE t1; +disconnect node_1a; +disconnect node_1b; +disconnect node_1c; +# +# Case 2: We execute bf kill to wsrep_innobase_kill_one_trx +# function just after wsrep_thd_LOCK(thd) call. Then we +# try to kill victim transaction by KILL QUERY +# +CREATE TABLE t1(id int not null primary key, b int) engine=innodb; +INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5); +connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; +begin; +update t1 set b = b * 10 where id between 2 and 4; +connect node_1b, 127.0.0.1, root, , test, $NODE_MYPORT_1; +connect node_1c, 127.0.0.1, root, , test, $NODE_MYPORT_1; +SET DEBUG_SYNC='wsrep_after_BF_victim_lock SIGNAL bf_kill WAIT_FOR bf_continue'; +ALTER TABLE t1 ADD UNIQUE KEY b1(b);; +connection node_1; +SET DEBUG_SYNC='now WAIT_FOR bf_kill'; +connection node_1b; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) NOT NULL, + `b` int(11) DEFAULT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `b1` (`b`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +id b +1 1 +2 2 +3 3 +4 4 +5 5 +connection node_1; +SET DEBUG_SYNC= 'RESET'; +DROP TABLE t1; +disconnect node_1a; +disconnect node_1b; +disconnect node_1c; +# +# Case 3: Create victim transaction and try to send user KILL +# from several threads +# +CREATE TABLE t1(id int not null primary key, b int) engine=innodb; +INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5); +connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; +begin; +update t1 set b = b * 10 where id between 2 and 4; +connect node_1b, 127.0.0.1, root, , test, $NODE_MYPORT_1; +connect node_1c, 127.0.0.1, root, , test, $NODE_MYPORT_1; +connect node_1d, 127.0.0.1, root, , test, $NODE_MYPORT_1; +connection node_1b; +connection node_1c; +connection node_1d; +connection node_1; +disconnect node_1a; +disconnect node_1b; +disconnect node_1c; +disconnect node_1d; +DROP TABLE t1; +# +# Case 4: MDL-conflict, we execute ALTER until we hit gap in +# wsrep_abort_transaction, while we are there we try to +# manually KILL conflicting transaction (UPDATE) and +# send conflicting transaction from other node to be executed +# in this node by applier. As ALTER and KILL are TOI they +# are not executed concurrently. Similarly UPDATE from other +# node will wait for certification. +# +CREATE TABLE t1(id int not null primary key, b int) engine=innodb; +INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5); +connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; +begin; +update t1 set b = b * 10 where id between 2 and 4; +connect node_1b, 127.0.0.1, root, , test, $NODE_MYPORT_1; +connect node_1c, 127.0.0.1, root, , test, $NODE_MYPORT_1; +SET DEBUG_SYNC='wsrep_abort_victim_unlocked SIGNAL bf_kill_unlocked WAIT_FOR bf_continue'; +ALTER TABLE t1 ADD UNIQUE KEY b1(b);; +connection node_1; +SET DEBUG_SYNC='now WAIT_FOR bf_kill_unlocked'; +connection node_1b; +connection node_2; +update t1 set b = b + 1000 where id between 2 and 4;; +connection node_1; +SET DEBUG_SYNC='now SIGNAL bf_continue'; +connection node_1c; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) NOT NULL, + `b` int(11) DEFAULT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `b1` (`b`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +SELECT * FROM t1; +id b +1 1 +5 5 +2 1002 +3 1003 +4 1004 +connection node_1b; +connection node_1; +SET DEBUG_SYNC= 'RESET'; +SELECT * FROM t1; +id b +1 1 +5 5 +2 1002 +3 1003 +4 1004 +connection node_2; +SHOW CREATE TABLE t1; +Table Create Table +t1 CREATE TABLE `t1` ( + `id` int(11) NOT NULL, + `b` int(11) DEFAULT NULL, + PRIMARY KEY (`id`), + UNIQUE KEY `b1` (`b`) +) ENGINE=InnoDB DEFAULT CHARSET=latin1 +SELECT * FROM t1; +id b +1 1 +5 5 +2 1002 +3 1003 +4 1004 +DROP TABLE t1; +disconnect node_1a; +disconnect node_1c; diff --git a/mysql-test/suite/galera/r/galera_toi_ddl_fk_insert.result b/mysql-test/suite/galera/r/galera_toi_ddl_fk_insert.result index 94752ed7c76..a972394f87c 100644 --- a/mysql-test/suite/galera/r/galera_toi_ddl_fk_insert.result +++ b/mysql-test/suite/galera/r/galera_toi_ddl_fk_insert.result @@ -21,22 +21,6 @@ connection node_1a; connection node_1b; connection node_2; connection node_2a; -connection node_1; -SET SESSION wsrep_sync_wait=15; -SELECT COUNT(*) FROM parent; -COUNT(*) -20001 -SELECT COUNT(*) FROM child; -COUNT(*) -10000 -connection node_2; -SET SESSION wsrep_sync_wait=15; -SELECT COUNT(*) FROM parent; -COUNT(*) -20001 -SELECT COUNT(*) FROM child; -COUNT(*) -10000 DROP TABLE child; DROP TABLE parent; DROP TABLE ten; diff --git a/mysql-test/suite/galera/t/galera_UK_conflict.test b/mysql-test/suite/galera/t/galera_UK_conflict.test index 57bafbf8ae0..83d0e47dc3d 100644 --- a/mysql-test/suite/galera/t/galera_UK_conflict.test +++ b/mysql-test/suite/galera/t/galera_UK_conflict.test @@ -140,9 +140,13 @@ SELECT * FROM t1; # original state in node 1 INSERT INTO t1 VALUES (7,7,7); INSERT INTO t1 VALUES (8,8,8); +SELECT COUNT(*) FROM t1; SELECT * FROM t1; --connection node_1 +--let $wait_condition = SELECT COUNT(*) = 7 FROM t1 +--source include/wait_condition.inc +SELECT COUNT(*) FROM t1; SELECT * FROM t1; DROP TABLE t1; diff --git a/mysql-test/suite/galera/t/galera_bf_kill_debug.cnf b/mysql-test/suite/galera/t/galera_bf_kill_debug.cnf new file mode 100644 index 00000000000..77bb6af9f35 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_bf_kill_debug.cnf @@ -0,0 +1,9 @@ +!include ../galera_2nodes.cnf + +[mysqld.1] +wsrep_log_conflicts=ON +wsrep_debug=1 + +[mysqld.2] +wsrep_log_conflicts=ON +wsrep_debug=1 diff --git a/mysql-test/suite/galera/t/galera_bf_kill_debug.test b/mysql-test/suite/galera/t/galera_bf_kill_debug.test new file mode 100644 index 00000000000..f83d4a28ce9 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_bf_kill_debug.test @@ -0,0 +1,283 @@ +--source include/galera_cluster.inc +--source include/have_debug.inc +--source include/have_debug_sync.inc + +--echo # +--echo # Case 1: We execute bf kill to wsrep_innobase_kill_one_trx +--echo # function just before wsrep_thd_LOCK(thd) call. Then we +--echo # try to kill victim transaction by KILL QUERY +--echo # + +CREATE TABLE t1(id int not null primary key, b int) engine=innodb; +INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5); + +# +# This will be victim transaction for both bf kill and +# user KILL +# +--connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1 +begin; +update t1 set b = b * 10 where id between 2 and 4; + +# +# Take thread id for above query +# +--connect node_1b, 127.0.0.1, root, , test, $NODE_MYPORT_1 +--let $k_thread = `SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'root' AND COMMAND = 'Sleep' LIMIT 1` + +# +# Set DEBUG_SYNC and send conflicting DDL that will be TOI (bf) and +# cause bf_kill +# +--connect node_1c, 127.0.0.1, root, , test, $NODE_MYPORT_1 +SET DEBUG_SYNC='wsrep_before_BF_victim_lock SIGNAL bf_kill WAIT_FOR bf_continue'; +--send ALTER TABLE t1 ADD UNIQUE KEY b1(b); + +# +# Wait until we have reached the sync point +# +--connection node_1 +SET DEBUG_SYNC='now WAIT_FOR bf_kill'; + +# +# Try to kill update query +# +--connection node_1b +--disable_query_log +--send_eval KILL QUERY $k_thread; + + +# +# Let bf_kill continue +# +--connection node_1 +SET DEBUG_SYNC='now SIGNAL bf_continue'; +--connection node_1c +--reap +SHOW CREATE TABLE t1; +SELECT * FROM t1; + +--connection node_1b +--reap +--enable_query_log + +--connection node_1 +SET DEBUG_SYNC= 'RESET'; +DROP TABLE t1; + +--disconnect node_1a +--disconnect node_1b +--disconnect node_1c + +--echo # +--echo # Case 2: We execute bf kill to wsrep_innobase_kill_one_trx +--echo # function just after wsrep_thd_LOCK(thd) call. Then we +--echo # try to kill victim transaction by KILL QUERY +--echo # + +CREATE TABLE t1(id int not null primary key, b int) engine=innodb; +INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5); + +# +# This will be victim transaction for both bf kill and +# user KILL +# +--connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1 +begin; +update t1 set b = b * 10 where id between 2 and 4; + +# +# Take thread id for above query +# +--connect node_1b, 127.0.0.1, root, , test, $NODE_MYPORT_1 +--let $k_thread = `SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'root' AND COMMAND = 'Sleep' LIMIT 1` + +# +# Set DEBUG_SYNC and send conflicting DDL that will be TOI (bf) and +# cause bf_kill +# +--connect node_1c, 127.0.0.1, root, , test, $NODE_MYPORT_1 +SET DEBUG_SYNC='wsrep_after_BF_victim_lock SIGNAL bf_kill WAIT_FOR bf_continue'; +--send ALTER TABLE t1 ADD UNIQUE KEY b1(b); + +# +# Wait until we have reached the sync point +# +--connection node_1 +SET DEBUG_SYNC='now WAIT_FOR bf_kill'; + +# +# Try to kill update query +# +--connection node_1b +--disable_query_log +--send_eval KILL QUERY $k_thread; + +# +# Let bf_kill continue +# +--connection node_1 +SET DEBUG_SYNC='now SIGNAL bf_continue'; +--connection node_1c +--reap +SHOW CREATE TABLE t1; +SELECT * FROM t1; + +--connection node_1b +--reap +--enable_query_log + +--connection node_1 +SET DEBUG_SYNC= 'RESET'; +DROP TABLE t1; + +--disconnect node_1a +--disconnect node_1b +--disconnect node_1c + +--echo # +--echo # Case 3: Create victim transaction and try to send user KILL +--echo # from several threads +--echo # + +CREATE TABLE t1(id int not null primary key, b int) engine=innodb; +INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5); + +# +# This will be victim transaction for user KILL +# +--connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1 +begin; +update t1 set b = b * 10 where id between 2 and 4; + +# +# Take thread id for above query +# +--connect node_1b, 127.0.0.1, root, , test, $NODE_MYPORT_1 +--connect node_1c, 127.0.0.1, root, , test, $NODE_MYPORT_1 +--connect node_1d, 127.0.0.1, root, , test, $NODE_MYPORT_1 + +--connection node_1b +--let $k_thread = `SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'root' AND COMMAND = 'Sleep' LIMIT 1` + +# +# Try to kill update query from several connections concurrently +# +--disable_query_log +--send_eval KILL QUERY $k_thread; + +--connection node_1c +--disable_query_log +--send_eval KILL QUERY $k_thread; + +--connection node_1d +--disable_query_log +--send_eval KILL QUERY $k_thread; + +# +# We do not know execution order so any of these could fail as KILL +# has been already done +# +--connection node_1b +--enable_query_log +--error 0,ER_KILL_DENIED_ERROR +--reap +--connection node_1c +--enable_query_log +--error 0,ER_KILL_DENIED_ERROR +--reap +--connection node_1d +--enable_query_log +--error 0,ER_KILL_DENIED_ERROR +--reap + +--connection node_1 +--disconnect node_1a +--disconnect node_1b +--disconnect node_1c +--disconnect node_1d +DROP TABLE t1; + +--echo # +--echo # Case 4: MDL-conflict, we execute ALTER until we hit gap in +--echo # wsrep_abort_transaction, while we are there we try to +--echo # manually KILL conflicting transaction (UPDATE) and +--echo # send conflicting transaction from other node to be executed +--echo # in this node by applier. As ALTER and KILL are TOI they +--echo # are not executed concurrently. Similarly UPDATE from other +--echo # node will wait for certification. +--echo # + +CREATE TABLE t1(id int not null primary key, b int) engine=innodb; +INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5); + +# +# This will be victim transaction for both bf kill and +# user KILL, and should not have any effect on result +# +--connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1 +begin; +update t1 set b = b * 10 where id between 2 and 4; + +# +# Take thread id for above query +# +--connect node_1b, 127.0.0.1, root, , test, $NODE_MYPORT_1 +--let $k_thread = `SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'root' AND COMMAND = 'Sleep' LIMIT 1` + +# +# Set DEBUG_SYNC and send conflicting DDL that will be TOI (bf) and +# cause bf_kill but let's execute it only to gap in wsrep_abort_transaction +# +--connect node_1c, 127.0.0.1, root, , test, $NODE_MYPORT_1 +SET DEBUG_SYNC='wsrep_abort_victim_unlocked SIGNAL bf_kill_unlocked WAIT_FOR bf_continue'; +--send ALTER TABLE t1 ADD UNIQUE KEY b1(b); + +# +# Wait until we have reached the sync point +# +--connection node_1 +SET DEBUG_SYNC='now WAIT_FOR bf_kill_unlocked'; + +# +# Try to kill update query +# +--connection node_1b +--disable_query_log +--send_eval KILL QUERY $k_thread; + +# +# Send conflicting update from other node, this should be applied on both nodes +# but should not kill ALTER +# +--enable_query_log +--connection node_2 +--send update t1 set b = b + 1000 where id between 2 and 4; + +# +# Let bf_kill continue +# +--connection node_1 +SET DEBUG_SYNC='now SIGNAL bf_continue'; +--connection node_1c +--reap +SHOW CREATE TABLE t1; +SELECT * FROM t1; + +--connection node_1b +--reap +--enable_query_log + +--connection node_1 +SET DEBUG_SYNC= 'RESET'; +SELECT * FROM t1; + +--connection node_2 +--reap +SHOW CREATE TABLE t1; +SELECT * FROM t1; +DROP TABLE t1; + +--disconnect node_1a +--disconnect node_1c + diff --git a/mysql-test/suite/galera/t/galera_toi_ddl_fk_insert.test b/mysql-test/suite/galera/t/galera_toi_ddl_fk_insert.test index fadc94d78ff..3b4b427f551 100644 --- a/mysql-test/suite/galera/t/galera_toi_ddl_fk_insert.test +++ b/mysql-test/suite/galera/t/galera_toi_ddl_fk_insert.test @@ -54,15 +54,11 @@ INSERT INTO parent VALUES (1, 0); --connection node_2a --reap ---connection node_1 -SET SESSION wsrep_sync_wait=15; -SELECT COUNT(*) FROM parent; -SELECT COUNT(*) FROM child; - ---connection node_2 -SET SESSION wsrep_sync_wait=15; -SELECT COUNT(*) FROM parent; -SELECT COUNT(*) FROM child; +# +# ALTER TABLE could bf kill one or more of INSERTs to parent, so +# the actual number of rows in PARENT depends on whether +# the INSERT is committed before ALTER TABLE is executed +# DROP TABLE child; DROP TABLE parent; diff --git a/mysql-test/suite/wsrep/t/variables.test b/mysql-test/suite/wsrep/t/variables.test index 0cf13650ce0..875315c0e7c 100644 --- a/mysql-test/suite/wsrep/t/variables.test +++ b/mysql-test/suite/wsrep/t/variables.test @@ -66,7 +66,7 @@ call mtr.add_suppression("WSREP: Failed to get provider options"); #evalp SET GLOBAL wsrep_provider= '$WSREP_PROVIDER'; ---replace_regex /.*libgalera_smm.*/libgalera_smm.so/ +--replace_regex /.*libgalera.*/libgalera_smm.so/ SELECT @@global.wsrep_provider; SELECT @@global.wsrep_slave_threads; SELECT @@global.wsrep_cluster_address; @@ -77,7 +77,7 @@ SHOW STATUS LIKE 'wsrep_thread_count'; #evalp SET GLOBAL wsrep_provider= '$WSREP_PROVIDER'; ---replace_regex /.*libgalera_smm.*/libgalera_smm.so/ +--replace_regex /.*libgalera.*/libgalera_smm.so/ SELECT @@global.wsrep_provider; SELECT @@global.wsrep_cluster_address; SELECT @@global.wsrep_on; @@ -101,7 +101,7 @@ SELECT VARIABLE_VALUE AS EXPECT_1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VA SELECT VARIABLE_VALUE AS EXPECT_1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_rollbacker_thread_count'; SELECT VARIABLE_VALUE AS EXPECT_2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_thread_count'; ---replace_regex /.*libgalera_smm.*/libgalera_smm.so/ +--replace_regex /.*libgalera.*/libgalera_smm.so/ SELECT @@global.wsrep_provider; SELECT @@global.wsrep_cluster_address; SELECT @@global.wsrep_on; -- cgit v1.2.1 From 47ba5523046094db33e68c92a182491a629bbd56 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Jan=20Lindstr=C3=B6m?= Date: Fri, 24 Sep 2021 07:33:27 +0300 Subject: Revert "MDEV-24978 : SIGABRT in __libc_message" This reverts commit 30dea4599e44e3008fb9bc5fe79ab5747841f21f. --- mysql-test/suite/galera/r/galera_fulltext.result | 28 ------------------------ mysql-test/suite/galera/t/galera_fulltext.test | 23 ------------------- 2 files changed, 51 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/suite/galera/r/galera_fulltext.result b/mysql-test/suite/galera/r/galera_fulltext.result index bb482b7f4f7..f52f5c996a3 100644 --- a/mysql-test/suite/galera/r/galera_fulltext.result +++ b/mysql-test/suite/galera/r/galera_fulltext.result @@ -36,34 +36,6 @@ DROP TABLE t1; DROP TABLE ten; connection node_1; SET @value=REPEAT (1,5001); -CREATE TABLE t (a VARCHAR(5000),FULLTEXT (a)) engine=innodb; -INSERT IGNORE INTO t VALUES(@value); -Warnings: -Warning 1265 Data truncated for column 'a' at row 1 -SELECT COUNT(*) FROM t; -COUNT(*) -1 -connection node_2; -SELECT COUNT(*) FROM t; -COUNT(*) -1 -connection node_1; -DROP TABLE t; -CREATE TABLE t (a VARCHAR(5000)) engine=innodb; -INSERT IGNORE INTO t VALUES(@value); -Warnings: -Warning 1265 Data truncated for column 'a' at row 1 -SELECT COUNT(*) FROM t; -COUNT(*) -1 -connection node_2; -SELECT COUNT(*) FROM t; -COUNT(*) -1 -connection node_1; -DROP TABLE t; -connection node_1; -SET @value=REPEAT (1,5001); CREATE TABLE t (a VARCHAR(5000),FULLTEXT (a)) engine=innodb DEFAULT CHARSET=utf8; INSERT IGNORE INTO t VALUES(@value); Warnings: diff --git a/mysql-test/suite/galera/t/galera_fulltext.test b/mysql-test/suite/galera/t/galera_fulltext.test index 25f4f83b7b7..76c29da4123 100644 --- a/mysql-test/suite/galera/t/galera_fulltext.test +++ b/mysql-test/suite/galera/t/galera_fulltext.test @@ -60,29 +60,6 @@ SELECT COUNT(f1) = 1000 FROM t1 WHERE MATCH(f1) AGAINST ('abcdefjhk'); DROP TABLE t1; DROP TABLE ten; -# -# MDEV-24978 : SIGABRT in __libc_message -# ---connection node_1 -SET @value=REPEAT (1,5001); -CREATE TABLE t (a VARCHAR(5000),FULLTEXT (a)) engine=innodb; -INSERT IGNORE INTO t VALUES(@value); -SELECT COUNT(*) FROM t; - ---connection node_2 -SELECT COUNT(*) FROM t; - ---connection node_1 -DROP TABLE t; -CREATE TABLE t (a VARCHAR(5000)) engine=innodb; -INSERT IGNORE INTO t VALUES(@value); -SELECT COUNT(*) FROM t; - ---connection node_2 -SELECT COUNT(*) FROM t; - ---connection node_1 -DROP TABLE t; # # Case 2: UTF-8 -- cgit v1.2.1 From ca7046dc19d8bbb5916c89d9e98e9d4632294fa8 Mon Sep 17 00:00:00 2001 From: Vladislav Vaintroub Date: Fri, 24 Sep 2021 01:33:05 +0200 Subject: MDEV-11499 mysqltest, Windows : improve diagnostics if server fails to shutdown Create minidump when server fails to shutdown. If process is being debugged, cause a debug break. Moves some code which is part of safe_kill into mysys, as both safe_kill, and mysqltest produce minidumps on different timeouts. Small cleanup in wait_until_dead() - replace inefficient loop with a single wait. --- mysql-test/lib/My/SafeProcess/CMakeLists.txt | 3 +- mysql-test/lib/My/SafeProcess/safe_kill_win.cc | 109 +------------------------ 2 files changed, 4 insertions(+), 108 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/lib/My/SafeProcess/CMakeLists.txt b/mysql-test/lib/My/SafeProcess/CMakeLists.txt index 33ff99da89b..4946af7d899 100644 --- a/mysql-test/lib/My/SafeProcess/CMakeLists.txt +++ b/mysql-test/lib/My/SafeProcess/CMakeLists.txt @@ -17,7 +17,8 @@ IF (WIN32) ADD_EXECUTABLE(my_safe_process safe_process_win.cc) ADD_EXECUTABLE(my_safe_kill safe_kill_win.cc) - TARGET_LINK_LIBRARIES(my_safe_kill dbghelp psapi) + TARGET_INCLUDE_DIRECTORIES(my_safe_kill PRIVATE ${CMAKE_SOURCE_DIR}/include) + TARGET_LINK_LIBRARIES(my_safe_kill mysys psapi) ELSE() ADD_EXECUTABLE(my_safe_process safe_process.cc) ENDIF() diff --git a/mysql-test/lib/My/SafeProcess/safe_kill_win.cc b/mysql-test/lib/My/SafeProcess/safe_kill_win.cc index 4a9d5f2b8cc..375ed80b292 100644 --- a/mysql-test/lib/My/SafeProcess/safe_kill_win.cc +++ b/mysql-test/lib/My/SafeProcess/safe_kill_win.cc @@ -26,19 +26,7 @@ #include #include #include - -#ifdef _MSC_VER -/* Silence warning in OS header dbghelp.h */ -#pragma warning(push) -#pragma warning(disable : 4091) -#endif - -#include - -#ifdef _MSC_VER -/* Silence warning in OS header dbghelp.h */ -#pragma warning(pop) -#endif +#include #include #include @@ -64,106 +52,13 @@ static std::vector find_children(DWORD pid) return children; } -void dump_single_process(DWORD pid) -{ - HANDLE file = 0; - HANDLE process= 0; - DWORD size= MAX_PATH; - char path[MAX_PATH]; - char working_dir[MAX_PATH]; - char tmpname[MAX_PATH]; - char *filename= 0; - - process= OpenProcess(PROCESS_QUERY_INFORMATION | PROCESS_VM_READ, FALSE, pid); - if (!process) - { - fprintf(stderr, "safe_kill : cannot open process pid=%lu to create dump, last error %lu\n", - pid, GetLastError()); - goto exit; - } - - if (QueryFullProcessImageName(process, 0, path, &size) == 0) - { - fprintf(stderr, "safe_kill : cannot read process path for pid %lu, last error %lu\n", - pid, GetLastError()); - goto exit; - } - - filename= strrchr(path, '\\'); - if (filename) - { - filename++; - // We are not interested in dump of some proceses (my_safe_process.exe,cmd.exe) - // since they are only used to start up other programs. - // We're interested however in their children; - const char *exclude_programs[] = {"my_safe_process.exe","cmd.exe", 0}; - for(size_t i=0; exclude_programs[i]; i++) - if (_stricmp(filename, exclude_programs[i]) == 0) - goto exit; - } - else - filename= path; - - // Add .dmp extension - char *p; - if ((p= strrchr(filename, '.')) == 0) - p= filename + strlen(filename); - - strncpy(p, ".dmp", path + MAX_PATH - p); - - // Íf file with this name exist, generate unique name with .dmp extension - if (GetFileAttributes(filename) != INVALID_FILE_ATTRIBUTES) - { - if (!GetTempFileName(".", filename, 0, tmpname)) - { - fprintf(stderr, "GetTempFileName failed, last error %lu", GetLastError()); - goto exit; - } - strncat_s(tmpname, ".dmp", sizeof(tmpname)); - filename= tmpname; - } - - - if (!GetCurrentDirectory(MAX_PATH, working_dir)) - { - fprintf(stderr, "GetCurrentDirectory failed, last error %lu", GetLastError()); - goto exit; - } - - file= CreateFile(filename, GENERIC_READ | GENERIC_WRITE, - 0, 0, CREATE_ALWAYS, FILE_ATTRIBUTE_NORMAL, 0); - - if (file == INVALID_HANDLE_VALUE) - { - fprintf(stderr, "safe_kill : CreateFile() failed for file %s, working dir %s, last error = %lu\n", - filename, working_dir, GetLastError()); - goto exit; - } - - if (!MiniDumpWriteDump(process, pid, file, MiniDumpNormal, 0, 0, 0)) - { - fprintf(stderr, "Failed to write minidump to %s, working dir %s, last error %lu\n", - filename, working_dir, GetLastError()); - goto exit; - } - - fprintf(stderr, "Minidump written to %s, directory %s\n", filename, working_dir); - -exit: - if (process != 0 && process != INVALID_HANDLE_VALUE) - CloseHandle(process); - - if (file != 0 && file != INVALID_HANDLE_VALUE) - CloseHandle(file); -} - static int create_dump(DWORD pid, int recursion_depth= 5) { if (recursion_depth < 0) return 0; - dump_single_process(pid); + my_create_minidump(pid, TRUE); std::vector children= find_children(pid); for(size_t i=0; i < children.size(); i++) create_dump(children[i], recursion_depth -1); -- cgit v1.2.1 From f59f5c4a10151b18d1407065ca48746384b6a25a Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Fri, 24 Sep 2021 16:21:20 +0300 Subject: Revert MDEV-25114 Revert 88a4be75a5f3b8d59ac8f6347ff2c197813c05dc and 9d97f92febc89941784d17d59c60275e21140ce0, which had been prematurely pushed by accident. --- .../suite/galera/r/galera_UK_conflict.result | 7 - .../suite/galera/r/galera_bf_kill_debug.result | 163 ------------ .../suite/galera/r/galera_toi_ddl_fk_insert.result | 16 ++ mysql-test/suite/galera/t/galera_UK_conflict.test | 4 - mysql-test/suite/galera/t/galera_bf_kill_debug.cnf | 9 - .../suite/galera/t/galera_bf_kill_debug.test | 283 --------------------- .../suite/galera/t/galera_toi_ddl_fk_insert.test | 14 +- mysql-test/suite/wsrep/t/variables.test | 6 +- 8 files changed, 28 insertions(+), 474 deletions(-) delete mode 100644 mysql-test/suite/galera/r/galera_bf_kill_debug.result delete mode 100644 mysql-test/suite/galera/t/galera_bf_kill_debug.cnf delete mode 100644 mysql-test/suite/galera/t/galera_bf_kill_debug.test (limited to 'mysql-test') diff --git a/mysql-test/suite/galera/r/galera_UK_conflict.result b/mysql-test/suite/galera/r/galera_UK_conflict.result index 2795a86d6a6..76649f1b268 100644 --- a/mysql-test/suite/galera/r/galera_UK_conflict.result +++ b/mysql-test/suite/galera/r/galera_UK_conflict.result @@ -68,9 +68,6 @@ f1 f2 f3 10 10 0 INSERT INTO t1 VALUES (7,7,7); INSERT INTO t1 VALUES (8,8,8); -SELECT COUNT(*) FROM t1; -COUNT(*) -7 SELECT * FROM t1; f1 f2 f3 1 1 0 @@ -81,9 +78,6 @@ f1 f2 f3 8 8 8 10 10 0 connection node_1; -SELECT COUNT(*) FROM t1; -COUNT(*) -7 SELECT * FROM t1; f1 f2 f3 1 1 0 @@ -91,6 +85,5 @@ f1 f2 f3 4 4 2 5 5 2 7 7 7 -8 8 8 10 10 0 DROP TABLE t1; diff --git a/mysql-test/suite/galera/r/galera_bf_kill_debug.result b/mysql-test/suite/galera/r/galera_bf_kill_debug.result deleted file mode 100644 index c20bb80131a..00000000000 --- a/mysql-test/suite/galera/r/galera_bf_kill_debug.result +++ /dev/null @@ -1,163 +0,0 @@ -# -# Case 1: We execute bf kill to wsrep_innobase_kill_one_trx -# function just before wsrep_thd_LOCK(thd) call. Then we -# try to kill victim transaction by KILL QUERY -# -CREATE TABLE t1(id int not null primary key, b int) engine=innodb; -INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5); -connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; -begin; -update t1 set b = b * 10 where id between 2 and 4; -connect node_1b, 127.0.0.1, root, , test, $NODE_MYPORT_1; -connect node_1c, 127.0.0.1, root, , test, $NODE_MYPORT_1; -SET DEBUG_SYNC='wsrep_before_BF_victim_lock SIGNAL bf_kill WAIT_FOR bf_continue'; -ALTER TABLE t1 ADD UNIQUE KEY b1(b);; -connection node_1; -SET DEBUG_SYNC='now WAIT_FOR bf_kill'; -connection node_1b; -Table Create Table -t1 CREATE TABLE `t1` ( - `id` int(11) NOT NULL, - `b` int(11) DEFAULT NULL, - PRIMARY KEY (`id`), - UNIQUE KEY `b1` (`b`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 -id b -1 1 -2 2 -3 3 -4 4 -5 5 -connection node_1; -SET DEBUG_SYNC= 'RESET'; -DROP TABLE t1; -disconnect node_1a; -disconnect node_1b; -disconnect node_1c; -# -# Case 2: We execute bf kill to wsrep_innobase_kill_one_trx -# function just after wsrep_thd_LOCK(thd) call. Then we -# try to kill victim transaction by KILL QUERY -# -CREATE TABLE t1(id int not null primary key, b int) engine=innodb; -INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5); -connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; -begin; -update t1 set b = b * 10 where id between 2 and 4; -connect node_1b, 127.0.0.1, root, , test, $NODE_MYPORT_1; -connect node_1c, 127.0.0.1, root, , test, $NODE_MYPORT_1; -SET DEBUG_SYNC='wsrep_after_BF_victim_lock SIGNAL bf_kill WAIT_FOR bf_continue'; -ALTER TABLE t1 ADD UNIQUE KEY b1(b);; -connection node_1; -SET DEBUG_SYNC='now WAIT_FOR bf_kill'; -connection node_1b; -Table Create Table -t1 CREATE TABLE `t1` ( - `id` int(11) NOT NULL, - `b` int(11) DEFAULT NULL, - PRIMARY KEY (`id`), - UNIQUE KEY `b1` (`b`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 -id b -1 1 -2 2 -3 3 -4 4 -5 5 -connection node_1; -SET DEBUG_SYNC= 'RESET'; -DROP TABLE t1; -disconnect node_1a; -disconnect node_1b; -disconnect node_1c; -# -# Case 3: Create victim transaction and try to send user KILL -# from several threads -# -CREATE TABLE t1(id int not null primary key, b int) engine=innodb; -INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5); -connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; -begin; -update t1 set b = b * 10 where id between 2 and 4; -connect node_1b, 127.0.0.1, root, , test, $NODE_MYPORT_1; -connect node_1c, 127.0.0.1, root, , test, $NODE_MYPORT_1; -connect node_1d, 127.0.0.1, root, , test, $NODE_MYPORT_1; -connection node_1b; -connection node_1c; -connection node_1d; -connection node_1; -disconnect node_1a; -disconnect node_1b; -disconnect node_1c; -disconnect node_1d; -DROP TABLE t1; -# -# Case 4: MDL-conflict, we execute ALTER until we hit gap in -# wsrep_abort_transaction, while we are there we try to -# manually KILL conflicting transaction (UPDATE) and -# send conflicting transaction from other node to be executed -# in this node by applier. As ALTER and KILL are TOI they -# are not executed concurrently. Similarly UPDATE from other -# node will wait for certification. -# -CREATE TABLE t1(id int not null primary key, b int) engine=innodb; -INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5); -connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1; -begin; -update t1 set b = b * 10 where id between 2 and 4; -connect node_1b, 127.0.0.1, root, , test, $NODE_MYPORT_1; -connect node_1c, 127.0.0.1, root, , test, $NODE_MYPORT_1; -SET DEBUG_SYNC='wsrep_abort_victim_unlocked SIGNAL bf_kill_unlocked WAIT_FOR bf_continue'; -ALTER TABLE t1 ADD UNIQUE KEY b1(b);; -connection node_1; -SET DEBUG_SYNC='now WAIT_FOR bf_kill_unlocked'; -connection node_1b; -connection node_2; -update t1 set b = b + 1000 where id between 2 and 4;; -connection node_1; -SET DEBUG_SYNC='now SIGNAL bf_continue'; -connection node_1c; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `id` int(11) NOT NULL, - `b` int(11) DEFAULT NULL, - PRIMARY KEY (`id`), - UNIQUE KEY `b1` (`b`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 -SELECT * FROM t1; -id b -1 1 -5 5 -2 1002 -3 1003 -4 1004 -connection node_1b; -connection node_1; -SET DEBUG_SYNC= 'RESET'; -SELECT * FROM t1; -id b -1 1 -5 5 -2 1002 -3 1003 -4 1004 -connection node_2; -SHOW CREATE TABLE t1; -Table Create Table -t1 CREATE TABLE `t1` ( - `id` int(11) NOT NULL, - `b` int(11) DEFAULT NULL, - PRIMARY KEY (`id`), - UNIQUE KEY `b1` (`b`) -) ENGINE=InnoDB DEFAULT CHARSET=latin1 -SELECT * FROM t1; -id b -1 1 -5 5 -2 1002 -3 1003 -4 1004 -DROP TABLE t1; -disconnect node_1a; -disconnect node_1c; diff --git a/mysql-test/suite/galera/r/galera_toi_ddl_fk_insert.result b/mysql-test/suite/galera/r/galera_toi_ddl_fk_insert.result index a972394f87c..94752ed7c76 100644 --- a/mysql-test/suite/galera/r/galera_toi_ddl_fk_insert.result +++ b/mysql-test/suite/galera/r/galera_toi_ddl_fk_insert.result @@ -21,6 +21,22 @@ connection node_1a; connection node_1b; connection node_2; connection node_2a; +connection node_1; +SET SESSION wsrep_sync_wait=15; +SELECT COUNT(*) FROM parent; +COUNT(*) +20001 +SELECT COUNT(*) FROM child; +COUNT(*) +10000 +connection node_2; +SET SESSION wsrep_sync_wait=15; +SELECT COUNT(*) FROM parent; +COUNT(*) +20001 +SELECT COUNT(*) FROM child; +COUNT(*) +10000 DROP TABLE child; DROP TABLE parent; DROP TABLE ten; diff --git a/mysql-test/suite/galera/t/galera_UK_conflict.test b/mysql-test/suite/galera/t/galera_UK_conflict.test index 83d0e47dc3d..57bafbf8ae0 100644 --- a/mysql-test/suite/galera/t/galera_UK_conflict.test +++ b/mysql-test/suite/galera/t/galera_UK_conflict.test @@ -140,13 +140,9 @@ SELECT * FROM t1; # original state in node 1 INSERT INTO t1 VALUES (7,7,7); INSERT INTO t1 VALUES (8,8,8); -SELECT COUNT(*) FROM t1; SELECT * FROM t1; --connection node_1 ---let $wait_condition = SELECT COUNT(*) = 7 FROM t1 ---source include/wait_condition.inc -SELECT COUNT(*) FROM t1; SELECT * FROM t1; DROP TABLE t1; diff --git a/mysql-test/suite/galera/t/galera_bf_kill_debug.cnf b/mysql-test/suite/galera/t/galera_bf_kill_debug.cnf deleted file mode 100644 index 77bb6af9f35..00000000000 --- a/mysql-test/suite/galera/t/galera_bf_kill_debug.cnf +++ /dev/null @@ -1,9 +0,0 @@ -!include ../galera_2nodes.cnf - -[mysqld.1] -wsrep_log_conflicts=ON -wsrep_debug=1 - -[mysqld.2] -wsrep_log_conflicts=ON -wsrep_debug=1 diff --git a/mysql-test/suite/galera/t/galera_bf_kill_debug.test b/mysql-test/suite/galera/t/galera_bf_kill_debug.test deleted file mode 100644 index f83d4a28ce9..00000000000 --- a/mysql-test/suite/galera/t/galera_bf_kill_debug.test +++ /dev/null @@ -1,283 +0,0 @@ ---source include/galera_cluster.inc ---source include/have_debug.inc ---source include/have_debug_sync.inc - ---echo # ---echo # Case 1: We execute bf kill to wsrep_innobase_kill_one_trx ---echo # function just before wsrep_thd_LOCK(thd) call. Then we ---echo # try to kill victim transaction by KILL QUERY ---echo # - -CREATE TABLE t1(id int not null primary key, b int) engine=innodb; -INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5); - -# -# This will be victim transaction for both bf kill and -# user KILL -# ---connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1 -begin; -update t1 set b = b * 10 where id between 2 and 4; - -# -# Take thread id for above query -# ---connect node_1b, 127.0.0.1, root, , test, $NODE_MYPORT_1 ---let $k_thread = `SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'root' AND COMMAND = 'Sleep' LIMIT 1` - -# -# Set DEBUG_SYNC and send conflicting DDL that will be TOI (bf) and -# cause bf_kill -# ---connect node_1c, 127.0.0.1, root, , test, $NODE_MYPORT_1 -SET DEBUG_SYNC='wsrep_before_BF_victim_lock SIGNAL bf_kill WAIT_FOR bf_continue'; ---send ALTER TABLE t1 ADD UNIQUE KEY b1(b); - -# -# Wait until we have reached the sync point -# ---connection node_1 -SET DEBUG_SYNC='now WAIT_FOR bf_kill'; - -# -# Try to kill update query -# ---connection node_1b ---disable_query_log ---send_eval KILL QUERY $k_thread; - - -# -# Let bf_kill continue -# ---connection node_1 -SET DEBUG_SYNC='now SIGNAL bf_continue'; ---connection node_1c ---reap -SHOW CREATE TABLE t1; -SELECT * FROM t1; - ---connection node_1b ---reap ---enable_query_log - ---connection node_1 -SET DEBUG_SYNC= 'RESET'; -DROP TABLE t1; - ---disconnect node_1a ---disconnect node_1b ---disconnect node_1c - ---echo # ---echo # Case 2: We execute bf kill to wsrep_innobase_kill_one_trx ---echo # function just after wsrep_thd_LOCK(thd) call. Then we ---echo # try to kill victim transaction by KILL QUERY ---echo # - -CREATE TABLE t1(id int not null primary key, b int) engine=innodb; -INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5); - -# -# This will be victim transaction for both bf kill and -# user KILL -# ---connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1 -begin; -update t1 set b = b * 10 where id between 2 and 4; - -# -# Take thread id for above query -# ---connect node_1b, 127.0.0.1, root, , test, $NODE_MYPORT_1 ---let $k_thread = `SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'root' AND COMMAND = 'Sleep' LIMIT 1` - -# -# Set DEBUG_SYNC and send conflicting DDL that will be TOI (bf) and -# cause bf_kill -# ---connect node_1c, 127.0.0.1, root, , test, $NODE_MYPORT_1 -SET DEBUG_SYNC='wsrep_after_BF_victim_lock SIGNAL bf_kill WAIT_FOR bf_continue'; ---send ALTER TABLE t1 ADD UNIQUE KEY b1(b); - -# -# Wait until we have reached the sync point -# ---connection node_1 -SET DEBUG_SYNC='now WAIT_FOR bf_kill'; - -# -# Try to kill update query -# ---connection node_1b ---disable_query_log ---send_eval KILL QUERY $k_thread; - -# -# Let bf_kill continue -# ---connection node_1 -SET DEBUG_SYNC='now SIGNAL bf_continue'; ---connection node_1c ---reap -SHOW CREATE TABLE t1; -SELECT * FROM t1; - ---connection node_1b ---reap ---enable_query_log - ---connection node_1 -SET DEBUG_SYNC= 'RESET'; -DROP TABLE t1; - ---disconnect node_1a ---disconnect node_1b ---disconnect node_1c - ---echo # ---echo # Case 3: Create victim transaction and try to send user KILL ---echo # from several threads ---echo # - -CREATE TABLE t1(id int not null primary key, b int) engine=innodb; -INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5); - -# -# This will be victim transaction for user KILL -# ---connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1 -begin; -update t1 set b = b * 10 where id between 2 and 4; - -# -# Take thread id for above query -# ---connect node_1b, 127.0.0.1, root, , test, $NODE_MYPORT_1 ---connect node_1c, 127.0.0.1, root, , test, $NODE_MYPORT_1 ---connect node_1d, 127.0.0.1, root, , test, $NODE_MYPORT_1 - ---connection node_1b ---let $k_thread = `SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'root' AND COMMAND = 'Sleep' LIMIT 1` - -# -# Try to kill update query from several connections concurrently -# ---disable_query_log ---send_eval KILL QUERY $k_thread; - ---connection node_1c ---disable_query_log ---send_eval KILL QUERY $k_thread; - ---connection node_1d ---disable_query_log ---send_eval KILL QUERY $k_thread; - -# -# We do not know execution order so any of these could fail as KILL -# has been already done -# ---connection node_1b ---enable_query_log ---error 0,ER_KILL_DENIED_ERROR ---reap ---connection node_1c ---enable_query_log ---error 0,ER_KILL_DENIED_ERROR ---reap ---connection node_1d ---enable_query_log ---error 0,ER_KILL_DENIED_ERROR ---reap - ---connection node_1 ---disconnect node_1a ---disconnect node_1b ---disconnect node_1c ---disconnect node_1d -DROP TABLE t1; - ---echo # ---echo # Case 4: MDL-conflict, we execute ALTER until we hit gap in ---echo # wsrep_abort_transaction, while we are there we try to ---echo # manually KILL conflicting transaction (UPDATE) and ---echo # send conflicting transaction from other node to be executed ---echo # in this node by applier. As ALTER and KILL are TOI they ---echo # are not executed concurrently. Similarly UPDATE from other ---echo # node will wait for certification. ---echo # - -CREATE TABLE t1(id int not null primary key, b int) engine=innodb; -INSERT INTO t1 values (1,1),(2,2),(3,3),(4,4),(5,5); - -# -# This will be victim transaction for both bf kill and -# user KILL, and should not have any effect on result -# ---connect node_1a, 127.0.0.1, root, , test, $NODE_MYPORT_1 -begin; -update t1 set b = b * 10 where id between 2 and 4; - -# -# Take thread id for above query -# ---connect node_1b, 127.0.0.1, root, , test, $NODE_MYPORT_1 ---let $k_thread = `SELECT ID FROM INFORMATION_SCHEMA.PROCESSLIST WHERE USER = 'root' AND COMMAND = 'Sleep' LIMIT 1` - -# -# Set DEBUG_SYNC and send conflicting DDL that will be TOI (bf) and -# cause bf_kill but let's execute it only to gap in wsrep_abort_transaction -# ---connect node_1c, 127.0.0.1, root, , test, $NODE_MYPORT_1 -SET DEBUG_SYNC='wsrep_abort_victim_unlocked SIGNAL bf_kill_unlocked WAIT_FOR bf_continue'; ---send ALTER TABLE t1 ADD UNIQUE KEY b1(b); - -# -# Wait until we have reached the sync point -# ---connection node_1 -SET DEBUG_SYNC='now WAIT_FOR bf_kill_unlocked'; - -# -# Try to kill update query -# ---connection node_1b ---disable_query_log ---send_eval KILL QUERY $k_thread; - -# -# Send conflicting update from other node, this should be applied on both nodes -# but should not kill ALTER -# ---enable_query_log ---connection node_2 ---send update t1 set b = b + 1000 where id between 2 and 4; - -# -# Let bf_kill continue -# ---connection node_1 -SET DEBUG_SYNC='now SIGNAL bf_continue'; ---connection node_1c ---reap -SHOW CREATE TABLE t1; -SELECT * FROM t1; - ---connection node_1b ---reap ---enable_query_log - ---connection node_1 -SET DEBUG_SYNC= 'RESET'; -SELECT * FROM t1; - ---connection node_2 ---reap -SHOW CREATE TABLE t1; -SELECT * FROM t1; -DROP TABLE t1; - ---disconnect node_1a ---disconnect node_1c - diff --git a/mysql-test/suite/galera/t/galera_toi_ddl_fk_insert.test b/mysql-test/suite/galera/t/galera_toi_ddl_fk_insert.test index 3b4b427f551..fadc94d78ff 100644 --- a/mysql-test/suite/galera/t/galera_toi_ddl_fk_insert.test +++ b/mysql-test/suite/galera/t/galera_toi_ddl_fk_insert.test @@ -54,11 +54,15 @@ INSERT INTO parent VALUES (1, 0); --connection node_2a --reap -# -# ALTER TABLE could bf kill one or more of INSERTs to parent, so -# the actual number of rows in PARENT depends on whether -# the INSERT is committed before ALTER TABLE is executed -# +--connection node_1 +SET SESSION wsrep_sync_wait=15; +SELECT COUNT(*) FROM parent; +SELECT COUNT(*) FROM child; + +--connection node_2 +SET SESSION wsrep_sync_wait=15; +SELECT COUNT(*) FROM parent; +SELECT COUNT(*) FROM child; DROP TABLE child; DROP TABLE parent; diff --git a/mysql-test/suite/wsrep/t/variables.test b/mysql-test/suite/wsrep/t/variables.test index 875315c0e7c..0cf13650ce0 100644 --- a/mysql-test/suite/wsrep/t/variables.test +++ b/mysql-test/suite/wsrep/t/variables.test @@ -66,7 +66,7 @@ call mtr.add_suppression("WSREP: Failed to get provider options"); #evalp SET GLOBAL wsrep_provider= '$WSREP_PROVIDER'; ---replace_regex /.*libgalera.*/libgalera_smm.so/ +--replace_regex /.*libgalera_smm.*/libgalera_smm.so/ SELECT @@global.wsrep_provider; SELECT @@global.wsrep_slave_threads; SELECT @@global.wsrep_cluster_address; @@ -77,7 +77,7 @@ SHOW STATUS LIKE 'wsrep_thread_count'; #evalp SET GLOBAL wsrep_provider= '$WSREP_PROVIDER'; ---replace_regex /.*libgalera.*/libgalera_smm.so/ +--replace_regex /.*libgalera_smm.*/libgalera_smm.so/ SELECT @@global.wsrep_provider; SELECT @@global.wsrep_cluster_address; SELECT @@global.wsrep_on; @@ -101,7 +101,7 @@ SELECT VARIABLE_VALUE AS EXPECT_1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VA SELECT VARIABLE_VALUE AS EXPECT_1 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_rollbacker_thread_count'; SELECT VARIABLE_VALUE AS EXPECT_2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_thread_count'; ---replace_regex /.*libgalera.*/libgalera_smm.so/ +--replace_regex /.*libgalera_smm.*/libgalera_smm.so/ SELECT @@global.wsrep_provider; SELECT @@global.wsrep_cluster_address; SELECT @@global.wsrep_on; -- cgit v1.2.1 From 1a62c878970fea6d2013c432bbd5aae30dbaca89 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Jan=20Lindstr=C3=B6m?= Date: Mon, 27 Sep 2021 08:25:22 +0300 Subject: Remove test from galera_fulltext until MDEV-24978 is fixed. --- mysql-test/suite/galera/r/galera_fulltext.result | 28 --------------- mysql-test/suite/galera/t/galera_fulltext.test | 43 ++++++++++++------------ 2 files changed, 21 insertions(+), 50 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/suite/galera/r/galera_fulltext.result b/mysql-test/suite/galera/r/galera_fulltext.result index f52f5c996a3..18e3bff40fc 100644 --- a/mysql-test/suite/galera/r/galera_fulltext.result +++ b/mysql-test/suite/galera/r/galera_fulltext.result @@ -34,31 +34,3 @@ COUNT(f1) = 1000 1 DROP TABLE t1; DROP TABLE ten; -connection node_1; -SET @value=REPEAT (1,5001); -CREATE TABLE t (a VARCHAR(5000),FULLTEXT (a)) engine=innodb DEFAULT CHARSET=utf8; -INSERT IGNORE INTO t VALUES(@value); -Warnings: -Warning 1265 Data truncated for column 'a' at row 1 -SELECT COUNT(*) FROM t; -COUNT(*) -1 -connection node_2; -SELECT COUNT(*) FROM t; -COUNT(*) -1 -connection node_1; -DROP TABLE t; -CREATE TABLE t (a VARCHAR(5000)) engine=innodb DEFAULT CHARSET=utf8; -INSERT IGNORE INTO t VALUES(@value); -Warnings: -Warning 1265 Data truncated for column 'a' at row 1 -SELECT COUNT(*) FROM t; -COUNT(*) -1 -connection node_2; -SELECT COUNT(*) FROM t; -COUNT(*) -1 -connection node_1; -DROP TABLE t; diff --git a/mysql-test/suite/galera/t/galera_fulltext.test b/mysql-test/suite/galera/t/galera_fulltext.test index 76c29da4123..7e2fc5e581d 100644 --- a/mysql-test/suite/galera/t/galera_fulltext.test +++ b/mysql-test/suite/galera/t/galera_fulltext.test @@ -1,6 +1,4 @@ ---source include/big_test.inc --source include/galera_cluster.inc ---source include/have_innodb.inc # # InnoDB FULLTEXT indexes @@ -63,24 +61,25 @@ DROP TABLE ten; # # Case 2: UTF-8 +# TODO: MDEV-24978 # ---connection node_1 -SET @value=REPEAT (1,5001); -CREATE TABLE t (a VARCHAR(5000),FULLTEXT (a)) engine=innodb DEFAULT CHARSET=utf8; -INSERT IGNORE INTO t VALUES(@value); -SELECT COUNT(*) FROM t; - ---connection node_2 -SELECT COUNT(*) FROM t; - ---connection node_1 -DROP TABLE t; -CREATE TABLE t (a VARCHAR(5000)) engine=innodb DEFAULT CHARSET=utf8; -INSERT IGNORE INTO t VALUES(@value); -SELECT COUNT(*) FROM t; - ---connection node_2 -SELECT COUNT(*) FROM t; - ---connection node_1 -DROP TABLE t; +#--connection node_1 +#SET @value=REPEAT (1,5001); +#CREATE TABLE t (a VARCHAR(5000),FULLTEXT (a)) engine=innodb DEFAULT CHARSET=utf8; +#INSERT IGNORE INTO t VALUES(@value); +#SELECT COUNT(*) FROM t; +# +#--connection node_2 +#SELECT COUNT(*) FROM t; +# +#--connection node_1 +#DROP TABLE t; +#CREATE TABLE t (a VARCHAR(5000)) engine=innodb DEFAULT CHARSET=utf8; +#INSERT IGNORE INTO t VALUES(@value); +#SELECT COUNT(*) FROM t; +# +#--connection node_2 +#SELECT COUNT(*) FROM t; +# +#--connection node_1 +#DROP TABLE t; -- cgit v1.2.1 From 3690c549c6e72646ba74f6b4c83813ee4ac3aea4 Mon Sep 17 00:00:00 2001 From: Oleksandr Byelkin Date: Fri, 23 Jul 2021 11:14:13 +0200 Subject: MDEV-24454 Crash at change_item_tree Use in_sum_func (and so nest_level) only in LEX to which SELECT lex belong to Reduce usage of current_select (because it does not always point on the correct SELECT_LEX, for example with prepare. Change context for all classes inherited from Item_ident (was only for Item_field) in case of pushing down it to HAVING. Now name resolution context have to have SELECT_LEX reference if the context is present. Fixed feedback plugin stack usage. --- mysql-test/r/view.result | 43 +++++++++++++++++++ .../suite/plugins/r/feedback_plugin_load.result | 16 +++++++ .../suite/plugins/t/feedback_plugin_load.test | 7 ++++ mysql-test/t/view.test | 49 ++++++++++++++++++++++ 4 files changed, 115 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/r/view.result b/mysql-test/r/view.result index ef4f0a48534..bae415c17ea 100644 --- a/mysql-test/r/view.result +++ b/mysql-test/r/view.result @@ -6833,5 +6833,48 @@ sum(z) DROP TABLE t1; DROP VIEW v1; # +# MDEV-24454: Crash at change_item_tree +# +CREATE TABLE t1(f0 INT); +CREATE VIEW v1 AS +SELECT +f0 AS f1 +FROM t1; +CREATE VIEW v2 AS +SELECT +(SELECT GROUP_CONCAT(v1.f1 SEPARATOR ', ') +FROM v1 n) AS f2, +GROUP_CONCAT('' SEPARATOR ', ') AS f3 +FROM v1; +CREATE VIEW v3 AS +SELECT 1 as f4 FROM v2; +CREATE PROCEDURE p1() +SELECT * FROM v3; +CALL p1(); +f4 +1 +CALL p1(); +f4 +1 +drop procedure p1; +drop view v1,v2,v3; +drop table t1; +# +# MDEV-25631: Crash in st_select_lex::mark_as_dependent with +# VIEW, aggregate and subquery +# +CREATE TABLE t1 (i1 int); +insert into t1 values (1),(2),(3); +CREATE VIEW v1 AS +SELECT t1.i1 FROM (t1 a JOIN t1 ON (t1.i1 = (SELECT t1.i1 FROM t1 b))); +SELECT 1 FROM (SELECT count(((SELECT i1 FROM v1))) FROM v1) dt ; +ERROR 21000: Subquery returns more than 1 row +delete from t1 where i1 > 1; +SELECT 1 FROM (SELECT count(((SELECT i1 FROM v1))) FROM v1) dt ; +1 +1 +drop view v1; +drop table t1; +# # End of 10.2 tests # diff --git a/mysql-test/suite/plugins/r/feedback_plugin_load.result b/mysql-test/suite/plugins/r/feedback_plugin_load.result index 843cd15ac94..8b2a561b9f0 100644 --- a/mysql-test/suite/plugins/r/feedback_plugin_load.result +++ b/mysql-test/suite/plugins/r/feedback_plugin_load.result @@ -24,3 +24,19 @@ VARIABLE_VALUE>0 VARIABLE_NAME 1 Collation used latin1_swedish_ci 1 Collation used utf8_bin 1 Collation used utf8_general_ci +prepare stmt from "SELECT VARIABLE_VALUE>0, VARIABLE_NAME FROM INFORMATION_SCHEMA.FEEDBACK WHERE VARIABLE_NAME LIKE 'Collation used %' ORDER BY VARIABLE_NAME"; +execute stmt; +VARIABLE_VALUE>0 VARIABLE_NAME +1 Collation used binary +1 Collation used latin1_bin +1 Collation used latin1_swedish_ci +1 Collation used utf8_bin +1 Collation used utf8_general_ci +execute stmt; +VARIABLE_VALUE>0 VARIABLE_NAME +1 Collation used binary +1 Collation used latin1_bin +1 Collation used latin1_swedish_ci +1 Collation used utf8_bin +1 Collation used utf8_general_ci +deallocate prepare stmt; diff --git a/mysql-test/suite/plugins/t/feedback_plugin_load.test b/mysql-test/suite/plugins/t/feedback_plugin_load.test index c0546cef0f9..9fbe523e5f3 100644 --- a/mysql-test/suite/plugins/t/feedback_plugin_load.test +++ b/mysql-test/suite/plugins/t/feedback_plugin_load.test @@ -42,3 +42,10 @@ if (`SELECT VERSION() LIKE '%embedded%'`) SELECT VARIABLE_VALUE>0, VARIABLE_NAME FROM INFORMATION_SCHEMA.FEEDBACK WHERE VARIABLE_NAME LIKE 'Collation used %' ORDER BY VARIABLE_NAME; + +prepare stmt from "SELECT VARIABLE_VALUE>0, VARIABLE_NAME FROM INFORMATION_SCHEMA.FEEDBACK WHERE VARIABLE_NAME LIKE 'Collation used %' ORDER BY VARIABLE_NAME"; + +execute stmt; +execute stmt; + +deallocate prepare stmt; diff --git a/mysql-test/t/view.test b/mysql-test/t/view.test index 8cb00f7a6f4..128fa853e10 100644 --- a/mysql-test/t/view.test +++ b/mysql-test/t/view.test @@ -6559,6 +6559,55 @@ SELECT sum(z) FROM v1; DROP TABLE t1; DROP VIEW v1; +--echo # +--echo # MDEV-24454: Crash at change_item_tree +--echo # + +CREATE TABLE t1(f0 INT); + +CREATE VIEW v1 AS +SELECT + f0 AS f1 +FROM t1; + +CREATE VIEW v2 AS +SELECT + (SELECT GROUP_CONCAT(v1.f1 SEPARATOR ', ') + FROM v1 n) AS f2, + GROUP_CONCAT('' SEPARATOR ', ') AS f3 +FROM v1; + +CREATE VIEW v3 AS +SELECT 1 as f4 FROM v2; + +CREATE PROCEDURE p1() + SELECT * FROM v3; + +CALL p1(); +CALL p1(); + +drop procedure p1; +drop view v1,v2,v3; +drop table t1; + +--echo # +--echo # MDEV-25631: Crash in st_select_lex::mark_as_dependent with +--echo # VIEW, aggregate and subquery +--echo # + +CREATE TABLE t1 (i1 int); +insert into t1 values (1),(2),(3); #not important +CREATE VIEW v1 AS + SELECT t1.i1 FROM (t1 a JOIN t1 ON (t1.i1 = (SELECT t1.i1 FROM t1 b))); + +--error ER_SUBQUERY_NO_1_ROW +SELECT 1 FROM (SELECT count(((SELECT i1 FROM v1))) FROM v1) dt ; +delete from t1 where i1 > 1; +SELECT 1 FROM (SELECT count(((SELECT i1 FROM v1))) FROM v1) dt ; + +drop view v1; +drop table t1; + --echo # --echo # End of 10.2 tests --echo # -- cgit v1.2.1 From e55c303cc5d955eb3467fc6a89d8537900fb31c3 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Jan=20Lindstr=C3=B6m?= Date: Fri, 24 Sep 2021 11:11:57 +0300 Subject: Add wait_condition before problematic select --- mysql-test/suite/galera/disabled.def | 1 - .../suite/galera/t/galera_var_ignore_apply_errors.cnf | 7 +++++++ .../galera/t/galera_var_ignore_apply_errors.test | 19 ++++++++++++++++++- 3 files changed, 25 insertions(+), 2 deletions(-) create mode 100644 mysql-test/suite/galera/t/galera_var_ignore_apply_errors.cnf (limited to 'mysql-test') diff --git a/mysql-test/suite/galera/disabled.def b/mysql-test/suite/galera/disabled.def index d742879bb10..96005fccf54 100644 --- a/mysql-test/suite/galera/disabled.def +++ b/mysql-test/suite/galera/disabled.def @@ -33,7 +33,6 @@ galera_pc_recovery : MDEV-25199 cluster fails to start up galera_shutdown_nonprim : MDEV-21493 galera.galera_shutdown_nonprim galera_toi_ddl_nonconflicting : MDEV-21518 galera.galera_toi_ddl_nonconflicting galera_toi_truncate : MDEV-22996 Hang on galera_toi_truncate test case -galera_var_ignore_apply_errors : MDEV-20451: Lock wait timeout exceeded in galera_var_ignore_apply_errors galera_var_node_address : MDEV-20485 Galera test failure galera_var_notify_cmd : MDEV-21905 Galera test galera_var_notify_cmd causes hang galera_var_reject_queries : assertion in inline_mysql_socket_send diff --git a/mysql-test/suite/galera/t/galera_var_ignore_apply_errors.cnf b/mysql-test/suite/galera/t/galera_var_ignore_apply_errors.cnf new file mode 100644 index 00000000000..545cc8147e0 --- /dev/null +++ b/mysql-test/suite/galera/t/galera_var_ignore_apply_errors.cnf @@ -0,0 +1,7 @@ +!include ../galera_2nodes.cnf + +[mysqld.1] +wsrep_debug=1 + +[mysqld.2] +wsrep_debug=1 diff --git a/mysql-test/suite/galera/t/galera_var_ignore_apply_errors.test b/mysql-test/suite/galera/t/galera_var_ignore_apply_errors.test index 0f8efad5163..5a00048a90e 100644 --- a/mysql-test/suite/galera/t/galera_var_ignore_apply_errors.test +++ b/mysql-test/suite/galera/t/galera_var_ignore_apply_errors.test @@ -3,7 +3,6 @@ # --source include/galera_cluster.inc ---source include/have_innodb.inc # @@ -73,11 +72,15 @@ SET GLOBAL wsrep_on = ON; DELETE FROM t1 WHERE f1 = 1; --connection node_1 +--let $wait_condition = SELECT COUNT(*) = 0 FROM t1; +--source include/wait_condition.inc SELECT COUNT(*) as expect_0 FROM t1; --connection node_2 --source include/galera_wait_ready.inc --let $wait_condition = SELECT VARIABLE_VALUE = 2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; --source include/wait_condition.inc +--let $wait_condition = SELECT COUNT(*) = 0 FROM t1; +--source include/wait_condition.inc SELECT COUNT(*) as expect_0 FROM t1; DROP TABLE t1; @@ -97,11 +100,15 @@ DELETE FROM t1 WHERE f1 = 2; COMMIT; --connection node_1 +--let $wait_condition = SELECT COUNT(*) = 1 FROM t1; +--source include/wait_condition.inc SELECT COUNT(*) as expect_1 FROM t1; --connection node_2 --source include/galera_wait_ready.inc --let $wait_condition = SELECT VARIABLE_VALUE = 2 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; --source include/wait_condition.inc +--let $wait_condition = SELECT COUNT(*) = 1 FROM t1; +--source include/wait_condition.inc SELECT COUNT(*) as expect_1 FROM t1; DROP TABLE t1; @@ -132,6 +139,8 @@ SELECT COUNT(*) as expect_0 FROM t1; --let $wait_condition = SELECT VARIABLE_VALUE = 'Primary' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status'; --source include/wait_condition.inc SELECT VARIABLE_VALUE = 'Primary' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status'; +--let $wait_condition = SELECT COUNT(*) = 0 FROM t1; +--source include/wait_condition.inc SELECT COUNT(*) as expect_0 FROM t1; DROP TABLE t1; @@ -171,6 +180,8 @@ SELECT COUNT(*) as expect_0 FROM t1; --let $wait_condition = SELECT VARIABLE_VALUE = 'Primary' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status'; --source include/wait_condition.inc SELECT VARIABLE_VALUE = 'Primary' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status'; +--let $wait_condition = SELECT COUNT(*) = 0 FROM t1; +--source include/wait_condition.inc SELECT COUNT(*) as expect_0 FROM t1; DROP TABLE t1; @@ -205,6 +216,8 @@ SELECT COUNT(*) as expect_0 FROM t1; --let $wait_condition = SELECT VARIABLE_VALUE = 'Primary' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status'; --source include/wait_condition.inc SELECT VARIABLE_VALUE = 'Primary' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status'; +--let $wait_condition = SELECT COUNT(*) = 0 FROM t1; +--source include/wait_condition.inc SELECT COUNT(*) as expect_0 FROM t1; DROP TABLE t1,t2; @@ -239,6 +252,10 @@ SELECT COUNT(*) as expect_0 FROM child; --let $wait_condition = SELECT VARIABLE_VALUE = 'Primary' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status'; --source include/wait_condition.inc SELECT VARIABLE_VALUE = 'Primary' FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_status'; +--let $wait_condition = SELECT COUNT(*) = 0 FROM parent; +--source include/wait_condition.inc +--let $wait_condition = SELECT COUNT(*) = 0 FROM child; +--source include/wait_condition.inc SELECT COUNT(*) as expect_0 FROM parent; SELECT COUNT(*) as expect_0 FROM child; DROP TABLE child, parent; -- cgit v1.2.1 From 05abcd7e600ddc2dee280b000439ded2855ff772 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Jan=20Lindstr=C3=B6m?= Date: Fri, 24 Sep 2021 12:28:15 +0300 Subject: MDEV-21806 : galera.galera_partition MTR failed: failed to recover from DONOR state Add wait_condition to wait until all nodes are in cluster --- mysql-test/suite/galera/r/galera_partition.result | 2 ++ mysql-test/suite/galera/t/galera_partition.cnf | 4 ++++ mysql-test/suite/galera/t/galera_partition.test | 6 ++++++ 3 files changed, 12 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/suite/galera/r/galera_partition.result b/mysql-test/suite/galera/r/galera_partition.result index f09a0272eda..0e8894794d7 100644 --- a/mysql-test/suite/galera/r/galera_partition.result +++ b/mysql-test/suite/galera/r/galera_partition.result @@ -424,3 +424,5 @@ connection node_4; connection node_1; DROP TABLE t1; DROP PROCEDURE p1; +disconnect node_3; +disconnect node_4; diff --git a/mysql-test/suite/galera/t/galera_partition.cnf b/mysql-test/suite/galera/t/galera_partition.cnf index e6cb13ef523..525eece04ab 100644 --- a/mysql-test/suite/galera/t/galera_partition.cnf +++ b/mysql-test/suite/galera/t/galera_partition.cnf @@ -3,16 +3,20 @@ [mysqld.1] wsrep_provider_options='base_port=@mysqld.1.#galera_port;gcache.size=10M;gmcast.segment=1' wsrep_slave_threads=10 +wsrep_debug=1 [mysqld.2] wsrep_provider_options='base_port=@mysqld.2.#galera_port;gcache.size=10M;gmcast.segment=1' wsrep_slave_threads=10 +wsrep_debug=1 [mysqld.3] wsrep_provider_options='base_port=@mysqld.3.#galera_port;gcache.size=10M;gmcast.segment=2' wsrep_slave_threads=10 +wsrep_debug=1 [mysqld.4] wsrep_provider_options='base_port=@mysqld.4.#galera_port;gcache.size=10M;gmcast.segment=3' wsrep_slave_threads=10 +wsrep_debug=1 diff --git a/mysql-test/suite/galera/t/galera_partition.test b/mysql-test/suite/galera/t/galera_partition.test index bf3f02eaa92..3de45d54000 100644 --- a/mysql-test/suite/galera/t/galera_partition.test +++ b/mysql-test/suite/galera/t/galera_partition.test @@ -11,6 +11,8 @@ call mtr.add_suppression("WSREP: ALTER TABLE isolation failure"); --connect node_4, 127.0.0.1, root, , test, $NODE_MYPORT_4 --connection node_1 +--let $wait_condition = SELECT VARIABLE_VALUE = 4 FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME = 'wsrep_cluster_size'; +--source include/wait_condition.inc CREATE TABLE t1( id bigint unsigned NOT NULL AUTO_INCREMENT, @@ -449,3 +451,7 @@ reap; DROP TABLE t1; DROP PROCEDURE p1; +--disconnect node_3 +--disconnect node_4 + + -- cgit v1.2.1 From 1f099418b6cce00b72f601288735bd7454d037cd Mon Sep 17 00:00:00 2001 From: Vladislav Vaintroub Date: Mon, 27 Sep 2021 17:43:36 +0200 Subject: MDEV-20699 mysqldump of routines causes MariaDB to get killed by oom-killer The reason for this behavior is that SP get cached, per connection. The stored_program_cache is size of this cache, which amounts to 256 routines by default. A compiled stored procedure can easily be several megabytes in size. Thus calling SHOW CREATE PROCEDURE for all stored procedures, like mysqldump does, can require significant amount of memory. Fixed by bypassing the cache for "SHOW CREATE". This should normally be fine also perfomance-wise, as cache is meant to be used for repeated execution, not repeated SHOW CREATEs. Added a test to verify that CREATE PROCEDURE + SHOW CREATE PROCEURE do not cache, i.e amount of allocated memory does not change. Note, there is a change in existing behavior in an edge case : If "SHOW CREATE PROCEDURE p1" called from p1, after p1 was altered, now this will now return altered code. Previour behavior - relied on caching and would return old code. The previous behavior might was not necessarily correct. --- mysql-test/main/sp-bugs.result | 4 ++-- mysql-test/main/sp-lock.result | 8 +------- mysql-test/main/sp-lock.test | 3 --- mysql-test/main/sp.result | 22 ++++++++++++++++++++++ mysql-test/main/sp.test | 17 +++++++++++++++++ 5 files changed, 42 insertions(+), 12 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/main/sp-bugs.result b/mysql-test/main/sp-bugs.result index f88b3b137d3..d758981b6ba 100644 --- a/mysql-test/main/sp-bugs.result +++ b/mysql-test/main/sp-bugs.result @@ -182,7 +182,7 @@ RETURN 0; END latin1 latin1_swedish_ci latin1_swedish_ci SHOW CREATE FUNCTION TESTF_bug11763507; Function sql_mode Create Function character_set_client collation_connection Database Collation -testf_bug11763507 CREATE DEFINER=`root`@`localhost` FUNCTION `testf_bug11763507`() RETURNS int(11) +TESTF_bug11763507 CREATE DEFINER=`root`@`localhost` FUNCTION `TESTF_bug11763507`() RETURNS int(11) BEGIN RETURN 0; END latin1 latin1_swedish_ci latin1_swedish_ci @@ -212,7 +212,7 @@ SELECT "PROCEDURE testp_bug11763507"; END latin1 latin1_swedish_ci latin1_swedish_ci SHOW CREATE PROCEDURE TESTP_bug11763507; Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation -testp_bug11763507 CREATE DEFINER=`root`@`localhost` PROCEDURE `testp_bug11763507`() +TESTP_bug11763507 CREATE DEFINER=`root`@`localhost` PROCEDURE `TESTP_bug11763507`() BEGIN SELECT "PROCEDURE testp_bug11763507"; END latin1 latin1_swedish_ci latin1_swedish_ci diff --git a/mysql-test/main/sp-lock.result b/mysql-test/main/sp-lock.result index acf951f6676..ec8d8970ae3 100644 --- a/mysql-test/main/sp-lock.result +++ b/mysql-test/main/sp-lock.result @@ -703,9 +703,6 @@ connection default; # # SHOW CREATE PROCEDURE p1 called from p1, after p1 was altered # -# We are just covering the existing behaviour with tests. The -# results are not necessarily correct." -# CREATE PROCEDURE p1() BEGIN SELECT get_lock("test", 10); @@ -736,10 +733,7 @@ get_lock("test", 10) 1 Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation p1 STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION CREATE DEFINER=`root`@`localhost` PROCEDURE `p1`() -BEGIN -SELECT get_lock("test", 10); -SHOW CREATE PROCEDURE p1; -END latin1 latin1_swedish_ci latin1_swedish_ci +BEGIN END latin1 latin1_swedish_ci latin1_swedish_ci connection con3; disconnect con3; connection con2; diff --git a/mysql-test/main/sp-lock.test b/mysql-test/main/sp-lock.test index 83ea07d4bda..703c59e8f28 100644 --- a/mysql-test/main/sp-lock.test +++ b/mysql-test/main/sp-lock.test @@ -807,9 +807,6 @@ connection default; --echo # --echo # SHOW CREATE PROCEDURE p1 called from p1, after p1 was altered --echo # ---echo # We are just covering the existing behaviour with tests. The ---echo # results are not necessarily correct." ---echo # delimiter |; CREATE PROCEDURE p1() diff --git a/mysql-test/main/sp.result b/mysql-test/main/sp.result index 6510eaf77f5..694c7bcd20e 100644 --- a/mysql-test/main/sp.result +++ b/mysql-test/main/sp.result @@ -8893,4 +8893,26 @@ ERROR 42000: You have an error in your SQL syntax; check the manual that corresp BEGIN RETURN ''; END' at line 2 +SELECT VARIABLE_VALUE into @global_mem_used FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +SELECT VARIABLE_VALUE into @local_mem_used FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +CREATE PROCEDURE sp0() SELECT 1; +SHOW CREATE PROCEDURE sp0; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +sp0 STRICT_ALL_TABLES CREATE DEFINER=`root`@`localhost` PROCEDURE `sp0`() +SELECT 1 latin1 latin1_swedish_ci latin1_swedish_ci +DROP PROCEDURE sp0; +SELECT VARIABLE_VALUE into @global_mem_used FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +SELECT VARIABLE_VALUE into @local_mem_used FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +CREATE PROCEDURE sp1() SELECT 1; +SHOW CREATE PROCEDURE sp1; +Procedure sql_mode Create Procedure character_set_client collation_connection Database Collation +sp1 STRICT_ALL_TABLES CREATE DEFINER=`root`@`localhost` PROCEDURE `sp1`() +SELECT 1 latin1 latin1_swedish_ci latin1_swedish_ci +SELECT VARIABLE_VALUE-@local_mem_used FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +VARIABLE_VALUE-@local_mem_used +0 +SELECT VARIABLE_VALUE-@global_mem_used FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +VARIABLE_VALUE-@global_mem_used +0 +DROP PROCEDURE sp1; # End of 10.3 tests diff --git a/mysql-test/main/sp.test b/mysql-test/main/sp.test index d6fba77b854..50ae78c8431 100644 --- a/mysql-test/main/sp.test +++ b/mysql-test/main/sp.test @@ -10431,4 +10431,21 @@ END; $$ DELIMITER ;$$ +# MDEV-20699 do not cache SP in SHOW CREATE +# Warmup round, this might allocate some memory for session variable +# and the output +SELECT VARIABLE_VALUE into @global_mem_used FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +SELECT VARIABLE_VALUE into @local_mem_used FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +CREATE PROCEDURE sp0() SELECT 1; +SHOW CREATE PROCEDURE sp0; +DROP PROCEDURE sp0; + +#Check that CREATE/SHOW does not use memory in caches. +SELECT VARIABLE_VALUE into @global_mem_used FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +SELECT VARIABLE_VALUE into @local_mem_used FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +CREATE PROCEDURE sp1() SELECT 1; +SHOW CREATE PROCEDURE sp1; +SELECT VARIABLE_VALUE-@local_mem_used FROM INFORMATION_SCHEMA.SESSION_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +SELECT VARIABLE_VALUE-@global_mem_used FROM INFORMATION_SCHEMA.GLOBAL_STATUS WHERE VARIABLE_NAME='MEMORY_USED'; +DROP PROCEDURE sp1; --echo # End of 10.3 tests -- cgit v1.2.1 From b2a5e0f28232b56c5c36e65a457d41d819b279bf Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Wed, 29 Sep 2021 12:13:11 +0300 Subject: Make innodb.innodb_defrag_stats more deterministic Let us mask the actual values of the defragmentation-related fields, because they may vary. Also, remove the dependency on purge, and instead delete records by a ROLLBACK of INSERT. --- .../suite/innodb/r/innodb_defrag_stats.result | 229 ++++++++++----------- mysql-test/suite/innodb/t/innodb_defrag_stats.test | 76 +++---- 2 files changed, 143 insertions(+), 162 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/suite/innodb/r/innodb_defrag_stats.result b/mysql-test/suite/innodb/r/innodb_defrag_stats.result index 6c5fe1817e2..d1e9e2e78ae 100644 --- a/mysql-test/suite/innodb/r/innodb_defrag_stats.result +++ b/mysql-test/suite/innodb/r/innodb_defrag_stats.result @@ -1,123 +1,118 @@ SET GLOBAL innodb_defragment_stats_accuracy = 20; DELETE FROM mysql.innodb_index_stats; # Create table. -CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), KEY SECOND(a, b)) ENGINE=INNODB; -INSERT INTO t1 SELECT seq, REPEAT('A', 256) FROM seq_1_to_1024; +CREATE TABLE t1 (a INT PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), +KEY SECOND(a, b)) ENGINE=INNODB STATS_PERSISTENT=0; +INSERT INTO t1 SELECT 100*FLOOR(seq/70)+seq%70, REPEAT('A', 256) +FROM seq_1_to_1024; # Not enough page splits to trigger persistent stats write yet. -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -count(stat_value) = 0 -1 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -count(stat_value) = 0 -1 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) = 0 -1 -INSERT INTO t1 SELECT seq, REPEAT('A', 256) FROM seq_1025_to_2048; -# Persistent stats recorded. -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -count(stat_value) > 0 -1 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -count(stat_value) = 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) > 0 -1 -# Delete some rows. +SELECT * FROM mysql.innodb_index_stats; +database_name table_name index_name last_update stat_name stat_value sample_size stat_description +INSERT INTO t1 SELECT 100*FLOOR(seq/70)+seq%70, REPEAT('A', 256) +FROM seq_1025_to_1433; BEGIN; -delete from t1 where a between 100 * 20 and 100 * 20 + 30; -delete from t1 where a between 100 * 19 and 100 * 19 + 30; -delete from t1 where a between 100 * 18 and 100 * 18 + 30; -delete from t1 where a between 100 * 17 and 100 * 17 + 30; -delete from t1 where a between 100 * 16 and 100 * 16 + 30; -delete from t1 where a between 100 * 15 and 100 * 15 + 30; -delete from t1 where a between 100 * 14 and 100 * 14 + 30; -delete from t1 where a between 100 * 13 and 100 * 13 + 30; -delete from t1 where a between 100 * 12 and 100 * 12 + 30; -delete from t1 where a between 100 * 11 and 100 * 11 + 30; -delete from t1 where a between 100 * 10 and 100 * 10 + 30; -delete from t1 where a between 100 * 9 and 100 * 9 + 30; -delete from t1 where a between 100 * 8 and 100 * 8 + 30; -delete from t1 where a between 100 * 7 and 100 * 7 + 30; -delete from t1 where a between 100 * 6 and 100 * 6 + 30; -delete from t1 where a between 100 * 5 and 100 * 5 + 30; -delete from t1 where a between 100 * 4 and 100 * 4 + 30; -delete from t1 where a between 100 * 3 and 100 * 3 + 30; -delete from t1 where a between 100 * 2 and 100 * 2 + 30; -delete from t1 where a between 100 * 1 and 100 * 1 + 30; -COMMIT; -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -count(stat_value) > 0 -1 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -count(stat_value) = 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) > 0 -1 +INSERT INTO t1 SELECT 100*20+seq, REPEAT('A', 256) +FROM seq_70_to_99; +INSERT INTO t1 SELECT 100*19+seq, REPEAT('A', 256) +FROM seq_70_to_99; +INSERT INTO t1 SELECT 100*18+seq, REPEAT('A', 256) +FROM seq_70_to_99; +INSERT INTO t1 SELECT 100*17+seq, REPEAT('A', 256) +FROM seq_70_to_99; +INSERT INTO t1 SELECT 100*16+seq, REPEAT('A', 256) +FROM seq_70_to_99; +INSERT INTO t1 SELECT 100*15+seq, REPEAT('A', 256) +FROM seq_70_to_99; +INSERT INTO t1 SELECT 100*14+seq, REPEAT('A', 256) +FROM seq_70_to_99; +INSERT INTO t1 SELECT 100*13+seq, REPEAT('A', 256) +FROM seq_70_to_99; +INSERT INTO t1 SELECT 100*12+seq, REPEAT('A', 256) +FROM seq_70_to_99; +INSERT INTO t1 SELECT 100*11+seq, REPEAT('A', 256) +FROM seq_70_to_99; +INSERT INTO t1 SELECT 100*10+seq, REPEAT('A', 256) +FROM seq_70_to_99; +INSERT INTO t1 SELECT 100*9+seq, REPEAT('A', 256) +FROM seq_70_to_99; +INSERT INTO t1 SELECT 100*8+seq, REPEAT('A', 256) +FROM seq_70_to_99; +INSERT INTO t1 SELECT 100*7+seq, REPEAT('A', 256) +FROM seq_70_to_99; +INSERT INTO t1 SELECT 100*6+seq, REPEAT('A', 256) +FROM seq_70_to_99; +INSERT INTO t1 SELECT 100*5+seq, REPEAT('A', 256) +FROM seq_70_to_99; +INSERT INTO t1 SELECT 100*4+seq, REPEAT('A', 256) +FROM seq_70_to_99; +INSERT INTO t1 SELECT 100*3+seq, REPEAT('A', 256) +FROM seq_70_to_99; +INSERT INTO t1 SELECT 100*2+seq, REPEAT('A', 256) +FROM seq_70_to_99; +INSERT INTO t1 SELECT 100*1+seq, REPEAT('A', 256) +FROM seq_70_to_99; +ROLLBACK; +SELECT @@GLOBAL.innodb_force_recovery<2 "have background defragmentation"; +have background defragmentation +1 +SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; +table_name index_name stat_name +t1 PRIMARY n_leaf_pages_defrag +t1 PRIMARY n_leaf_pages_reserved +t1 PRIMARY n_page_split +t1 SECOND n_leaf_pages_defrag +t1 SECOND n_leaf_pages_reserved +t1 SECOND n_page_split optimize table t1; Table Op Msg_type Msg_text test.t1 optimize status OK -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -count(stat_value) > 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -count(stat_value) > 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) > 0 -1 +SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; +table_name index_name stat_name +t1 PRIMARY n_leaf_pages_defrag +t1 PRIMARY n_leaf_pages_reserved +t1 PRIMARY n_page_split +t1 PRIMARY n_pages_freed +t1 SECOND n_leaf_pages_defrag +t1 SECOND n_leaf_pages_reserved +t1 SECOND n_page_split +t1 SECOND n_pages_freed set global innodb_defragment_stats_accuracy = 40; INSERT INTO t1 (b) SELECT b from t1; -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -count(stat_value) > 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -count(stat_value) > 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) > 0 -1 +SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; +table_name index_name stat_name +t1 PRIMARY n_leaf_pages_defrag +t1 PRIMARY n_leaf_pages_reserved +t1 PRIMARY n_page_split +t1 PRIMARY n_pages_freed +t1 SECOND n_leaf_pages_defrag +t1 SECOND n_leaf_pages_reserved +t1 SECOND n_page_split +t1 SECOND n_pages_freed INSERT INTO t1 (b) SELECT b from t1; -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -count(stat_value) > 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -count(stat_value) > 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) > 0 -1 +SELECT stat_name FROM mysql.innodb_index_stats WHERE table_name='t1'; +stat_name +n_leaf_pages_defrag +n_leaf_pages_defrag +n_leaf_pages_reserved +n_leaf_pages_reserved +n_page_split +n_page_split +n_pages_freed +n_pages_freed # Table rename should cause stats rename. rename table t1 to t2; -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -count(stat_value) = 0 -1 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -count(stat_value) = 0 -1 -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) = 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_page_split'); -count(stat_value) > 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_pages_freed'); -count(stat_value) > 0 -1 -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_leaf_pages_defrag'); -count(stat_value) > 0 -1 +SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; +table_name index_name stat_name +t2 PRIMARY n_leaf_pages_defrag +t2 PRIMARY n_leaf_pages_reserved +t2 PRIMARY n_page_split +t2 PRIMARY n_pages_freed +t2 SECOND n_leaf_pages_defrag +t2 SECOND n_leaf_pages_reserved +t2 SECOND n_page_split +t2 SECOND n_pages_freed # Drop index should cause stats drop, but will not. drop index SECOND on t2; -SELECT stat_name, stat_value>0 FROM mysql.innodb_index_stats -WHERE table_name like '%t2%' AND index_name='SECOND'; -stat_name stat_value>0 -n_leaf_pages_defrag 1 -n_leaf_pages_reserved 1 -n_page_split 1 -n_pages_freed 1 # # MDEV-26636: Statistics must not be written for temporary tables # @@ -125,20 +120,18 @@ SET GLOBAL innodb_defragment_stats_accuracy = 1; CREATE TEMPORARY TABLE t (a INT PRIMARY KEY, c CHAR(255) NOT NULL) ENGINE=InnoDB; INSERT INTO t SELECT seq, '' FROM seq_1_to_100; -SELECT * FROM mysql.innodb_index_stats where table_name like '%t1%'; -database_name table_name index_name last_update stat_name stat_value sample_size stat_description -SELECT table_name, index_name, stat_name, stat_value>0 -FROM mysql.innodb_index_stats; -table_name index_name stat_name stat_value>0 -t2 PRIMARY n_leaf_pages_defrag 1 -t2 PRIMARY n_leaf_pages_reserved 1 -t2 PRIMARY n_page_split 1 -t2 PRIMARY n_pages_freed 1 -t2 SECOND n_leaf_pages_defrag 1 -t2 SECOND n_leaf_pages_reserved 1 -t2 SECOND n_page_split 1 -t2 SECOND n_pages_freed 1 +SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; +table_name index_name stat_name +t2 PRIMARY n_leaf_pages_defrag +t2 PRIMARY n_leaf_pages_reserved +t2 PRIMARY n_page_split +t2 PRIMARY n_pages_freed +t2 SECOND n_leaf_pages_defrag +t2 SECOND n_leaf_pages_reserved +t2 SECOND n_page_split +t2 SECOND n_pages_freed # Clean up +ALTER TABLE t2 STATS_PERSISTENT=1; DROP TABLE t2; SELECT * FROM mysql.innodb_index_stats; database_name table_name index_name last_update stat_name stat_value sample_size stat_description diff --git a/mysql-test/suite/innodb/t/innodb_defrag_stats.test b/mysql-test/suite/innodb/t/innodb_defrag_stats.test index e1e88a07477..799faa93ff0 100644 --- a/mysql-test/suite/innodb/t/innodb_defrag_stats.test +++ b/mysql-test/suite/innodb/t/innodb_defrag_stats.test @@ -8,77 +8,65 @@ SET GLOBAL innodb_defragment_stats_accuracy = 20; DELETE FROM mysql.innodb_index_stats; --echo # Create table. -CREATE TABLE t1 (a INT NOT NULL PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), KEY SECOND(a, b)) ENGINE=INNODB; +CREATE TABLE t1 (a INT PRIMARY KEY AUTO_INCREMENT, b VARCHAR(256), + KEY SECOND(a, b)) ENGINE=INNODB STATS_PERSISTENT=0; -INSERT INTO t1 SELECT seq, REPEAT('A', 256) FROM seq_1_to_1024; +INSERT INTO t1 SELECT 100*FLOOR(seq/70)+seq%70, REPEAT('A', 256) +FROM seq_1_to_1024; --echo # Not enough page splits to trigger persistent stats write yet. -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); - -INSERT INTO t1 SELECT seq, REPEAT('A', 256) FROM seq_1025_to_2048; +SELECT * FROM mysql.innodb_index_stats; ---echo # Persistent stats recorded. -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); +INSERT INTO t1 SELECT 100*FLOOR(seq/70)+seq%70, REPEAT('A', 256) +FROM seq_1025_to_1433; ---echo # Delete some rows. BEGIN; let $num_delete = 20; while ($num_delete) { - let $j = 100 * $num_delete; - eval delete from t1 where a between $j and $j + 30; + eval INSERT INTO t1 SELECT 100*$num_delete+seq, REPEAT('A', 256) + FROM seq_70_to_99; dec $num_delete; } -COMMIT; +ROLLBACK; + +SELECT @@GLOBAL.innodb_force_recovery<2 "have background defragmentation"; + +# Wait for defrag_pool to be processed. -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); +let $wait_timeout=30; +let $wait_condition = SELECT COUNT(*)>0 FROM mysql.innodb_index_stats; +--source include/wait_condition.inc + +--sorted_result +SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; optimize table t1; -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); +--sorted_result +SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; set global innodb_defragment_stats_accuracy = 40; INSERT INTO t1 (b) SELECT b from t1; -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); - +--sorted_result +SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; INSERT INTO t1 (b) SELECT b from t1; -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); - +--sorted_result +SELECT stat_name FROM mysql.innodb_index_stats WHERE table_name='t1'; --echo # Table rename should cause stats rename. rename table t1 to t2; -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_page_split'); -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_pages_freed'); -select count(stat_value) = 0 from mysql.innodb_index_stats where table_name like '%t1%' and stat_name in ('n_leaf_pages_defrag'); - -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_page_split'); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_pages_freed'); -select count(stat_value) > 0 from mysql.innodb_index_stats where table_name like '%t2%' and stat_name in ('n_leaf_pages_defrag'); +--sorted_result +SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; --echo # Drop index should cause stats drop, but will not. drop index SECOND on t2; ---sorted_result -SELECT stat_name, stat_value>0 FROM mysql.innodb_index_stats -WHERE table_name like '%t2%' AND index_name='SECOND'; - --echo # --echo # MDEV-26636: Statistics must not be written for temporary tables --echo # @@ -89,13 +77,13 @@ INSERT INTO t SELECT seq, '' FROM seq_1_to_100; --source include/restart_mysqld.inc -SELECT * FROM mysql.innodb_index_stats where table_name like '%t1%'; - --sorted_result -SELECT table_name, index_name, stat_name, stat_value>0 -FROM mysql.innodb_index_stats; +SELECT table_name, index_name, stat_name FROM mysql.innodb_index_stats; --echo # Clean up +# Starting with 10.6, DROP TABLE will not touch persistent statistics +# (not defragmentation statistics either) if the table has none! +ALTER TABLE t2 STATS_PERSISTENT=1; DROP TABLE t2; SELECT * FROM mysql.innodb_index_stats; -- cgit v1.2.1 From 4e9366df7b097ae38db8ead75a4f4e5d58ad8dca Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Wed, 29 Sep 2021 14:50:38 +0300 Subject: MDEV-26672 test fixup Occasionally, after restart, additional transactions will have been executed, possibly related to innodb_stats_auto_recalc. We should only care that the transaction ID sequence does not go backwards. --- mysql-test/suite/innodb/t/undo_truncate.test | 4 ++-- 1 file changed, 2 insertions(+), 2 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/suite/innodb/t/undo_truncate.test b/mysql-test/suite/innodb/t/undo_truncate.test index 32697e59c91..51bb4f4b9fc 100644 --- a/mysql-test/suite/innodb/t/undo_truncate.test +++ b/mysql-test/suite/innodb/t/undo_truncate.test @@ -53,7 +53,7 @@ let $trx_after= `select substr('$trx_after',9)`; drop table t1, t2; -if ($trx_before != $trx_after) +if ($trx_before > $trx_after) { - echo Transaction sequence mismatch: $trx_before != $trx_after; + echo Transaction sequence mismatch: $trx_before > $trx_after; } -- cgit v1.2.1 From f3bc4f49f7c02018cac2c721837f9d1f52e9fff0 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Wed, 29 Sep 2021 15:16:04 +0300 Subject: MDEV-20699 fixup: Re-record compat/oracle.sp-package result --- mysql-test/suite/compat/oracle/r/sp-package.result | 8 ++++++++ 1 file changed, 8 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/suite/compat/oracle/r/sp-package.result b/mysql-test/suite/compat/oracle/r/sp-package.result index 9a53b04d4ad..598c766c808 100644 --- a/mysql-test/suite/compat/oracle/r/sp-package.result +++ b/mysql-test/suite/compat/oracle/r/sp-package.result @@ -699,6 +699,10 @@ END character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci +Warnings: +Level Note +Code 1585 +Message This function 'concat' has the same name as a native function SHOW CREATE PACKAGE BODY test2; Package body test2 sql_mode PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,ORACLE,NO_KEY_OPTIONS,NO_TABLE_OPTIONS,NO_FIELD_OPTIONS,NO_AUTO_CREATE_USER,SIMULTANEOUS_ASSIGNMENT @@ -719,6 +723,10 @@ END character_set_client latin1 collation_connection latin1_swedish_ci Database Collation latin1_swedish_ci +Warnings: +Level Note +Code 1585 +Message This function 'concat' has the same name as a native function DROP PACKAGE BODY test2; SELECT test2.f1(); ERROR 42000: FUNCTION test.test2.f1 does not exist -- cgit v1.2.1 From 333d6c30f87a7862a2d6ca379c49e2ea46451ebc Mon Sep 17 00:00:00 2001 From: Vladislav Vaintroub Date: Wed, 29 Sep 2021 20:40:00 +0200 Subject: MDEV-20699 followup. Normally we disable caching of routines in "SHOW CREATE". Introduce an exception, if debug_dbug="+d,cache_sp_in_show_create". lock_sync.test needs a way to populate the cache without side effects, or else it runs into debug_sync timeouts. So, this possibility to cache will be remain only for very special tests. --- mysql-test/main/lock_sync.result | 3 +++ mysql-test/main/lock_sync.test | 3 +++ 2 files changed, 6 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/main/lock_sync.result b/mysql-test/main/lock_sync.result index d017cf90cb8..55ceaedd04f 100644 --- a/mysql-test/main/lock_sync.result +++ b/mysql-test/main/lock_sync.result @@ -205,6 +205,8 @@ end| # called below. # connection con1; +set @save_dbug=@@debug_dbug; +set debug_dbug="+d,cache_sp_in_show_create"; # Cache all functions used in the tests below so statements # calling them won't need to open and lock mysql.proc table # and we can assume that each statement locks its tables @@ -229,6 +231,7 @@ show create function f14; show create function f15; show create function f16; show create function f17; +set debug_dbug=@save_dbug; connection default; # # 1. Statements that read tables and do not use subqueries. diff --git a/mysql-test/main/lock_sync.test b/mysql-test/main/lock_sync.test index af8435f7fbb..b576f211792 100644 --- a/mysql-test/main/lock_sync.test +++ b/mysql-test/main/lock_sync.test @@ -232,6 +232,8 @@ let $con_aux2= con2; let $table= t1; connection con1; +set @save_dbug=@@debug_dbug; +set debug_dbug="+d,cache_sp_in_show_create"; --echo # Cache all functions used in the tests below so statements --echo # calling them won't need to open and lock mysql.proc table --echo # and we can assume that each statement locks its tables @@ -257,6 +259,7 @@ show create function f14; show create function f15; show create function f16; show create function f17; +set debug_dbug=@save_dbug; --enable_result_log connection default; -- cgit v1.2.1