From c03841ec0e2b7ac11711243d29821038b26e3edf Mon Sep 17 00:00:00 2001 From: Sergei Petrunia Date: Thu, 8 Apr 2021 17:25:02 +0300 Subject: MDEV-23634: Select query hanged the server and leads to OOM ... Handle "col<>const" in the same way that MDEV-21958 did for "col NOT IN(const-list)": do not use the condition for range/index_merge accesses if there is a unique UNIQUE KEY(col). The testcase is in main/range.test. The rest of test updates are due to widespread use of 'pk<>1' in the testsuite. Changed the test to use different but equivalent forms of the conditions. --- mysql-test/main/func_group.result | 2 +- mysql-test/main/func_group.test | 2 +- mysql-test/main/group_min_max.result | 2 +- mysql-test/main/index_merge_myisam.result | 3 ++- mysql-test/main/index_merge_myisam.test | 4 +++- mysql-test/main/innodb_icp.result | 12 ++++++------ mysql-test/main/myisam_icp.result | 12 ++++++------ mysql-test/main/range.result | 11 +++++++++++ mysql-test/main/range.test | 11 +++++++++++ mysql-test/main/range_mrr_icp.result | 11 +++++++++++ mysql-test/main/range_vs_index_merge.result | 8 ++++---- mysql-test/main/range_vs_index_merge.test | 8 ++++---- mysql-test/main/range_vs_index_merge_innodb.result | 8 ++++---- mysql-test/main/subselect_mat_cost_bugs.result | 8 ++++---- mysql-test/main/subselect_mat_cost_bugs.test | 8 ++++---- 15 files changed, 73 insertions(+), 37 deletions(-) (limited to 'mysql-test/main') diff --git a/mysql-test/main/func_group.result b/mysql-test/main/func_group.result index fc6f93a938a..071c155cd6b 100644 --- a/mysql-test/main/func_group.result +++ b/mysql-test/main/func_group.result @@ -606,7 +606,7 @@ select min(a1) from t1 where a1 > 'KKK' or a1 < 'XXX'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index PRIMARY PRIMARY 3 NULL 15 Using where; Using index explain -select min(a1) from t1 where a1 != 'KKK'; +select min(a1) from t1 where (a1 < 'KKK' or a1 > 'KKK'); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index PRIMARY PRIMARY 3 NULL 15 Using where; Using index explain diff --git a/mysql-test/main/func_group.test b/mysql-test/main/func_group.test index 10b92cbadca..a28b39c28f6 100644 --- a/mysql-test/main/func_group.test +++ b/mysql-test/main/func_group.test @@ -359,7 +359,7 @@ select min(t1.a1), min(t2.a4) from t1,t2 where t1.a1 < 'KKK' and t2.a4 < 'KKK'; explain select min(a1) from t1 where a1 > 'KKK' or a1 < 'XXX'; explain -select min(a1) from t1 where a1 != 'KKK'; +select min(a1) from t1 where (a1 < 'KKK' or a1 > 'KKK'); explain select max(a3) from t1 where a2 < 2 and a3 < 'SEA'; explain diff --git a/mysql-test/main/group_min_max.result b/mysql-test/main/group_min_max.result index a28cc418207..8d240f9f36d 100644 --- a/mysql-test/main/group_min_max.result +++ b/mysql-test/main/group_min_max.result @@ -2940,7 +2940,7 @@ NULL EXPLAIN SELECT MIN( a ) FROM t1 WHERE a <> NULL; id select_type table type possible_keys key key_len ref rows Extra -x x x x x x x x x Impossible WHERE noticed after reading const tables +x x x x x x x x x Using where; Using index SELECT MIN( a ) FROM t1 WHERE a <> NULL; MIN( a ) NULL diff --git a/mysql-test/main/index_merge_myisam.result b/mysql-test/main/index_merge_myisam.result index 387bab6b408..55caf051720 100644 --- a/mysql-test/main/index_merge_myisam.result +++ b/mysql-test/main/index_merge_myisam.result @@ -1687,7 +1687,8 @@ INSERT INTO t1 VALUES ALTER TABLE t1 ENABLE KEYS; EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY , i , c1 , c2) -WHERE pk = 255 OR i = 22 OR (pk IN (1 , 136) AND c2 IN ('c' , 'w') AND (c1 NOT BETWEEN 'e' AND 'i' OR c2 > 'g')) OR pk != 1 ; +WHERE pk = 255 OR i = 22 OR (pk IN (1 , 136) AND c2 IN ('c' , 'w') AND (c1 +NOT BETWEEN 'e' AND 'i' OR c2 > 'g')) OR (pk is not null and (pk <1 or pk>1)) ; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index_merge PRIMARY,c1,i,c2 PRIMARY,i 0,5 NULL 69 Using sort_union(PRIMARY,i); Using where DROP TABLE t1; diff --git a/mysql-test/main/index_merge_myisam.test b/mysql-test/main/index_merge_myisam.test index 0b0d8d60c15..b77c9bc1ca2 100644 --- a/mysql-test/main/index_merge_myisam.test +++ b/mysql-test/main/index_merge_myisam.test @@ -236,9 +236,11 @@ INSERT INTO t1 VALUES ALTER TABLE t1 ENABLE KEYS; +# note: (pk is not null and (pk <1 or pk>1)) below is a sargable form of pk!=1 EXPLAIN SELECT * FROM t1 FORCE KEY (PRIMARY , i , c1 , c2) -WHERE pk = 255 OR i = 22 OR (pk IN (1 , 136) AND c2 IN ('c' , 'w') AND (c1 NOT BETWEEN 'e' AND 'i' OR c2 > 'g')) OR pk != 1 ; +WHERE pk = 255 OR i = 22 OR (pk IN (1 , 136) AND c2 IN ('c' , 'w') AND (c1 +NOT BETWEEN 'e' AND 'i' OR c2 > 'g')) OR (pk is not null and (pk <1 or pk>1)) ; DROP TABLE t1; diff --git a/mysql-test/main/innodb_icp.result b/mysql-test/main/innodb_icp.result index 949bc9a00d8..96ff1964ac1 100644 --- a/mysql-test/main/innodb_icp.result +++ b/mysql-test/main/innodb_icp.result @@ -455,11 +455,11 @@ c1 INT NOT NULL, PRIMARY KEY (pk) ); INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1); -EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3; +EXPLAIN SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 4 Using where SET SESSION optimizer_switch='index_condition_pushdown=off'; -SELECT pk, c1 FROM t1 WHERE pk <> 3; +SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3); pk c1 1 9 2 7 @@ -687,23 +687,23 @@ INSERT INTO t2 VALUES ('Ill'), ('eckqzsflbzaffti'), ('w'), ('she'), ('gxbwypqtjzwywwer'), ('w'); SET SESSION optimizer_switch='index_condition_pushdown=off'; EXPLAIN -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a 1 Using where -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; b c 1 4 SET SESSION optimizer_switch='index_condition_pushdown=on'; EXPLAIN -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY PRIMARY 4 NULL 1 Using where; Using filesort 1 SIMPLE t2 ref a a 515 test.t1.a 1 Using where -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; b c 1 4 diff --git a/mysql-test/main/myisam_icp.result b/mysql-test/main/myisam_icp.result index 7f34b274764..68770229926 100644 --- a/mysql-test/main/myisam_icp.result +++ b/mysql-test/main/myisam_icp.result @@ -448,11 +448,11 @@ c1 INT NOT NULL, PRIMARY KEY (pk) ); INSERT INTO t1 VALUES (1,9),(2,7),(3,6),(4,3),(5,1); -EXPLAIN SELECT pk, c1 FROM t1 WHERE pk <> 3; +EXPLAIN SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 5 Using where SET SESSION optimizer_switch='index_condition_pushdown=off'; -SELECT pk, c1 FROM t1 WHERE pk <> 3; +SELECT pk, c1 FROM t1 WHERE (pk<3 or pk>3); pk c1 1 9 2 7 @@ -680,23 +680,23 @@ INSERT INTO t2 VALUES ('Ill'), ('eckqzsflbzaffti'), ('w'), ('she'), ('gxbwypqtjzwywwer'), ('w'); SET SESSION optimizer_switch='index_condition_pushdown=off'; EXPLAIN -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 1 SIMPLE t2 ref a a 515 const 1 Using where -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; b c 1 4 SET SESSION optimizer_switch='index_condition_pushdown=on'; EXPLAIN -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 system PRIMARY NULL NULL NULL 1 1 SIMPLE t2 ref a a 515 const 1 Using where -SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND t1.b != 0 +SELECT t1.b, t1.c FROM t1, t2 WHERE t1.a = t2.a AND (t1.b<0 OR t1.b>0) HAVING t1.c != 5 ORDER BY t1.c; b c 1 4 diff --git a/mysql-test/main/range.result b/mysql-test/main/range.result index 60c79c02c44..afd1571283f 100644 --- a/mysql-test/main/range.result +++ b/mysql-test/main/range.result @@ -3244,6 +3244,17 @@ SELECT * FROM t1 JOIN t2 ON (t2.code = t1.b) WHERE t1.a NOT IN ('baz', 'qux') OR id a b code num DROP TABLE t1, t2; # +# MDEV-23634: Select query hanged the server and leads to OOM ... +# (The fix is to add the same handling for "col!=const" as MDEV-21958 did for NOT IN) +# +create table t1 (pk int primary key, a int); +insert into t1 (pk) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +# must not use range: +explain select * from t1 force index (primary) where pk != 1 and pk!=2 ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 10 Using where +drop table t1; +# # MDEV-22251: get_key_scans_params: Conditional jump or move depends on uninitialised value # create table t1 (pk int, i int, v int, primary key (pk), key(v)); diff --git a/mysql-test/main/range.test b/mysql-test/main/range.test index d06053d07e3..de2a428c49a 100644 --- a/mysql-test/main/range.test +++ b/mysql-test/main/range.test @@ -2215,6 +2215,17 @@ SELECT * FROM t1 JOIN t2 ON (t2.code = t1.b) WHERE t1.a NOT IN ('baz', 'qux') OR DROP TABLE t1, t2; +--echo # +--echo # MDEV-23634: Select query hanged the server and leads to OOM ... +--echo # (The fix is to add the same handling for "col!=const" as MDEV-21958 did for NOT IN) +--echo # +create table t1 (pk int primary key, a int); +insert into t1 (pk) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); + +--echo # must not use range: +explain select * from t1 force index (primary) where pk != 1 and pk!=2 ; +drop table t1; + --echo # --echo # MDEV-22251: get_key_scans_params: Conditional jump or move depends on uninitialised value --echo # diff --git a/mysql-test/main/range_mrr_icp.result b/mysql-test/main/range_mrr_icp.result index 257314539c5..3fce8b0fc23 100644 --- a/mysql-test/main/range_mrr_icp.result +++ b/mysql-test/main/range_mrr_icp.result @@ -3241,6 +3241,17 @@ SELECT * FROM t1 JOIN t2 ON (t2.code = t1.b) WHERE t1.a NOT IN ('baz', 'qux') OR id a b code num DROP TABLE t1, t2; # +# MDEV-23634: Select query hanged the server and leads to OOM ... +# (The fix is to add the same handling for "col!=const" as MDEV-21958 did for NOT IN) +# +create table t1 (pk int primary key, a int); +insert into t1 (pk) values (0),(1),(2),(3),(4),(5),(6),(7),(8),(9); +# must not use range: +explain select * from t1 force index (primary) where pk != 1 and pk!=2 ; +id select_type table type possible_keys key key_len ref rows Extra +1 SIMPLE t1 ALL PRIMARY NULL NULL NULL 10 Using where +drop table t1; +# # MDEV-22251: get_key_scans_params: Conditional jump or move depends on uninitialised value # create table t1 (pk int, i int, v int, primary key (pk), key(v)); diff --git a/mysql-test/main/range_vs_index_merge.result b/mysql-test/main/range_vs_index_merge.result index f0bf2224c92..286338d0433 100644 --- a/mysql-test/main/range_vs_index_merge.result +++ b/mysql-test/main/range_vs_index_merge.result @@ -1810,11 +1810,11 @@ CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX idx(b)); INSERT INTO t1 VALUES (167,9999), (168,10000); EXPLAIN SELECT * FROM t1 -WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2; +WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range PRIMARY,idx PRIMARY 0 NULL 2 Using index condition; Using where SELECT * FROM t1 -WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2; +WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2); a b 167 9999 168 10000 @@ -1846,12 +1846,12 @@ test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN SELECT * FROM t1 FORCE KEY (state,capital) -WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 +WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9) OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range state,capital state 71 NULL 8 Using index condition; Using where SELECT * FROM t1 FORCE KEY (state,capital) -WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 +WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9) OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; id state capital 4 Florida Tallahassee diff --git a/mysql-test/main/range_vs_index_merge.test b/mysql-test/main/range_vs_index_merge.test index 5ed5f621ab6..94210ce5dd3 100644 --- a/mysql-test/main/range_vs_index_merge.test +++ b/mysql-test/main/range_vs_index_merge.test @@ -1231,9 +1231,9 @@ INSERT INTO t1 VALUES (167,9999), (168,10000); EXPLAIN SELECT * FROM t1 - WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2; + WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2); SELECT * FROM t1 - WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2; + WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2); DROP TABLE t1; @@ -1266,10 +1266,10 @@ ANALYZE TABLE t1; EXPLAIN SELECT * FROM t1 FORCE KEY (state,capital) -WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 +WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9) OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; SELECT * FROM t1 FORCE KEY (state,capital) -WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 +WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9) OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; DROP TABLE t1; diff --git a/mysql-test/main/range_vs_index_merge_innodb.result b/mysql-test/main/range_vs_index_merge_innodb.result index 4cb4745d63a..65800e7397b 100644 --- a/mysql-test/main/range_vs_index_merge_innodb.result +++ b/mysql-test/main/range_vs_index_merge_innodb.result @@ -1816,11 +1816,11 @@ CREATE TABLE t1 (a int PRIMARY KEY, b int, INDEX idx(b)); INSERT INTO t1 VALUES (167,9999), (168,10000); EXPLAIN SELECT * FROM t1 -WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2; +WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2); id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 index PRIMARY,idx idx 5 NULL 2 Using where; Using index SELECT * FROM t1 -WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR a!=2; +WHERE a BETWEEN 4 AND 5 AND b IN (255,4) OR a IN (2,14,25) OR (a<2 or a>2); a b 167 9999 168 10000 @@ -1852,12 +1852,12 @@ test.t1 analyze status Engine-independent statistics collected test.t1 analyze status OK EXPLAIN SELECT * FROM t1 FORCE KEY (state,capital) -WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 +WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9) OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; id select_type table type possible_keys key key_len ref rows Extra 1 SIMPLE t1 range state,capital state 71 NULL 8 Using index condition; Using where SELECT * FROM t1 FORCE KEY (state,capital) -WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND id != 9 +WHERE ( state = 'Alabama' OR state >= 'Colorado' ) AND (id<9 or id>9) OR ( capital >= 'Topeka' OR state = 'Kansas' ) AND state != 'Texas'; id state capital 4 Florida Tallahassee diff --git a/mysql-test/main/subselect_mat_cost_bugs.result b/mysql-test/main/subselect_mat_cost_bugs.result index a18c5e608f1..ecceac27b2d 100644 --- a/mysql-test/main/subselect_mat_cost_bugs.result +++ b/mysql-test/main/subselect_mat_cost_bugs.result @@ -189,8 +189,8 @@ SELECT alias2.f2 AS field1 FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1 WHERE ( SELECT t2.f2 -FROM t2 JOIN t1 ON t1.f1 -WHERE t1.f1 AND alias2.f10 +FROM t2 JOIN t1 ON (t1.f1>0 or t1.f1<0) +WHERE (t1.f1>0 or t1.f1<0) AND alias2.f10 ) ORDER BY field1 ; id select_type table type possible_keys key key_len ref rows Extra @@ -203,8 +203,8 @@ SELECT alias2.f2 AS field1 FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1 WHERE ( SELECT t2.f2 -FROM t2 JOIN t1 ON t1.f1 -WHERE t1.f1 AND alias2.f10 +FROM t2 JOIN t1 ON (t1.f1>0 or t1.f1<0) +WHERE (t1.f1>0 or t1.f1<0) AND alias2.f10 ) ORDER BY field1 ; field1 diff --git a/mysql-test/main/subselect_mat_cost_bugs.test b/mysql-test/main/subselect_mat_cost_bugs.test index 028cdced560..ba1aad06a15 100644 --- a/mysql-test/main/subselect_mat_cost_bugs.test +++ b/mysql-test/main/subselect_mat_cost_bugs.test @@ -217,8 +217,8 @@ SELECT alias2.f2 AS field1 FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1 WHERE ( SELECT t2.f2 - FROM t2 JOIN t1 ON t1.f1 - WHERE t1.f1 AND alias2.f10 + FROM t2 JOIN t1 ON (t1.f1>0 or t1.f1<0) + WHERE (t1.f1>0 or t1.f1<0) AND alias2.f10 ) ORDER BY field1 ; @@ -226,8 +226,8 @@ SELECT alias2.f2 AS field1 FROM t1 AS alias1 JOIN ( SELECT * FROM t2 ) AS alias2 ON alias2.f3 = alias1.f1 WHERE ( SELECT t2.f2 - FROM t2 JOIN t1 ON t1.f1 - WHERE t1.f1 AND alias2.f10 + FROM t2 JOIN t1 ON (t1.f1>0 or t1.f1<0) + WHERE (t1.f1>0 or t1.f1<0) AND alias2.f10 ) ORDER BY field1 ; -- cgit v1.2.1 From 61f84bba603aa85957b48d151f9ddf5ba4e71ab1 Mon Sep 17 00:00:00 2001 From: Dmitry Shulga Date: Tue, 13 Apr 2021 09:38:32 +0700 Subject: MDEV-25197: The statement set password=password('') executed in PS mode fails in case it is run by a user with expired password A user connected to a server with an expired password can't change password with the statement "SET password=..." if this statement is run in PS mode. In mentioned use case a user gets the error ER_MUST_CHANGE_PASSWORD on attempt to run the statement PREPARE stmt FOR "SET password=..."; The reason of failure to reset password by a locked user using the statement PREPARE stmt FOR "SET password=..." is that PS-related statements are not listed among the commands allowed for execution by a user with expired password. However, simple adding of PS-related statements (PREPARE FOR/EXECUTE/DEALLOCATE PREPARE ) to the list of statements allowed for execution by a locked user is not enough to solve problems, since it opens the opportunity for a locked user to execute any statement in the PS mode. To exclude this opportunity, additional checking that the statement being prepared for execution in PS-mode is the SET statement has to be added. This extra checking has been added by this patch into the method Prepared_statement::prepared() that executed on preparing any statement for execution in PS-mode. --- mysql-test/main/ps.result | 32 ++++++++++++++++++++++++++++++++ mysql-test/main/ps.test | 37 +++++++++++++++++++++++++++++++++++++ 2 files changed, 69 insertions(+) (limited to 'mysql-test/main') diff --git a/mysql-test/main/ps.result b/mysql-test/main/ps.result index 4d757986f9c..b10a75e7399 100644 --- a/mysql-test/main/ps.result +++ b/mysql-test/main/ps.result @@ -5536,5 +5536,37 @@ DEALLOCATE PREPARE stmt; DROP VIEW v1; DROP TABLE t1; # +# MDEV-25197: The statement set password=password('') executed in PS mode +# fails in case it is run by a user with expired password +# +CREATE USER user1@localhost PASSWORD EXPIRE; +SET @disconnect_on_expired_password_save=@@global.disconnect_on_expired_password; +SET GLOBAL disconnect_on_expired_password=OFF; +connect con1,localhost,user1; +connection con1; +# Check that no regular statement like SELECT can be prepared +# by a user with an expired password +PREPARE stmt FROM "SELECT 1"; +ERROR HY000: You must SET PASSWORD before executing this statement +# Check that the DEALLOCATE PREPARE statement can be run by a user +# with an expired password +PREPARE stmt FROM "SET password=password('')"; +DEALLOCATE PREPARE stmt; +# Check that the SET PASSWORD statement can be executed in PS mode by +# a user with an expired password +PREPARE stmt FROM "SET password=password('')"; +EXECUTE stmt; +PREPARE stmt FROM "SELECT 1"; +# Check that user's password is not expired anymore +EXECUTE stmt; +1 +1 +DEALLOCATE PREPARE stmt; +# Clean up +disconnect con1; +connection default; +SET GLOBAL disconnect_on_expired_password=@disconnect_on_expired_password_save; +DROP USER user1@localhost; +# # End of 10.4 tests # diff --git a/mysql-test/main/ps.test b/mysql-test/main/ps.test index 2ce78b78e90..62a7efffbfd 100644 --- a/mysql-test/main/ps.test +++ b/mysql-test/main/ps.test @@ -4979,6 +4979,43 @@ DEALLOCATE PREPARE stmt; DROP VIEW v1; DROP TABLE t1; +--echo # +--echo # MDEV-25197: The statement set password=password('') executed in PS mode +--echo # fails in case it is run by a user with expired password +--echo # +CREATE USER user1@localhost PASSWORD EXPIRE; + +SET @disconnect_on_expired_password_save=@@global.disconnect_on_expired_password; +SET GLOBAL disconnect_on_expired_password=OFF; + +connect(con1,localhost,user1); +connection con1; +--echo # Check that no regular statement like SELECT can be prepared +--echo # by a user with an expired password +--error ER_MUST_CHANGE_PASSWORD +PREPARE stmt FROM "SELECT 1"; + +--echo # Check that the DEALLOCATE PREPARE statement can be run by a user +--echo # with an expired password +PREPARE stmt FROM "SET password=password('')"; +DEALLOCATE PREPARE stmt; + +--echo # Check that the SET PASSWORD statement can be executed in PS mode by +--echo # a user with an expired password +PREPARE stmt FROM "SET password=password('')"; +EXECUTE stmt; +PREPARE stmt FROM "SELECT 1"; +--echo # Check that user's password is not expired anymore +EXECUTE stmt; +DEALLOCATE PREPARE stmt; + +--echo # Clean up +disconnect con1; +connection default; + +SET GLOBAL disconnect_on_expired_password=@disconnect_on_expired_password_save; +DROP USER user1@localhost; + --echo # --echo # End of 10.4 tests --echo # -- cgit v1.2.1