From 0ec27d7b1f7b5f1f49a3099faa554994bc58603a Mon Sep 17 00:00:00 2001 From: Monty Date: Wed, 2 Feb 2022 14:26:38 +0200 Subject: Don't run innodb_defgragment under valgrind (too slow) --- mysql-test/suite/innodb/t/innodb_defragment.test | 2 ++ 1 file changed, 2 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/suite/innodb/t/innodb_defragment.test b/mysql-test/suite/innodb/t/innodb_defragment.test index d9f5f56316e..51ef78377cb 100644 --- a/mysql-test/suite/innodb/t/innodb_defragment.test +++ b/mysql-test/suite/innodb/t/innodb_defragment.test @@ -1,6 +1,8 @@ --source include/have_innodb.inc --source include/big_test.inc --source include/not_embedded.inc +# Valgrind is to slow for this test +--source include/not_valgrind.inc set global innodb_defragment_stats_accuracy = 80; -- cgit v1.2.1 From a1c23807530c6ffd5d400f71d9226bca5b91fb62 Mon Sep 17 00:00:00 2001 From: Monty Date: Sun, 6 Feb 2022 16:05:48 +0200 Subject: MENT-328 Retry BACKUP STAGE BLOCK DDL in case of deadlocks MENT-328 wrongly assumed that the backup failed because of warnings from mariabackup about not found files. This is normal (and the error message should be deleted). randgen failed because mariabackup didn't retry BACKUP STAGE BLOCK DDL if it failed with a deadlock. To simplify things, I implemented the retry loop in the server as this particular deadlock should be quickly resolved. --- mysql-test/main/backup_locks.result | 23 ++++++++++++++++++++++- mysql-test/main/backup_locks.test | 31 +++++++++++++++++++++++++++++-- 2 files changed, 51 insertions(+), 3 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/main/backup_locks.result b/mysql-test/main/backup_locks.result index 1505c39f166..1e567c1a58d 100644 --- a/mysql-test/main/backup_locks.result +++ b/mysql-test/main/backup_locks.result @@ -39,6 +39,28 @@ MDL_INTENTION_EXCLUSIVE Schema metadata lock test select * from t1; ERROR 40001: Deadlock found when trying to get lock; try restarting transaction backup unlock; +connection con1; +connection default; +# +# Check that BACKUP LOCK blocks some operations +# +create sequence seq1; +create sequence seq2; +backup lock seq1; +connection con1; +CREATE OR REPLACE SEQUENCE seq1 START -28; +ERROR HY000: Sequence 'test.seq1' values are conflicting +SET STATEMENT max_statement_time=10 FOR CREATE OR REPLACE SEQUENCE seq1 START 50; +ERROR 70100: Query execution was interrupted (max_statement_time exceeded) +SET STATEMENT max_statement_time=10 FOR ALTER SEQUENCE IF EXISTS seq1 NOMAXVALUE; +ERROR 70100: Query execution was interrupted (max_statement_time exceeded) +SET STATEMENT max_statement_time=10 FOR ALTER SEQUENCE IF EXISTS seq1 MAXVALUE 1000; +ERROR 70100: Query execution was interrupted (max_statement_time exceeded) +SET STATEMENT max_statement_time=10 for rename table seq2 to seq3, seq3 to seq1; +ERROR 70100: Query execution was interrupted (max_statement_time exceeded) +connection default; +backup unlock; +drop table seq1,seq2; # # BACKUP LOCK and BACKUP UNLOCK are not allowed in procedures. # @@ -141,7 +163,6 @@ ERROR HY000: Can't execute the given command because you have active locked tabl SET STATEMENT max_statement_time=180 FOR BACKUP LOCK test.u; # restart # -connection con1; connection default; disconnect con1; show tables; diff --git a/mysql-test/main/backup_locks.test b/mysql-test/main/backup_locks.test index d2f3d95d703..1271abfd993 100644 --- a/mysql-test/main/backup_locks.test +++ b/mysql-test/main/backup_locks.test @@ -43,10 +43,39 @@ SELECT LOCK_MODE, LOCK_TYPE, TABLE_SCHEMA, TABLE_NAME FROM information_schema.me --error ER_LOCK_DEADLOCK select * from t1; backup unlock; +connection con1; +--reap +connection default; + +--echo # +--echo # Check that BACKUP LOCK blocks some operations +--echo # + +# These test has to be done with timeouts as we want to ensure that the tables +# doesn't change + +create sequence seq1; +create sequence seq2; +backup lock seq1; +connection con1; +--error ER_SEQUENCE_INVALID_DATA +CREATE OR REPLACE SEQUENCE seq1 START -28; +--error ER_STATEMENT_TIMEOUT +SET STATEMENT max_statement_time=10 FOR CREATE OR REPLACE SEQUENCE seq1 START 50; +--error ER_STATEMENT_TIMEOUT +SET STATEMENT max_statement_time=10 FOR ALTER SEQUENCE IF EXISTS seq1 NOMAXVALUE; +--error ER_STATEMENT_TIMEOUT +SET STATEMENT max_statement_time=10 FOR ALTER SEQUENCE IF EXISTS seq1 MAXVALUE 1000; +--error ER_STATEMENT_TIMEOUT +SET STATEMENT max_statement_time=10 for rename table seq2 to seq3, seq3 to seq1; +connection default; +backup unlock; +drop table seq1,seq2; --echo # --echo # BACKUP LOCK and BACKUP UNLOCK are not allowed in procedures. --echo # + delimiter |; --error ER_SP_BADSTATEMENT CREATE PROCEDURE p_BACKUP_LOCK() @@ -162,8 +191,6 @@ SET STATEMENT max_statement_time=180 FOR BACKUP LOCK test.u; --echo # -connection con1; ---reap connection default; disconnect con1; show tables; -- cgit v1.2.1 From d314bd266491baf0954d13fa51dc22b730a6f4d1 Mon Sep 17 00:00:00 2001 From: Monty Date: Wed, 2 Feb 2022 14:09:21 +0200 Subject: MDEV-27442 Wrong result upon query with DISTINCT and EXISTS subquery The problem was that get_best_group_min_max() did not check if fields used by the "group_min_max optimization" where used in sub queries. Because of this, it did not detect that a key (b,a) was used in the WHERE clause for the statement: SELECT DISTINCT b FROM t1 WHERE EXISTS ( SELECT 1 FROM DUAL WHERE a > 1 ). Fixed by also traversing the sub queries when checking if a field is used. This disables group_min_max_optimization for the above query. Reviewer: Sergei Petrunia --- mysql-test/main/group_min_max.result | 16 ++++++++++++++++ mysql-test/main/group_min_max.test | 11 +++++++++++ 2 files changed, 27 insertions(+) (limited to 'mysql-test') diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result index a17f3f09c3b..f264f221ab5 100644 --- a/mysql-test/main/group_min_max.result +++ b/mysql-test/main/group_min_max.result @@ -4027,3 +4027,19 @@ drop table t1; # # End of 10.1 tests # +# +# MDEV-27442 Wrong result upon query with DISTINCT and EXISTS subquery +# +CREATE TABLE t1 (a int, b int, KEY b (b,a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (0,100),(2,100),(2,101),(3,102); +# Must not use Using index for group-by +explain SELECT DISTINCT b FROM t1 WHERE EXISTS ( SELECT 1 FROM DUAL WHERE a > 1 ); +id select_type table type possible_keys key key_len ref rows Extra +1 PRIMARY t1 index NULL b 10 NULL 4 Using where; Using index +2 DEPENDENT SUBQUERY NULL NULL NULL NULL NULL NULL NULL No tables used +SELECT DISTINCT b FROM t1 WHERE EXISTS ( SELECT 1 FROM DUAL WHERE a > 1 ); +b +100 +101 +102 +DROP TABLE t1; diff --git a/mysql-test/main/group_min_max.test b/mysql-test/main/group_min_max.test index 526552dda92..4622fe473a0 100644 --- a/mysql-test/main/group_min_max.test +++ b/mysql-test/main/group_min_max.test @@ -1689,3 +1689,14 @@ drop table t1; --echo # --echo # End of 10.1 tests --echo # + +--echo # +--echo # MDEV-27442 Wrong result upon query with DISTINCT and EXISTS subquery +--echo # + +CREATE TABLE t1 (a int, b int, KEY b (b,a)) ENGINE=MyISAM; +INSERT INTO t1 VALUES (0,100),(2,100),(2,101),(3,102); +--echo # Must not use Using index for group-by +explain SELECT DISTINCT b FROM t1 WHERE EXISTS ( SELECT 1 FROM DUAL WHERE a > 1 ); +SELECT DISTINCT b FROM t1 WHERE EXISTS ( SELECT 1 FROM DUAL WHERE a > 1 ); +DROP TABLE t1; -- cgit v1.2.1 From 38058c04a4fc021f381f8000e40ed23bd4fb8d75 Mon Sep 17 00:00:00 2001 From: Monty Date: Wed, 2 Feb 2022 14:25:25 +0200 Subject: MDEV-26585 Wrong query results when `using index for group-by` The problem was that "group_min_max optimization" does not work if some aggregate functions, like COUNT(*), is used. The function get_best_group_min_max() is using the join->sum_funcs array to check which aggregate functions are used. The bug was that aggregates in HAVING where not yet added to join->sum_funcs at the time get_best_group_min_max() was called. Fixed by populate join->sum_funcs already in prepare, which means that all sum functions will be in join->sum_funcs in get_best_group_min_max(). A benefit of this approach is that we can remove several calls to make_sum_func_list() from the code and simplify the function. I removed some wrong setting of 'sort_and_group'. This variable is set when alloc_group_fields() is called, as part of allocating the cache needed by end_send_group() and does not need to be set by other functions. One problematic thing was that Spider is using *join->sum_funcs to detect at which stage the optimizer is and do internal calculations of aggregate functions. Updating join->sum_funcs early caused Spider to fail when trying to find min/max values in opt_sum_query(). Fixed by temporarily resetting sum_funcs during opt_sum_query(). Reviewer: Sergei Petrunia --- mysql-test/main/group_min_max.result | 21 +++++++++++++++++++++ mysql-test/main/group_min_max.test | 22 +++++++++++++++++++++- 2 files changed, 42 insertions(+), 1 deletion(-) (limited to 'mysql-test') diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result index f264f221ab5..4f7c49092d3 100644 --- a/mysql-test/main/group_min_max.result +++ b/mysql-test/main/group_min_max.result @@ -4043,3 +4043,24 @@ b 101 102 DROP TABLE t1; +# +# MDEV-26585 Wrong query results when `using index for group-by` +# +CREATE TABLE `t1` ( +`id` int(11) NOT NULL AUTO_INCREMENT, +`owner_id` int(11) DEFAULT NULL, +`foo` tinyint(1) DEFAULT 0, +`whatever` varchar(255) DEFAULT NULL, +PRIMARY KEY (`id`), +KEY `index_t1_on_owner_id_and_foo` (`owner_id`,`foo`) +) engine=InnoDB DEFAULT CHARSET=utf8; +INSERT INTO t1 (owner_id, foo, whatever) +VALUES (1, TRUE, "yello"), (1, FALSE, "yello"), (2, TRUE, "yello"), +(2, TRUE, "yello"), (2, FALSE, "yello"); +EXPLAIN SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1); +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 index NULL index_t1_on_owner_id_and_foo 7 NULL 5 Using where; Using index +SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1); +owner_id +1 +DROP TABLE t1; diff --git a/mysql-test/main/group_min_max.test b/mysql-test/main/group_min_max.test index 4622fe473a0..b1d912684c6 100644 --- a/mysql-test/main/group_min_max.test +++ b/mysql-test/main/group_min_max.test @@ -4,7 +4,7 @@ # --source include/default_optimizer_switch.inc - +--source include/have_innodb.inc # # TODO: # Add queries with: @@ -1700,3 +1700,23 @@ INSERT INTO t1 VALUES (0,100),(2,100),(2,101),(3,102); explain SELECT DISTINCT b FROM t1 WHERE EXISTS ( SELECT 1 FROM DUAL WHERE a > 1 ); SELECT DISTINCT b FROM t1 WHERE EXISTS ( SELECT 1 FROM DUAL WHERE a > 1 ); DROP TABLE t1; + +--echo # +--echo # MDEV-26585 Wrong query results when `using index for group-by` +--echo # + +CREATE TABLE `t1` ( + `id` int(11) NOT NULL AUTO_INCREMENT, + `owner_id` int(11) DEFAULT NULL, + `foo` tinyint(1) DEFAULT 0, + `whatever` varchar(255) DEFAULT NULL, + PRIMARY KEY (`id`), + KEY `index_t1_on_owner_id_and_foo` (`owner_id`,`foo`) +) engine=InnoDB DEFAULT CHARSET=utf8; + +INSERT INTO t1 (owner_id, foo, whatever) +VALUES (1, TRUE, "yello"), (1, FALSE, "yello"), (2, TRUE, "yello"), + (2, TRUE, "yello"), (2, FALSE, "yello"); +EXPLAIN SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1); +SELECT DISTINCT owner_id FROM t1 WHERE foo = true GROUP BY owner_id HAVING (COUNT(*) = 1); +DROP TABLE t1; -- cgit v1.2.1 From 5c46751f238ee8dcef1e718ac5f63952bff5d09d Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Marko=20M=C3=A4kel=C3=A4?= Date: Wed, 9 Feb 2022 08:36:41 +0200 Subject: MDEV-27734 Set innodb_change_buffering=none by default The aim of the InnoDB change buffer is to avoid delays when a leaf page of a secondary index is not present in the buffer pool, and a record needs to be inserted, delete-marked, or purged. Instead of reading the page into the buffer pool for making such a modification, we may insert a record to the change buffer (a special index tree in the InnoDB system tablespace). The buffered changes are guaranteed to be merged if the index page actually needs to be read later. The change buffer could be useful when the database is stored on a rotational medium (hard disk) where random seeks are slower than sequential reads or writes. Obviously, the change buffer will cause write amplification, due to potentially large amount of metadata that is being written to the change buffer. We will have to write redo log records for modifying the change buffer tree as well as the user tablespace. Furthermore, in the user tablespace, we must maintain a change buffer bitmap page that uses 2 bits for estimating the amount of free space in pages, and 1 bit to specify whether buffered changes exist. This bitmap needs to be updated on every operation, which could reduce performance. Even if the change buffer were free of bugs such as MDEV-24449 (potentially causing the corruption of any page in the system tablespace) or MDEV-26977 (corruption of secondary indexes due to a currently unknown reason), it will make diagnosis of other data corruption harder. Because of all this, it is best to disable the change buffer by default. --- mysql-test/suite/innodb/r/ibuf_not_empty.result | 1 + .../suite/innodb/r/innodb-change-buffer-recovery.result | 1 + mysql-test/suite/innodb/t/ibuf_not_empty.test | 1 + .../suite/innodb/t/innodb-change-buffer-recovery.test | 1 + .../suite/sys_vars/r/innodb_change_buffering_basic.result | 14 +++++++------- mysql-test/suite/sys_vars/r/sysvars_innodb.result | 2 +- 6 files changed, 12 insertions(+), 8 deletions(-) (limited to 'mysql-test') diff --git a/mysql-test/suite/innodb/r/ibuf_not_empty.result b/mysql-test/suite/innodb/r/ibuf_not_empty.result index d1b8203b063..f2da89990b0 100644 --- a/mysql-test/suite/innodb/r/ibuf_not_empty.result +++ b/mysql-test/suite/innodb/r/ibuf_not_empty.result @@ -5,6 +5,7 @@ c INT, INDEX(b)) ENGINE=InnoDB STATS_PERSISTENT=0; SET GLOBAL innodb_change_buffering_debug = 1; +SET GLOBAL innodb_change_buffering=all; INSERT INTO t1 SELECT 0,'x',1 FROM seq_1_to_1024; # restart: --innodb-force-recovery=6 --innodb-change-buffer-dump check table t1; diff --git a/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result b/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result index 678c8c67be5..f676d15b134 100644 --- a/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result +++ b/mysql-test/suite/innodb/r/innodb-change-buffer-recovery.result @@ -13,6 +13,7 @@ c INT, INDEX(b)) ENGINE=InnoDB STATS_PERSISTENT=0; SET GLOBAL innodb_change_buffering_debug = 1; +SET GLOBAL innodb_change_buffering = all; INSERT INTO t1 SELECT 0,'x',1 FROM seq_1_to_8192; BEGIN; SELECT b FROM t1 LIMIT 3; diff --git a/mysql-test/suite/innodb/t/ibuf_not_empty.test b/mysql-test/suite/innodb/t/ibuf_not_empty.test index 545a78c887e..96ceb81ac00 100644 --- a/mysql-test/suite/innodb/t/ibuf_not_empty.test +++ b/mysql-test/suite/innodb/t/ibuf_not_empty.test @@ -24,6 +24,7 @@ ENGINE=InnoDB STATS_PERSISTENT=0; # change buffering is possible, so that the change buffer will be used # whenever possible. SET GLOBAL innodb_change_buffering_debug = 1; +SET GLOBAL innodb_change_buffering=all; # Create enough rows for the table, so that the change buffer will be # used for modifying the secondary index page. There must be multiple diff --git a/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test b/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test index a12ca43cec1..129037e783b 100644 --- a/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test +++ b/mysql-test/suite/innodb/t/innodb-change-buffer-recovery.test @@ -33,6 +33,7 @@ ENGINE=InnoDB STATS_PERSISTENT=0; # change buffering is possible, so that the change buffer will be used # whenever possible. SET GLOBAL innodb_change_buffering_debug = 1; +SET GLOBAL innodb_change_buffering = all; let SEARCH_FILE = $MYSQLTEST_VARDIR/log/mysqld.1.err; # Create enough rows for the table, so that the change buffer will be diff --git a/mysql-test/suite/sys_vars/r/innodb_change_buffering_basic.result b/mysql-test/suite/sys_vars/r/innodb_change_buffering_basic.result index 92e22c6aa34..c11f4ee617c 100644 --- a/mysql-test/suite/sys_vars/r/innodb_change_buffering_basic.result +++ b/mysql-test/suite/sys_vars/r/innodb_change_buffering_basic.result @@ -1,28 +1,28 @@ SET @start_global_value = @@global.innodb_change_buffering; SELECT @start_global_value; @start_global_value -all +none Valid values are 'all', 'deletes', 'changes', 'inserts', 'none', 'purges' select @@global.innodb_change_buffering in ('all', 'deletes', 'changes', 'inserts', 'none', 'purges'); @@global.innodb_change_buffering in ('all', 'deletes', 'changes', 'inserts', 'none', 'purges') 1 select @@global.innodb_change_buffering; @@global.innodb_change_buffering -all +none select @@session.innodb_change_buffering; ERROR HY000: Variable 'innodb_change_buffering' is a GLOBAL variable show global variables like 'innodb_change_buffering'; Variable_name Value -innodb_change_buffering all +innodb_change_buffering none show session variables like 'innodb_change_buffering'; Variable_name Value -innodb_change_buffering all +innodb_change_buffering none select * from information_schema.global_variables where variable_name='innodb_change_buffering'; VARIABLE_NAME VARIABLE_VALUE -INNODB_CHANGE_BUFFERING all +INNODB_CHANGE_BUFFERING none select * from information_schema.session_variables where variable_name='innodb_change_buffering'; VARIABLE_NAME VARIABLE_VALUE -INNODB_CHANGE_BUFFERING all +INNODB_CHANGE_BUFFERING none set global innodb_change_buffering='none'; select @@global.innodb_change_buffering; @@global.innodb_change_buffering @@ -62,4 +62,4 @@ ERROR 42000: Variable 'innodb_change_buffering' can't be set to the value of 'so SET @@global.innodb_change_buffering = @start_global_value; SELECT @@global.innodb_change_buffering; @@global.innodb_change_buffering -all +none diff --git a/mysql-test/suite/sys_vars/r/sysvars_innodb.result b/mysql-test/suite/sys_vars/r/sysvars_innodb.result index ecd107f67c8..c333b56fd2e 100644 --- a/mysql-test/suite/sys_vars/r/sysvars_innodb.result +++ b/mysql-test/suite/sys_vars/r/sysvars_innodb.result @@ -311,7 +311,7 @@ READ_ONLY NO COMMAND_LINE_ARGUMENT OPTIONAL VARIABLE_NAME INNODB_CHANGE_BUFFERING SESSION_VALUE NULL -DEFAULT_VALUE all +DEFAULT_VALUE none VARIABLE_SCOPE GLOBAL VARIABLE_TYPE ENUM VARIABLE_COMMENT Buffer changes to secondary indexes. -- cgit v1.2.1