Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.4.10, 10.4.11, 10.4(EOL)
-
None
-
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
Issue Links
- relates to
-
-
- Closed
-
-
-
- Closed
-
-
-
- Closed
-