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

Unexpected failure with query using indirectly a recursive CTE twice

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.3.30, 10.4.20, 10.5.11, 10.6.3, 10.2(EOL), 10.3(EOL), 10.4(EOL), 10.5, 10.6
    • 10.2.40, 10.3.31, 10.4.21, 10.5.12, 10.6.4
    • Optimizer - CTE
    • Windows 10 Professional (different DBs and machines) and Linux x64 unknown Distribution (dbfiddle.uk).

    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

      Attachments

        Issue Links

          Activity

            alice Alice Sherepa added a comment -

            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
            
            

            alice Alice Sherepa added a comment - 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
            igor Igor Babaev added a comment -

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

            igor Igor Babaev added a comment - This bug was caused by the same problem as MDEV-26025 .
            igor Igor Babaev added a comment -

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

            igor Igor Babaev added a comment - This bug was fixed by the patch for MDEV-26025 . Only the test case has to be merged upstream.

            People

              igor Igor Babaev
              miridb Miri
              Votes:
              0 Vote for this issue
              Watchers:
              4 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.