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

          is caused by

          Bug - A problem which impairs or prevents the functions of the product. MDEV-26025 Server crashes while executing query with CTE in PS/SP

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed
          relates to

          Bug - A problem which impairs or prevents the functions of the product. MDEV-23886 Reusing CTE inside a function fails with table doesn't exist

          • Critical - Crashes, loss of data, severe memory leak.
          • Closed

          Activity

            Ascending order - Click to sort in descending order
            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.