[MDEV-26202] Unexpected failure with query using indirectly a recursive CTE twice Created: 2021-07-21  Updated: 2021-08-03  Resolved: 2021-08-03

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - CTE
Affects Version/s: 10.3.30, 10.4.20, 10.5.11, 10.6.3, 10.2, 10.3, 10.4, 10.5, 10.6
Fix Version/s: 10.2.40, 10.3.31, 10.4.21, 10.5.12, 10.6.4

Type: Bug Priority: Major
Reporter: Miri Assignee: Igor Babaev
Resolution: Fixed Votes: 0
Labels: crash, regression
Environment:

Windows 10 Professional (different DBs and machines) and Linux x64 unknown Distribution (dbfiddle.uk).

Attachments: File DB_crash_MariaDB_all_new_Versions.sql    
Issue Links:
Problem/Incident
is caused by MDEV-26025 Server crashes while executing query ... Closed
Relates
relates to MDEV-23886 Reusing CTE inside a function fails w... Closed

 Description   

Yesterday I wanted to use an older query for reporting reasons. Every time I run the query there was an error message on the client:

/* SQL Error (2013): Lost connection to MySQL server during query */

I tried multiple servers (different versions of MariaDB) but nothing helped. Then I looked on the Windows 10 server event viewer and realized that the MariaDB server has crashed and was restarted every single time! (option on Windows after service crashed)
I can reproduce that problem after 1 or 2 seconds runtime of my query on all my DB-Versions (10.3.30, 10.4.20, 10.5.11, 10.6.3).

Then I tried older versions of MariaDB 10.6.3 (crashed) => 10.6.2 (OK) and MariaDB 10.5.11 (crashed) => 10.5.10 (OK), 10.5.5 (OK), 10.5.2 (OK).

My query has 700 lines and I tried to reduce, strip down and rename everything. I have attached an (hopefully!) working example. The problem is (recursive?) CTE related and my example has no other tables any longer.

Best,
Miriam

 Comments   
Comment by Alice Sherepa [ 2021-07-21 ]

Thank you!
I repeated on 10.2-10.6

WITH RECURSIVE rcte AS ( 
SELECT 1 AS rcte
 
UNION ALL
 
SELECT rcte + 1 FROM rcte WHERE rcte < 10),
 
 
 
tb AS (SELECT * FROM rcte),
 
cte3 AS (SELECT 1 FROM cte),
 
cte AS ( SELECT 1 FROM  tb)
 
SELECT * FROM cte;

on 10.2

ERROR 1030 (HY000): Got error 1 "Operation not permitted" from storage engine MEMORY

10.3-10.6: crash

10.3 cf6d83e7d68bd2bc0af0404bf4

 
Version: '10.3.31-MariaDB-debug-log'  
210721 16:06:24 [ERROR] mysqld got signal 11 ;
 
 
 
Server version: 10.3.31-MariaDB-debug-log
 
 
 
mysys/stacktrace.c:174(my_print_stacktrace)[0x560356438a65]
 
sql/signal_handler.cc:221(handle_fatal_signal)[0x560354e8b377]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x12730)[0x7f9b0de0d730]
 
heap/hp_info.c:34(heap_info)[0x56035541d684]
 
heap/ha_heap.cc:368(ha_heap::info(unsigned int))[0x56035540e880]
 
sql/table.cc:8561(TABLE_LIST::fetch_number_of_rows())[0x560354a1d4bb]
 
sql/sql_select.cc:4557(make_join_statistics(JOIN*, List<TABLE_LIST>&, st_dynamic_array*))[0x5603547b5582]
 
sql/sql_select.cc:1973(JOIN::optimize_inner())[0x56035479b408]
 
sql/sql_select.cc:1515(JOIN::optimize())[0x560354796801]
 
sql/sql_select.cc:4326(mysql_select(THD*, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x5603547b39de]
 
sql/sql_select.cc:372(handle_select(THD*, LEX*, select_result*, unsigned long))[0x56035478a4a6]
 
sql/sql_parse.cc:6339(execute_sqlcom_select(THD*, TABLE_LIST*))[0x5603546f5e28]
 
sql/sql_parse.cc:3870(mysql_execute_command(THD*))[0x5603546e3e27]
 
sql/sql_parse.cc:7870(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x5603546fff17]
 
sql/sql_parse.cc:1855(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x5603546d66ae]
 
sql/sql_parse.cc:1398(do_command(THD*))[0x5603546d2df5]
 
sql/sql_connect.cc:1403(do_handle_one_connection(CONNECT*))[0x560354ac8362]
 
sql/sql_connect.cc:1309(handle_one_connection)[0x560354ac7c1a]
 
perfschema/pfs.cc:1871(pfs_spawn_thread)[0x560356245eb1]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x7fa3)[0x7f9b0de02fa3]
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7f9b0dd334cf]
 
 
 
Query (0x62b000000410): WITH RECURSIVE rcte AS ( 
SELECT 1 AS rcte
 
UNION ALL
 
SELECT rcte + 1 FROM rcte WHERE rcte < 10),
 
tb AS (SELECT * FROM rcte),
 
cte3 AS (SELECT 1 FROM cte),
 
cte AS ( SELECT 1 FROM  tb)
 
SELECT * FROM cte

Comment by Igor Babaev [ 2021-07-22 ]

This bug was caused by the same problem as MDEV-26025.

Comment by Igor Babaev [ 2021-08-03 ]

This bug was fixed by the patch for MDEV-26025. Only the test case has to be merged upstream.

Generated at Thu Feb 08 09:43:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.