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

Lateral derived optimization causes signal 11 crash in CTE query and sometimes reports index corruption.

Details

    Description

      Running the attached query on the attached data set results in a signal 11 crash when "split_materialized=on" (default). split_materialized=off stops the crashing immediately.

      When the query is run with split_materialized=off, and split_materialized is turned on again, the query sometimes runs once or twice without crashing, and then proceeds to crash as before. This behaviour is identical across all versions tested, implying some asynchronous process like flushing contributes to the crash.

      Particularly noteworthy is that per the user this (theoretically non-updating) query causes index corruption that propagates to slaves with row-based replication.

      Backtrace also attached. Note that the crash only happens with certain additional configurations. In the enclosed unencrypted test configuration (cs0280391-server.cnf), removing the persistent statistics section of the configuration stops the crash.

      Attachments

        Issue Links

          Activity

            alice Alice Sherepa added a comment - - edited

            This is a duplicate of MDEV-23723. But please before closing it make sure that the patch fixes the original test. While trying to simplify the query, I've got 2 variations of the failures- the first - crash as MDEV-23723, the second - a lot of errors about corrupted indexes in the error log, smth like

            2021-04-19 10:57:22 5 [ERROR] InnoDB: Clustered record for sec rec not found index `idx_auction_lots_05` of table `test`.`auction_lots_test`
            InnoDB: sec index record PHYSICAL RECORD: n_fields 5; compact format; info bits 0
             0: len 8; hex 8000000000018c73; asc        s;;
             1: len 30; hex 8000000000dca0c400000000000080000000000000800000000000005f52; asc                             _R; (total 255 bytes);
             2: len 8; hex 800000000000005f; asc        _;;
             3: len 30; hex 52808000000000000000800000000000000080000000000000004e415f64; asc R                         NA_d; (total 239 bytes);
             4: len 0; hex ; asc ;;
            

            Version: '10.5.9-MariaDB-log'
            210419 10:43:50 [ERROR] mysqld got signal 11 ;
             
            sigaction.c:0(__restore_rt)[0x7f3a7eb683c0]
            row/row0sel.cc:4795(row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long))[0x5640155df117]
            handler/ha_innodb.cc:8780(ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function))[0x5640154f3fdf]
            sql/handler.cc:3124(handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function))[0x5640151d95d8]
            sql/sql_select.cc:21449(join_read_always_key(st_join_table*))[0x56401500e3f7]
            sql/sql_select.cc:20686(sub_select(JOIN*, st_join_table*, bool))[0x564015001f5a]
            sql/sql_select.cc:20223(JOIN::exec_inner())[0x56401503084f]
            sql/sql_select.cc:4247(JOIN::exec())[0x564015030ab3]
            sql/sql_select.cc:4721(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x56401502ec9e]
            sql/sql_derived.cc:1266(mysql_derived_fill(THD*, LEX*, TABLE_LIST*))[0x564014f8f7ce]
            sql/sql_derived.cc:206(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x564014f8f374]
            sql/sql_select.cc:13644(st_join_table::preread_init())[0x564015001c59]
            sql/sql_select.cc:20650(sub_select(JOIN*, st_join_table*, bool))[0x564015001f28]
            sql/sql_class.h:4065(THD::get_stmt_da())[0x564014ffc46c]
            sql/sql_select.cc:20696(sub_select(JOIN*, st_join_table*, bool))[0x564015001f6f]
            sql/sql_class.h:4065(THD::get_stmt_da())[0x564014ffc46c]
            sql/sql_select.cc:20696(sub_select(JOIN*, st_join_table*, bool))[0x564015001fcd]
            sql/sql_select.cc:20223(do_select)[0x56401503084f]
            sql/sql_select.cc:4247(JOIN::exec())[0x564015030ab3]
            sql/sql_select.cc:4721(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x56401502ec9e]
            sql/sql_select.cc:429(handle_select(THD*, LEX*, select_result*, unsigned long))[0x56401502f7da]
            sql/sql_parse.cc:6283(execute_sqlcom_select(THD*, TABLE_LIST*))[0x564014e93b65]
            sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x564014fd40c4]
            sql/sql_parse.cc:8063(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x564014fd7169]
            sql/sql_audit.h:169(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x564014fd9215]
            sql/sql_parse.cc:1375(do_command(THD*))[0x564014fdabdb]
            sql/sql_connect.cc:1410(do_handle_one_connection(CONNECT*, bool))[0x5640150c5bc2]
            sql/sql_connect.cc:1318(handle_one_connection)[0x5640150c5e84]
            perfschema/pfs.cc:2204(pfs_spawn_thread)[0x56401544928d]
            nptl/pthread_create.c:478(start_thread)[0x7f3a7eb5c609]
            x86_64/clone.S:97(__GI___clone)[0x7f3a7e700293]
            

            alice Alice Sherepa added a comment - - edited This is a duplicate of MDEV-23723 . But please before closing it make sure that the patch fixes the original test. While trying to simplify the query, I've got 2 variations of the failures- the first - crash as MDEV-23723 , the second - a lot of errors about corrupted indexes in the error log, smth like 2021-04-19 10:57:22 5 [ERROR] InnoDB: Clustered record for sec rec not found index `idx_auction_lots_05` of table `test`.`auction_lots_test` InnoDB: sec index record PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 8; hex 8000000000018c73; asc s;; 1: len 30; hex 8000000000dca0c400000000000080000000000000800000000000005f52; asc _R; (total 255 bytes); 2: len 8; hex 800000000000005f; asc _;; 3: len 30; hex 52808000000000000000800000000000000080000000000000004e415f64; asc R NA_d; (total 239 bytes); 4: len 0; hex ; asc ;; Version: '10.5.9-MariaDB-log' 210419 10:43:50 [ERROR] mysqld got signal 11 ;   sigaction.c:0(__restore_rt)[0x7f3a7eb683c0] row/row0sel.cc:4795(row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long))[0x5640155df117] handler/ha_innodb.cc:8780(ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function))[0x5640154f3fdf] sql/handler.cc:3124(handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function))[0x5640151d95d8] sql/sql_select.cc:21449(join_read_always_key(st_join_table*))[0x56401500e3f7] sql/sql_select.cc:20686(sub_select(JOIN*, st_join_table*, bool))[0x564015001f5a] sql/sql_select.cc:20223(JOIN::exec_inner())[0x56401503084f] sql/sql_select.cc:4247(JOIN::exec())[0x564015030ab3] sql/sql_select.cc:4721(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x56401502ec9e] sql/sql_derived.cc:1266(mysql_derived_fill(THD*, LEX*, TABLE_LIST*))[0x564014f8f7ce] sql/sql_derived.cc:206(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x564014f8f374] sql/sql_select.cc:13644(st_join_table::preread_init())[0x564015001c59] sql/sql_select.cc:20650(sub_select(JOIN*, st_join_table*, bool))[0x564015001f28] sql/sql_class.h:4065(THD::get_stmt_da())[0x564014ffc46c] sql/sql_select.cc:20696(sub_select(JOIN*, st_join_table*, bool))[0x564015001f6f] sql/sql_class.h:4065(THD::get_stmt_da())[0x564014ffc46c] sql/sql_select.cc:20696(sub_select(JOIN*, st_join_table*, bool))[0x564015001fcd] sql/sql_select.cc:20223(do_select)[0x56401503084f] sql/sql_select.cc:4247(JOIN::exec())[0x564015030ab3] sql/sql_select.cc:4721(mysql_select(THD*, TABLE_LIST*, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x56401502ec9e] sql/sql_select.cc:429(handle_select(THD*, LEX*, select_result*, unsigned long))[0x56401502f7da] sql/sql_parse.cc:6283(execute_sqlcom_select(THD*, TABLE_LIST*))[0x564014e93b65] sql/sql_parse.cc:3978(mysql_execute_command(THD*))[0x564014fd40c4] sql/sql_parse.cc:8063(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x564014fd7169] sql/sql_audit.h:169(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x564014fd9215] sql/sql_parse.cc:1375(do_command(THD*))[0x564014fdabdb] sql/sql_connect.cc:1410(do_handle_one_connection(CONNECT*, bool))[0x5640150c5bc2] sql/sql_connect.cc:1318(handle_one_connection)[0x5640150c5e84] perfschema/pfs.cc:2204(pfs_spawn_thread)[0x56401544928d] nptl/pthread_create.c:478(start_thread)[0x7f3a7eb5c609] x86_64/clone.S:97(__GI___clone)[0x7f3a7e700293]

            Ok, I was able to reproduce. The crash goes away when I apply the patch for MDEV-23723.

            psergei Sergei Petrunia added a comment - Ok, I was able to reproduce. The crash goes away when I apply the patch for MDEV-23723 .

            Closing as Duplicate of MDEV-23723.

            psergei Sergei Petrunia added a comment - Closing as Duplicate of MDEV-23723 .

            People

              psergei Sergei Petrunia
              juan.vera Juan
              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.