06 Jun '20
revision-id: 04b31342547907c9b44632510e99c0126a5879ae (mariadb-10.2.31-259-g04b3134)
parent(s): a8c200c73c96d35b9e58fdcde0987f0ac5cb9b1a
author: Igor Babaev
committer: Igor Babaev
timestamp: 2020-06-05 22:15:04 -0700
message:
MDEV-22748 MariaDB crash on WITH RECURSIVE large query
This bug is the same as the bug MDEV-17024. The crashes caused by these
bugs were due to premature cleanups of the unit specifying recursive CTEs
that happened in some cases when there were several outer references the
same recursive CTE.
The problem of premature cleanups for recursive CTEs could be already
resolved by the correction in TABLE_LIST::set_as_with_table() introduced
in this patch. ALL other changes introduced by the patches for MDEV-17024
and MDEV-22748 guarantee that this clean-ups are performed as soon as
possible: when the select containing the last outer reference to a
recursive CTE is being cleaned up the specification of the recursive CTE
should be cleaned up as well.
---
mysql-test/r/cte_recursive.result | 170 ++++++++++++++++++++++++++++++++++++--
mysql-test/t/cte_recursive.test | 70 +++++++++++++++-
sql/sql_cte.cc | 1 +
sql/sql_derived.cc | 6 +-
sql/sql_union.cc | 13 +++
5 files changed, 247 insertions(+), 13 deletions(-)
diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result
index 0b8bc3f..6404931 100644
--- a/mysql-test/r/cte_recursive.result
+++ b/mysql-test/r/cte_recursive.result
@@ -691,13 +691,13 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
1 PRIMARY <derived3> ref key0 key0 5 c.h_id 2 100.00
1 PRIMARY <derived3> ref key0 key0 5 c.w_id 2 100.00
-2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 100.00 Using where
3 DERIVED folks ALL NULL NULL NULL NULL 12 100.00 Using where
4 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00
4 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join)
5 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00
5 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join)
NULL UNION RESULT <union3,4,5> ALL NULL NULL NULL NULL NULL NULL
+2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 100.00 Using where
Warnings:
Note 1003 with recursive ancestor_couple_ids as (select `a`.`father` AS `h_id`,`a`.`mother` AS `w_id` from `coupled_ancestors` `a` where `a`.`father` is not null and `a`.`mother` is not null), coupled_ancestors as (select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' union all select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `fa` where `test`.`p`.`id` = `fa`.`h_id` union all select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `ma` where `test`.`p`.`id` = `ma`.`w_id`)select `h`.`name` AS `name`,`h`.`dob` AS `dob`,`w`.`name
` AS `name`,`w`.`dob` AS `dob` from `ancestor_couple_ids` `c` join `coupled_ancestors` `h` join `coupled_ancestors` `w` where `h`.`id` = `c`.`h_id` and `w`.`id` = `c`.`w_id`
# simple mutual recursion
@@ -1302,12 +1302,12 @@ id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived4> ALL NULL NULL NULL NULL 24
4 DERIVED folks ALL NULL NULL NULL NULL 12 Using where
6 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12
+5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24
+NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL
3 DERIVED folks ALL NULL NULL NULL NULL 12 Using where
2 RECURSIVE UNION folks ALL PRIMARY NULL NULL NULL 12
2 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join)
NULL UNION RESULT <union3,2> ALL NULL NULL NULL NULL NULL
-5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24
-NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL
explain FORMAT=JSON
with recursive
prev_gen
@@ -3326,13 +3326,13 @@ select * from cte1, cte2;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived4> ALL NULL NULL NULL NULL 6 100.00
1 PRIMARY <derived5> ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join)
+2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
4 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
5 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
-2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
-3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
-NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1003 with recursive rcte as (select 1 AS `a` union select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)select `cte1`.`c1` AS `c1`,`cte2`.`c2` AS `c2` from `cte1` join `cte2`
prepare stmt from "with recursive
@@ -3391,6 +3391,65 @@ cte2 as
(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7)
select * from cte1, cte2 where cte1.c1 = 3;
c1 c2
+with recursive
+rcte(a) as
+(select 1 union select cast(a+1 as unsigned) from rcte where a < 10),
+cte1 as
+(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3),
+cte2 as
+(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7)
+select * from cte2, cte1;
+c2 c1
+1 2
+explain extended with recursive
+rcte(a) as
+(select 1 union select cast(a+1 as unsigned) from rcte where a < 10),
+cte1 as
+(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3),
+cte2 as
+(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7)
+select * from cte2, cte1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY <derived5> ALL NULL NULL NULL NULL 6 100.00
+1 PRIMARY <derived4> ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join)
+2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
+5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
+5 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
+4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
+4 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 with recursive rcte as (select 1 AS `a` union select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)select `cte2`.`c2` AS `c2`,`cte1`.`c1` AS `c1` from `cte2` join `cte1`
+prepare stmt from "with recursive
+rcte(a) as
+(select 1 union select cast(a+1 as unsigned) from rcte where a < 10),
+cte1 as
+(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3),
+cte2 as
+(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7)
+select * from cte2, cte1";
+execute stmt;
+c2 c1
+1 2
+execute stmt;
+c2 c1
+1 2
+drop procedure p;
+drop table t2;
+create table t2 (c1 int, c2 int);
+create procedure p() insert into t2 with recursive
+rcte(a) as
+(select 1 union select cast(a+1 as unsigned) from rcte where a < 10),
+cte1 as
+(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3),
+cte2 as
+(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7)
+select * from cte2, cte1;
+call p();
+select * from t2;
+c1 c2
+1 2
drop procedure p;
drop table t1,t2;
#
@@ -3809,4 +3868,101 @@ ANALYZE
}
drop function f1;
drop table t1,t2;
-End of 10.2 tests
+#
+# MDEV-22748: two materialized CTEs using the same recursive CTE
+# (see also test case for MDEV-17024)
+#
+CREATE TABLE t1 (YEAR int(4), d1 date , d2 date) ;
+INSERT INTO t1 VALUES (2018,'2018-01-01','2018-09-20');
+CREATE TABLE t2 (id int, tm date);
+INSERT INTO t2 VALUES (1,'2018-08-30'),(2,'2018-08-30'),(3,'2018-08-30');
+CREATE TABLE t3 (id int, tm date);
+INSERT INTO t3 VALUES (1,'2018-08-30'),(2,'2018-08-30');
+WITH RECURSIVE
+cte AS
+(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn
+FROM t1
+UNION ALL
+SELECT YEAR(cte.st + INTERVAL 1 MONTH),
+cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY
+FROM cte JOIN t1
+WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ),
+cte2 AS (SELECT YEAR, COUNT(*)
+FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn),
+cte3 AS (SELECT YEAR, COUNT(*)
+FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn)
+SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR);
+YEAR d1 d2
+2018 2018-01-01 2018-09-20
+EXPLAIN EXTENDED WITH RECURSIVE
+cte AS
+(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn
+FROM t1
+UNION ALL
+SELECT YEAR(cte.st + INTERVAL 1 MONTH),
+cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY
+FROM cte JOIN t1
+WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ),
+cte2 AS (SELECT YEAR, COUNT(*)
+FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn),
+cte3 AS (SELECT YEAR, COUNT(*)
+FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn)
+SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
+1 PRIMARY <derived5> ref key0 key0 9 const 0 0.00 Using where
+1 PRIMARY <derived4> ref key0 key0 9 const 0 0.00 Using where
+2 DERIVED t1 system NULL NULL NULL NULL 1 100.00
+3 RECURSIVE UNION t1 system NULL NULL NULL NULL 1 100.00
+3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
+4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00
+4 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
+5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00
+5 DERIVED t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 with recursive cte as (select year(`test`.`t1`.`d1`) AS `YEAR`,`test`.`t1`.`d1` AS `st`,`test`.`t1`.`d1` + interval 1 month AS `fn` from `test`.`t1` union all select year(`cte`.`st` + interval 1 month) AS `YEAR(cte.st + INTERVAL 1 MONTH)`,`cte`.`st` + interval 1 month AS `cte.st + INTERVAL 1 MONTH`,`test`.`t1`.`d2` + interval 1 day AS `t1.d2 + INTERVAL 1 DAY` from `cte` join `test`.`t1` where `cte`.`st` + interval 1 month < `test`.`t1`.`d2`), cte2 as (select `cte`.`YEAR` AS `YEAR`,count(0) AS `COUNT(*)` from `cte` join `test`.`t2` where `test`.`t2`.`tm` between `cte`.`st` and `cte`.`fn`), cte3 as (select `cte`.`YEAR` AS `YEAR`,count(0) AS `COUNT(*)` from `cte` join `test`.`t3` where `test`.`t3`.`tm` between `cte`.`st` and `cte`.`fn`)select 2018 AS `YEAR`,'2018-01-01' AS `d1`,'2018-09-20' AS `d2` from `cte2` join `cte3` where 2018 = `cte3`.`YEAR` and 2018 = `cte2`.`YEAR`
+PREPARE stmt FROM "WITH RECURSIVE
+cte AS
+(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn
+FROM t1
+UNION ALL
+SELECT YEAR(cte.st + INTERVAL 1 MONTH),
+cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY
+FROM cte JOIN t1
+WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ),
+cte2 AS (SELECT YEAR, COUNT(*)
+FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn),
+cte3 AS (SELECT YEAR, COUNT(*)
+FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn)
+SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR)";
+EXECUTE stmt;
+YEAR d1 d2
+2018 2018-01-01 2018-09-20
+EXECUTE stmt;
+YEAR d1 d2
+2018 2018-01-01 2018-09-20
+CREATE TABLE t4 (YEAR int(4), d1 date , d2 date);
+CREATE PROCEDURE p() INSERT INTO t4 WITH RECURSIVE
+cte AS
+(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn
+FROM t1
+UNION ALL
+SELECT YEAR(cte.st + INTERVAL 1 MONTH),
+cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY
+FROM cte JOIN t1
+WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ),
+cte2 AS (SELECT YEAR, COUNT(*)
+FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn),
+cte3 AS (SELECT YEAR, COUNT(*)
+FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn)
+SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR);
+CALL p();
+SELECT * FROM t4;
+YEAR d1 d2
+2018 2018-01-01 2018-09-20
+DROP PROCEDURE p;
+DROP TABLE t1,t2,t3,t4;
+#
+# End of 10.2 tests
+#
diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test
index 1c0280f..d190458 100644
--- a/mysql-test/t/cte_recursive.test
+++ b/mysql-test/t/cte_recursive.test
@@ -2390,6 +2390,30 @@ select * from cte1, cte2 where cte1.c1 = 3;
eval $q3;
+let $q4=
+with recursive
+rcte(a) as
+(select 1 union select cast(a+1 as unsigned) from rcte where a < 10),
+cte1 as
+(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3),
+cte2 as
+(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7)
+select * from cte2, cte1;
+
+eval $q4;
+eval explain extended $q4;
+eval prepare stmt from "$q4";
+execute stmt;
+execute stmt;
+
+drop procedure p;
+drop table t2;
+
+create table t2 (c1 int, c2 int);
+eval create procedure p() insert into t2 $q4;
+call p();
+select * from t2;
+
drop procedure p;
drop table t1,t2;
@@ -2574,4 +2598,48 @@ eval analyze format=json $q;
drop function f1;
drop table t1,t2;
---echo End of 10.2 tests
+--echo #
+--echo # MDEV-22748: two materialized CTEs using the same recursive CTE
+--echo # (see also test case for MDEV-17024)
+--echo #
+
+CREATE TABLE t1 (YEAR int(4), d1 date , d2 date) ;
+INSERT INTO t1 VALUES (2018,'2018-01-01','2018-09-20');
+CREATE TABLE t2 (id int, tm date);
+INSERT INTO t2 VALUES (1,'2018-08-30'),(2,'2018-08-30'),(3,'2018-08-30');
+CREATE TABLE t3 (id int, tm date);
+INSERT INTO t3 VALUES (1,'2018-08-30'),(2,'2018-08-30');
+
+let $q=
+WITH RECURSIVE
+cte AS
+ (SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn
+ FROM t1
+ UNION ALL
+ SELECT YEAR(cte.st + INTERVAL 1 MONTH),
+ cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY
+ FROM cte JOIN t1
+ WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ),
+cte2 AS (SELECT YEAR, COUNT(*)
+ FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn),
+cte3 AS (SELECT YEAR, COUNT(*)
+ FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn)
+SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR);
+
+eval $q;
+eval EXPLAIN EXTENDED $q;
+eval PREPARE stmt FROM "$q";
+EXECUTE stmt;
+EXECUTE stmt;
+
+CREATE TABLE t4 (YEAR int(4), d1 date , d2 date);
+eval CREATE PROCEDURE p() INSERT INTO t4 $q;
+CALL p();
+SELECT * FROM t4;
+
+DROP PROCEDURE p;
+DROP TABLE t1,t2,t3,t4;
+
+--echo #
+--echo # End of 10.2 tests
+--echo #
diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc
index d922a7a..fe8e0de 100644
--- a/sql/sql_cte.cc
+++ b/sql/sql_cte.cc
@@ -1099,6 +1099,7 @@ bool TABLE_LIST::set_as_with_table(THD *thd, With_element *with_elem)
{
derived= with_elem->spec;
if (derived != select_lex->master_unit() &&
+ !with_elem->is_recursive &&
!is_with_table_recursive_reference())
{
derived->move_as_slave(select_lex);
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index 6785bf8..39499e6 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -1054,7 +1054,6 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived)
DBUG_ASSERT(derived->table && derived->table->is_created());
select_union *derived_result= derived->derived_result;
SELECT_LEX *save_current_select= lex->current_select;
- bool derived_recursive_is_filled= false;
if (derived_is_recursive)
{
@@ -1067,7 +1066,6 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived)
{
/* In this case all iteration are performed */
res= derived->fill_recursive(thd);
- derived_recursive_is_filled= true;
}
}
else if (unit->is_union())
@@ -1123,9 +1121,7 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived)
}
}
- if (res || (!lex->describe &&
- (!derived_is_recursive ||
- derived_recursive_is_filled)))
+ if (res || (!lex->describe && !derived_is_recursive))
unit->cleanup();
lex->current_select= save_current_select;
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 7e4d06b..b2198eb 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -1540,6 +1540,19 @@ bool st_select_lex::cleanup()
delete join;
join= 0;
}
+ for (TABLE_LIST *tbl= get_table_list(); tbl; tbl= tbl->next_local)
+ {
+ if (tbl->is_recursive_with_table() &&
+ !tbl->is_with_table_recursive_reference())
+ {
+ /*
+ If query is killed before open_and_process_table() for tbl
+ is called then 'with' is already set, but 'derived' is not.
+ */
+ st_select_lex_unit *unit= tbl->with->spec;
+ error|= (bool) error | (uint) unit->cleanup();
+ }
+ }
for (SELECT_LEX_UNIT *lex_unit= first_inner_unit(); lex_unit ;
lex_unit= lex_unit->next_unit())
{
1
0
05 Jun '20
revision-id: 238c500f077ed7a77330794defd36bc96d1ba030 (mariadb-10.2.31-259-g238c500)
parent(s): a8c200c73c96d35b9e58fdcde0987f0ac5cb9b1a
author: Igor Babaev
committer: Igor Babaev
timestamp: 2020-06-05 13:11:28 -0700
message:
MDEV-22748 MariaDB crash on WITH RECURSIVE large query
This bug is the same as the bug MDEV-17024. The crashes caused by these
bugs were due to premature cleanups of the unit specifying recursive CTEs
that happened in some cases when there were several outer references the
same recursive CTE.
The problem of premature cleanups for recursive CTEs could be already
resolved by the correction in TABLE_LIST::set_as_with_table() introduced
in this patch. ALL other changes introduced by the patches for MDEV-17024
and MDEV-22748 guarantee that this clean-ups are performed as soon as
possible: when the select containing the last outer reference to a
recursive CTE is being cleaned up the specification of the recursive CTE
should be cleaned up as well.
---
mysql-test/r/cte_recursive.result | 170 ++++++++++++++++++++++++++++++++++++--
mysql-test/t/cte_recursive.test | 70 +++++++++++++++-
sql/sql_cte.cc | 1 +
sql/sql_derived.cc | 6 +-
sql/sql_union.cc | 6 ++
5 files changed, 240 insertions(+), 13 deletions(-)
diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result
index 0b8bc3f..6404931 100644
--- a/mysql-test/r/cte_recursive.result
+++ b/mysql-test/r/cte_recursive.result
@@ -691,13 +691,13 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
1 PRIMARY <derived3> ref key0 key0 5 c.h_id 2 100.00
1 PRIMARY <derived3> ref key0 key0 5 c.w_id 2 100.00
-2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 100.00 Using where
3 DERIVED folks ALL NULL NULL NULL NULL 12 100.00 Using where
4 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00
4 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join)
5 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00
5 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join)
NULL UNION RESULT <union3,4,5> ALL NULL NULL NULL NULL NULL NULL
+2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 100.00 Using where
Warnings:
Note 1003 with recursive ancestor_couple_ids as (select `a`.`father` AS `h_id`,`a`.`mother` AS `w_id` from `coupled_ancestors` `a` where `a`.`father` is not null and `a`.`mother` is not null), coupled_ancestors as (select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' union all select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `fa` where `test`.`p`.`id` = `fa`.`h_id` union all select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `ma` where `test`.`p`.`id` = `ma`.`w_id`)select `h`.`name` AS `name`,`h`.`dob` AS `dob`,`w`.`name
` AS `name`,`w`.`dob` AS `dob` from `ancestor_couple_ids` `c` join `coupled_ancestors` `h` join `coupled_ancestors` `w` where `h`.`id` = `c`.`h_id` and `w`.`id` = `c`.`w_id`
# simple mutual recursion
@@ -1302,12 +1302,12 @@ id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived4> ALL NULL NULL NULL NULL 24
4 DERIVED folks ALL NULL NULL NULL NULL 12 Using where
6 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12
+5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24
+NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL
3 DERIVED folks ALL NULL NULL NULL NULL 12 Using where
2 RECURSIVE UNION folks ALL PRIMARY NULL NULL NULL 12
2 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join)
NULL UNION RESULT <union3,2> ALL NULL NULL NULL NULL NULL
-5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24
-NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL
explain FORMAT=JSON
with recursive
prev_gen
@@ -3326,13 +3326,13 @@ select * from cte1, cte2;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived4> ALL NULL NULL NULL NULL 6 100.00
1 PRIMARY <derived5> ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join)
+2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
4 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
5 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
-2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
-3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
-NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1003 with recursive rcte as (select 1 AS `a` union select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)select `cte1`.`c1` AS `c1`,`cte2`.`c2` AS `c2` from `cte1` join `cte2`
prepare stmt from "with recursive
@@ -3391,6 +3391,65 @@ cte2 as
(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7)
select * from cte1, cte2 where cte1.c1 = 3;
c1 c2
+with recursive
+rcte(a) as
+(select 1 union select cast(a+1 as unsigned) from rcte where a < 10),
+cte1 as
+(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3),
+cte2 as
+(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7)
+select * from cte2, cte1;
+c2 c1
+1 2
+explain extended with recursive
+rcte(a) as
+(select 1 union select cast(a+1 as unsigned) from rcte where a < 10),
+cte1 as
+(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3),
+cte2 as
+(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7)
+select * from cte2, cte1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY <derived5> ALL NULL NULL NULL NULL 6 100.00
+1 PRIMARY <derived4> ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join)
+2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
+5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
+5 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
+4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
+4 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 with recursive rcte as (select 1 AS `a` union select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)select `cte2`.`c2` AS `c2`,`cte1`.`c1` AS `c1` from `cte2` join `cte1`
+prepare stmt from "with recursive
+rcte(a) as
+(select 1 union select cast(a+1 as unsigned) from rcte where a < 10),
+cte1 as
+(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3),
+cte2 as
+(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7)
+select * from cte2, cte1";
+execute stmt;
+c2 c1
+1 2
+execute stmt;
+c2 c1
+1 2
+drop procedure p;
+drop table t2;
+create table t2 (c1 int, c2 int);
+create procedure p() insert into t2 with recursive
+rcte(a) as
+(select 1 union select cast(a+1 as unsigned) from rcte where a < 10),
+cte1 as
+(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3),
+cte2 as
+(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7)
+select * from cte2, cte1;
+call p();
+select * from t2;
+c1 c2
+1 2
drop procedure p;
drop table t1,t2;
#
@@ -3809,4 +3868,101 @@ ANALYZE
}
drop function f1;
drop table t1,t2;
-End of 10.2 tests
+#
+# MDEV-22748: two materialized CTEs using the same recursive CTE
+# (see also test case for MDEV-17024)
+#
+CREATE TABLE t1 (YEAR int(4), d1 date , d2 date) ;
+INSERT INTO t1 VALUES (2018,'2018-01-01','2018-09-20');
+CREATE TABLE t2 (id int, tm date);
+INSERT INTO t2 VALUES (1,'2018-08-30'),(2,'2018-08-30'),(3,'2018-08-30');
+CREATE TABLE t3 (id int, tm date);
+INSERT INTO t3 VALUES (1,'2018-08-30'),(2,'2018-08-30');
+WITH RECURSIVE
+cte AS
+(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn
+FROM t1
+UNION ALL
+SELECT YEAR(cte.st + INTERVAL 1 MONTH),
+cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY
+FROM cte JOIN t1
+WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ),
+cte2 AS (SELECT YEAR, COUNT(*)
+FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn),
+cte3 AS (SELECT YEAR, COUNT(*)
+FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn)
+SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR);
+YEAR d1 d2
+2018 2018-01-01 2018-09-20
+EXPLAIN EXTENDED WITH RECURSIVE
+cte AS
+(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn
+FROM t1
+UNION ALL
+SELECT YEAR(cte.st + INTERVAL 1 MONTH),
+cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY
+FROM cte JOIN t1
+WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ),
+cte2 AS (SELECT YEAR, COUNT(*)
+FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn),
+cte3 AS (SELECT YEAR, COUNT(*)
+FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn)
+SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
+1 PRIMARY <derived5> ref key0 key0 9 const 0 0.00 Using where
+1 PRIMARY <derived4> ref key0 key0 9 const 0 0.00 Using where
+2 DERIVED t1 system NULL NULL NULL NULL 1 100.00
+3 RECURSIVE UNION t1 system NULL NULL NULL NULL 1 100.00
+3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
+4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00
+4 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
+5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00
+5 DERIVED t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 with recursive cte as (select year(`test`.`t1`.`d1`) AS `YEAR`,`test`.`t1`.`d1` AS `st`,`test`.`t1`.`d1` + interval 1 month AS `fn` from `test`.`t1` union all select year(`cte`.`st` + interval 1 month) AS `YEAR(cte.st + INTERVAL 1 MONTH)`,`cte`.`st` + interval 1 month AS `cte.st + INTERVAL 1 MONTH`,`test`.`t1`.`d2` + interval 1 day AS `t1.d2 + INTERVAL 1 DAY` from `cte` join `test`.`t1` where `cte`.`st` + interval 1 month < `test`.`t1`.`d2`), cte2 as (select `cte`.`YEAR` AS `YEAR`,count(0) AS `COUNT(*)` from `cte` join `test`.`t2` where `test`.`t2`.`tm` between `cte`.`st` and `cte`.`fn`), cte3 as (select `cte`.`YEAR` AS `YEAR`,count(0) AS `COUNT(*)` from `cte` join `test`.`t3` where `test`.`t3`.`tm` between `cte`.`st` and `cte`.`fn`)select 2018 AS `YEAR`,'2018-01-01' AS `d1`,'2018-09-20' AS `d2` from `cte2` join `cte3` where 2018 = `cte3`.`YEAR` and 2018 = `cte2`.`YEAR`
+PREPARE stmt FROM "WITH RECURSIVE
+cte AS
+(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn
+FROM t1
+UNION ALL
+SELECT YEAR(cte.st + INTERVAL 1 MONTH),
+cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY
+FROM cte JOIN t1
+WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ),
+cte2 AS (SELECT YEAR, COUNT(*)
+FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn),
+cte3 AS (SELECT YEAR, COUNT(*)
+FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn)
+SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR)";
+EXECUTE stmt;
+YEAR d1 d2
+2018 2018-01-01 2018-09-20
+EXECUTE stmt;
+YEAR d1 d2
+2018 2018-01-01 2018-09-20
+CREATE TABLE t4 (YEAR int(4), d1 date , d2 date);
+CREATE PROCEDURE p() INSERT INTO t4 WITH RECURSIVE
+cte AS
+(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn
+FROM t1
+UNION ALL
+SELECT YEAR(cte.st + INTERVAL 1 MONTH),
+cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY
+FROM cte JOIN t1
+WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ),
+cte2 AS (SELECT YEAR, COUNT(*)
+FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn),
+cte3 AS (SELECT YEAR, COUNT(*)
+FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn)
+SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR);
+CALL p();
+SELECT * FROM t4;
+YEAR d1 d2
+2018 2018-01-01 2018-09-20
+DROP PROCEDURE p;
+DROP TABLE t1,t2,t3,t4;
+#
+# End of 10.2 tests
+#
diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test
index 1c0280f..d190458 100644
--- a/mysql-test/t/cte_recursive.test
+++ b/mysql-test/t/cte_recursive.test
@@ -2390,6 +2390,30 @@ select * from cte1, cte2 where cte1.c1 = 3;
eval $q3;
+let $q4=
+with recursive
+rcte(a) as
+(select 1 union select cast(a+1 as unsigned) from rcte where a < 10),
+cte1 as
+(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3),
+cte2 as
+(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7)
+select * from cte2, cte1;
+
+eval $q4;
+eval explain extended $q4;
+eval prepare stmt from "$q4";
+execute stmt;
+execute stmt;
+
+drop procedure p;
+drop table t2;
+
+create table t2 (c1 int, c2 int);
+eval create procedure p() insert into t2 $q4;
+call p();
+select * from t2;
+
drop procedure p;
drop table t1,t2;
@@ -2574,4 +2598,48 @@ eval analyze format=json $q;
drop function f1;
drop table t1,t2;
---echo End of 10.2 tests
+--echo #
+--echo # MDEV-22748: two materialized CTEs using the same recursive CTE
+--echo # (see also test case for MDEV-17024)
+--echo #
+
+CREATE TABLE t1 (YEAR int(4), d1 date , d2 date) ;
+INSERT INTO t1 VALUES (2018,'2018-01-01','2018-09-20');
+CREATE TABLE t2 (id int, tm date);
+INSERT INTO t2 VALUES (1,'2018-08-30'),(2,'2018-08-30'),(3,'2018-08-30');
+CREATE TABLE t3 (id int, tm date);
+INSERT INTO t3 VALUES (1,'2018-08-30'),(2,'2018-08-30');
+
+let $q=
+WITH RECURSIVE
+cte AS
+ (SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn
+ FROM t1
+ UNION ALL
+ SELECT YEAR(cte.st + INTERVAL 1 MONTH),
+ cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY
+ FROM cte JOIN t1
+ WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ),
+cte2 AS (SELECT YEAR, COUNT(*)
+ FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn),
+cte3 AS (SELECT YEAR, COUNT(*)
+ FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn)
+SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR);
+
+eval $q;
+eval EXPLAIN EXTENDED $q;
+eval PREPARE stmt FROM "$q";
+EXECUTE stmt;
+EXECUTE stmt;
+
+CREATE TABLE t4 (YEAR int(4), d1 date , d2 date);
+eval CREATE PROCEDURE p() INSERT INTO t4 $q;
+CALL p();
+SELECT * FROM t4;
+
+DROP PROCEDURE p;
+DROP TABLE t1,t2,t3,t4;
+
+--echo #
+--echo # End of 10.2 tests
+--echo #
diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc
index d922a7a..fe8e0de 100644
--- a/sql/sql_cte.cc
+++ b/sql/sql_cte.cc
@@ -1099,6 +1099,7 @@ bool TABLE_LIST::set_as_with_table(THD *thd, With_element *with_elem)
{
derived= with_elem->spec;
if (derived != select_lex->master_unit() &&
+ !with_elem->is_recursive &&
!is_with_table_recursive_reference())
{
derived->move_as_slave(select_lex);
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index 6785bf8..39499e6 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -1054,7 +1054,6 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived)
DBUG_ASSERT(derived->table && derived->table->is_created());
select_union *derived_result= derived->derived_result;
SELECT_LEX *save_current_select= lex->current_select;
- bool derived_recursive_is_filled= false;
if (derived_is_recursive)
{
@@ -1067,7 +1066,6 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived)
{
/* In this case all iteration are performed */
res= derived->fill_recursive(thd);
- derived_recursive_is_filled= true;
}
}
else if (unit->is_union())
@@ -1123,9 +1121,7 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived)
}
}
- if (res || (!lex->describe &&
- (!derived_is_recursive ||
- derived_recursive_is_filled)))
+ if (res || (!lex->describe && !derived_is_recursive))
unit->cleanup();
lex->current_select= save_current_select;
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 7e4d06b..65e03de 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -1540,6 +1540,12 @@ bool st_select_lex::cleanup()
delete join;
join= 0;
}
+ for (TABLE_LIST *tbl= get_table_list(); tbl; tbl= tbl->next_local)
+ {
+ if (tbl->is_recursive_with_table() &&
+ !tbl->is_with_table_recursive_reference())
+ error|= (bool) error | (uint) tbl->get_unit()->cleanup();
+ }
for (SELECT_LEX_UNIT *lex_unit= first_inner_unit(); lex_unit ;
lex_unit= lex_unit->next_unit())
{
1
0
[Commits] 147fd5d: MDEV-22042 Server crash in Item_field::print on ANALYZE FORMAT=JSON
by IgorBabaev 05 Jun '20
by IgorBabaev 05 Jun '20
05 Jun '20
revision-id: 147fd5df70026648789740e98956d6afde055906 (mariadb-10.2.31-257-g147fd5d)
parent(s): 15cdcb2af8d2ed0969e00633c6eed1f414cbd92d
author: Igor Babaev
committer: Igor Babaev
timestamp: 2020-06-05 10:38:40 -0700
message:
MDEV-22042 Server crash in Item_field::print on ANALYZE FORMAT=JSON
When processing a query with a recursive CTE a temporary table is used for
each recursive reference of the CTE. As any temporary table it uses its own
mem-root for table definition structures. Due to specifics of the current
implementation of ANALYZE stmt command this mem-root can be freed only at
the very of query processing. Such deallocation of mem-root memory happens
in close_thread_tables(). The function looks through the list of the tmp
tables rec_tables attached to the THD of the query and frees corresponding
mem-roots. If the query uses a stored function then such list is created
for each query of the function. When a new rec_list has to be created the
old one has to be saved and then restored at the proper moment.
The bug occurred because only one rec_list for the query containing CTE was
created. As a result close_thread_tables() freed tmp mem-roots used for
rec_tables prematurely destroying some data needed for the output produced
by the ANALYZE command.
---
mysql-test/r/cte_recursive.result | 162 ++++++++++++++++++++++++++++++++++++++
mysql-test/t/cte_recursive.test | 39 +++++++++
sql/sp_head.cc | 4 +
3 files changed, 205 insertions(+)
diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result
index 667b8c4..0b8bc3f 100644
--- a/mysql-test/r/cte_recursive.result
+++ b/mysql-test/r/cte_recursive.result
@@ -3648,3 +3648,165 @@ select * from t1 as t;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t ALL NULL NULL NULL NULL 4
drop table t1,t2;
+#
+# MDEV-22042: ANALYZE of query using stored function and recursive CTE
+#
+create table t1 (a1 varchar(20),a2 varchar(20)) engine=myisam;
+insert into t1 values (1,1),(2,2),(3,3);
+create table t2 (
+a2 varchar(20) primary key, b1 varchar(20), key (b1)
+) engine=myisam;
+insert into t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7);
+insert into t2 values (11,11),(12,12),(13,13),(14,14),(15,15),(16,16),(17,17);
+create function f1(id varchar(20)) returns varchar(50)
+begin
+declare res varchar (50);
+select a2 into res from t2 where a2=id and b1=1 limit 1;
+return res;
+end$$
+select fv
+from (select t1.a1, f1(t1.a2) fv from t1) dt
+where (dt.a1) in (with recursive cte as (select a2 from t2 where a2='2'
+ union select tt2.a2 from t2 tt2 join cte on tt2.b1=cte.a2)
+select a2 from cte);
+fv
+NULL
+explain select fv
+from (select t1.a1, f1(t1.a2) fv from t1) dt
+where (dt.a1) in (with recursive cte as (select a2 from t2 where a2='2'
+ union select tt2.a2 from t2 tt2 join cte on tt2.b1=cte.a2)
+select a2 from cte);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join)
+3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2
+4 DERIVED t2 const PRIMARY PRIMARY 22 const 1 Using index
+5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 2
+5 RECURSIVE UNION tt2 ref b1 b1 23 cte.a2 2
+NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL
+analyze format=json select fv
+from (select t1.a1, f1(t1.a2) fv from t1) dt
+where (dt.a1) in (with recursive cte as (select a2 from t2 where a2='2'
+ union select tt2.a2 from t2 tt2 join cte on tt2.b1=cte.a2)
+select a2 from cte);
+ANALYZE
+{
+ "query_block": {
+ "select_id": 1,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "table": {
+ "table_name": "<subquery3>",
+ "access_type": "ALL",
+ "possible_keys": ["distinct_key"],
+ "r_loops": 1,
+ "rows": 2,
+ "r_rows": 1,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100,
+ "materialized": {
+ "unique": 1,
+ "query_block": {
+ "select_id": 3,
+ "table": {
+ "table_name": "<derived4>",
+ "access_type": "ALL",
+ "r_loops": 1,
+ "rows": 2,
+ "r_rows": 1,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100,
+ "materialized": {
+ "query_block": {
+ "recursive_union": {
+ "table_name": "<union4,5>",
+ "access_type": "ALL",
+ "r_loops": 0,
+ "r_rows": null,
+ "query_specifications": [
+ {
+ "query_block": {
+ "select_id": 4,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "table": {
+ "table_name": "t2",
+ "access_type": "const",
+ "possible_keys": ["PRIMARY"],
+ "key": "PRIMARY",
+ "key_length": "22",
+ "used_key_parts": ["a2"],
+ "ref": ["const"],
+ "r_loops": 0,
+ "rows": 1,
+ "r_rows": null,
+ "filtered": 100,
+ "r_filtered": null,
+ "using_index": true
+ }
+ }
+ },
+ {
+ "query_block": {
+ "select_id": 5,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "table": {
+ "table_name": "<derived4>",
+ "access_type": "ALL",
+ "r_loops": 1,
+ "rows": 2,
+ "r_rows": 1,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100
+ },
+ "table": {
+ "table_name": "tt2",
+ "access_type": "ref",
+ "possible_keys": ["b1"],
+ "key": "b1",
+ "key_length": "23",
+ "used_key_parts": ["b1"],
+ "ref": ["cte.a2"],
+ "r_loops": 1,
+ "rows": 2,
+ "r_rows": 1,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100
+ }
+ }
+ }
+ ]
+ }
+ }
+ }
+ }
+ }
+ }
+ },
+ "block-nl-join": {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "r_loops": 1,
+ "rows": 3,
+ "r_rows": 3,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100
+ },
+ "buffer_type": "flat",
+ "buffer_size": "256Kb",
+ "join_type": "BNL",
+ "attached_condition": "t1.a1 = cte.a2",
+ "r_filtered": 33.333
+ }
+ }
+}
+drop function f1;
+drop table t1,t2;
+End of 10.2 tests
diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test
index 2c20e09..1c0280f 100644
--- a/mysql-test/t/cte_recursive.test
+++ b/mysql-test/t/cte_recursive.test
@@ -2536,3 +2536,42 @@ with recursive cte as
select * from t1 as t;
drop table t1,t2;
+
+--echo #
+--echo # MDEV-22042: ANALYZE of query using stored function and recursive CTE
+--echo #
+
+create table t1 (a1 varchar(20),a2 varchar(20)) engine=myisam;
+insert into t1 values (1,1),(2,2),(3,3);
+
+create table t2 (
+a2 varchar(20) primary key, b1 varchar(20), key (b1)
+) engine=myisam;
+insert into t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7);
+insert into t2 values (11,11),(12,12),(13,13),(14,14),(15,15),(16,16),(17,17);
+
+delimiter $$;
+create function f1(id varchar(20)) returns varchar(50)
+begin
+ declare res varchar (50);
+ select a2 into res from t2 where a2=id and b1=1 limit 1;
+ return res;
+end$$
+delimiter ;$$
+
+let q=
+select fv
+from (select t1.a1, f1(t1.a2) fv from t1) dt
+where (dt.a1) in (with recursive cte as (select a2 from t2 where a2='2'
+ union select tt2.a2 from t2 tt2 join cte on tt2.b1=cte.a2)
+select a2 from cte);
+
+eval $q;
+eval explain $q;
+--source include/analyze-format.inc
+eval analyze format=json $q;
+
+drop function f1;
+drop table t1,t2;
+
+--echo End of 10.2 tests
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index 1f5c6e9..1565500 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -1123,6 +1123,7 @@ sp_head::execute(THD *thd, bool merge_da_on_success)
backup_arena;
query_id_t old_query_id;
TABLE *old_derived_tables;
+ TABLE *old_rec_tables;
LEX *old_lex;
Item_change_list old_change_list;
String old_packet;
@@ -1198,6 +1199,8 @@ sp_head::execute(THD *thd, bool merge_da_on_success)
old_query_id= thd->query_id;
old_derived_tables= thd->derived_tables;
thd->derived_tables= 0;
+ old_rec_tables= thd->rec_tables;
+ thd->rec_tables= 0;
save_sql_mode= thd->variables.sql_mode;
thd->variables.sql_mode= m_sql_mode;
save_abort_on_warning= thd->abort_on_warning;
@@ -1379,6 +1382,7 @@ sp_head::execute(THD *thd, bool merge_da_on_success)
thd->set_query_id(old_query_id);
DBUG_ASSERT(!thd->derived_tables);
thd->derived_tables= old_derived_tables;
+ thd->rec_tables= old_rec_tables;
thd->variables.sql_mode= save_sql_mode;
thd->abort_on_warning= save_abort_on_warning;
thd->m_reprepare_observer= save_reprepare_observer;
1
0
[Commits] ca43dc2: MDEV-22042 Server crash in Item_field::print on ANALYZE FORMAT=JSON
by IgorBabaev 05 Jun '20
by IgorBabaev 05 Jun '20
05 Jun '20
revision-id: ca43dc27f404ce83004ddcc3bb75166e3c021010 (mariadb-10.2.31-252-gca43dc2)
parent(s): 29ed04cb6d8e930c2b2bad7baf4cdae02cf712cd
author: Igor Babaev
committer: Igor Babaev
timestamp: 2020-06-05 09:37:02 -0700
message:
MDEV-22042 Server crash in Item_field::print on ANALYZE FORMAT=JSON
When processing a query with a recursive CTE a temporary table is used for
each recursive reference of the CTE. As any temporary table it uses its own
mem-root for table definition structures. Due to specifics of the current
implementation of ANALYZE stmt command this mem-root can be freed only at
the very of query processing. Such deallocation of mem-root memory happens
in close_thread_tables(). The function looks through the list of the tmp
tables rec_tables attached to the THD of the query and frees corresponding
mem-roots. If the query uses a stored function then such list is created
for each query of the function. When a new rec_list has to be created the
old one has to be saved and then restored at the proper moment.
The bug occurred because only one rec_list for the query containing CTE was
created. As a result close_thread_tables() freed tmp mem-roots used for
rec_tables prematurely destroying some data needed for the output produced
by the ANALYZE command.
---
mysql-test/r/cte_recursive.result | 162 ++++++++++++++++++++++++++++++++++++++
mysql-test/t/cte_recursive.test | 40 ++++++++++
sql/sp_head.cc | 4 +
3 files changed, 206 insertions(+)
diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result
index 667b8c4..51358de 100644
--- a/mysql-test/r/cte_recursive.result
+++ b/mysql-test/r/cte_recursive.result
@@ -3648,3 +3648,165 @@ select * from t1 as t;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t ALL NULL NULL NULL NULL 4
drop table t1,t2;
+#
+# MDEV-22042: ANALYZE of query using stored function and recursive CTE
+#
+create table t1 (a1 varchar(20),a2 varchar(20)) engine=myisam;
+insert into t1 values (1,1),(2,2),(3,3);
+create table t2 (
+a2 varchar(20) primary key, b1 varchar(20), key (b1)
+) engine=myisam;
+insert into t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7);
+insert into t2 values (11,11),(12,12),(13,13),(14,14),(15,15),(16,16),(17,17);
+create function f1(id varchar(20)) returns varchar(50)
+begin
+declare res varchar (50);
+select a2 into res from t2 where a2=id and b1=1 limit 1;
+return res;
+end$$
+select fv
+from (select t1.a1, f1(t1.a2) fv from t1) dt
+where (dt.a1) in (with recursive cte as (select a2 from t2 where a2='2'
+ union select tt2.a2 from t2 tt2 join cte on tt2.b1=cte.a2)
+select a2 from cte);
+fv
+NULL
+explain select fv
+from (select t1.a1, f1(t1.a2) fv from t1) dt
+where (dt.a1) in (with recursive cte as (select a2 from t2 where a2='2'
+ union select tt2.a2 from t2 tt2 join cte on tt2.b1=cte.a2)
+select a2 from cte);
+id select_type table type possible_keys key key_len ref rows Extra
+1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 2
+1 PRIMARY t1 ALL NULL NULL NULL NULL 3 Using where; Using join buffer (flat, BNL join)
+3 MATERIALIZED <derived4> ALL NULL NULL NULL NULL 2
+4 DERIVED t2 const PRIMARY PRIMARY 22 const 1 Using index
+5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 2
+5 RECURSIVE UNION tt2 ref b1 b1 23 cte.a2 2
+NULL UNION RESULT <union4,5> ALL NULL NULL NULL NULL NULL
+analyze format=json select fv
+from (select t1.a1, f1(t1.a2) fv from t1) dt
+where (dt.a1) in (with recursive cte as (select a2 from t2 where a2='2'
+ union select tt2.a2 from t2 tt2 join cte on tt2.b1=cte.a2)
+select a2 from cte);
+ANALYZE
+{
+ "query_block": {
+ "select_id": 1,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "table": {
+ "table_name": "<subquery3>",
+ "access_type": "ALL",
+ "possible_keys": ["distinct_key"],
+ "r_loops": 1,
+ "rows": 2,
+ "r_rows": 1,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100,
+ "materialized": {
+ "unique": 1,
+ "query_block": {
+ "select_id": 3,
+ "table": {
+ "table_name": "<derived4>",
+ "access_type": "ALL",
+ "r_loops": 1,
+ "rows": 2,
+ "r_rows": 1,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100,
+ "materialized": {
+ "query_block": {
+ "recursive_union": {
+ "table_name": "<union4,5>",
+ "access_type": "ALL",
+ "r_loops": 0,
+ "r_rows": null,
+ "query_specifications": [
+ {
+ "query_block": {
+ "select_id": 4,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "table": {
+ "table_name": "t2",
+ "access_type": "const",
+ "possible_keys": ["PRIMARY"],
+ "key": "PRIMARY",
+ "key_length": "22",
+ "used_key_parts": ["a2"],
+ "ref": ["const"],
+ "r_loops": 0,
+ "rows": 1,
+ "r_rows": null,
+ "filtered": 100,
+ "r_filtered": null,
+ "using_index": true
+ }
+ }
+ },
+ {
+ "query_block": {
+ "select_id": 5,
+ "r_loops": 1,
+ "r_total_time_ms": "REPLACED",
+ "table": {
+ "table_name": "<derived4>",
+ "access_type": "ALL",
+ "r_loops": 1,
+ "rows": 2,
+ "r_rows": 1,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100
+ },
+ "table": {
+ "table_name": "tt2",
+ "access_type": "ref",
+ "possible_keys": ["b1"],
+ "key": "b1",
+ "key_length": "23",
+ "used_key_parts": ["b1"],
+ "ref": ["cte.a2"],
+ "r_loops": 1,
+ "rows": 2,
+ "r_rows": 1,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100
+ }
+ }
+ }
+ ]
+ }
+ }
+ }
+ }
+ }
+ }
+ },
+ "block-nl-join": {
+ "table": {
+ "table_name": "t1",
+ "access_type": "ALL",
+ "r_loops": 1,
+ "rows": 3,
+ "r_rows": 3,
+ "r_total_time_ms": "REPLACED",
+ "filtered": 100,
+ "r_filtered": 100
+ },
+ "buffer_type": "flat",
+ "buffer_size": "256Kb",
+ "join_type": "BNL",
+ "attached_condition": "t1.a1 = cte.a2",
+ "r_filtered": 33.333
+ }
+ }
+}
+drop function f1;
+drop table t1,t2;
+End of 10.2 tests
diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test
index 2c20e09..5a1d055 100644
--- a/mysql-test/t/cte_recursive.test
+++ b/mysql-test/t/cte_recursive.test
@@ -2536,3 +2536,43 @@ with recursive cte as
select * from t1 as t;
drop table t1,t2;
+
+--echo #
+--echo # MDEV-22042: ANALYZE of query using stored function and recursive CTE
+--echo #
+
+create table t1 (a1 varchar(20),a2 varchar(20)) engine=myisam;
+insert into t1 values (1,1),(2,2),(3,3);
+
+create table t2 (
+a2 varchar(20) primary key, b1 varchar(20), key (b1)
+) engine=myisam;
+insert into t2 values (1,1),(2,2),(3,3),(4,4),(5,5),(6,6),(7,7);
+insert into t2 values (11,11),(12,12),(13,13),(14,14),(15,15),(16,16),(17,17);
+
+delimiter $$;
+create function f1(id varchar(20)) returns varchar(50)
+begin
+ declare res varchar (50);
+ select a2 into res from t2 where a2=id and b1=1 limit 1;
+ return res;
+end$$
+delimiter ;$$
+
+let q=
+select fv
+from (select t1.a1, f1(t1.a2) fv from t1) dt
+where (dt.a1) in (with recursive cte as (select a2 from t2 where a2='2'
+ union select tt2.a2 from t2 tt2 join cte on tt2.b1=cte.a2)
+select a2 from cte);
+
+eval $q;
+eval explain $q;
+--source include/analyze-format.inc
+eval analyze format=json $q;
+
+drop function f1;
+drop table t1,t2;
+
+--echo End of 10.2 tests
+
diff --git a/sql/sp_head.cc b/sql/sp_head.cc
index 1f5c6e9..1565500 100644
--- a/sql/sp_head.cc
+++ b/sql/sp_head.cc
@@ -1123,6 +1123,7 @@ sp_head::execute(THD *thd, bool merge_da_on_success)
backup_arena;
query_id_t old_query_id;
TABLE *old_derived_tables;
+ TABLE *old_rec_tables;
LEX *old_lex;
Item_change_list old_change_list;
String old_packet;
@@ -1198,6 +1199,8 @@ sp_head::execute(THD *thd, bool merge_da_on_success)
old_query_id= thd->query_id;
old_derived_tables= thd->derived_tables;
thd->derived_tables= 0;
+ old_rec_tables= thd->rec_tables;
+ thd->rec_tables= 0;
save_sql_mode= thd->variables.sql_mode;
thd->variables.sql_mode= m_sql_mode;
save_abort_on_warning= thd->abort_on_warning;
@@ -1379,6 +1382,7 @@ sp_head::execute(THD *thd, bool merge_da_on_success)
thd->set_query_id(old_query_id);
DBUG_ASSERT(!thd->derived_tables);
thd->derived_tables= old_derived_tables;
+ thd->rec_tables= old_rec_tables;
thd->variables.sql_mode= save_sql_mode;
thd->abort_on_warning= save_abort_on_warning;
thd->m_reprepare_observer= save_reprepare_observer;
1
0
Test message
BR
Sergei
--
Sergei Petrunia, Software Developer
MariaDB Corporation | Skype: sergefp | Blog: http://s.petrunia.net/blog
1
0
31 May '20
revision-id: 1586e2b7ae28bdfa936e3c3eab7f39e8d7af4aa7 (mariadb-10.2.31-237-g1586e2b)
parent(s): 069200267db4cb79bbd55d06cd7ea9e1acd86ce8
author: Igor Babaev
committer: Igor Babaev
timestamp: 2020-05-31 10:58:09 -0700
message:
MDEV-22748 MariaDB crash on WITH RECURSIVE large query
This bug is the same as the bug MDEV-17024. The crashes caused by these
bugs were due to premature cleanups of the unit specifying recursive CTEs
that happened in some cases when there were several outer references the
same recursive CTE.
The problem of premature cleanups for recursive CTEs could be already
resolved by the correction in TABLE_LIST::set_as_with_table() introduced
in this patch. ALL other changes introduced by the patches for MDEV-17024
and MDEV-22748 guarantee that this clean-ups are performed as soon as
possible: when the select containing the last outer reference to a
recursive CTE is being cleaned up the specification of the recursive CTE
should be cleaned up as well.
---
mysql-test/r/cte_recursive.result | 169 ++++++++++++++++++++++++++++++++++++--
mysql-test/t/cte_recursive.test | 70 ++++++++++++++++
sql/sql_cte.cc | 1 +
sql/sql_derived.cc | 6 +-
sql/sql_union.cc | 6 ++
5 files changed, 241 insertions(+), 11 deletions(-)
diff --git a/mysql-test/r/cte_recursive.result b/mysql-test/r/cte_recursive.result
index 667b8c4..98947b9 100644
--- a/mysql-test/r/cte_recursive.result
+++ b/mysql-test/r/cte_recursive.result
@@ -691,13 +691,13 @@ id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
1 PRIMARY <derived3> ref key0 key0 5 c.h_id 2 100.00
1 PRIMARY <derived3> ref key0 key0 5 c.w_id 2 100.00
-2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 100.00 Using where
3 DERIVED folks ALL NULL NULL NULL NULL 12 100.00 Using where
4 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00
4 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join)
5 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00
5 RECURSIVE UNION p ALL NULL NULL NULL NULL 12 100.00 Using where; Using join buffer (flat, BNL join)
NULL UNION RESULT <union3,4,5> ALL NULL NULL NULL NULL NULL NULL
+2 DERIVED <derived3> ALL NULL NULL NULL NULL 12 100.00 Using where
Warnings:
Note 1003 with recursive ancestor_couple_ids as (select `a`.`father` AS `h_id`,`a`.`mother` AS `w_id` from `coupled_ancestors` `a` where `a`.`father` is not null and `a`.`mother` is not null), coupled_ancestors as (select `test`.`folks`.`id` AS `id`,`test`.`folks`.`name` AS `name`,`test`.`folks`.`dob` AS `dob`,`test`.`folks`.`father` AS `father`,`test`.`folks`.`mother` AS `mother` from `test`.`folks` where `test`.`folks`.`name` = 'Me' union all select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `fa` where `test`.`p`.`id` = `fa`.`h_id` union all select `test`.`p`.`id` AS `id`,`test`.`p`.`name` AS `name`,`test`.`p`.`dob` AS `dob`,`test`.`p`.`father` AS `father`,`test`.`p`.`mother` AS `mother` from `test`.`folks` `p` join `ancestor_couple_ids` `ma` where `test`.`p`.`id` = `ma`.`w_id`)select `h`.`name` AS `name`,`h`.`dob` AS `dob`,`w`.`name
` AS `name`,`w`.`dob` AS `dob` from `ancestor_couple_ids` `c` join `coupled_ancestors` `h` join `coupled_ancestors` `w` where `h`.`id` = `c`.`h_id` and `w`.`id` = `c`.`w_id`
# simple mutual recursion
@@ -1302,12 +1302,12 @@ id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <derived4> ALL NULL NULL NULL NULL 24
4 DERIVED folks ALL NULL NULL NULL NULL 12 Using where
6 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12
+5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24
+NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL
3 DERIVED folks ALL NULL NULL NULL NULL 12 Using where
2 RECURSIVE UNION folks ALL PRIMARY NULL NULL NULL 12
2 RECURSIVE UNION <derived3> ALL NULL NULL NULL NULL 12 Using where; Using join buffer (flat, BNL join)
NULL UNION RESULT <union3,2> ALL NULL NULL NULL NULL NULL
-5 RECURSIVE UNION <derived4> ALL NULL NULL NULL NULL 24
-NULL UNION RESULT <union4,6,5> ALL NULL NULL NULL NULL NULL
explain FORMAT=JSON
with recursive
prev_gen
@@ -3326,13 +3326,13 @@ select * from cte1, cte2;
id select_type table type possible_keys key key_len ref rows filtered Extra
1 PRIMARY <derived4> ALL NULL NULL NULL NULL 6 100.00
1 PRIMARY <derived5> ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join)
+2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
4 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
5 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
-2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
-3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
-NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
Warnings:
Note 1003 with recursive rcte as (select 1 AS `a` union select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)select `cte1`.`c1` AS `c1`,`cte2`.`c2` AS `c2` from `cte1` join `cte2`
prepare stmt from "with recursive
@@ -3391,6 +3391,65 @@ cte2 as
(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7)
select * from cte1, cte2 where cte1.c1 = 3;
c1 c2
+with recursive
+rcte(a) as
+(select 1 union select cast(a+1 as unsigned) from rcte where a < 10),
+cte1 as
+(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3),
+cte2 as
+(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7)
+select * from cte2, cte1;
+c2 c1
+1 2
+explain extended with recursive
+rcte(a) as
+(select 1 union select cast(a+1 as unsigned) from rcte where a < 10),
+cte1 as
+(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3),
+cte2 as
+(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7)
+select * from cte2, cte1;
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY <derived5> ALL NULL NULL NULL NULL 6 100.00
+1 PRIMARY <derived4> ALL NULL NULL NULL NULL 6 100.00 Using join buffer (flat, BNL join)
+2 DERIVED NULL NULL NULL NULL NULL NULL NULL NULL No tables used
+3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
+5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
+5 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
+4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
+4 DERIVED t1 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 with recursive rcte as (select 1 AS `a` union select cast(`rcte`.`a` + 1 as unsigned) AS `cast(a+1 as unsigned)` from `rcte` where `rcte`.`a` < 10), cte1 as (select count(0) AS `c1` from `rcte` join `test`.`t1` where `rcte`.`a` between 3 and 5 and `test`.`t1`.`id` = `rcte`.`a` - 3), cte2 as (select count(0) AS `c2` from `rcte` join `test`.`t1` where `rcte`.`a` between 7 and 8 and `test`.`t1`.`id` = `rcte`.`a` - 7)select `cte2`.`c2` AS `c2`,`cte1`.`c1` AS `c1` from `cte2` join `cte1`
+prepare stmt from "with recursive
+rcte(a) as
+(select 1 union select cast(a+1 as unsigned) from rcte where a < 10),
+cte1 as
+(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3),
+cte2 as
+(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7)
+select * from cte2, cte1";
+execute stmt;
+c2 c1
+1 2
+execute stmt;
+c2 c1
+1 2
+drop procedure p;
+drop table t2;
+create table t2 (c1 int, c2 int);
+create procedure p() insert into t2 with recursive
+rcte(a) as
+(select 1 union select cast(a+1 as unsigned) from rcte where a < 10),
+cte1 as
+(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3),
+cte2 as
+(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7)
+select * from cte2, cte1;
+call p();
+select * from t2;
+c1 c2
+1 2
drop procedure p;
drop table t1,t2;
#
@@ -3648,3 +3707,101 @@ select * from t1 as t;
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY t ALL NULL NULL NULL NULL 4
drop table t1,t2;
+#
+# MDEV-22748: two materialized CTEs using the same recursive CTE
+# (see also test case for MDEV-17024)
+#
+CREATE TABLE t1 (YEAR int(4), d1 date , d2 date) ;
+INSERT INTO t1 VALUES (2018,'2018-01-01','2018-09-20');
+CREATE TABLE t2 (id int, tm date);
+INSERT INTO t2 VALUES (1,'2018-08-30'),(2,'2018-08-30'),(3,'2018-08-30');
+CREATE TABLE t3 (id int, tm date);
+INSERT INTO t3 VALUES (1,'2018-08-30'),(2,'2018-08-30');
+WITH RECURSIVE
+cte AS
+(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn
+FROM t1
+UNION ALL
+SELECT YEAR(cte.st + INTERVAL 1 MONTH),
+cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY
+FROM cte JOIN t1
+WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ),
+cte2 AS (SELECT YEAR, COUNT(*)
+FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn),
+cte3 AS (SELECT YEAR, COUNT(*)
+FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn)
+SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR);
+YEAR d1 d2
+2018 2018-01-01 2018-09-20
+EXPLAIN EXTENDED WITH RECURSIVE
+cte AS
+(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn
+FROM t1
+UNION ALL
+SELECT YEAR(cte.st + INTERVAL 1 MONTH),
+cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY
+FROM cte JOIN t1
+WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ),
+cte2 AS (SELECT YEAR, COUNT(*)
+FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn),
+cte3 AS (SELECT YEAR, COUNT(*)
+FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn)
+SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR);
+id select_type table type possible_keys key key_len ref rows filtered Extra
+1 PRIMARY t1 system NULL NULL NULL NULL 1 100.00
+1 PRIMARY <derived5> ref key0 key0 9 const 0 0.00 Using where
+1 PRIMARY <derived4> ref key0 key0 9 const 0 0.00 Using where
+2 DERIVED t1 system NULL NULL NULL NULL 1 100.00
+3 RECURSIVE UNION t1 system NULL NULL NULL NULL 1 100.00
+3 RECURSIVE UNION <derived2> ALL NULL NULL NULL NULL 2 100.00 Using where
+NULL UNION RESULT <union2,3> ALL NULL NULL NULL NULL NULL NULL
+4 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00
+4 DERIVED t2 ALL NULL NULL NULL NULL 3 100.00 Using where; Using join buffer (flat, BNL join)
+5 DERIVED <derived2> ALL NULL NULL NULL NULL 2 100.00
+5 DERIVED t3 ALL NULL NULL NULL NULL 2 100.00 Using where; Using join buffer (flat, BNL join)
+Warnings:
+Note 1003 with recursive cte as (select year(`test`.`t1`.`d1`) AS `YEAR`,`test`.`t1`.`d1` AS `st`,`test`.`t1`.`d1` + interval 1 month AS `fn` from `test`.`t1` union all select year(`cte`.`st` + interval 1 month) AS `YEAR(cte.st + INTERVAL 1 MONTH)`,`cte`.`st` + interval 1 month AS `cte.st + INTERVAL 1 MONTH`,`test`.`t1`.`d2` + interval 1 day AS `t1.d2 + INTERVAL 1 DAY` from `cte` join `test`.`t1` where `cte`.`st` + interval 1 month < `test`.`t1`.`d2`), cte2 as (select `cte`.`YEAR` AS `YEAR`,count(0) AS `COUNT(*)` from `cte` join `test`.`t2` where `test`.`t2`.`tm` between `cte`.`st` and `cte`.`fn`), cte3 as (select `cte`.`YEAR` AS `YEAR`,count(0) AS `COUNT(*)` from `cte` join `test`.`t3` where `test`.`t3`.`tm` between `cte`.`st` and `cte`.`fn`)select 2018 AS `YEAR`,'2018-01-01' AS `d1`,'2018-09-20' AS `d2` from `cte2` join `cte3` where 2018 = `cte3`.`YEAR` and 2018 = `cte2`.`YEAR`
+PREPARE stmt FROM "WITH RECURSIVE
+cte AS
+(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn
+FROM t1
+UNION ALL
+SELECT YEAR(cte.st + INTERVAL 1 MONTH),
+cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY
+FROM cte JOIN t1
+WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ),
+cte2 AS (SELECT YEAR, COUNT(*)
+FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn),
+cte3 AS (SELECT YEAR, COUNT(*)
+FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn)
+SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR)";
+EXECUTE stmt;
+YEAR d1 d2
+2018 2018-01-01 2018-09-20
+EXECUTE stmt;
+YEAR d1 d2
+2018 2018-01-01 2018-09-20
+CREATE TABLE t4 (YEAR int(4), d1 date , d2 date);
+CREATE PROCEDURE p() INSERT INTO t4 WITH RECURSIVE
+cte AS
+(SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn
+FROM t1
+UNION ALL
+SELECT YEAR(cte.st + INTERVAL 1 MONTH),
+cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY
+FROM cte JOIN t1
+WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ),
+cte2 AS (SELECT YEAR, COUNT(*)
+FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn),
+cte3 AS (SELECT YEAR, COUNT(*)
+FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn)
+SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR);
+CALL p();
+SELECT * FROM t4;
+YEAR d1 d2
+2018 2018-01-01 2018-09-20
+DROP PROCEDURE p;
+DROP TABLE t1,t2,t3,t4;
+#
+# End of 10.2 tests
+#
diff --git a/mysql-test/t/cte_recursive.test b/mysql-test/t/cte_recursive.test
index 2c20e09..1256e66 100644
--- a/mysql-test/t/cte_recursive.test
+++ b/mysql-test/t/cte_recursive.test
@@ -2390,6 +2390,30 @@ select * from cte1, cte2 where cte1.c1 = 3;
eval $q3;
+let $q4=
+with recursive
+rcte(a) as
+(select 1 union select cast(a+1 as unsigned) from rcte where a < 10),
+cte1 as
+(select count(*) as c1 from rcte,t1 where a between 3 and 5 and id=a-3),
+cte2 as
+(select count(*) as c2 from rcte,t1 where a between 7 and 8 and id=a-7)
+select * from cte2, cte1;
+
+eval $q4;
+eval explain extended $q4;
+eval prepare stmt from "$q4";
+execute stmt;
+execute stmt;
+
+drop procedure p;
+drop table t2;
+
+create table t2 (c1 int, c2 int);
+eval create procedure p() insert into t2 $q4;
+call p();
+select * from t2;
+
drop procedure p;
drop table t1,t2;
@@ -2536,3 +2560,49 @@ with recursive cte as
select * from t1 as t;
drop table t1,t2;
+
+--echo #
+--echo # MDEV-22748: two materialized CTEs using the same recursive CTE
+--echo # (see also test case for MDEV-17024)
+--echo #
+
+CREATE TABLE t1 (YEAR int(4), d1 date , d2 date) ;
+INSERT INTO t1 VALUES (2018,'2018-01-01','2018-09-20');
+CREATE TABLE t2 (id int, tm date);
+INSERT INTO t2 VALUES (1,'2018-08-30'),(2,'2018-08-30'),(3,'2018-08-30');
+CREATE TABLE t3 (id int, tm date);
+INSERT INTO t3 VALUES (1,'2018-08-30'),(2,'2018-08-30');
+
+let $q=
+WITH RECURSIVE
+cte AS
+ (SELECT YEAR(t1.d1) AS YEAR, t1.d1 AS st, t1.d1 + INTERVAL 1 MONTH AS fn
+ FROM t1
+ UNION ALL
+ SELECT YEAR(cte.st + INTERVAL 1 MONTH),
+ cte.st + INTERVAL 1 MONTH, t1.d2 + INTERVAL 1 DAY
+ FROM cte JOIN t1
+ WHERE cte.st + INTERVAL 1 MONTH < t1.d2 ),
+cte2 AS (SELECT YEAR, COUNT(*)
+ FROM cte JOIN t2 ON t2.tm BETWEEN cte.st AND cte.fn),
+cte3 AS (SELECT YEAR, COUNT(*)
+ FROM cte JOIN t3 ON t3.tm BETWEEN cte.st AND cte.fn)
+SELECT t1.* FROM t1 JOIN cte2 USING (YEAR) JOIN cte3 USING (YEAR);
+
+eval $q;
+eval EXPLAIN EXTENDED $q;
+eval PREPARE stmt FROM "$q";
+EXECUTE stmt;
+EXECUTE stmt;
+
+CREATE TABLE t4 (YEAR int(4), d1 date , d2 date);
+eval CREATE PROCEDURE p() INSERT INTO t4 $q;
+CALL p();
+SELECT * FROM t4;
+
+DROP PROCEDURE p;
+DROP TABLE t1,t2,t3,t4;
+
+--echo #
+--echo # End of 10.2 tests
+--echo #
diff --git a/sql/sql_cte.cc b/sql/sql_cte.cc
index d922a7a..fe8e0de 100644
--- a/sql/sql_cte.cc
+++ b/sql/sql_cte.cc
@@ -1099,6 +1099,7 @@ bool TABLE_LIST::set_as_with_table(THD *thd, With_element *with_elem)
{
derived= with_elem->spec;
if (derived != select_lex->master_unit() &&
+ !with_elem->is_recursive &&
!is_with_table_recursive_reference())
{
derived->move_as_slave(select_lex);
diff --git a/sql/sql_derived.cc b/sql/sql_derived.cc
index 6785bf8..39499e6 100644
--- a/sql/sql_derived.cc
+++ b/sql/sql_derived.cc
@@ -1054,7 +1054,6 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived)
DBUG_ASSERT(derived->table && derived->table->is_created());
select_union *derived_result= derived->derived_result;
SELECT_LEX *save_current_select= lex->current_select;
- bool derived_recursive_is_filled= false;
if (derived_is_recursive)
{
@@ -1067,7 +1066,6 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived)
{
/* In this case all iteration are performed */
res= derived->fill_recursive(thd);
- derived_recursive_is_filled= true;
}
}
else if (unit->is_union())
@@ -1123,9 +1121,7 @@ bool mysql_derived_fill(THD *thd, LEX *lex, TABLE_LIST *derived)
}
}
- if (res || (!lex->describe &&
- (!derived_is_recursive ||
- derived_recursive_is_filled)))
+ if (res || (!lex->describe && !derived_is_recursive))
unit->cleanup();
lex->current_select= save_current_select;
diff --git a/sql/sql_union.cc b/sql/sql_union.cc
index 7e4d06b..65e03de 100644
--- a/sql/sql_union.cc
+++ b/sql/sql_union.cc
@@ -1540,6 +1540,12 @@ bool st_select_lex::cleanup()
delete join;
join= 0;
}
+ for (TABLE_LIST *tbl= get_table_list(); tbl; tbl= tbl->next_local)
+ {
+ if (tbl->is_recursive_with_table() &&
+ !tbl->is_with_table_recursive_reference())
+ error|= (bool) error | (uint) tbl->get_unit()->cleanup();
+ }
for (SELECT_LEX_UNIT *lex_unit= first_inner_unit(); lex_unit ;
lex_unit= lex_unit->next_unit())
{
1
0
This is a test, please ignore.
--
Daniel Bartholomew, MariaDB Release Manager
MariaDB | https://mariadb.com
1
0
[Commits] 85b116d1ab9: MDEV-22317: SIGSEGV in my_free/delete_dynamic in optimized builds (ARIA)
by sujatha 27 Apr '20
by sujatha 27 Apr '20
27 Apr '20
revision-id: 85b116d1ab9ea85dcef63d259b8f6366466e2750 (mariadb-10.5.2-185-g85b116d1ab9)
parent(s): fbe2712705d464bf8488df249c36115e2c1f63f7
author: Sujatha
committer: Sujatha
timestamp: 2020-04-27 17:43:51 +0530
message:
MDEV-22317: SIGSEGV in my_free/delete_dynamic in optimized builds (ARIA)
Problem:
=======
SET @@GLOBAL.replicate_wild_ignore_table='';
SET @@GLOBAL.replicate_wild_do_table='';
Reports following valgrind error.
Conditional jump or move depends on uninitialised value(s)
Rpl_filter::set_wild_ignore_table(char const*) (rpl_filter.cc:439)
Conditional jump or move depends on uninitialised value(s)
at 0xF60390: delete_dynamic (array.c:304)
by 0x74F3F2: Rpl_filter::set_wild_do_table(char const*) (rpl_filter.cc:421)
Analysis:
========
List of values provided for options "wild_do_table" and "wild_ignore_table" are
stored in DYNAMIC_ARRAYS. When an empty list is provided these dynamic arrays
are not initialized. Existing code treats empty element list as an error and
tries to clean the uninitialized list. This results in above valgrind issue.
Fix:
===
The clean up should be initiated only when there is an error while parsing the
'wild_do_table' or 'wild_ignore_table' list and the dynamic_array is in
initialized state. Otherwise for empty list it should simply return success.
---
mysql-test/suite/rpl/r/rpl_filter_wild_tables_dynamic.result | 2 ++
mysql-test/suite/rpl/t/rpl_filter_wild_tables_dynamic.test | 2 ++
sql/rpl_filter.cc | 4 ++--
3 files changed, 6 insertions(+), 2 deletions(-)
diff --git a/mysql-test/suite/rpl/r/rpl_filter_wild_tables_dynamic.result b/mysql-test/suite/rpl/r/rpl_filter_wild_tables_dynamic.result
index 47cd362a549..fe0b283fc7c 100644
--- a/mysql-test/suite/rpl/r/rpl_filter_wild_tables_dynamic.result
+++ b/mysql-test/suite/rpl/r/rpl_filter_wild_tables_dynamic.result
@@ -7,6 +7,8 @@ SET @@GLOBAL.replicate_wild_ignore_table="test.b%";
ERROR HY000: This operation cannot be performed as you have a running slave ''; run STOP SLAVE '' first
connection slave;
include/stop_slave.inc
+SET @@GLOBAL.replicate_wild_do_table="";
+SET @@GLOBAL.replicate_wild_ignore_table="";
SET @@GLOBAL.replicate_wild_do_table="test.a%";
SET @@GLOBAL.replicate_wild_ignore_table="test.b%";
include/start_slave.inc
diff --git a/mysql-test/suite/rpl/t/rpl_filter_wild_tables_dynamic.test b/mysql-test/suite/rpl/t/rpl_filter_wild_tables_dynamic.test
index 6db61927eec..657a95cec15 100644
--- a/mysql-test/suite/rpl/t/rpl_filter_wild_tables_dynamic.test
+++ b/mysql-test/suite/rpl/t/rpl_filter_wild_tables_dynamic.test
@@ -13,6 +13,8 @@ SET @@GLOBAL.replicate_wild_ignore_table="test.b%";
connection slave;
source include/stop_slave.inc;
+SET @@GLOBAL.replicate_wild_do_table="";
+SET @@GLOBAL.replicate_wild_ignore_table="";
SET @@GLOBAL.replicate_wild_do_table="test.a%";
SET @@GLOBAL.replicate_wild_ignore_table="test.b%";
source include/start_slave.inc;
diff --git a/sql/rpl_filter.cc b/sql/rpl_filter.cc
index 635a0f4e2d6..49b498d3568 100644
--- a/sql/rpl_filter.cc
+++ b/sql/rpl_filter.cc
@@ -416,7 +416,7 @@ Rpl_filter::set_wild_do_table(const char* table_spec)
status= parse_filter_rule(table_spec, &Rpl_filter::add_wild_do_table);
- if (!wild_do_table.elements)
+ if (status && wild_do_table_inited)
{
delete_dynamic(&wild_do_table);
wild_do_table_inited= 0;
@@ -436,7 +436,7 @@ Rpl_filter::set_wild_ignore_table(const char* table_spec)
status= parse_filter_rule(table_spec, &Rpl_filter::add_wild_ignore_table);
- if (!wild_ignore_table.elements)
+ if (status && wild_ignore_table_inited)
{
delete_dynamic(&wild_ignore_table);
wild_ignore_table_inited= 0;
1
0
[Commits] fb963a7fd3b: MDEV-21117: --tc-heuristic-recover=rollback is not replication safe
by sujatha 21 Apr '20
by sujatha 21 Apr '20
21 Apr '20
revision-id: fb963a7fd3b3e0733fb7ad9ece6256aebd8c176c (mariadb-10.1.43-112-gfb963a7fd3b)
parent(s): ad4b70562bb94dd063eebde5189c6e730d3120a2
author: Sujatha
committer: Sujatha
timestamp: 2020-04-21 18:19:34 +0530
message:
MDEV-21117: --tc-heuristic-recover=rollback is not replication safe
Problem:
=======
When run after master server crash --tc-heuristic-recover=rollback produces
inconsistent server state with binlog still containing transactions that were
rolled back by the option. Such way recovered server may not be used for
replication.
Fix:
===
During "--tc-heuristic-recover=ROLLBACK", query the storage engine to get
binlog file name and position corresponding to the last committed transaction.
This marks the consistent binlog state. If last_commit_file is not set then
checkpoint binary log file is considered as starting point.
Look for first transactional event beyond the consistent binlog state. This
will be the starting point for heuristic rollback. Consider this event
specific starting point as binlog truncation position. Now traverse the rest
of binlogs beyond this point. During this traversal check for the presence of
DDL or non transactional operations, as they cannot be safely rolled back. If
such events are found the truncation will not happen it will return an error.
If only transactional events are found beyond the binlog truncation position
it is safe to truncate binlog. The log gets truncated to the identified
position and the GTID state is adjusted accordingly. If there are more binary
logs beyond the being truncated file they are all removed.
If recovery is initiated based on last committed transaction specific binary
log name and position, and there is failure during recovery, then recovery may
be retried by remove the source of the failure. i.e
--tc-heuristic-recover=rollback may be retried.
Retry will not be successful if recovery is based on checkpoint file. As at
the end of tc-heuristic-recover a new binary log file is created which will
change the checkpoint file. Appropriate error message is written to error log
for each case.
---
.../r/binlog_heuristic_rollback_active_log.result | 18 +
.../binlog/r/binlog_truncate_multi_log.result | 33 ++
.../r/binlog_truncate_multi_log_unsafe.result | 30 ++
.../suite/binlog/r/binlog_truncate_none.result | 42 ++
.../binlog/r/binlog_truncate_retry_success.result | 28 +
.../t/binlog_heuristic_rollback_active_log.test | 75 +++
.../suite/binlog/t/binlog_truncate_multi_log.test | 87 +++
.../binlog/t/binlog_truncate_multi_log_unsafe.test | 106 ++++
.../suite/binlog/t/binlog_truncate_none.test | 130 +++++
.../binlog/t/binlog_truncate_retry_success.test | 84 +++
.../suite/rpl/r/rpl_heuristic_fail_over.result | 53 ++
.../suite/rpl/t/rpl_heuristic_fail_over.test | 160 ++++++
sql/log.cc | 589 ++++++++++++++++++++-
sql/log.h | 7 +-
sql/mysqld.h | 3 +-
storage/innobase/handler/ha_innodb.cc | 7 +
storage/innobase/log/log0recv.cc | 5 +-
storage/xtradb/handler/ha_innodb.cc | 7 +
storage/xtradb/log/log0recv.cc | 5 +-
19 files changed, 1457 insertions(+), 12 deletions(-)
diff --git a/mysql-test/suite/binlog/r/binlog_heuristic_rollback_active_log.result b/mysql-test/suite/binlog/r/binlog_heuristic_rollback_active_log.result
new file mode 100644
index 00000000000..eff95d05aac
--- /dev/null
+++ b/mysql-test/suite/binlog/r/binlog_heuristic_rollback_active_log.result
@@ -0,0 +1,18 @@
+call mtr.add_suppression("Can't init tc log");
+call mtr.add_suppression("Aborting");
+RESET MASTER;
+CREATE TABLE t ( f INT ) ENGINE=INNODB;
+INSERT INTO t VALUES (10);
+SET DEBUG_SYNC= "commit_before_update_end_pos SIGNAL con1_ready WAIT_FOR con1_go";
+INSERT INTO t VALUES (20);
+SET DEBUG_SYNC= "now WAIT_FOR con1_ready";
+# Kill the server
+"One row should be present in table 't'"
+SELECT COUNT(*) FROM t;
+COUNT(*)
+1
+"gtid_binlog_state should be 0-1-2
+SELECT @@GLOBAL.gtid_binlog_state;
+@@GLOBAL.gtid_binlog_state
+0-1-2
+DROP TABLE t;
diff --git a/mysql-test/suite/binlog/r/binlog_truncate_multi_log.result b/mysql-test/suite/binlog/r/binlog_truncate_multi_log.result
new file mode 100644
index 00000000000..1a3d49b5463
--- /dev/null
+++ b/mysql-test/suite/binlog/r/binlog_truncate_multi_log.result
@@ -0,0 +1,33 @@
+SET @old_max_binlog_size= @@GLOBAL.max_binlog_size;
+SET GLOBAL max_binlog_size= 4096;
+call mtr.add_suppression("Can't init tc log");
+call mtr.add_suppression("Aborting");
+RESET MASTER;
+CREATE TABLE t1 (a INT PRIMARY KEY, b MEDIUMTEXT) ENGINE=Innodb;
+FLUSH LOGS;
+"List of binary logs before rotation"
+show binary logs;
+Log_name File_size
+master-bin.000001 #
+master-bin.000002 #
+SET DEBUG_SYNC= "commit_after_release_LOCK_log SIGNAL con1_ready WAIT_FOR con1_go";
+INSERT INTO t1 VALUES (2, REPEAT("x", 4100));
+SET DEBUG_SYNC= "now WAIT_FOR con1_ready";
+"List of binary logs after rotation"
+show binary logs;
+Log_name File_size
+master-bin.000001 #
+master-bin.000002 #
+master-bin.000003 #
+# Kill the server
+"Zero rows shoule be present in table"
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+0
+SELECT @@GLOBAL.gtid_current_pos;
+@@GLOBAL.gtid_current_pos
+0-1-1
+DROP TABLE t1;
+SELECT @@GLOBAL.gtid_binlog_state;
+@@GLOBAL.gtid_binlog_state
+0-1-2
diff --git a/mysql-test/suite/binlog/r/binlog_truncate_multi_log_unsafe.result b/mysql-test/suite/binlog/r/binlog_truncate_multi_log_unsafe.result
new file mode 100644
index 00000000000..5b7d45d81de
--- /dev/null
+++ b/mysql-test/suite/binlog/r/binlog_truncate_multi_log_unsafe.result
@@ -0,0 +1,30 @@
+SET @old_max_binlog_size= @@global.max_binlog_size;
+SET GLOBAL max_binlog_size= 4096;
+call mtr.add_suppression("Table '.*tm' is marked as crashed and should be repaired");
+call mtr.add_suppression("Got an error from unknown thread");
+call mtr.add_suppression("Checking table: '.*tm'");
+call mtr.add_suppression("Recovering table: '.*tm'");
+call mtr.add_suppression("tc-heuristic-recover cannot trim the binary log to");
+call mtr.add_suppression("Can't init tc log");
+call mtr.add_suppression("Aborting");
+RESET MASTER;
+CREATE TABLE ti (a INT PRIMARY KEY, b MEDIUMTEXT) ENGINE=Innodb;
+CREATE TABLE tm (f INT) ENGINE=MYISAM;
+SET DEBUG_SYNC= "commit_before_get_LOCK_commit_ordered SIGNAL con1_ready WAIT_FOR con1_go";
+INSERT INTO ti VALUES (2, REPEAT("x", 4100));
+SET DEBUG_SYNC= "now WAIT_FOR con1_ready";
+INSERT INTO tm VALUES (30);;
+# Kill the server
+FOUND /tc-heuristic-recover cannot trim the binary log to File/ in mysqld.1.err
+"Zero rows shoule be present in 'ti' table."
+SELECT COUNT(*) FROM ti;
+COUNT(*)
+0
+"One row must be present in 'tm' table."
+SELECT COUNT(*) FROM tm;
+COUNT(*)
+1
+SELECT @@GLOBAL.gtid_current_pos;
+@@GLOBAL.gtid_current_pos
+0-1-4
+DROP TABLE ti, tm;
diff --git a/mysql-test/suite/binlog/r/binlog_truncate_none.result b/mysql-test/suite/binlog/r/binlog_truncate_none.result
new file mode 100644
index 00000000000..bae87985208
--- /dev/null
+++ b/mysql-test/suite/binlog/r/binlog_truncate_none.result
@@ -0,0 +1,42 @@
+SET @old_max_binlog_size= @@global.max_binlog_size;
+SET GLOBAL max_binlog_size= 4096;
+call mtr.add_suppression("Can't init tc log");
+call mtr.add_suppression("Aborting");
+RESET MASTER;
+CREATE TABLE t1 (a INT PRIMARY KEY, b MEDIUMTEXT) ENGINE=Innodb;
+INSERT INTO t1 VALUES (1, REPEAT("x", 4100));
+CREATE TABLE t2 (a INT PRIMARY KEY, b MEDIUMTEXT) ENGINE=Innodb;
+show binary logs;
+Log_name File_size
+master-bin.000001 #
+master-bin.000002 #
+# Kill the server
+"Zero records should be there."
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+1
+show binary logs;
+Log_name File_size
+master-bin.000001 #
+master-bin.000002 #
+master-bin.000003 #
+master-bin.000004 #
+DROP TABLE t1,t2;
+call mtr.add_suppression("Can't init tc log");
+call mtr.add_suppression("Aborting");
+RESET MASTER;
+CREATE TABLE t1 (a INT PRIMARY KEY, b MEDIUMTEXT) ENGINE=Innodb;
+CREATE TABLE t2 (a INT PRIMARY KEY, b MEDIUMTEXT) ENGINE=Innodb;
+show binary logs;
+Log_name File_size
+master-bin.000001 #
+# Kill the server
+"Zero records should be there."
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+0
+SHOW TABLES;
+Tables_in_test
+t1
+t2
+DROP TABLE t1,t2;
diff --git a/mysql-test/suite/binlog/r/binlog_truncate_retry_success.result b/mysql-test/suite/binlog/r/binlog_truncate_retry_success.result
new file mode 100644
index 00000000000..cd0c4c23d05
--- /dev/null
+++ b/mysql-test/suite/binlog/r/binlog_truncate_retry_success.result
@@ -0,0 +1,28 @@
+call mtr.add_suppression("Can't init tc log");
+call mtr.add_suppression("Aborting");
+RESET MASTER;
+CREATE TABLE t1 (a INT PRIMARY KEY, b MEDIUMTEXT) ENGINE=Innodb;
+INSERT INTO t1 VALUES (1,'dummy');
+SET DEBUG_SYNC= "commit_after_release_LOCK_log SIGNAL con1_ready WAIT_FOR con1_go";
+INSERT INTO t1 VALUES (2,'dummy');
+SET DEBUG_SYNC= "now WAIT_FOR con1_ready";
+"List of binary logs"
+show binary logs;
+Log_name File_size
+master-bin.000001 #
+# Kill the server
+TEST 1: Recovery with error
+TEST 2: Recovery without error
+"One row shoule be present in table"
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+1
+"Two gtids should be present 0-1-2"
+SELECT @@GLOBAL.gtid_current_pos;
+@@GLOBAL.gtid_current_pos
+0-1-2
+"Two gtids should be present 0-1-3"
+DROP TABLE t1;
+SELECT @@GLOBAL.gtid_binlog_state;
+@@GLOBAL.gtid_binlog_state
+0-1-3
diff --git a/mysql-test/suite/binlog/t/binlog_heuristic_rollback_active_log.test b/mysql-test/suite/binlog/t/binlog_heuristic_rollback_active_log.test
new file mode 100644
index 00000000000..87af83a7a92
--- /dev/null
+++ b/mysql-test/suite/binlog/t/binlog_heuristic_rollback_active_log.test
@@ -0,0 +1,75 @@
+# ==== Purpose ====
+#
+# Test verifies the truncation of single binary log file.
+#
+# ==== Implementation ====
+#
+# Steps:
+# 0 - Create table t1 and insert a row.
+# 1 - Insert an another row such that it gets written to binlog but commit
+# in engine fails as server crashed at this point.
+# 2 - Restart server with --tc-heuristic-recover=ROLLBACK
+# 3 - Upon server start 'master-bin.000001' will be truncated to contain
+# only the first insert
+#
+# ==== References ====
+#
+# MDEV-21117: --tc-heuristic-recover=rollback is not replication safe
+
+
+--source include/have_innodb.inc
+--source include/have_log_bin.inc
+--source include/have_debug.inc
+--source include/have_binlog_format_statement.inc
+
+call mtr.add_suppression("Can't init tc log");
+call mtr.add_suppression("Aborting");
+
+connect(master,localhost,root,,);
+connect(master1,localhost,root,,);
+
+--connection master
+RESET MASTER;
+CREATE TABLE t ( f INT ) ENGINE=INNODB;
+INSERT INTO t VALUES (10);
+
+--connection master1
+# Hold insert after write to binlog and before "run_commit_ordered" in engine
+SET DEBUG_SYNC= "commit_before_update_end_pos SIGNAL con1_ready WAIT_FOR con1_go";
+send INSERT INTO t VALUES (20);
+
+--connection master
+SET DEBUG_SYNC= "now WAIT_FOR con1_ready";
+
+--write_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+wait
+EOF
+
+--source include/kill_mysqld.inc
+--source include/wait_until_disconnected.inc
+#
+# Server restart
+#
+--append_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+restart: --tc-heuristic-recover=ROLLBACK
+EOF
+--source include/wait_until_disconnected.inc
+
+--append_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+restart:
+EOF
+
+connection default;
+--enable_reconnect
+--source include/wait_until_connected_again.inc
+
+--connection master
+--enable_reconnect
+--source include/wait_until_connected_again.inc
+
+--echo "One row should be present in table 't'"
+SELECT COUNT(*) FROM t;
+
+--echo "gtid_binlog_state should be 0-1-2
+SELECT @@GLOBAL.gtid_binlog_state;
+DROP TABLE t;
diff --git a/mysql-test/suite/binlog/t/binlog_truncate_multi_log.test b/mysql-test/suite/binlog/t/binlog_truncate_multi_log.test
new file mode 100644
index 00000000000..0a6e011627e
--- /dev/null
+++ b/mysql-test/suite/binlog/t/binlog_truncate_multi_log.test
@@ -0,0 +1,87 @@
+# ==== Purpose ====
+#
+# Test verifies truncation of multiple binary logs.
+#
+# ==== Implementation ====
+#
+# Steps:
+# 0 - Create a table in innodb engine and execute FLUSH LOGS command to
+# generate a new binary log.
+# 1 - Set max_binlog_size= 4096. Insert a row such that the max_binlog_size
+# is reached and binary log gets rotated.
+# 2 - Using debug simulation make the server crash at a point where the DML
+# transaction is written to binary log but not committed in engine.
+# 3 - At the time of crash three binary logs will be there
+# master-bin.0000001, master-bin.000002 and master-bin.000003.
+# 4 - Restart server with --tc-heuristic-recover=ROLLBACK
+# 5 - Since the prepared DML in master-bin.000002 the binary log will be
+# truncated and master-bin.000003 will be removed.
+#
+# ==== References ====
+#
+# MDEV-21117: --tc-heuristic-recover=rollback is not replication safe
+
+
+--source include/have_innodb.inc
+--source include/have_log_bin.inc
+--source include/have_debug.inc
+--source include/have_debug_sync.inc
+--source include/have_binlog_format_row.inc
+
+SET @old_max_binlog_size= @@GLOBAL.max_binlog_size;
+SET GLOBAL max_binlog_size= 4096;
+call mtr.add_suppression("Can't init tc log");
+call mtr.add_suppression("Aborting");
+
+RESET MASTER;
+
+CREATE TABLE t1 (a INT PRIMARY KEY, b MEDIUMTEXT) ENGINE=Innodb;
+FLUSH LOGS;
+connect(master1,localhost,root,,);
+connect(master2,localhost,root,,);
+
+--connection master1
+# Hold insert after write to binlog and before "run_commit_ordered" in engine.
+# Use "commit_after_release_LOCK_log" sync point. This point is reached after
+# the binary log end position is updated which actually triggers binlog to be
+# rotated.
+--echo "List of binary logs before rotation"
+--source include/show_binary_logs.inc
+SET DEBUG_SYNC= "commit_after_release_LOCK_log SIGNAL con1_ready WAIT_FOR con1_go";
+send INSERT INTO t1 VALUES (2, REPEAT("x", 4100));
+
+--connection master2
+SET DEBUG_SYNC= "now WAIT_FOR con1_ready";
+--echo "List of binary logs after rotation"
+--source include/show_binary_logs.inc
+--write_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+wait
+EOF
+
+--source include/kill_mysqld.inc
+--source include/wait_until_disconnected.inc
+
+#
+# Server restart
+#
+--append_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+restart: --tc-heuristic-recover=ROLLBACK --debug-dbug=d,simulate_innodb_forget_commit_pos
+EOF
+--source include/wait_until_disconnected.inc
+
+--append_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+restart:
+EOF
+
+connection default;
+--enable_reconnect
+--source include/wait_until_connected_again.inc
+
+--echo "Zero rows shoule be present in table"
+SELECT COUNT(*) FROM t1;
+
+SELECT @@GLOBAL.gtid_current_pos;
+
+DROP TABLE t1;
+SELECT @@GLOBAL.gtid_binlog_state;
+
diff --git a/mysql-test/suite/binlog/t/binlog_truncate_multi_log_unsafe.test b/mysql-test/suite/binlog/t/binlog_truncate_multi_log_unsafe.test
new file mode 100644
index 00000000000..cf4f3d34a24
--- /dev/null
+++ b/mysql-test/suite/binlog/t/binlog_truncate_multi_log_unsafe.test
@@ -0,0 +1,106 @@
+# ==== Purpose ====
+#
+# Test verifies truncation of multiple binary logs will report an error on
+# unsafe scenario.
+#
+# ==== Implementation ====
+#
+# Steps:
+# 0 - Set max_binlog_size= 4096. Create a table 'ti' using transactional
+# storage engine. Do an insert such that the max_binlog_size is reached
+# and binary log gets rotated. Hold this thread at a position where
+# transaction is written to binary log but not committed in engine.
+# 1 - Create a table named 'tm' using non transactional storage engine.
+# 2 - Insert a row in 'tm' table. The DML will reach the engine and it is
+# also written to binary log.
+# 3 - Kill and restart the server with --tc-heuristic-recover=ROLLBACK
+# 4 - Check for binary log truncation unsafe message in error log.
+# 5 - No rows should be present in 'ti' table. One row should be present in
+# 'tm' table.
+#
+# ==== References ====
+#
+# MDEV-21117: --tc-heuristic-recover=rollback is not replication safe
+
+
+--source include/have_innodb.inc
+--source include/have_log_bin.inc
+--source include/have_debug.inc
+--source include/have_debug_sync.inc
+--source include/have_binlog_format_row.inc
+
+SET @old_max_binlog_size= @@global.max_binlog_size;
+SET GLOBAL max_binlog_size= 4096;
+
+call mtr.add_suppression("Table '.*tm' is marked as crashed and should be repaired");
+call mtr.add_suppression("Got an error from unknown thread");
+call mtr.add_suppression("Checking table: '.*tm'");
+call mtr.add_suppression("Recovering table: '.*tm'");
+call mtr.add_suppression("tc-heuristic-recover cannot trim the binary log to");
+call mtr.add_suppression("Can't init tc log");
+call mtr.add_suppression("Aborting");
+
+RESET MASTER;
+
+CREATE TABLE ti (a INT PRIMARY KEY, b MEDIUMTEXT) ENGINE=Innodb;
+CREATE TABLE tm (f INT) ENGINE=MYISAM;
+
+connect(master1,localhost,root,,);
+connect(master2,localhost,root,,);
+
+--connection master1
+# Hold insert after write to binlog and before "run_commit_ordered" in engine
+SET DEBUG_SYNC= "commit_before_get_LOCK_commit_ordered SIGNAL con1_ready WAIT_FOR con1_go";
+--send INSERT INTO ti VALUES (2, REPEAT("x", 4100))
+
+--connection master2
+SET DEBUG_SYNC= "now WAIT_FOR con1_ready";
+--send INSERT INTO tm VALUES (30);
+
+--connection default
+--write_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+wait
+EOF
+
+--source include/kill_mysqld.inc
+--source include/wait_until_disconnected.inc
+
+#
+# Server restart
+#
+--append_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+restart: --tc-heuristic-recover=ROLLBACK --debug-dbug=d,simulate_innodb_forget_commit_pos
+EOF
+--source include/wait_until_disconnected.inc
+
+--append_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+restart:
+EOF
+
+connection default;
+--enable_reconnect
+--source include/wait_until_connected_again.inc
+
+# Check error log for correct messages.
+let $log_error_= `SELECT @@GLOBAL.log_error`;
+if(!$log_error_)
+{
+ # MySQL Server on windows is started with --console and thus
+ # does not know the location of its .err log, use default location
+ let $log_error_ = $MYSQLTEST_VARDIR/log/mysqld.1.err;
+}
+--let SEARCH_FILE=$log_error_
+--let SEARCH_RANGE=-50000
+--let SEARCH_PATTERN=tc-heuristic-recover cannot trim the binary log to File
+--source include/search_pattern_in_file.inc
+
+--echo "Zero rows shoule be present in 'ti' table."
+SELECT COUNT(*) FROM ti;
+--echo "One row must be present in 'tm' table."
+--replace_regex /Table '.*tm/Table 'tm/
+--disable_warnings
+SELECT COUNT(*) FROM tm;
+--enable_warnings
+SELECT @@GLOBAL.gtid_current_pos;
+
+DROP TABLE ti, tm;
diff --git a/mysql-test/suite/binlog/t/binlog_truncate_none.test b/mysql-test/suite/binlog/t/binlog_truncate_none.test
new file mode 100644
index 00000000000..cc3fd4f0b37
--- /dev/null
+++ b/mysql-test/suite/binlog/t/binlog_truncate_none.test
@@ -0,0 +1,130 @@
+# ==== Purpose ====
+#
+# Test case verifies no binlog truncation happens when non transactional
+# events are found in binlog after the last committed transaction.
+#
+# ==== Implementation ====
+#
+# Steps:
+# 0 - Set max_binlog_size= 4096. Create a table and do an insert such that
+# the max_binlog_size is reached and binary log gets rotated.
+# 1 - Create a table in newly created binary log and crash the server
+# 2 - Restart server with --tc-heuristic-recover=ROLLBACK
+# 3 - Recovery code will get the last committed DML specific postion and
+# will try to check if binlog can be truncated upto this position.
+# Since a DDL is present beyond this no truncation will happen.
+# ==== References ====
+#
+# MDEV-21117: --tc-heuristic-recover=rollback is not replication safe
+
+
+--source include/have_innodb.inc
+--source include/have_log_bin.inc
+--source include/have_debug.inc
+--source include/have_binlog_format_row.inc
+
+SET @old_max_binlog_size= @@global.max_binlog_size;
+SET GLOBAL max_binlog_size= 4096;
+
+call mtr.add_suppression("Can't init tc log");
+call mtr.add_suppression("Aborting");
+
+RESET MASTER;
+
+CREATE TABLE t1 (a INT PRIMARY KEY, b MEDIUMTEXT) ENGINE=Innodb;
+INSERT INTO t1 VALUES (1, REPEAT("x", 4100));
+CREATE TABLE t2 (a INT PRIMARY KEY, b MEDIUMTEXT) ENGINE=Innodb;
+--source include/show_binary_logs.inc
+
+--write_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+wait
+EOF
+
+--source include/kill_mysqld.inc
+--source include/wait_until_disconnected.inc
+
+#
+# Server restart
+#
+--append_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+restart: --tc-heuristic-recover=ROLLBACK
+EOF
+--source include/wait_until_disconnected.inc
+
+--append_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+restart:
+EOF
+
+connection default;
+--enable_reconnect
+--source include/wait_until_connected_again.inc
+
+--echo "Zero records should be there."
+SELECT COUNT(*) FROM t1;
+--source include/show_binary_logs.inc
+DROP TABLE t1,t2;
+
+# ==== Purpose ====
+#
+# Test case verifies no binlog truncation happens when only DDLs are present in
+# the binary log. Since none of the DMLs are performed in storage engine,
+# Engine will not have last committed transaction file name or position.
+# Truncation code should return success.
+#
+# ==== Implementation ====
+#
+# Steps:
+# 0 - Create two table t1, t2
+# 1 - Kill and restart server with --tc-heuristic-recover=ROLLBACK
+# 2 - Only DDL statements are present in the binary log. Since
+# no DML was performed engine will not have last commited transaction
+# specific binary log name and position. Since no transactional events
+# are found, truncation code should simply return.
+#
+# ==== References ====
+#
+# MDEV-21117: --tc-heuristic-recover=rollback is not replication safe
+
+
+--source include/have_innodb.inc
+--source include/have_log_bin.inc
+--source include/have_debug.inc
+--source include/have_binlog_format_row.inc
+
+call mtr.add_suppression("Can't init tc log");
+call mtr.add_suppression("Aborting");
+
+RESET MASTER;
+
+CREATE TABLE t1 (a INT PRIMARY KEY, b MEDIUMTEXT) ENGINE=Innodb;
+CREATE TABLE t2 (a INT PRIMARY KEY, b MEDIUMTEXT) ENGINE=Innodb;
+--source include/show_binary_logs.inc
+
+--write_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+wait
+EOF
+
+--source include/kill_mysqld.inc
+--source include/wait_until_disconnected.inc
+
+#
+# Server restart
+#
+--append_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+restart: --tc-heuristic-recover=ROLLBACK --debug-dbug=d,simulate_innodb_forget_commit_pos
+EOF
+--source include/wait_until_disconnected.inc
+
+--append_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+restart:
+EOF
+
+connection default;
+--enable_reconnect
+--source include/wait_until_connected_again.inc
+
+--echo "Zero records should be there."
+SELECT COUNT(*) FROM t1;
+SHOW TABLES;
+DROP TABLE t1,t2;
+
diff --git a/mysql-test/suite/binlog/t/binlog_truncate_retry_success.test b/mysql-test/suite/binlog/t/binlog_truncate_retry_success.test
new file mode 100644
index 00000000000..6e92667c9b4
--- /dev/null
+++ b/mysql-test/suite/binlog/t/binlog_truncate_retry_success.test
@@ -0,0 +1,84 @@
+# ==== Purpose ====
+#
+# Test verifies that tc-heuristic-recover=ROLLBACK can be retried.
+#
+# ==== Implementation ====
+#
+# Steps:
+# 0 - Create a table in Innodb storage engine. Insert a row into the table.
+# During the commit of this DML engine will persist last committed
+# transaction specific binary log file name and position.
+# 1 - Do an another DML into the table, and simulate a crash in the middle
+# of DML commit, so that DML is present in binary log but not committed in
+# the storage engine.
+# 2 - Restart the server using --tc-heuristic-recover=ROLLBACK. Simulate an
+# error during the binary log rollback operation. Verify appropriate
+# error is reported in the error log.
+# 3 - Retry the --tc-heuristic-recver=ROLLBACK operation. Verify that
+# binary log gets truncated as expected.
+# 4 - Verify that global gtid state is according to the rolled back
+# transaction.
+# ==== References ====
+#
+# MDEV-21117: --tc-heuristic-recover=rollback is not replication safe
+
+
+--source include/have_innodb.inc
+--source include/not_embedded.inc
+--source include/have_log_bin.inc
+--source include/have_debug.inc
+--source include/have_debug_sync.inc
+--source include/have_binlog_format_row.inc
+
+call mtr.add_suppression("Can't init tc log");
+call mtr.add_suppression("Aborting");
+
+RESET MASTER;
+
+CREATE TABLE t1 (a INT PRIMARY KEY, b MEDIUMTEXT) ENGINE=Innodb;
+INSERT INTO t1 VALUES (1,'dummy');
+connect(master1,localhost,root,,);
+connect(master2,localhost,root,,);
+
+--connection master1
+SET DEBUG_SYNC= "commit_after_release_LOCK_log SIGNAL con1_ready WAIT_FOR con1_go";
+send INSERT INTO t1 VALUES (2,'dummy');
+
+--connection master2
+SET DEBUG_SYNC= "now WAIT_FOR con1_ready";
+--echo "List of binary logs"
+--source include/show_binary_logs.inc
+--write_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+wait
+EOF
+
+--source include/kill_mysqld.inc
+--source include/wait_until_disconnected.inc
+
+#
+# Server restart
+#
+--echo TEST 1: Recovery with error
+--error 1
+--exec $MYSQLD_LAST_CMD --tc-heuristic-recover=ROLLBACK --debug-dbug=d,fault_injection_opening_binlog > $MYSQLTEST_VARDIR/log/mysqld.1.err 2>&1
+--source include/wait_until_disconnected.inc
+
+--echo TEST 2: Recovery without error
+--error 1
+--exec $MYSQLD_LAST_CMD --tc-heuristic-recover=ROLLBACK > $MYSQLTEST_VARDIR/log/mysqld.1.err 2>&1
+--source include/wait_until_disconnected.inc
+
+--source include/start_mysqld.inc
+
+--echo "One row shoule be present in table"
+SELECT COUNT(*) FROM t1;
+
+--echo "Two gtids should be present 0-1-2"
+SELECT @@GLOBAL.gtid_current_pos;
+
+--echo "Two gtids should be present 0-1-3"
+DROP TABLE t1;
+SELECT @@GLOBAL.gtid_binlog_state;
+
+--disconnect master1
+--disconnect master2
diff --git a/mysql-test/suite/rpl/r/rpl_heuristic_fail_over.result b/mysql-test/suite/rpl/r/rpl_heuristic_fail_over.result
new file mode 100644
index 00000000000..02fc47def41
--- /dev/null
+++ b/mysql-test/suite/rpl/r/rpl_heuristic_fail_over.result
@@ -0,0 +1,53 @@
+include/rpl_init.inc [topology=1->2]
+include/stop_slave.inc
+set global rpl_semi_sync_slave_enabled = 1;
+CHANGE MASTER TO master_use_gtid= current_pos;
+include/start_slave.inc
+ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB;
+set global rpl_semi_sync_master_enabled = 1;
+set global rpl_semi_sync_master_wait_point=AFTER_SYNC;
+SET @old_max_binlog_size= @@global.max_binlog_size;
+SET GLOBAL max_binlog_size= 4096;
+call mtr.add_suppression("Can't init tc log");
+call mtr.add_suppression("Aborting");
+CREATE TABLE t1 (a INT PRIMARY KEY, b MEDIUMTEXT) ENGINE=Innodb;
+INSERT INTO t1 VALUES (1, 'dummy1');
+SET DEBUG_SYNC= "commit_before_update_end_pos SIGNAL con1_ready WAIT_FOR con1_go";
+INSERT INTO t1 VALUES (2, REPEAT("x", 4100));
+SET DEBUG_SYNC= "now WAIT_FOR con1_ready";
+# Kill the server
+include/stop_slave.inc
+SELECT @@GLOBAL.gtid_current_pos;
+@@GLOBAL.gtid_current_pos
+0-1-5
+FOUND /tc-heuristic-recover: Truncated binlog File: \.\/master\-bin\.000001 of Size:[0-9]*, to Position */ in mysqld.1.err
+set global rpl_semi_sync_master_enabled = 1;
+set global rpl_semi_sync_master_wait_point=AFTER_SYNC;
+CHANGE MASTER TO master_host='127.0.0.1', master_port=SERVER_MYPORT_2, master_user='root', master_use_gtid=CURRENT_POS;
+set global rpl_semi_sync_slave_enabled = 1;
+include/start_slave.inc
+INSERT INTO t1 VALUES (3, 'dummy3');
+SELECT COUNT(*) FROM t1;
+COUNT(*)
+2
+SHOW VARIABLES LIKE 'gtid_current_pos';
+Variable_name Value
+gtid_current_pos 0-2-6
+SHOW VARIABLES LIKE 'gtid_current_pos';
+Variable_name Value
+gtid_current_pos 0-2-6
+DROP TABLE t1;
+set global rpl_semi_sync_master_enabled = 0;
+set global rpl_semi_sync_slave_enabled = 0;
+set global rpl_semi_sync_master_wait_point=default;
+set global rpl_semi_sync_master_enabled = 0;
+set global rpl_semi_sync_slave_enabled = 0;
+set global rpl_semi_sync_master_wait_point=default;
+include/stop_slave.inc
+RESET MASTER;
+RESET SLAVE;
+RESET MASTER;
+RESET SLAVE;
+CHANGE MASTER TO master_host='127.0.0.1', master_port=SERVER_MYPORT_1, master_user='root', master_use_gtid=no;
+include/start_slave.inc
+include/rpl_end.inc
diff --git a/mysql-test/suite/rpl/t/rpl_heuristic_fail_over.test b/mysql-test/suite/rpl/t/rpl_heuristic_fail_over.test
new file mode 100644
index 00000000000..39b1f7545d0
--- /dev/null
+++ b/mysql-test/suite/rpl/t/rpl_heuristic_fail_over.test
@@ -0,0 +1,160 @@
+# ==== Purpose ====
+#
+# Test verifies replication failover scenario.
+#
+# ==== Implementation ====
+#
+# Steps:
+# 0 - Have two servers with id's 1 and 2. Enable semi-sync based
+# replication. Have semi sync master wait point as 'after_sync'.
+# 1 - Create a table and insert a row. While inserting second row simulate
+# a server crash at once the transaction is written to binlog, flushed
+# and synced but the binlog position is not updated.
+# 2 - Restart the server using tc-heuristic-recover=ROLLBACK
+# 3 - Post restart switch the crashed master to slave. Execute CHANGE MASTER
+# TO command to connect to server id 2.
+# 4 - Slave should be able to connect to master.
+# 5 - Execute some DML on new master with server id 2. Ensure that it gets
+# replicated to server id 1.
+# 6 - Verify the "gtid_current_pos" for correctness.
+# 7 - Clean up
+#
+# ==== References ====
+#
+# MDEV-21117: --tc-heuristic-recover=rollback is not replication safe
+
+
+--source include/have_semisync.inc
+--source include/have_innodb.inc
+--source include/have_log_bin.inc
+--source include/have_debug.inc
+--source include/have_debug_sync.inc
+--source include/have_binlog_format_row.inc
+--let $rpl_topology=1->2
+--source include/rpl_init.inc
+
+--connection server_2
+--source include/stop_slave.inc
+set global rpl_semi_sync_slave_enabled = 1;
+CHANGE MASTER TO master_use_gtid= current_pos;
+--source include/start_slave.inc
+
+
+--connection server_1
+ALTER TABLE mysql.gtid_slave_pos ENGINE=InnoDB;
+set global rpl_semi_sync_master_enabled = 1;
+set global rpl_semi_sync_master_wait_point=AFTER_SYNC;
+SET @old_max_binlog_size= @@global.max_binlog_size;
+SET GLOBAL max_binlog_size= 4096;
+
+call mtr.add_suppression("Can't init tc log");
+call mtr.add_suppression("Aborting");
+
+CREATE TABLE t1 (a INT PRIMARY KEY, b MEDIUMTEXT) ENGINE=Innodb;
+INSERT INTO t1 VALUES (1, 'dummy1');
+--save_master_pos
+
+--connection server_2
+--sync_with_master
+
+--connection server_1
+connect(master1,localhost,root,,);
+connect(master2,localhost,root,,);
+
+--connection master1
+# Hold insert after write to binlog and before "run_commit_ordered" in engine
+SET DEBUG_SYNC= "commit_before_update_end_pos SIGNAL con1_ready WAIT_FOR con1_go";
+send INSERT INTO t1 VALUES (2, REPEAT("x", 4100));
+
+--connection master2
+SET DEBUG_SYNC= "now WAIT_FOR con1_ready";
+--write_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+wait
+EOF
+
+--source include/kill_mysqld.inc
+--source include/wait_until_disconnected.inc
+
+--connection server_2
+--error 2003
+--source include/stop_slave.inc
+SELECT @@GLOBAL.gtid_current_pos;
+
+--connection server_1
+#
+# Server restart
+#
+--append_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+restart: --tc-heuristic-recover=ROLLBACK
+EOF
+--source include/wait_until_disconnected.inc
+
+--append_file $MYSQLTEST_VARDIR/tmp/mysqld.1.expect
+restart:
+EOF
+
+connection default;
+--enable_reconnect
+--source include/wait_until_connected_again.inc
+
+--connection server_1
+--enable_reconnect
+--source include/wait_until_connected_again.inc
+
+# Check error log for correct messages.
+let $log_error_= `SELECT @@GLOBAL.log_error`;
+if(!$log_error_)
+{
+ # MySQL Server on windows is started with --console and thus
+ # does not know the location of its .err log, use default location
+ let $log_error_ = $MYSQLTEST_VARDIR/log/mysqld.1.err;
+}
+--let SEARCH_FILE=$log_error_
+--let SEARCH_RANGE=-50000
+--let SEARCH_PATTERN=tc-heuristic-recover: Truncated binlog File: \.\/master\-bin\.000001 of Size:[0-9]*, to Position *
+--source include/search_pattern_in_file.inc
+
+--connection server_2
+set global rpl_semi_sync_master_enabled = 1;
+set global rpl_semi_sync_master_wait_point=AFTER_SYNC;
+
+--connection server_1
+--replace_result $SERVER_MYPORT_2 SERVER_MYPORT_2
+eval CHANGE MASTER TO master_host='127.0.0.1', master_port=$SERVER_MYPORT_2, master_user='root', master_use_gtid=CURRENT_POS;
+set global rpl_semi_sync_slave_enabled = 1;
+--source include/start_slave.inc
+
+--connection server_2
+INSERT INTO t1 VALUES (3, 'dummy3');
+--save_master_pos
+
+--connection server_1
+--sync_with_master
+SELECT COUNT(*) FROM t1;
+SHOW VARIABLES LIKE 'gtid_current_pos';
+
+--connection server_2
+SHOW VARIABLES LIKE 'gtid_current_pos';
+DROP TABLE t1;
+set global rpl_semi_sync_master_enabled = 0;
+set global rpl_semi_sync_slave_enabled = 0;
+set global rpl_semi_sync_master_wait_point=default;
+--save_master_pos
+
+--connection server_1
+--sync_with_master
+set global rpl_semi_sync_master_enabled = 0;
+set global rpl_semi_sync_slave_enabled = 0;
+set global rpl_semi_sync_master_wait_point=default;
+--source include/stop_slave.inc
+RESET MASTER;
+RESET SLAVE;
+
+--connection server_2
+RESET MASTER;
+RESET SLAVE;
+--replace_result $SERVER_MYPORT_1 SERVER_MYPORT_1
+eval CHANGE MASTER TO master_host='127.0.0.1', master_port=$SERVER_MYPORT_1, master_user='root', master_use_gtid=no;
+--source include/start_slave.inc
+
+--source include/rpl_end.inc
diff --git a/sql/log.cc b/sql/log.cc
index 0efef6d1e29..66445944b40 100644
--- a/sql/log.cc
+++ b/sql/log.cc
@@ -3164,6 +3164,7 @@ MYSQL_BIN_LOG::MYSQL_BIN_LOG(uint *sync_period)
checksum_alg_reset(BINLOG_CHECKSUM_ALG_UNDEF),
relay_log_checksum_alg(BINLOG_CHECKSUM_ALG_UNDEF),
description_event_for_exec(0), description_event_for_queue(0),
+ last_commit_pos_offset(0),
current_binlog_id(0)
{
/*
@@ -3173,6 +3174,7 @@ MYSQL_BIN_LOG::MYSQL_BIN_LOG(uint *sync_period)
before main().
*/
index_file_name[0] = 0;
+ last_commit_pos_file[0]= 0;
bzero((char*) &index_file, sizeof(index_file));
bzero((char*) &purge_index_file, sizeof(purge_index_file));
}
@@ -4576,8 +4578,8 @@ int MYSQL_BIN_LOG::open_purge_index_file(bool destroy)
0, 0, MYF(MY_WME | MY_NABP | MY_WAIT_IF_FULL)))
{
error= 1;
- sql_print_error("MYSQL_BIN_LOG::open_purge_index_file failed to open register "
- " file.");
+ sql_print_error("MYSQL_BIN_LOG::open_purge_index_file failed to open "
+ "register file.");
}
}
DBUG_RETURN(error);
@@ -4742,7 +4744,7 @@ int MYSQL_BIN_LOG::purge_index_entry(THD *thd, ulonglong *reclaimed_space,
}
goto err;
}
-
+
error= 0;
DBUG_PRINT("info",("purging %s",log_info.log_file_name));
@@ -7877,6 +7879,7 @@ MYSQL_BIN_LOG::trx_group_commit_leader(group_commit_entry *leader)
first= false;
}
+ DEBUG_SYNC(leader->thd, "commit_before_update_end_pos");
/* update binlog_end_pos so it can be read by dump thread
*
* note: must be _after_ the RUN_HOOK(after_flush) or else
@@ -8964,7 +8967,7 @@ int TC_LOG_MMAP::open(const char *opt_name)
{
if (my_errno != ENOENT)
goto err;
- if (using_heuristic_recover())
+ if (using_heuristic_recover(opt_name))
return 1;
if ((fd= mysql_file_create(key_file_tclog, logname, CREATE_MODE,
O_RDWR | O_CLOEXEC, MYF(MY_WME))) < 0)
@@ -9497,14 +9500,40 @@ TC_LOG_MMAP tc_log_mmap;
1 heuristic recovery was performed
*/
-int TC_LOG::using_heuristic_recover()
+int TC_LOG::using_heuristic_recover(const char* opt_name)
{
+ LOG_INFO log_info;
+ int error;
+
if (!tc_heuristic_recover)
return 0;
sql_print_information("Heuristic crash recovery mode");
+
if (ha_recover(0))
+ {
sql_print_error("Heuristic crash recovery failed");
+ }
+
+ if (!strcmp(opt_name, opt_bin_logname) &&
+ tc_heuristic_recover == TC_HEURISTIC_RECOVER_ROLLBACK)
+ {
+ if ((error= mysql_bin_log.find_log_pos(&log_info, NullS, 1)))
+ {
+ if (error != LOG_INFO_EOF)
+ sql_print_error("tc-heuristic-recover failed in find_log_pos(). "
+ "Error: %d", error);
+ }
+ else
+ {
+ if ((error= heuristic_binlog_rollback()))
+ {
+ sql_print_error("Heuristic crash recovery of binary log failed.");
+ return 1;
+ }
+ }
+ }
+
sql_print_information("Please restart mysqld without --tc-heuristic-recover");
return 1;
}
@@ -9512,6 +9541,554 @@ int TC_LOG::using_heuristic_recover()
/****** transaction coordinator log for 2pc - binlog() based solution ******/
#define TC_LOG_BINLOG MYSQL_BIN_LOG
+/**
+ Truncates the current binlog to specified position. Removes the rest of binlogs
+ which are present after this binlog file.
+
+ @param truncate_file Holds the binlog name to be truncated
+ @param truncate_pos Position within binlog from where it needs to
+ truncated.
+
+ @retval true ok
+ @retval false error
+
+*/
+bool MYSQL_BIN_LOG::truncate_and_remove_binlogs(const char *truncate_file,
+ my_off_t truncate_pos)
+{
+ int error= 0;
+#ifdef HAVE_REPLICATION
+ LOG_INFO log_info;
+ THD *thd= current_thd;
+ my_off_t index_file_offset= 0;
+ File file= -1;
+ IO_CACHE cache;
+ MY_STAT s;
+ my_off_t binlog_size;
+
+ if ((error= find_log_pos(&log_info, truncate_file, 1)))
+ {
+ sql_print_error("tc-heuristic-recover: Failed to locate binary log file:%s."
+ "Error:%d", truncate_file, error);
+ goto end;
+ }
+
+ while (!(error= find_next_log(&log_info, 1)))
+ {
+ if (!index_file_offset)
+ {
+ index_file_offset= log_info.index_file_start_offset;
+ if ((error= open_purge_index_file(TRUE)))
+ {
+ sql_print_error("tc-heuristic-recover: Failed to open purge index "
+ "file:%s. Error:%d", purge_index_file_name, error);
+ goto end;
+ }
+ }
+ if ((error= register_purge_index_entry(log_info.log_file_name)))
+ {
+ sql_print_error("tc-heuristic-recover: Failed to copy %s to purge index"
+ " file. Error:%d", log_info.log_file_name, error);
+ goto end;
+ }
+ }
+
+ if (error != LOG_INFO_EOF)
+ {
+ sql_print_error("tc-heuristic-recover: Failed to find the next binlog to "
+ "add to purge index register. Error:%d", error);
+ goto end;
+ }
+
+ if (is_inited_purge_index_file())
+ {
+ if (!index_file_offset)
+ index_file_offset= log_info.index_file_start_offset;
+
+ if ((error= sync_purge_index_file()))
+ {
+ sql_print_error("tc-heuristic-recover: Failed to flush purge index "
+ "file. Error:%d", error);
+ goto end;
+ }
+
+ // Trim index file
+ if ((error=
+ mysql_file_chsize(index_file.file, index_file_offset, '\n',
+ MYF(MY_WME))) ||
+ (error=
+ mysql_file_sync(index_file.file, MYF(MY_WME|MY_SYNC_FILESIZE))))
+ {
+ sql_print_error("tc-heuristic-recover: Failed to trim binlog index "
+ "file:%s to offset:%llu. Error:%d", index_file_name,
+ index_file_offset);
+ mysql_file_close(index_file.file, MYF(MY_WME));
+ goto end;
+ }
+
+ /* Reset data in old index cache */
+ if ((error= reinit_io_cache(&index_file, READ_CACHE, (my_off_t) 0, 0, 1)))
+ {
+ sql_print_error("tc-heuristic-recover: Failed to reinit binlog index "
+ "file. Error:%d", error);
+ mysql_file_close(index_file.file, MYF(MY_WME));
+ goto end;
+ }
+
+ /* Read each entry from purge_index_file and delete the file. */
+ if ((error= purge_index_entry(thd, NULL, TRUE)))
+ {
+ sql_print_error("tc-heuristic-recover: Failed to process registered "
+ "files that would be purged.");
+ goto end;
+ }
+ }
+
+ DBUG_ASSERT(truncate_pos);
+
+ if ((file= mysql_file_open(key_file_binlog, truncate_file,
+ O_RDWR | O_BINARY, MYF(MY_WME))) < 0)
+ {
+ error= 1;
+ sql_print_error("tc-heuristic-recover: Failed to open binlog file:%s for "
+ "truncation.", truncate_file);
+ goto end;
+ }
+ my_stat(truncate_file, &s, MYF(0));
+ binlog_size= s.st_size;
+
+ /* Change binlog file size to truncate_pos */
+ if ((error=
+ mysql_file_chsize(file, truncate_pos, 0, MYF(MY_WME))) ||
+ (error= mysql_file_sync(file, MYF(MY_WME|MY_SYNC_FILESIZE))))
+ {
+ sql_print_error("tc-heuristic-recover: Failed to trim the "
+ "binlog file:%s to size:%llu. Error:%d",
+ truncate_file, truncate_pos, error);
+ goto end;
+ }
+ else
+ {
+ sql_print_information("tc-heuristic-recover: Truncated binlog "
+ "File: %s of Size:%llu, to Position:%llu.",
+ truncate_file, binlog_size, truncate_pos);
+ }
+ if (!(error= init_io_cache(&cache, file, IO_SIZE, WRITE_CACHE,
+ (my_off_t) truncate_pos, 0, MYF(MY_WME|MY_NABP))))
+ {
+ /*
+ Write Stop_log_event to ensure clean end point for the binary log being
+ truncated.
+ */
+ Stop_log_event se;
+ se.checksum_alg= (enum_binlog_checksum_alg) binlog_checksum_options;
+ if ((error= write_event(&se, &cache)))
+ {
+ sql_print_error("tc-heuristic-recover: Failed to write stop event to "
+ "binary log. Errno:%d",
+ (cache.error == -1) ? my_errno : error);
+ goto end;
+ }
+ if ((error= flush_io_cache(&cache)) ||
+ (error= mysql_file_sync(file, MYF(MY_WME|MY_SYNC_FILESIZE))))
+ {
+ sql_print_error("tc-heuristic-recover: Faild to write stop event to "
+ "binary log. Errno:%d",
+ (cache.error == -1) ? my_errno : error);
+ }
+ }
+ else
+ sql_print_error("tc-heuristic-recover: Failed to initialize binary log "
+ "cache for writing stop event. Errno:%d",
+ (cache.error == -1) ? my_errno : error);
+
+end:
+ if (file >= 0)
+ {
+ end_io_cache(&cache);
+ mysql_file_close(file, MYF(MY_WME));
+ }
+ error= error || close_purge_index_file();
+#endif
+ return error > 0;
+}
+
+/**
+ Returns the checkpoint binlog file name found in the lastest binlog file.
+
+ @param checkpoint_file Holds the binlog checkpoint file name.
+
+ @retval 0 ok
+ @retval 1 error
+
+*/
+int TC_LOG_BINLOG::get_binlog_checkpoint_file(char* checkpoint_file)
+{
+ Log_event *ev= NULL;
+ bool binlog_checkpoint_found= false;
+ LOG_INFO log_info;
+ const char *errmsg;
+ IO_CACHE log;
+ File file;
+ Format_description_log_event fdle(BINLOG_VERSION);
+ char log_name[FN_REFLEN];
+ int error=1;
+
+ if (!fdle.is_valid())
+ return 1;
+
+ if ((error= find_log_pos(&log_info, NullS, 1)))
+ {
+ sql_print_error("tc-heuristic-recover: find_log_pos() failed to read first "
+ "binary log entry from index file.(error: %d)", error);
+ return error;
+ }
+
+ // Move to the last binary log.
+ do
+ {
+ strmake_buf(log_name, log_info.log_file_name);
+ } while (!(error= find_next_log(&log_info, 1)));
+
+ if (error != LOG_INFO_EOF)
+ {
+ sql_print_error("tc-heuristic-recover: find_next_log() failed "
+ "(error: %d)", error);
+ return error;
+ }
+ if ((file= open_binlog(&log, log_name, &errmsg)) < 0)
+ {
+ sql_print_error("tc-heuristic-recover failed to open the binlog:%s for "
+ "reading checkpoint file name. Error: %s",
+ log_info.log_file_name, errmsg);
+ return error;
+ }
+ while (!binlog_checkpoint_found &&
+ (ev=
+ Log_event::read_log_event(&log, 0, &fdle, opt_master_verify_checksum))
+ && ev->is_valid())
+ {
+ enum Log_event_type typ= ev->get_type_code();
+ if (typ == BINLOG_CHECKPOINT_EVENT)
+ {
+ size_t dir_len;
+ Binlog_checkpoint_log_event *cev= (Binlog_checkpoint_log_event *)ev;
+ if (cev->binlog_file_len >= FN_REFLEN)
+ {
+ sql_print_error("tc-heuristic-recover: Incorrect binlog checkpoint "
+ "event with too long file name found.");
+ delete ev;
+ ev= NULL;
+ end_io_cache(&log);
+ mysql_file_close(file, MYF(MY_WME));
+ return 1;
+ }
+ else
+ {
+ dir_len= dirname_length(log_name);
+ strmake(strnmov(checkpoint_file, log_name, dir_len),
+ cev->binlog_file_name, FN_REFLEN - 1 - dir_len);
+ binlog_checkpoint_found= true;
+ }
+ }
+ delete ev;
+ ev= NULL;
+ } // End of while
+ end_io_cache(&log);
+ mysql_file_close(file, MYF(MY_WME));
+ file= -1;
+ /*
+ Old binary log without checkpoint found, binlog truncation is not
+ possible. Hence return error.
+ */
+ if (!binlog_checkpoint_found)
+ return 1;
+
+ return 0;
+}
+
+
+/**
+ Truncates the binary log, according to the transactions that got rolled
+ back from engine, during heuristic-recover=ROLLBACK. Global GTID state is
+ adjusted as per the truncated binlog.
+
+ Called from @c TC_LOG::using_heuristic_recover(const char* opt_name)
+
+ @param opt_name The base name of binary log.
+
+ @return indicates success or failure of binlog rollback
+ @retval 0 success
+ @retval 1 failure
+
+*/
+int TC_LOG_BINLOG::heuristic_binlog_rollback()
+{
+ int error=0;
+#ifdef HAVE_REPLICATION
+ Log_event *ev= NULL;
+ char engine_commit_file[FN_REFLEN];
+ char binlog_truncate_file_name[FN_REFLEN];
+ char checkpoint_file[FN_REFLEN];
+ my_off_t binlog_truncate_pos= 0;
+ my_off_t engine_commit_pos= 0;
+ LOG_INFO log_info;
+ const char *errmsg;
+ IO_CACHE log;
+ File file=-1;
+ Format_description_log_event fdle(BINLOG_VERSION);
+ bool found_engine_commit_pos= false;
+ bool found_truncate_pos= false;
+ bool is_safe= true;
+ my_off_t tmp_truncate_pos=0;
+ rpl_gtid last_gtid;
+ bool last_gtid_standalone= false;
+ bool last_gtid_valid= false;
+ bool is_checkpoint_based_recovery= false;
+
+
+ DBUG_EXECUTE_IF("simulate_innodb_forget_commit_pos",
+ {
+ last_commit_pos_file[0]= 0;
+ };);
+
+ // Initialize engine_commit_file/pos
+ if (last_commit_pos_file[0] != 0)
+ {
+ strmake_buf(engine_commit_file, last_commit_pos_file);
+ engine_commit_pos= last_commit_pos_offset;
+ sql_print_information("tc-heuristic-recover: Initialising heuristic "
+ "rollback of binary log using last committed "
+ "transaction specific binary log name:%s and "
+ "position:%llu", last_commit_pos_file,
+ last_commit_pos_offset);
+ }
+ else
+ {
+ if ((error= get_binlog_checkpoint_file(checkpoint_file)))
+ {
+ is_checkpoint_based_recovery= true;
+ sql_print_error("tc-heuristic-recover: Failed to read latest checkpoint "
+ "binary log name.");
+ goto end;
+ }
+ strmake_buf(engine_commit_file, checkpoint_file);
+ sql_print_information("tc-heuristic-recover: Initialising heuristic "
+ "rollback of binary log using last checkpoint "
+ "file:%s.", engine_commit_file);
+ /*
+ If there is no engine specific commit file we are doing checkpoint file
+ based recovery. Hence we mark "found_engine_commit_pos" true, and start
+ looking for the first transactional event group with is the candidate for
+ rollback.
+ */
+ found_engine_commit_pos= true;
+ }
+
+ if ((error= find_log_pos(&log_info, engine_commit_file, 1)))
+ {
+ sql_print_error("tc-heuristic-recover: Failed to locate binary log file:%s "
+ "in index file. Error:%d", engine_commit_file, error);
+ goto end;
+ }
+ if ((file= open_binlog(&log, log_info.log_file_name, &errmsg)) < 0 ||
+ DBUG_EVALUATE_IF("fault_injection_opening_binlog", (errmsg="Unknown"),
+ FALSE))
+ {
+ error= 1;
+ sql_print_error("tc-heuristic-recover: Failed to open the binlog:%s for "
+ "recovery. Error:%s", log_info.log_file_name, errmsg);
+ goto end;
+ }
+
+
+ error= read_state_from_file();
+ if (error && error != 2)
+ {
+ sql_print_error("tc-heuristic-recover: Failed to load global gtid binlog "
+ "state from file");
+ goto end;
+ }
+ if (!fdle.is_valid())
+ {
+ error= 1;
+ sql_print_error("tc-heuristic-recover: Failed due to invalid format "
+ "description log event.");
+ goto end;
+ }
+
+ for(;;)
+ {
+ while (is_safe &&
+ (ev= Log_event::read_log_event(&log, 0, &fdle,
+ opt_master_verify_checksum)) && ev->is_valid())
+ {
+ enum Log_event_type typ= ev->get_type_code();
+ switch (typ)
+ {
+ case XID_EVENT:
+ if (ev->log_pos == engine_commit_pos)
+ {
+ found_engine_commit_pos= true;
+ }
+ break;
+ case GTID_LIST_EVENT:
+ {
+ Gtid_list_log_event *glev= (Gtid_list_log_event *)ev;
+ /* Initialise the binlog state from the Gtid_list event. */
+ if (!found_truncate_pos && glev->count > 0 &&
+ rpl_global_gtid_binlog_state.load(glev->list, glev->count))
+ {
+ error= 1;
+ sql_print_error("tc-heuristic-recover: Failed to read GTID List "
+ "event.");
+ goto end;
+ }
+ }
+ break;
+ case GTID_EVENT:
+ {
+ Gtid_log_event *gev= (Gtid_log_event *)ev;
+
+ /* Update the binlog state with any GTID logged after Gtid_list. */
+ last_gtid.domain_id= gev->domain_id;
+ last_gtid.server_id= gev->server_id;
+ last_gtid.seq_no= gev->seq_no;
+ last_gtid_standalone=
+ ((gev->flags2 & Gtid_log_event::FL_STANDALONE) ? true : false);
+ last_gtid_valid= true;
+ if (gev->flags2 & Gtid_log_event::FL_TRANSACTIONAL &&
+ !found_truncate_pos)
+ {
+ if ((engine_commit_pos == 0 || found_engine_commit_pos))
+ {
+ found_truncate_pos= true;
+ strmake_buf(binlog_truncate_file_name, log_info.log_file_name);
+ binlog_truncate_pos= tmp_truncate_pos;
+ }
+ }
+ else
+ {
+ if (found_truncate_pos)
+ is_safe= false;
+ }
+ }
+ break;
+ default:
+ /* Nothing. */
+ break;
+ }// End switch
+ if (!found_truncate_pos && last_gtid_valid &&
+ ((last_gtid_standalone && !ev->is_part_of_group(typ)) ||
+ (!last_gtid_standalone &&
+ (typ == XID_EVENT ||
+ (typ == QUERY_EVENT &&
+ (((Query_log_event *)ev)->is_commit() ||
+ ((Query_log_event *)ev)->is_rollback()))))))
+ {
+ if ((error= rpl_global_gtid_binlog_state.update_nolock(&last_gtid,
+ false)))
+ {
+ sql_print_error("tc-heuristic-recover: Failed to update GTID within "
+ "global gtid state.");
+ goto end;
+ }
+ last_gtid_valid= false;
+ }
+ // Used to identify the last group specific end position.
+ tmp_truncate_pos= ev->log_pos;
+ delete ev;
+ ev= NULL;
+ }// End While
+ if (file >= 0)
+ {
+ end_io_cache(&log);
+ mysql_file_close(file, MYF(MY_WME));
+ file= -1;
+ }
+ if (is_safe)
+ {
+ if ((error= find_next_log(&log_info, 1)))
+ {
+ if (error != LOG_INFO_EOF)
+ {
+ sql_print_error("tc-heuristic-recover: Failed to read next binary "
+ "log during recovery.");
+ goto end;
+ }
+ else
+ {
+ error= 0; // LOG_INFO_EOF= -1 is not an error.
+ break;
+ }
+ }
+ if ((file= open_binlog(&log, log_info.log_file_name, &errmsg)) < 0)
+ {
+ error= 1;
+ sql_print_error("tc-heuristic-recover: Failed to open the binlog:%s for "
+ "recovery. Error:%s", log_info.log_file_name, errmsg);
+ goto end;
+ }
+ }
+ else
+ break;
+ } //end of for(;;)
+ sql_print_information("tc-heuristic-recover: Binary log to be truncated "
+ "File:%s Pos:%llu.", binlog_truncate_file_name,
+ binlog_truncate_pos);
+ if (!found_truncate_pos)
+ goto end; // Nothing to truncate
+ else
+ DBUG_ASSERT(binlog_truncate_pos > 0);
+
+ if (is_safe)
+ {
+ if ((error= truncate_and_remove_binlogs(binlog_truncate_file_name,
+ binlog_truncate_pos)))
+ {
+ sql_print_error("tc-heuristic-recover: Failed to trim the binary log to "
+ "File:%s Pos:%llu.", binlog_truncate_file_name,
+ binlog_truncate_pos);
+ goto end;
+ }
+ }
+ else
+ {
+ sql_print_error("tc-heuristic-recover cannot trim the binary log to "
+ "File:%s Pos:%llu as unsafe statements (non-trans/DDL) "
+ "statements are found beyond the truncation position.",
+ binlog_truncate_file_name, binlog_truncate_pos);
+ }
+ if ((error= write_state_to_file()))
+ {
+ sql_print_error("tc-heuristic-recover: Failed to write global gtid state "
+ "to file");
+ goto end;
+ }
+
+end:
+ if (file >= 0)
+ {
+ end_io_cache(&log);
+ mysql_file_close(file, MYF(MY_WME));
+ }
+ if (error)
+ {
+ if (is_checkpoint_based_recovery)
+ {
+ sql_print_error("tc-heuristic-recover failed during binary log rollback."
+ "Further retry attemps won't succeed.");
+ }
+ else
+ sql_print_error("tc-heuristic-recover failed during binary log rollback. "
+ "After removing the source of the failure "
+ "--tc-heuristic-recover=rollback may be retried.");
+ }
+
+#endif
+ return error;
+}
+
int TC_LOG_BINLOG::open(const char *opt_name)
{
int error= 1;
@@ -9526,7 +10103,7 @@ int TC_LOG_BINLOG::open(const char *opt_name)
return 1;
}
- if (using_heuristic_recover())
+ if (using_heuristic_recover(opt_name))
{
mysql_mutex_lock(&LOCK_log);
/* generate a new binlog to mask a corrupted one */
diff --git a/sql/log.h b/sql/log.h
index 277e5c6f69c..84c0a457b52 100644
--- a/sql/log.h
+++ b/sql/log.h
@@ -41,7 +41,8 @@ bool stmt_has_updated_non_trans_table(const THD* thd);
class TC_LOG
{
public:
- int using_heuristic_recover();
+ int using_heuristic_recover(const char* opt_name);
+ virtual int heuristic_binlog_rollback() { return 0; };
TC_LOG() {}
virtual ~TC_LOG() {}
@@ -694,6 +695,7 @@ class MYSQL_BIN_LOG: public TC_LOG, private MYSQL_LOG
void commit_checkpoint_notify(void *cookie);
int recover(LOG_INFO *linfo, const char *last_log_name, IO_CACHE *first_log,
Format_description_log_event *fdle, bool do_xa);
+ int heuristic_binlog_rollback();
int do_binlog_recovery(const char *opt_name, bool do_xa_recovery);
#if !defined(MYSQL_CLIENT)
@@ -794,6 +796,9 @@ class MYSQL_BIN_LOG: public TC_LOG, private MYSQL_LOG
int purge_first_log(Relay_log_info* rli, bool included);
int set_purge_index_file_name(const char *base_file_name);
int open_purge_index_file(bool destroy);
+ bool truncate_and_remove_binlogs(const char *truncate_file,
+ my_off_t truncate_pos);
+ int get_binlog_checkpoint_file(char* checkpoint_file);
bool is_inited_purge_index_file();
int close_purge_index_file();
int clean_purge_index_file();
diff --git a/sql/mysqld.h b/sql/mysqld.h
index e939524dbff..515d5f39c3d 100644
--- a/sql/mysqld.h
+++ b/sql/mysqld.h
@@ -94,6 +94,7 @@ extern "C" MYSQL_PLUGIN_IMPORT CHARSET_INFO *system_charset_info;
extern MYSQL_PLUGIN_IMPORT CHARSET_INFO *files_charset_info ;
extern MYSQL_PLUGIN_IMPORT CHARSET_INFO *national_charset_info;
extern MYSQL_PLUGIN_IMPORT CHARSET_INFO *table_alias_charset;
+extern MYSQL_PLUGIN_IMPORT bool opt_bin_log;
/**
Character set of the buildin error messages loaded from errmsg.sys.
@@ -104,7 +105,7 @@ extern CHARSET_INFO *character_set_filesystem;
extern MY_BITMAP temp_pool;
extern bool opt_large_files, server_id_supplied;
-extern bool opt_update_log, opt_bin_log, opt_error_log;
+extern bool opt_update_log, opt_error_log;
extern my_bool opt_log, opt_bootstrap;
extern my_bool opt_backup_history_log;
extern my_bool opt_backup_progress_log;
diff --git a/storage/innobase/handler/ha_innodb.cc b/storage/innobase/handler/ha_innodb.cc
index 4de2cdbeaec..552348ee2c5 100644
--- a/storage/innobase/handler/ha_innodb.cc
+++ b/storage/innobase/handler/ha_innodb.cc
@@ -16493,6 +16493,13 @@ innobase_xa_recover(
{
DBUG_ASSERT(hton == innodb_hton_ptr);
+ if (opt_bin_log)
+ {
+ mysql_bin_log.last_commit_pos_offset= trx_sys_mysql_bin_log_pos;
+ strmake_buf(mysql_bin_log.last_commit_pos_file,
+ trx_sys_mysql_bin_log_name);
+ }
+
if (len == 0 || xid_list == NULL) {
return(0);
diff --git a/storage/innobase/log/log0recv.cc b/storage/innobase/log/log0recv.cc
index 3b3c7c23224..c8a742d95e9 100644
--- a/storage/innobase/log/log0recv.cc
+++ b/storage/innobase/log/log0recv.cc
@@ -2,7 +2,7 @@
Copyright (c) 1997, 2017, Oracle and/or its affiliates. All Rights Reserved.
Copyright (c) 2012, Facebook Inc.
-Copyright (c) 2013, 2019, MariaDB Corporation.
+Copyright (c) 2013, 2020, MariaDB Corporation.
This program is free software; you can redistribute it and/or modify it under
the terms of the GNU General Public License as published by the Free Software
@@ -3301,7 +3301,8 @@ void
recv_recovery_from_checkpoint_finish(void)
/*======================================*/
{
- if (recv_needed_recovery) {
+ extern MYSQL_PLUGIN_IMPORT bool opt_bin_log;
+ if (opt_bin_log) {
trx_sys_print_mysql_master_log_pos();
trx_sys_print_mysql_binlog_offset();
}
diff --git a/storage/xtradb/handler/ha_innodb.cc b/storage/xtradb/handler/ha_innodb.cc
index 2aafb1a44ee..a966f726a03 100644
--- a/storage/xtradb/handler/ha_innodb.cc
+++ b/storage/xtradb/handler/ha_innodb.cc
@@ -17165,6 +17165,13 @@ innobase_xa_recover(
{
DBUG_ASSERT(hton == innodb_hton_ptr);
+ if (opt_bin_log)
+ {
+ mysql_bin_log.last_commit_pos_offset= trx_sys_mysql_bin_log_pos;
+ strmake_buf(mysql_bin_log.last_commit_pos_file,
+ trx_sys_mysql_bin_log_name);
+ }
+
if (len == 0 || xid_list == NULL) {
return(0);
diff --git a/storage/xtradb/log/log0recv.cc b/storage/xtradb/log/log0recv.cc
index dd55d31218a..a4844507328 100644
--- a/storage/xtradb/log/log0recv.cc
+++ b/storage/xtradb/log/log0recv.cc
@@ -2,7 +2,7 @@
Copyright (c) 1997, 2017, Oracle and/or its affiliates. All Rights Reserved.
Copyright (c) 2012, Facebook Inc.
-Copyright (c) 2013, 2019, MariaDB Corporation.
+Copyright (c) 2013, 2020, MariaDB Corporation.
This program is free software; you can redistribute it and/or modify it under
the terms of the GNU General Public License as published by the Free Software
@@ -3397,7 +3397,8 @@ void
recv_recovery_from_checkpoint_finish(void)
/*======================================*/
{
- if (recv_needed_recovery) {
+ extern MYSQL_PLUGIN_IMPORT bool opt_bin_log;
+ if (opt_bin_log) {
trx_sys_print_mysql_master_log_pos();
trx_sys_print_mysql_binlog_offset();
}
1
0
[Commits] 83b65d64459: MDEV-21480: Unique key using ref access though eq_ref access can be used
by Varun 21 Apr '20
by Varun 21 Apr '20
21 Apr '20
revision-id: 83b65d6445932b6465bfe81228dfff0324ea50e4 (mariadb-10.3.21-85-g83b65d64459)
parent(s): 42af2b1d8b8a8508c5aac5c2e0bcf4554c5200ce
author: Varun Gupta
committer: Varun Gupta
timestamp: 2020-04-21 14:11:29 +0530
message:
MDEV-21480: Unique key using ref access though eq_ref access can be used
For a unique key if all the keyparts are NOT NULL or the predicates involving
the keyparts is NULL rejecting, then we can use EQ_REF access instead of ref
access with the unique key
---
mysql-test/main/invisible_field.result | 2 +-
mysql-test/main/join.result | 4 +--
mysql-test/main/join.test | 2 +-
mysql-test/main/key.result | 42 +++++++++++++++++++++++++++
mysql-test/main/key.test | 21 ++++++++++++++
mysql-test/main/order_by.result | 2 +-
mysql-test/main/subselect_sj_nonmerged.result | 4 +--
mysql-test/main/table_elim.result | 2 +-
sql/sql_select.cc | 21 ++++++++++----
9 files changed, 86 insertions(+), 14 deletions(-)
diff --git a/mysql-test/main/invisible_field.result b/mysql-test/main/invisible_field.result
index 9cdc54f2e43..053a9fd8e50 100644
--- a/mysql-test/main/invisible_field.result
+++ b/mysql-test/main/invisible_field.result
@@ -405,7 +405,7 @@ c int(11) YES NULL
explain select * from t1,t2 where t1.b = t2.c and t1.c = t2.b;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 ALL NULL NULL NULL NULL 10 Using where
-1 SIMPLE t1 ref b,c b 5 test.t2.c 1 Using where
+1 SIMPLE t1 eq_ref b,c b 5 test.t2.c 1 Using where
select * from t1,t2 where t1.b = t2.c and t1.c = t2.b;
a a b c
1 1 1 1
diff --git a/mysql-test/main/join.result b/mysql-test/main/join.result
index fe6d18f7807..e667eab7eb9 100644
--- a/mysql-test/main/join.result
+++ b/mysql-test/main/join.result
@@ -3333,11 +3333,11 @@ show keys from t1;
Table Non_unique Key_name Seq_in_index Column_name Collation Cardinality Sub_part Packed Null Index_type Comment Index_comment
t1 0 PRIMARY 1 pk A 1010 NULL NULL BTREE
t1 0 a 1 a A 10 NULL NULL YES BTREE
-# t1 must use ref(t1.a=t0.a) and rows must be 1 (and not 45):
+# t1 must use eq_ref(t1.a=t0.a) and rows must be 1 (and not 45):
explain select * from t0,t1 where t0.a=t1.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t0 ALL NULL NULL NULL NULL 10 Using where
-1 SIMPLE t1 ref a a 5 test.t0.a 1
+1 SIMPLE t1 eq_ref a a 5 test.t0.a 1
drop table t0,t1;
#
# MDEV-21383: Possible range plan is not used under certain conditions
diff --git a/mysql-test/main/join.test b/mysql-test/main/join.test
index c72ff0e1a8c..07c72827d25 100644
--- a/mysql-test/main/join.test
+++ b/mysql-test/main/join.test
@@ -1744,7 +1744,7 @@ analyze table t1;
set myisam_stats_method=@tmp1;
show keys from t1;
---echo # t1 must use ref(t1.a=t0.a) and rows must be 1 (and not 45):
+--echo # t1 must use eq_ref(t1.a=t0.a) and rows must be 1 (and not 45):
explain select * from t0,t1 where t0.a=t1.a;
drop table t0,t1;
diff --git a/mysql-test/main/key.result b/mysql-test/main/key.result
index ba1124aaa14..6edb0404524 100644
--- a/mysql-test/main/key.result
+++ b/mysql-test/main/key.result
@@ -639,3 +639,45 @@ SHOW STATUS LIKE 'Last_query_cost';
Variable_name Value
Last_query_cost 14.199000
DROP TABLE t1;
+#
+# MDEV-21480: Unique key using ref access though eq_ref access can be used
+#
+create table t1(a int, b int,c int, primary key(a), unique key(b,c));
+insert into t1 select seq, seq, seq from seq_1_to_10;
+create table t2(a int, b int,c int);
+insert into t2 select seq, seq, seq+1 from seq_1_to_100;
+EXPLAIN SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 100 Using where
+1 SIMPLE t1 eq_ref b b 10 test.t2.a,test.t2.b 1 Using index
+SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b;
+c c
+1 2
+2 3
+3 4
+4 5
+5 6
+6 7
+7 8
+8 9
+9 10
+10 11
+alter table t1 drop PRIMARY KEY;
+alter table t1 add PRIMARY KEY(b,c);
+EXPLAIN SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b;
+id select_type table type possible_keys key key_len ref rows Extra
+1 SIMPLE t2 ALL NULL NULL NULL NULL 100 Using where
+1 SIMPLE t1 eq_ref PRIMARY,b PRIMARY 8 test.t2.a,test.t2.b 1 Using index
+SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b;
+c c
+1 2
+2 3
+3 4
+4 5
+5 6
+6 7
+7 8
+8 9
+9 10
+10 11
+drop table t1,t2;
diff --git a/mysql-test/main/key.test b/mysql-test/main/key.test
index 0695dc2eecb..b386f1298fd 100644
--- a/mysql-test/main/key.test
+++ b/mysql-test/main/key.test
@@ -1,6 +1,7 @@
--disable_warnings
drop table if exists t1,t2,t3;
--enable_warnings
+--source include/have_sequence.inc
SET SQL_WARNINGS=1;
#
@@ -581,3 +582,23 @@ EXPLAIN SELECT a, SUM( b ) FROM t1 FORCE INDEX( a ) GROUP BY a;
SHOW STATUS LIKE 'Last_query_cost';
DROP TABLE t1;
+
+--echo #
+--echo # MDEV-21480: Unique key using ref access though eq_ref access can be used
+--echo #
+
+create table t1(a int, b int,c int, primary key(a), unique key(b,c));
+insert into t1 select seq, seq, seq from seq_1_to_10;
+
+create table t2(a int, b int,c int);
+insert into t2 select seq, seq, seq+1 from seq_1_to_100;
+
+EXPLAIN SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b;
+SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b;
+
+alter table t1 drop PRIMARY KEY;
+alter table t1 add PRIMARY KEY(b,c);
+EXPLAIN SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b;
+SELECT t1.c, t2.c FROM t1, t2 WHERE t1.b=t2.a and t1.c=t2.b;
+
+drop table t1,t2;
diff --git a/mysql-test/main/order_by.result b/mysql-test/main/order_by.result
index ec03e8c5f45..1bb597095db 100644
--- a/mysql-test/main/order_by.result
+++ b/mysql-test/main/order_by.result
@@ -1515,7 +1515,7 @@ WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
ORDER BY t2.c LIMIT 1;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t2 range a,b,c c 5 NULL 420 Using where
-1 SIMPLE t1 ref a a 39 test.t2.a,const 1 Using where; Using index
+1 SIMPLE t1 eq_ref a a 39 test.t2.a,const 1 Using where; Using index
SELECT d FROM t3 AS t1, t2 AS t2
WHERE t2.b=14 AND t2.a=t1.a AND 5.1<t2.c AND t1.b='DE'
ORDER BY t2.c LIMIT 1;
diff --git a/mysql-test/main/subselect_sj_nonmerged.result b/mysql-test/main/subselect_sj_nonmerged.result
index 4d9a70e6bba..7b73ae0fc57 100644
--- a/mysql-test/main/subselect_sj_nonmerged.result
+++ b/mysql-test/main/subselect_sj_nonmerged.result
@@ -69,7 +69,7 @@ t4.b=t0.a and t4.a in (select max(t2.a) from t1, t2 group by t2.b);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5
1 PRIMARY t0 ALL NULL NULL NULL NULL 10 Using where; Using join buffer (flat, BNL join)
-1 PRIMARY t4 ref a a 10 <subquery2>.max(t2.a),test.t0.a 1
+1 PRIMARY t4 eq_ref a a 10 <subquery2>.max(t2.a),test.t0.a 1
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary
2 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join)
insert into t4 select 100 + (B.a *100 + A.a), 100 + (B.a*100 + A.a), 'filler' from t4 A, t0 B;
@@ -79,7 +79,7 @@ t4.b in (select max(t2.a) from t1, t2 group by t2.b);
id select_type table type possible_keys key key_len ref rows Extra
1 PRIMARY <subquery3> ALL distinct_key NULL NULL NULL 5
1 PRIMARY <subquery2> ALL distinct_key NULL NULL NULL 5 Using join buffer (flat, BNL join)
-1 PRIMARY t4 ref a a 10 <subquery2>.max(t2.a),<subquery3>.max(t2.a) 1
+1 PRIMARY t4 eq_ref a a 10 <subquery2>.max(t2.a),<subquery3>.max(t2.a) 1
3 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary
3 MATERIALIZED t1 ALL NULL NULL NULL NULL 10 Using join buffer (flat, BNL join)
2 MATERIALIZED t2 ALL NULL NULL NULL NULL 5 Using temporary
diff --git a/mysql-test/main/table_elim.result b/mysql-test/main/table_elim.result
index 7780e165451..d12b6470ecf 100644
--- a/mysql-test/main/table_elim.result
+++ b/mysql-test/main/table_elim.result
@@ -279,7 +279,7 @@ insert into t2 values
explain select t1.* from t1 left join t2 on t2.a=t1.a;
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE t1 index NULL PRIMARY 10 NULL 2 Using index
-1 SIMPLE t2 ref a a 3 test.t1.a 1 Using where
+1 SIMPLE t2 eq_ref a a 3 test.t1.a 1 Using where
drop table t1, t2;
#
# check UPDATE/DELETE that look like they could be eliminated
diff --git a/sql/sql_select.cc b/sql/sql_select.cc
index d6c4a49526c..de66f8bc59e 100644
--- a/sql/sql_select.cc
+++ b/sql/sql_select.cc
@@ -10066,6 +10066,7 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j,
uchar *key_buff=j->ref.key_buff, *null_ref_key= 0;
uint null_ref_part= NO_REF_PART;
bool keyuse_uses_no_tables= TRUE;
+ uint not_null_keyparts= 0;
if (ftkey)
{
j->ref.items[0]=((Item_func*)(keyuse->val))->key_item();
@@ -10095,6 +10096,8 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j,
j->ref.items[i]=keyuse->val; // Save for cond removal
j->ref.cond_guards[i]= keyuse->cond_guard;
+ if (!keyuse->val->maybe_null || keyuse->null_rejecting)
+ not_null_keyparts++;
/*
Set ref.null_rejecting to true only if we are going to inject a
"keyuse->val IS NOT NULL" predicate.
@@ -10154,12 +10157,18 @@ static bool create_ref_for_key(JOIN *join, JOIN_TAB *j,
ulong key_flags= j->table->actual_key_flags(keyinfo);
if (j->type == JT_CONST)
j->table->const_table= 1;
- else if (!((keyparts == keyinfo->user_defined_key_parts &&
- ((key_flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME)) ||
- (keyparts > keyinfo->user_defined_key_parts && // true only for extended keys
- MY_TEST(key_flags & HA_EXT_NOSAME) &&
- keyparts == keyinfo->ext_key_parts)) ||
- null_ref_key)
+ else if (!((keyparts == keyinfo->user_defined_key_parts &&
+ (
+ (key_flags & (HA_NOSAME | HA_NULL_PART_KEY)) == HA_NOSAME ||
+ /* Unique key and all keyparts are NULL rejecting */
+ ((key_flags & HA_NOSAME) && keyparts == not_null_keyparts)
+ )) ||
+ /* true only for extended keys */
+ (keyparts > keyinfo->user_defined_key_parts &&
+ MY_TEST(key_flags & HA_EXT_NOSAME) &&
+ keyparts == keyinfo->ext_key_parts)
+ ) ||
+ null_ref_key)
{
/* Must read with repeat */
j->type= null_ref_key ? JT_REF_OR_NULL : JT_REF;
1
0