Uploaded image for project: 'MariaDB Server'
  1. MariaDB Server
  2. MDEV-30474

Crash when after massively repeated C,U,D(especially INSERT with duplicated update) with ZERO SELECT

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Incomplete
    • 10.7.7
    • N/A
    • None
    • Windows 64.
      NVMe SSD SK Hynix p41.
      AMD CPU

    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)

      Attachments

        Issue Links

          Activity

            alice Alice Sherepa added a comment - - edited

            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)?

            alice Alice Sherepa added a comment - - edited 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)?
            npdmailing@gmail.com Mitchell Lee added a comment -

            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.

            npdmailing@gmail.com Mitchell Lee added a comment - 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.
            npdmailing@gmail.com Mitchell Lee added a comment - - edited

            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?

            npdmailing@gmail.com Mitchell Lee added a comment - - edited 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?
            npdmailing@gmail.com Mitchell Lee added a comment -

            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;

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

            npdmailing@gmail.com Mitchell Lee added a comment - 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; ---------------------
            alice Alice Sherepa added a comment -

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

            alice Alice Sherepa added a comment - Please add the error log and if possible - the exact steps how to repeat the issue (table structures, views, etc) + configuration file(s)
            npdmailing@gmail.com Mitchell Lee added a comment - - edited

            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.

            npdmailing@gmail.com Mitchell Lee added a comment - - edited 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.

            People

              Unassigned Unassigned
              npdmailing@gmail.com Mitchell Lee
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:

                Git Integration

                  Error rendering 'com.xiplink.jira.git.jira_git_plugin:git-issue-webpanel'. Please contact your Jira administrators.