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

Server crash/hang executing simple query involving indexed virtual InnoDB columns

Details

    Description

      The following query was working fine on MariaDB v10.3, but now reliably crashes the server since upgrading to v10.6.16 (logs a "got signal 11"), usually producing no stack trace either*, when executed against the data I have in my DB:

      SELECT RequestID,RequestDate FROM requests WHERE (CompanyID=70 OR (CompanyID IS NULL AND CompanyDel='foo')) AND StatusCode<=1;
      

      * Sometimes the log contains:
      malloc(): unaligned tcache chunk detected
      Fatal signal 6 while backtracing

      or else something like
      stack_bottom = 0x7f145affeb98 thread_stack 0x49000

      (WORSE: Every now and then it will hang mariadbd, rather than terminating it. This brings down the whole website until the mariadbd process is manually killed.)

      I believe the problem might possibly be connected to the definition of StatusCode:

      StatusCode tinyint unsigned AS (IF(InvoiceTS,IF(InvoiceTS=1,6,7),IF(DispatchDate,5^(Status&1),IF(Status&24,IF(Status&16,0,1),IF(Status&4,2,3))))) VIRTUAL
      

      Since it no longer crashes if you add "IGNORE INDEX (StatusCode)" to the query.
      Nor does it crash if you replace the reference to "StatusCode" with its definition expression, as given above.

      The definition of the complete table is as follows:

      CREATE TABLE requests (
        RequestID int unsigned NOT NULL AUTO_INCREMENT,
        RequestDate datetime NOT NULL DEFAULT NOW(),
        RequestDateDay date AS (CAST(RequestDate AS date)) VIRTUAL,
        CompanyID int unsigned NULL,  CompanyDel varchar(30) binary NOT NULL DEFAULT '',
        BillingEntityID int unsigned NULL,  BillingCompanyID int unsigned NULL,
        BillingCompanyDel varchar(30) binary NOT NULL DEFAULT '',
        BillingEntityDel varchar(30) binary NOT NULL DEFAULT '',
        Recipient varchar(100) NOT NULL,
        EmployeeID varchar(20) NOT NULL DEFAULT '',
        Department varchar(100) NOT NULL DEFAULT '',
        OrderNum varchar(20) NOT NULL DEFAULT '',
        CostCentre varchar(100) NOT NULL DEFAULT '',
        AwardName varchar(35) NOT NULL,
        AwardNameNSK varchar(65) NOT NULL.,
        ItemCode char(4) binary NOT NULL,
        CategoryID smallint signed NULL,
        ItemTitle varchar(50) NOT NULL,
        ItemOption varchar(50) NOT NULL,
        ItemAlert varchar(255) NOT NULL,
        ItemNotes text NULL,
        ItemAdditionalCost decimal(8,2) unsigned NOT NULL DEFAULT 0,
        ItemAdditionalDesc varchar(100) NOT NULL DEFAULT '',
        ItemAdditionalWeight mediumint unsigned NOT NULL DEFAULT 0,
        ItemSellPrice decimal(8,2) unsigned NOT NULL DEFAULT 0,
        DisplayedPrice decimal(8,2) unsigned NOT NULL DEFAULT 0,
        DisplayedPoints int unsigned NOT NULL DEFAULT 0,
        ItemPartsModified bit(1) NOT NULL DEFAULT 0,
        ItemDeleted bit(1) NOT NULL DEFAULT 0,
        ExtrasCode char(4) binary NOT NULL,
        ExtrasTitle varchar(50) NOT NULL,
        ExtrasAlert varchar(255) NOT NULL,
        ExtrasNotes text NULL,
        ExtrasAdditionalCost decimal(8,2) unsigned NOT NULL DEFAULT 0,
        ExtrasAdditionalDesc varchar(100) NOT NULL DEFAULT '',
        ExtrasAdditionalWeight mediumint unsigned NOT NULL DEFAULT 0.
        ExtrasSellPrice decimal(8,2) unsigned NOT NULL DEFAULT 0,
        ExtrasPartsModified bit(1) NOT NULL DEFAULT 0,
        ExtrasDeleted bit(1) NOT NULL DEFAULT 0,
        EngTypeID tinyint unsigned NOT NULL,
        EngCode char(4) NOT NULL DEFAULT '',
        EngCost decimal(8,2) unsigned NOT NULL DEFAULT 0,
        EngCostCustom bit(1) NOT NULL DEFAULT 0,
        EngCostIncurred decimal(8,2) unsigned NOT NULL DEFAULT 0,
        EngName varchar(100) NOT NULL DEFAULT '',
        EngYear decimal(4,0) unsigned NOT NULL DEFAULT 0,
        EngExtraDate decimal(4,0) unsigned zerofill NOT NULL DEFAULT 0,
        DeliveryAddress text NULL,
        PickAuth bit(1) NOT NULL DEFAULT 0,
        AuthLocHeading varchar(50) NOT NULL DEFAULT '',
        Delivery varchar(500) NOT NULL DEFAULT '',
        AuthoriserID int unsigned NOT NULL DEFAULT 0,
        RequiredBy date NOT NULL DEFAULT 0,
        CompanyNotes text NULL,
        CustomNotes text NULL,
        DeliveryInstructions text NULL,
        PSlipPrinted bit(1) NOT NULL DEFAULT 0,
        DispatchDate datetime NOT NULL DEFAULT 0,
        DispatchDateDay date AS (CAST(DispatchDate AS date)) VIRTUAL,
        ShippingWeight mediumint unsigned NOT NULL DEFAULT 0,
        CourierID tinyint unsigned NOT NULL DEFAULT 1,
        TrackingID varchar(30) NOT NULL DEFAULT '',
        FreightCost decimal(8,2) unsigned NOT NULL DEFAULT 0,
        FreightPrice decimal(8,2) unsigned NOT NULL DEFAULT 0,
        SellPrice decimal(8,2) unsigned AS (ItemSellPrice+ExtrasSellPrice) VIRTUAL,
        CreditPaymentOption bit(1) NOT NULL DEFAULT 0,
        InvoiceTS bigint NOT NULL DEFAULT 0,
        InvoiceDate date AS (IF(InvoiceTS>2,CAST(FROM_UNIXTIME(InvoiceTS>>4) AS date),IF(InvoiceTS=2,CAST(0 AS date),NULL))) VIRTUAL,
        InvoiceIdx int(7) unsigned zerofill AS (InvoiceTS-(UNIX_TIMESTAMP(InvoiceDate)<<4)) VIRTUAL,
        Status bit(7) NOT NULL DEFAULT 0,
        StatusCode tinyint unsigned AS (IF(InvoiceTS,IF(InvoiceTS=1,6,7),IF(DispatchDate,5^(Status&1),IF(Status&24,IF(Status&16,0,1),IF(Status&4,2,3))))) VIRTUAL,
        StatusFX bit(2) AS (IF(Status&96,IF(Status&64,2,1),0)) VIRTUAL,
        StatusConfirmed bit(1) AS (IF(Status&88,0,1)) VIRTUAL,
        StatusEngraverBatchable bit(1) AS (IF((Status BETWEEN 2 AND 3) AND EngTypeID>0 AND !DispatchDate AND !InvoiceTS,1,0)) VIRTUAL,
        StatusInvoiceable bit(1) AS (IF(!InvoiceTS AND DispatchDate AND !(Status&65),1,0)) VIRTUAL,
        LastLogEntry bigint NOT NULL DEFAULT 0,
        LastLogEntryTime datetime AS (FROM_UNIXTIME(FLOOR(LastLogEntry*0.001))) VIRTUAL,
        PRIMARY KEY (RequestID),
        KEY (CompanyID,CompanyDel,AwardName),
        KEY (CompanyID,CompanyDel,AwardNameNSK,AwardName),
        KEY (CompanyID,CompanyDel,StatusConfirmed,ItemCode,ItemDeleted),
        KEY (BillingCompanyID),
        KEY (BillingEntityID,BillingCompanyID,BillingEntityDel),
        KEY (BillingEntityID,BillingCompanyID,BillingCompanyDel,BillingEntityDel,StatusFX),
        KEY (Recipient),
        KEY (ItemCode,ItemDeleted),
        KEY (ExtrasCode,ExtrasDeleted),
        KEY (EngTypeID),
        KEY (AuthoriserID),
        KEY (CourierID),
        KEY (CategoryID),
        KEY (StatusEngraverBatchable),
        KEY (RequestDateDay),
        KEY (StatusFX),
        KEY (StatusCode,StatusFX),
        KEY (StatusConfirmed,ItemCode,ItemDeleted),
        KEY (StatusConfirmed,CompanyID,RequestDate),
        KEY (StatusConfirmed,CompanyID,AwardName,ItemCode,RequestDate),
        KEY (StatusInvoiceable,BillingEntityID,BillingCompanyID,BillingCompanyDel,BillingEntityDel,StatusFX,DispatchDateDay,CompanyID),
        KEY (InvoiceTS,CompanyID),
        KEY (InvoiceTS,BillingEntityID,BillingCompanyID,BillingCompanyDel,BillingEntityDel),
        FOREIGN KEY (CompanyID) REFERENCES companies (CompanyID) ON DELETE RESTRICT ON UPDATE CASCADE,
        FOREIGN KEY (BillingCompanyID) REFERENCES companies (CompanyID) ON DELETE RESTRICT ON UPDATE CASCADE,
        FOREIGN KEY (BillingEntityID) REFERENCES billingentities (BillingEntityID) ON DELETE RESTRICT ON UPDATE CASCADE,
        FOREIGN KEY (CategoryID) REFERENCES itemcategories (CategoryID) ON DELETE SET NULL ON UPDATE CASCADE,
        FOREIGN KEY (EngTypeID) REFERENCES engravingtypes (EngTypeID) ON DELETE RESTRICT ON UPDATE CASCADE,
        FOREIGN KEY (AuthoriserID) REFERENCES authorisers (AuthoriserID) ON DELETE RESTRICT ON UPDATE CASCADE,
        FOREIGN KEY (CourierID) REFERENCES couriers (CourierID) ON DELETE RESTRICT ON UPDATE CASCADE
      ) ENGINE=InnoDB;
      

      If I do EXPLAIN EXTENDED SELECT..., the result is:

      id = 1
      select_type = SIMPLE
      table = requests
      type = ref_or_null|filter
      possible_keys = StatusCode,CompanyID,CompanyID_2,CompanyID_3
      key = CompanyID_3|StatusCode
      key_len = 5|2
      ref = const
      rows = 2548 (1%)
      filtered = 0.00
      Extra = Using where; Using rowid filter

      I expect someone else will probably have reported the same bug already, but I couldn't find it, though these already-fixed bugs look potentially related:

      MDEV-18366
      MDEV-18486
      MDEV-26220

      I tried making a simplified test case, but couldn't reproduce the problem. I'll try again sometime in the next week or so, when I have more time, if no-one reports this bug as a duplicate of an existing one.

      Note that I already tried rebooting the server, and also running mariadb-check -r. Neither helped.

      Attachments

        Issue Links

          Activity

            alice Alice Sherepa added a comment -

            Is it possible for you to provide a dump of this table? You could upload it to ftp.askmonty.org/private, this way only MariaDB developers will have access to it.

            alice Alice Sherepa added a comment - Is it possible for you to provide a dump of this table? You could upload it to ftp.askmonty.org/private, this way only MariaDB developers will have access to it.
            Dean T Dean Trower added a comment -

            Sorry for the delay...
            I've now created a test case. Unzip and execute crashtest.sql from the attached zip file.
            This will create a table called `tbl`.

            To cause the crash/hang, you should then execute:

            SELECT RequestID,RequestDate FROM tbl WHERE (CompanyID=70 OR (CompanyID IS NULL AND CompanyDel='foo')) AND StatusCode<=1;
            

            Oddly, there are lots of columns and indexes in the test table that don't seem in any way related to the above query, but if you remove them, the crash no longer occurs. So I figure it has something to do with the size or layout of the table rows in memory or on disk?

            Dean T Dean Trower added a comment - Sorry for the delay... I've now created a test case. Unzip and execute crashtest.sql from the attached zip file. This will create a table called `tbl`. To cause the crash/hang, you should then execute: SELECT RequestID,RequestDate FROM tbl WHERE (CompanyID=70 OR (CompanyID IS NULL AND CompanyDel= 'foo' )) AND StatusCode<=1; Oddly, there are lots of columns and indexes in the test table that don't seem in any way related to the above query, but if you remove them, the crash no longer occurs. So I figure it has something to do with the size or layout of the table rows in memory or on disk?
            alice Alice Sherepa added a comment - - edited

            Thank you very much!
            I repeated on 10.4-10.11, while no crash on 11.0 and higher versions
            As a temporary workaround you could use set optimizer_switch='rowid_filter=off'; or upgrade to 11.0+ - I checked on 11.0.4 - no crash there.
            This is the same bug as MDEV-18911.

            MariaDB [test]> explain extended SELECT RequestID,RequestDate FROM tbl WHERE (CompanyID=70 OR (CompanyID IS NULL AND CompanyDel='foo')) AND StatusCode<=1;
            +------+-------------+-------+--------------------+----------------------+----------------------+---------+-------+-----------+----------+---------------------------------+
            | id   | select_type | table | type               | possible_keys        | key                  | key_len | ref   | rows      | filtered | Extra                           |
            +------+-------------+-------+--------------------+----------------------+----------------------+---------+-------+-----------+----------+---------------------------------+
            |    1 | SIMPLE      | tbl   | ref_or_null|filter | CompanyID,StatusCode | CompanyID|StatusCode | 5|2     | const | 2548 (1%) |     0.00 | Using where; Using rowid filter |
            +------+-------------+-------+--------------------+----------------------+----------------------+---------+-------+-----------+----------+---------------------------------+
            1 row in set, 1 warning (0,001 sec)
             
            Note (Code 1003): select `test`.`tbl`.`RequestID` AS `RequestID`,`test`.`tbl`.`RequestDate` AS `RequestDate` from `test`.`tbl` where (`test`.`tbl`.`CompanyID` = 70 or `test`.`tbl`.`CompanyDel` = 'foo' and `test`.`tbl`.`CompanyID` is null) and `test`.`tbl`.`StatusCode` <= 1
            MariaDB [test]> select version();
            +-----------------+
            | version()       |
            +-----------------+
            | 10.6.16-MariaDB |
            +-----------------+
            1 row in set (0,000 sec)
             
            MariaDB [test]>  SELECT RequestID,RequestDate FROM tbl WHERE (CompanyID=70 OR (CompanyID IS NULL AND CompanyDel='foo')) AND StatusCode<=1;
            ERROR 2013 (HY000): Lost connection to server during query
            ERROR 2006 (HY000): Server has gone away
            

            MariaDB [test]> SELECT RequestID,RequestDate FROM tbl WHERE (CompanyID=70 OR (CompanyID IS NULL AND CompanyDel='foo')) AND StatusCode<=1;
            Empty set (0,002 sec)
             
            MariaDB [test]> explain extended SELECT RequestID,RequestDate FROM tbl WHERE (CompanyID=70 OR (CompanyID IS NULL AND CompanyDel='foo')) AND StatusCode<=1;
            +------+-------------+-------+--------------+----------------------+----------------------+---------+------+----------+----------+---------------------------------+
            | id   | select_type | table | type         | possible_keys        | key                  | key_len | ref  | rows     | filtered | Extra                           |
            +------+-------------+-------+--------------+----------------------+----------------------+---------+------+----------+----------+---------------------------------+
            |    1 | SIMPLE      | tbl   | range|filter | CompanyID,StatusCode | StatusCode|CompanyID | 2|37    | NULL | 812 (1%) |     0.88 | Using where; Using rowid filter |
            +------+-------------+-------+--------------+----------------------+----------------------+---------+------+----------+----------+---------------------------------+
            1 row in set, 1 warning (0,001 sec)
             
            Note (Code 1003): select `test`.`tbl`.`RequestID` AS `RequestID`,`test`.`tbl`.`RequestDate` AS `RequestDate` from `test`.`tbl` where (`test`.`tbl`.`CompanyID` = 70 or `test`.`tbl`.`CompanyDel` = 'foo' and `test`.`tbl`.`CompanyID` is null) and `test`.`tbl`.`StatusCode` <= 1
             
            MariaDB [test]> select version();
            +----------------+
            | version()      |
            +----------------+
            | 11.0.4-MariaDB |
            +----------------+
            1 row in set (0,000 sec)
            

            Version: '10.6.17-MariaDB-debug-log'
            mariadbd: /10.6/src/storage/innobase/row/row0sel.cc:2934: void row_sel_field_store_in_mysql_format_func(byte*, const mysql_row_templ_t*, const dict_index_t*, ulint, const byte*, ulint): Assertion `(templ->is_virtual && !field) || (field && field->prefix_len ? field->prefix_len == len : templ->mysql_col_len == len)' failed.
            240124 11:22:22 [ERROR] mysqld got signal 6 ;
             
            /lib/x86_64-linux-gnu/libc.so.6(+0x33fd6)[0x7f01e13ddfd6]
            row/row0sel.cc:2938(row_sel_field_store_in_mysql_format_func(unsigned char*, mysql_row_templ_t const*, dict_index_t const*, unsigned long, unsigned char const*, unsigned long))[0x55611ea4b05a]
            row/row0sel.cc:3099(row_sel_store_mysql_field(unsigned char*, row_prebuilt_t*, unsigned char const*, dict_index_t const*, unsigned short const*, unsigned long, mysql_row_templ_t const*))[0x55611ea4c550]
            row/row0sel.cc:4063(row_search_idx_cond_check(unsigned char*, row_prebuilt_t*, unsigned char const*, unsigned short const*))[0x55611ea523b3]
            row/row0sel.cc:5470(row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long))[0x55611ea5ba1c]
            handler/ha_innodb.cc:9101(ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function))[0x55611e61a7db]
            sql/handler.h:3952(handler::index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function))[0x55611d9f7c91]
            sql/handler.cc:3521(handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function))[0x55611dbb5cbe]
            sql/sql_select.cc:22609(join_read_always_key(st_join_table*))[0x55611d45ff99]
            sql/sql_select.cc:23002(join_read_always_key_or_null(st_join_table*))[0x55611d4633f9]
            sql/sql_select.cc:21826(sub_select(JOIN*, st_join_table*, bool))[0x55611d45a5c8]
            sql/sql_select.cc:21352(do_select(JOIN*, Procedure*))[0x55611d4584da]
            sql/sql_select.cc:4882(JOIN::exec_inner())[0x55611d3e0da6]
            sql/sql_select.cc:4661(JOIN::exec())[0x55611d3de2a6]
            sql/sql_select.cc:5141(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*))[0x55611d3e27f9]
            sql/sql_select.cc:561(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55611d3b1b5f]
            sql/sql_parse.cc:6333(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55611d310f03]
            sql/sql_parse.cc:3963(mysql_execute_command(THD*, bool))[0x55611d2ff745]
            sql/sql_parse.cc:8100(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x55611d31c347]
            sql/sql_parse.cc:1898(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x55611d2f1d65]
            sql/sql_parse.cc:1409(do_command(THD*, bool))[0x55611d2eeaa6]
            sql/sql_connect.cc:1415(do_handle_one_connection(CONNECT*, bool))[0x55611d774e70]
            sql/sql_connect.cc:1319(handle_one_connection)[0x55611d7747cd]
            perfschema/pfs.cc:2203(pfs_spawn_thread)[0x55611e40ade2]
            nptl/pthread_create.c:478(start_thread)[0x7f01e18f8609]
             
            Query (0x62b0000c42a8): SELECT RequestID,RequestDate FROM tbl WHERE (CompanyID=70 OR (CompanyID IS NULL AND CompanyDel='foo')) AND StatusCode<=1
            

            while with a test from MDEV-18911:

            Version: '10.6.17-MariaDB-debug-log'  
            mariadbd: /10.6/src/storage/innobase/row/row0sel.cc:2934: void row_sel_field_store_in_mysql_format_func(byte*, const mysql_row_templ_t*, const dict_index_t*, ulint, const byte*, ulint): Assertion `(templ->is_virtual && !field) || (field && field->prefix_len ? field->prefix_len == len : templ->mysql_col_len == len)' failed.
            240124 12:46:42 [ERROR] mysqld got signal 6 ;
             
            /lib/x86_64-linux-gnu/libc.so.6(+0x33fd6)[0x7f82d7fe6fd6]
            row/row0sel.cc:2938(row_sel_field_store_in_mysql_format_func(unsigned char*, mysql_row_templ_t const*, dict_index_t const*, unsigned long, unsigned char const*, unsigned long))[0x56503868c05a]
            row/row0sel.cc:3099(row_sel_store_mysql_field(unsigned char*, row_prebuilt_t*, unsigned char const*, dict_index_t const*, unsigned short const*, unsigned long, mysql_row_templ_t const*))[0x56503868d550]
            row/row0sel.cc:4063(row_search_idx_cond_check(unsigned char*, row_prebuilt_t*, unsigned char const*, unsigned short const*))[0x5650386933b3]
            row/row0sel.cc:5470(row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long))[0x56503869ca1c]
            handler/ha_innodb.cc:9101(ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function))[0x56503825b7db]
            sql/handler.h:3952(handler::index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function))[0x565037638c91]
            sql/handler.cc:3521(handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function))[0x5650377f6a62]
            sql/sql_select.cc:22609(join_read_always_key(st_join_table*))[0x5650370a0f99]
            sql/sql_select.cc:21826(sub_select(JOIN*, st_join_table*, bool))[0x56503709b5c8]
            sql/sql_select.cc:22059(evaluate_join_record(JOIN*, st_join_table*, int))[0x56503709cebf]
            sql/sql_select.cc:21829(sub_select(JOIN*, st_join_table*, bool))[0x56503709b784]
            sql/sql_select.cc:21352(do_select(JOIN*, Procedure*))[0x5650370994da]
            sql/sql_select.cc:4882(JOIN::exec_inner())[0x565037021da6]
            sql/sql_select.cc:4661(JOIN::exec())[0x56503701f2a6]
            sql/sql_select.cc:5141(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*))[0x5650370237f9]
            sql/sql_select.cc:561(handle_select(THD*, LEX*, select_result*, unsigned long))[0x565036ff2b5f]
            sql/sql_parse.cc:6333(execute_sqlcom_select(THD*, TABLE_LIST*))[0x565036f51f03]
            sql/sql_parse.cc:3963(mysql_execute_command(THD*, bool))[0x565036f40745]
            sql/sql_parse.cc:8100(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x565036f5d347]
            sql/sql_parse.cc:1898(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x565036f32d65]
            sql/sql_parse.cc:1409(do_command(THD*, bool))[0x565036f2faa6]
            sql/sql_connect.cc:1415(do_handle_one_connection(CONNECT*, bool))[0x5650373b5e70]
            sql/sql_connect.cc:1319(handle_one_connection)[0x5650373b57cd]
            perfschema/pfs.cc:2203(pfs_spawn_thread)[0x56503804bde2]
            nptl/pthread_create.c:478(start_thread)[0x7f82d8501609]
             
            Query (0x62b0000c42a8): SELECT t1a.* FROM t1 AS t1a JOIN t1 AS t1b LEFT JOIN t2  ON (f = t1b.a) WHERE t1a.b >= 0 AND t1a.c = t1b.a
            

            alice Alice Sherepa added a comment - - edited Thank you very much! I repeated on 10.4-10.11, while no crash on 11.0 and higher versions As a temporary workaround you could use set optimizer_switch='rowid_filter=off'; or upgrade to 11.0+ - I checked on 11.0.4 - no crash there. This is the same bug as MDEV-18911 . MariaDB [test]> explain extended SELECT RequestID,RequestDate FROM tbl WHERE (CompanyID=70 OR (CompanyID IS NULL AND CompanyDel='foo')) AND StatusCode<=1; +------+-------------+-------+--------------------+----------------------+----------------------+---------+-------+-----------+----------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+--------------------+----------------------+----------------------+---------+-------+-----------+----------+---------------------------------+ | 1 | SIMPLE | tbl | ref_or_null|filter | CompanyID,StatusCode | CompanyID|StatusCode | 5|2 | const | 2548 (1%) | 0.00 | Using where; Using rowid filter | +------+-------------+-------+--------------------+----------------------+----------------------+---------+-------+-----------+----------+---------------------------------+ 1 row in set, 1 warning (0,001 sec)   Note (Code 1003): select `test`.`tbl`.`RequestID` AS `RequestID`,`test`.`tbl`.`RequestDate` AS `RequestDate` from `test`.`tbl` where (`test`.`tbl`.`CompanyID` = 70 or `test`.`tbl`.`CompanyDel` = 'foo' and `test`.`tbl`.`CompanyID` is null) and `test`.`tbl`.`StatusCode` <= 1 MariaDB [test]> select version(); +-----------------+ | version() | +-----------------+ | 10.6.16-MariaDB | +-----------------+ 1 row in set (0,000 sec)   MariaDB [test]> SELECT RequestID,RequestDate FROM tbl WHERE (CompanyID=70 OR (CompanyID IS NULL AND CompanyDel='foo')) AND StatusCode<=1; ERROR 2013 (HY000): Lost connection to server during query ERROR 2006 (HY000): Server has gone away MariaDB [test]> SELECT RequestID,RequestDate FROM tbl WHERE (CompanyID=70 OR (CompanyID IS NULL AND CompanyDel='foo')) AND StatusCode<=1; Empty set (0,002 sec)   MariaDB [test]> explain extended SELECT RequestID,RequestDate FROM tbl WHERE (CompanyID=70 OR (CompanyID IS NULL AND CompanyDel='foo')) AND StatusCode<=1; +------+-------------+-------+--------------+----------------------+----------------------+---------+------+----------+----------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +------+-------------+-------+--------------+----------------------+----------------------+---------+------+----------+----------+---------------------------------+ | 1 | SIMPLE | tbl | range|filter | CompanyID,StatusCode | StatusCode|CompanyID | 2|37 | NULL | 812 (1%) | 0.88 | Using where; Using rowid filter | +------+-------------+-------+--------------+----------------------+----------------------+---------+------+----------+----------+---------------------------------+ 1 row in set, 1 warning (0,001 sec)   Note (Code 1003): select `test`.`tbl`.`RequestID` AS `RequestID`,`test`.`tbl`.`RequestDate` AS `RequestDate` from `test`.`tbl` where (`test`.`tbl`.`CompanyID` = 70 or `test`.`tbl`.`CompanyDel` = 'foo' and `test`.`tbl`.`CompanyID` is null) and `test`.`tbl`.`StatusCode` <= 1   MariaDB [test]> select version(); +----------------+ | version() | +----------------+ | 11.0.4-MariaDB | +----------------+ 1 row in set (0,000 sec) Version: '10.6.17-MariaDB-debug-log' mariadbd: /10.6/src/storage/innobase/row/row0sel.cc:2934: void row_sel_field_store_in_mysql_format_func(byte*, const mysql_row_templ_t*, const dict_index_t*, ulint, const byte*, ulint): Assertion `(templ->is_virtual && !field) || (field && field->prefix_len ? field->prefix_len == len : templ->mysql_col_len == len)' failed. 240124 11:22:22 [ERROR] mysqld got signal 6 ;   /lib/x86_64-linux-gnu/libc.so.6(+0x33fd6)[0x7f01e13ddfd6] row/row0sel.cc:2938(row_sel_field_store_in_mysql_format_func(unsigned char*, mysql_row_templ_t const*, dict_index_t const*, unsigned long, unsigned char const*, unsigned long))[0x55611ea4b05a] row/row0sel.cc:3099(row_sel_store_mysql_field(unsigned char*, row_prebuilt_t*, unsigned char const*, dict_index_t const*, unsigned short const*, unsigned long, mysql_row_templ_t const*))[0x55611ea4c550] row/row0sel.cc:4063(row_search_idx_cond_check(unsigned char*, row_prebuilt_t*, unsigned char const*, unsigned short const*))[0x55611ea523b3] row/row0sel.cc:5470(row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long))[0x55611ea5ba1c] handler/ha_innodb.cc:9101(ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function))[0x55611e61a7db] sql/handler.h:3952(handler::index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function))[0x55611d9f7c91] sql/handler.cc:3521(handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function))[0x55611dbb5cbe] sql/sql_select.cc:22609(join_read_always_key(st_join_table*))[0x55611d45ff99] sql/sql_select.cc:23002(join_read_always_key_or_null(st_join_table*))[0x55611d4633f9] sql/sql_select.cc:21826(sub_select(JOIN*, st_join_table*, bool))[0x55611d45a5c8] sql/sql_select.cc:21352(do_select(JOIN*, Procedure*))[0x55611d4584da] sql/sql_select.cc:4882(JOIN::exec_inner())[0x55611d3e0da6] sql/sql_select.cc:4661(JOIN::exec())[0x55611d3de2a6] sql/sql_select.cc:5141(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*))[0x55611d3e27f9] sql/sql_select.cc:561(handle_select(THD*, LEX*, select_result*, unsigned long))[0x55611d3b1b5f] sql/sql_parse.cc:6333(execute_sqlcom_select(THD*, TABLE_LIST*))[0x55611d310f03] sql/sql_parse.cc:3963(mysql_execute_command(THD*, bool))[0x55611d2ff745] sql/sql_parse.cc:8100(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x55611d31c347] sql/sql_parse.cc:1898(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x55611d2f1d65] sql/sql_parse.cc:1409(do_command(THD*, bool))[0x55611d2eeaa6] sql/sql_connect.cc:1415(do_handle_one_connection(CONNECT*, bool))[0x55611d774e70] sql/sql_connect.cc:1319(handle_one_connection)[0x55611d7747cd] perfschema/pfs.cc:2203(pfs_spawn_thread)[0x55611e40ade2] nptl/pthread_create.c:478(start_thread)[0x7f01e18f8609]   Query (0x62b0000c42a8): SELECT RequestID,RequestDate FROM tbl WHERE (CompanyID=70 OR (CompanyID IS NULL AND CompanyDel='foo')) AND StatusCode<=1 while with a test from MDEV-18911 : Version: '10.6.17-MariaDB-debug-log' mariadbd: /10.6/src/storage/innobase/row/row0sel.cc:2934: void row_sel_field_store_in_mysql_format_func(byte*, const mysql_row_templ_t*, const dict_index_t*, ulint, const byte*, ulint): Assertion `(templ->is_virtual && !field) || (field && field->prefix_len ? field->prefix_len == len : templ->mysql_col_len == len)' failed. 240124 12:46:42 [ERROR] mysqld got signal 6 ;   /lib/x86_64-linux-gnu/libc.so.6(+0x33fd6)[0x7f82d7fe6fd6] row/row0sel.cc:2938(row_sel_field_store_in_mysql_format_func(unsigned char*, mysql_row_templ_t const*, dict_index_t const*, unsigned long, unsigned char const*, unsigned long))[0x56503868c05a] row/row0sel.cc:3099(row_sel_store_mysql_field(unsigned char*, row_prebuilt_t*, unsigned char const*, dict_index_t const*, unsigned short const*, unsigned long, mysql_row_templ_t const*))[0x56503868d550] row/row0sel.cc:4063(row_search_idx_cond_check(unsigned char*, row_prebuilt_t*, unsigned char const*, unsigned short const*))[0x5650386933b3] row/row0sel.cc:5470(row_search_mvcc(unsigned char*, page_cur_mode_t, row_prebuilt_t*, unsigned long, unsigned long))[0x56503869ca1c] handler/ha_innodb.cc:9101(ha_innobase::index_read(unsigned char*, unsigned char const*, unsigned int, ha_rkey_function))[0x56503825b7db] sql/handler.h:3952(handler::index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function))[0x565037638c91] sql/handler.cc:3521(handler::ha_index_read_map(unsigned char*, unsigned char const*, unsigned long, ha_rkey_function))[0x5650377f6a62] sql/sql_select.cc:22609(join_read_always_key(st_join_table*))[0x5650370a0f99] sql/sql_select.cc:21826(sub_select(JOIN*, st_join_table*, bool))[0x56503709b5c8] sql/sql_select.cc:22059(evaluate_join_record(JOIN*, st_join_table*, int))[0x56503709cebf] sql/sql_select.cc:21829(sub_select(JOIN*, st_join_table*, bool))[0x56503709b784] sql/sql_select.cc:21352(do_select(JOIN*, Procedure*))[0x5650370994da] sql/sql_select.cc:4882(JOIN::exec_inner())[0x565037021da6] sql/sql_select.cc:4661(JOIN::exec())[0x56503701f2a6] sql/sql_select.cc:5141(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*))[0x5650370237f9] sql/sql_select.cc:561(handle_select(THD*, LEX*, select_result*, unsigned long))[0x565036ff2b5f] sql/sql_parse.cc:6333(execute_sqlcom_select(THD*, TABLE_LIST*))[0x565036f51f03] sql/sql_parse.cc:3963(mysql_execute_command(THD*, bool))[0x565036f40745] sql/sql_parse.cc:8100(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x565036f5d347] sql/sql_parse.cc:1898(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool))[0x565036f32d65] sql/sql_parse.cc:1409(do_command(THD*, bool))[0x565036f2faa6] sql/sql_connect.cc:1415(do_handle_one_connection(CONNECT*, bool))[0x5650373b5e70] sql/sql_connect.cc:1319(handle_one_connection)[0x5650373b57cd] perfschema/pfs.cc:2203(pfs_spawn_thread)[0x56503804bde2] nptl/pthread_create.c:478(start_thread)[0x7f82d8501609]   Query (0x62b0000c42a8): SELECT t1a.* FROM t1 AS t1a JOIN t1 AS t1b LEFT JOIN t2 ON (f = t1b.a) WHERE t1a.b >= 0 AND t1a.c = t1b.a

            People

              Unassigned Unassigned
              Dean T Dean Trower
              Votes:
              0 Vote for this issue
              Watchers:
              3 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.