[MDEV-30474] Crash when after massively repeated C,U,D(especially INSERT with duplicated update) with ZERO SELECT Created: 2023-01-26  Updated: 2023-02-27  Resolved: 2023-02-27

Status: Closed
Project: MariaDB Server
Component/s: Optimizer - Window functions
Affects Version/s: 10.7.7
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Mitchell Lee Assignee: Unassigned
Resolution: Incomplete Votes: 0
Labels: None
Environment:

Windows 64.
NVMe SSD SK Hynix p41.
AMD CPU


Issue Links:
Relates
relates to MDEV-24684 Aggregate functions min max over a pa... Confirmed
relates to MDEV-30517 On windows 11, difference of innodb_f... Closed

 Description   

Hi.

By observing long term experiences,
as the report subject "Repeated C,U,D(especially INSERT with duplicated update) with ZERO SELECT" makes unexpected crash which very repeated symptom/crash.

Thing is that, usually, by the batch process (doesn't matter internal schduler or external script by connector), any of tables keep repeatly re-newed(delete all & insert by select. or insert with duplicated update).
After repeated update without selection several times(sometime, only dozen of times. sometime, several hundred times within short period time)
it makes crash as below log.
Doesn't matter which kind of table.
(also, seems it drain db internal buffer)

So please look into the error. which is very serious.
(Have no idea it related to SSD. But please resolve the issue some how.)
---------------------------------

server.dll!Item_sum_max::add()[item_sum.cc:2571]
server.dll!Frame_scan_cursor::compute_values_for_current_row()[sql_window.cc:2263]
server.dll!compute_window_func()[sql_window.cc:2901]
server.dll!Window_funcs_sort::exec()[sql_window.cc:3057]
server.dll!Window_funcs_computation::exec()[sql_window.cc:3184]
server.dll!AGGR_OP::end_send()[sql_select.cc:29767]
server.dll!sub_select_postjoin_aggr()[sql_select.cc:21051]
server.dll!do_select()[sql_select.cc:20887]
server.dll!JOIN::exec_inner()[sql_select.cc:4793]
server.dll!mysql_select()[sql_select.cc:5051]
server.dll!mysql_derived_fill()[sql_derived.cc:1294]
server.dll!mysql_handle_single_derived()[sql_derived.cc:200]
server.dll!st_join_table::preread_init()[sql_select.cc:14225]
server.dll!sub_select()[sql_select.cc:21302]
server.dll!do_select()[sql_select.cc:20885]
server.dll!JOIN::exec_inner()[sql_select.cc:4793]
server.dll!mysql_select()[sql_select.cc:5051]
server.dll!mysql_derived_fill()[sql_derived.cc:1294]
server.dll!mysql_handle_single_derived()[sql_derived.cc:200]
server.dll!st_join_table::preread_init()[sql_select.cc:14225]
server.dll!sub_select()[sql_select.cc:21302]
server.dll!evaluate_join_record()[sql_select.cc:21573]
server.dll!sub_select()[sql_select.cc:21342]
server.dll!evaluate_join_record()[sql_select.cc:21573]
server.dll!sub_select()[sql_select.cc:21381]
server.dll!evaluate_join_record()[sql_select.cc:21573]
server.dll!sub_select()[sql_select.cc:21342]
server.dll!do_select()[sql_select.cc:20885]
server.dll!JOIN::exec_inner()[sql_select.cc:4793]
server.dll!JOIN::exec()[sql_select.cc:4572]
server.dll!st_select_lex_unit::exec()[sql_union.cc:2248]
server.dll!mysql_derived_fill()[sql_derived.cc:1307]
server.dll!mysql_handle_single_derived()[sql_derived.cc:200]
server.dll!st_join_table::preread_init()[sql_select.cc:14225]
server.dll!sub_select()[sql_select.cc:21302]
server.dll!do_select()[sql_select.cc:20885]
server.dll!JOIN::exec_inner()[sql_select.cc:4793]
server.dll!mysql_select()[sql_select.cc:5051]
server.dll!mysql_derived_fill()[sql_derived.cc:1294]
server.dll!mysql_handle_single_derived()[sql_derived.cc:200]
server.dll!st_join_table::preread_init()[sql_select.cc:14225]
server.dll!sub_select()[sql_select.cc:21302]
server.dll!do_select()[sql_select.cc:20885]
server.dll!JOIN::exec_inner()[sql_select.cc:4793]
server.dll!mysql_select()[sql_select.cc:5051]
server.dll!handle_select()[sql_select.cc:554]
server.dll!mysql_execute_command()[sql_parse.cc:4716]
server.dll!sp_instr_stmt::exec_core()[sp_head.cc:3801]
server.dll!sp_lex_keeper::reset_lex_and_exec_core()[sp_head.cc:3524]
server.dll!sp_instr_stmt::execute()[sp_head.cc:3705]
server.dll!sp_head::execute()[sp_head.cc:1458]
server.dll!sp_head::execute_procedure()[sp_head.cc:2472]
server.dll!do_execute_sp()[sql_parse.cc:3023]
server.dll!Sql_cmd_call::execute()[sql_parse.cc:3269]
server.dll!mysql_execute_command()[sql_parse.cc:6001]
server.dll!sp_instr_stmt::exec_core()[sp_head.cc:3801]
server.dll!sp_lex_keeper::reset_lex_and_exec_core()[sp_head.cc:3524]
server.dll!sp_instr_stmt::execute()[sp_head.cc:3705]
server.dll!sp_head::execute()[sp_head.cc:1458]
server.dll!sp_head::execute_procedure()[sp_head.cc:2472]
server.dll!do_execute_sp()[sql_parse.cc:3023]
server.dll!Sql_cmd_call::execute()[sql_parse.cc:3269]
server.dll!mysql_execute_command()[sql_parse.cc:6001]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x23a2b8605f0): INSERT INTO xxxxxxxxxxxxxxxxxx (skip)



 Comments   
Comment by Alice Sherepa [ 2023-01-26 ]

It is related to the window function - probably MAX()..OVER(..) Is it possible for you to add the exact query, that is crashing (insert select, obfuscate the data)?

Comment by Mitchell Lee [ 2023-01-26 ]

Well... sorry.. I hope to give you the source query. But impossible. Sorry.
If it would possible, I would attached it.

BYW, this is quite old issue to me.
The sample crash log which include window function is only one out of tons of times of symtom
that I has another non-windowed function query crash case(which have no log for now, since I added blank/idle selection after the C,U,D for no purpose).

So...please think broadly the root cause. that not connected to specific case
My idea to dig the issue is that....some kind of UN-FLUSHED file handler issue
when repeatly update a table without selection.

Comment by Mitchell Lee [ 2023-01-26 ]

Forgot to mention.
The queris which make this issue for insert or update,
usually make 0 rows of records set to insert.
Again,
the batch logic(DB internal scheduler or external script via TCP) call a stored procedure
to make kinda recordset as cached table.
But the recordset which I expect not always exist. but sometime gathered.
So most of time, the batch logic, produce 0 rows or result,
and the INSERT INTO statement recieve 0 rows of records set to insert or dupl-update.

So,
this issue very related to 'massively repeated, 0 rows selected recordset insertion with no SELECT'
then crash.
Can you understand what I'm saying?

Comment by Mitchell Lee [ 2023-01-27 ]

One of a new crash occured today by
START TRANSACTION;
create table if not exists _temp_v_m_s_t_l SELECT * from table_xxxxx_same_as_the_view_xxxxx LIMIT 1;
TRUNCATE TABLE _temp_v_m_s_t_l;
insert into
_temp_v_m_s_t_l
select
*
from
view_xxxxx AS srcTT
;
COMMIT;

---------------------

Comment by Alice Sherepa [ 2023-01-27 ]

Please add the error log and if possible - the exact steps how to repeat the issue (table structures, views, etc) + configuration file(s)

Comment by Mitchell Lee [ 2023-02-26 ]

After upgrade to 10.7.8 and observing for a while,
it seems fixed by MDEV-30052 since the WINDOW clause in all over the db.
So I suggest to close.

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