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

ERROR 1032 Can't find record when running simple, single-table query

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Closed (View Workflow)
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 10.4.10, 10.4.11, 10.4
    • Fix Version/s: 10.4.12, 10.5.1
    • Component/s: Optimizer
    • Labels:
      None
    • Environment:
      Linux

      Description

      We have a data-dependent hence non easily reproducible error on several tables, that results in a

      ERROR 1032 (HY000): Can't find record in 'contact'
      

      It started happening when we upgraded from 10.3.16 to 10.4.10 and is still active after we upgraded to 10.4.11.

      It does resemble another issue that we saw earlier but (??) could not find when writing this, that implied a wrong execution plan but was supposedly fixed in 10.4.11.

      We caught one that we could reproduce, at least a few times, here's what we thought of gathering. We have enough traces that we can possibly catch some more if needed and gather more data.

      explain extended SELECT id FROM contact  WHERE 1=1  AND (first_name IS NOT NULL OR last_name IS NOT NULL OR email IS NOT NULL OR email_private IS NOT NULL OR phone IS NOT NULL OR mobile_phone IS NOT NULL) AND id != **redacted** AND (company_id =  **redacted**) AND (first_name = '**redacted**') AND (last_name = '**redacted**') AND email = '**redacted**' AND (gender = '**redacted**' OR gender IS NULL) AND (country_id = **redacted**) AND is_public=1 AND client_id IS NULL
      --------------
       
      +------+-------------+---------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+------+--------+----------+----------------------------------------------------------------------------+
      | id   | select_type | table   | type         | possible_keys                                                                                                                                                                                                                                                                                                                                                                                                          | key                     | key_len | ref  | rows   | filtered | Extra                                                                      |
      +------+-------------+---------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+------+--------+----------+----------------------------------------------------------------------------+
      |    1 | SIMPLE      | contact | range|filter | PRIMARY,contact_company_id_company_id,ixFirst_name,ixLast_name,ixPhone,ixGender,ixFirstName,contact_country,ixCompanyActive,ixCompanyActiveCreatedAt,ixCompanyFunction,ixIdActiveCreatedAt,ixPublic,ixEmailPrivate,ixCompanyActivePublicCountry,ixCompanyActivePublic,ixCountry,ixIdActive,ixEmail,ixIdActiveCompany,ixFirstNameLastName,ixClient,ixFirstNameGenderActivePublic,ixFirstLastEmailActive,ixEmailFullText | ixEmail|ixCompanyActive | 776|9   | NULL | 2 (0%) |     0.23 | Using index condition; Using where; Rowid-ordered scan; Using rowid filter |
      +------+-------------+---------+--------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-------------------------+---------+------+--------+----------+----------------------------------------------------------------------------+
      

      and here is the JSON version

      {
        "query_block": {
          "select_id": 1,
          "table": {
            "table_name": "contact",
            "access_type": "range",
            "possible_keys": [
              "PRIMARY",
              "contact_company_id_company_id",
              "ixFirst_name",
              "ixLast_name",
              "ixPhone",
              "ixGender",
              "ixFirstName",
              "contact_country",
              "ixCompanyActive",
              "ixCompanyActiveCreatedAt",
              "ixCompanyFunction",
              "ixIdActiveCreatedAt",
              "ixPublic",
              "ixEmailPrivate",
              "ixCompanyActivePublicCountry",
              "ixCompanyActivePublic",
              "ixCountry",
              "ixIdActive",
              "ixEmail",
              "ixIdActiveCompany",
              "ixFirstNameLastName",
              "ixClient",
              "ixFirstNameGenderActivePublic",
              "ixFirstLastEmailActive",
              "ixEmailFullText"
            ],
            "key": "ixEmail",
            "key_length": "776",
            "used_key_parts": ["email", "id"],
            "rowid_filter": {
              "range": {
                "key": "ixCompanyActive",
                "used_key_parts": ["company_id"]
              },
              "rows": 14,
              "selectivity_pct": 1.1e-4
            },
            "rows": 2,
            "filtered": 0.2299,
            "index_condition": "contact.`id` <> **redacted** and contact.email = '**redacted**'",
            "attached_condition": "contact.company_id = **redacted** and contact.country_id = **redacted** and contact.is_public = 1 and (contact.first_name is not null or contact.last_name is not null or contact.email is not null or contact.email_private is not null or contact.phone is not null or contact.mobile_phone is not null) and contact.first_name = '**redacted**' and contact.last_name = '**redacted**' and (contact.gender = '**redacted**' or contact.gender is null) and contact.client_id is null",
            "mrr_type": "Rowid-ordered scan"
          }
        }
      }
      

        Attachments

        1. create_minified_table.sql.gz
          1.08 MB
          Christophe Migliorini
        2. failing_domain.txt
          20 kB
          Antoine MARTIN

          Issue Links

            Activity

              People

              Assignee:
              igor Igor Babaev
              Reporter:
              c_migliorini Christophe Migliorini
              Votes:
              1 Vote for this issue
              Watchers:
              6 Start watching this issue

                Dates

                Created:
                Updated:
                Resolved: