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

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

    XMLWordPrintable

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

            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.