Details
-
Bug
-
Status: Closed (View Workflow)
-
Blocker
-
Resolution: Fixed
-
10.5.11, 10.3(EOL), 10.4(EOL), 10.5, 10.6
-
Docker: mariadb:10.5.11
AWS RDS: mariadb 10.5.8
Client: ConnectorJ 2.6.0
Description
Steps to reproduce:
1. Create a function, which selects data from any table. It is not enough to just return some constant value.
2. Create a view, which uses the function created in step 1.
3. Create a procedure, which runs for-loop. This for loop must query view created on step 2. However, it should not use any columns returned from the query, but a constant, eg. 1, for each row.
4. Call procedure twice from the same connection. First run success normally. The second one crashes the server without any explanation.
Minimal Reproducible Example is on attachment server_crash.sql.
All these details seem to be required to cause the server to crash. Interestingly enough, dropping some of them causes a different issue. If I select any value on cursor definition or skip the view and call the function directly, the first run results an error Table 'test_table' doesn't exist. Example of this is on file table_does_not_exist.sql.
Attachments
Activity
With the patch the test case with views
- on debug build: fails a debug assertion on the first execution of the SP, which didn't happen before,
- on non-debug build: fails same way as it did before, crashing in check_grant.
Also, it's not exactly specific to a view – as often happens, the view in that test case can be replaced with a subquery with the same effect, as in
declare _cur cursor for select 1 from (select get_name(id) from t1) sq; |
So the patch may indeed need further improvement.
JFYI rewritten without FOR it still cause error where should not (but does not crash)
CREATE TABLE t1 ( id int, name varchar(24));
|
INSERT INTO t1 values (1, 'x'), (2, 'y'), (3, 'z');
|
|
|
create function get_name(_id int) returns varchar(24)
|
return (select name from t1 where id = _id);
|
|
|
select get_name(id) from t1;
|
|
|
delimiter ^^;
|
|
|
create procedure test_proc()
|
begin
|
DECLARE done INT DEFAULT FALSE;
|
DECLARE rec ROW TYPE OF _cur;
|
declare _cur cursor for select get_name(id) from t1;
|
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
|
open _cur;
|
read_loop: LOOP
|
fetch _cur into rec;
|
IF done THEN
|
LEAVE read_loop;
|
END IF;
|
select 1;
|
end LOOP;
|
end;
|
^^
|
delimiter ;^^
|
|
|
--error 1146
|
call test_proc();
|
--error 1146
|
call test_proc();
|
My first patch does not work with views because view also add tables. So I come up with one lined fix which work with views.
commit d8e915a88fcdebd0b42f10b2f6d2165296056dd1 (HEAD -> bb-10.3-MDEV-26009, origin/bb-10.3-MDEV-26009)
|
Author: Oleksandr Byelkin <sanja@mariadb.com>
|
Date: Fri Mar 18 11:13:09 2022 +0100
|
|
|
MDEV-26009 Server crash when calling twice procedure using FOR-loop
|
|
The problem was that instructions sp_instr_cursor_copy_struct and
|
sp_instr_copen uses the same lex, adding and removing "tail" of
|
prelocked tables and forgetting that tail of all tables is kept in
|
LEX::query_tables_last. If the LEX used only by one instruction
|
or the query do not have prelocked tables it is not important.
|
But to work correctly in all cases LEX::query_tables_last should
|
be reset to make new tables added in the correct list (after last
|
table in the LEX instead after last table of the prelocking "tail"
|
which was cut).
|
This fixes suite without view, but test suite with a view crashes by other cause (maybe other bug or this patch should be impruved):
diff --git a/sql/sp_head.cc b/sql/sp_head.ccindex 57ab31d9edf..7f9e15d8dc9 100644--- a/sql/sp_head.cc+++ b/sql/sp_head.cc@@ -4584,6 +4584,7 @@ sp_instr_cursor_copy_struct::exec_core(THD *thd, uint *nextp)if (!row->arguments()){sp_cursor tmp(thd, &m_lex_keeper, true);+ TABLE_LIST **query_tables_last_save= m_lex_keeper.get_query_tables_last();// Open the cursor without copying dataif (!(ret= tmp.open(thd))){@@ -4605,6 +4606,7 @@ sp_instr_cursor_copy_struct::exec_core(THD *thd, uint *nextp)thd->restore_active_arena(thd->spcont->callers_arena, ¤t_arena);tmp.close(thd);}+ m_lex_keeper.set_query_tables_last(query_tables_last_save);}*nextp= m_ip + 1;DBUG_RETURN(ret);diff --git a/sql/sp_head.h b/sql/sp_head.hindex e1cfbb484ad..1fa9232b0d9 100644--- a/sql/sp_head.h+++ b/sql/sp_head.h@@ -1214,6 +1214,17 @@ class sp_lex_keeperm_lex->safe_to_cache_query= 0;}+ TABLE_LIST **get_query_tables_last()+ {+ return m_lex->query_tables_last;+ }++ void set_query_tables_last(TABLE_LIST ** t)+ {+ m_lex->query_tables_last= t;+ }++private:LEX *m_lex;