[MDEV-21356] ERROR 1032 Can't find record when running simple, single-table query Created: 2019-12-18  Updated: 2020-02-19  Resolved: 2020-01-19

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.4.10, 10.4.11, 10.4
Fix Version/s: 10.4.12, 10.5.1

Type: Bug Priority: Major
Reporter: Christophe Migliorini Assignee: Igor Babaev
Resolution: Fixed Votes: 1
Labels: None
Environment:

Linux


Attachments: File create_minified_table.sql.gz     Text File failing_domain.txt    
Issue Links:
Relates
relates to MDEV-21446 Assertion `!prebuilt->index->is_prima... Closed
relates to MDEV-19919 Assertion `!prebuilt->index->is_prima... Closed
relates to MDEV-20056 Assertion `!prebuilt->index->is_prima... Closed

 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"
 
    }
 
  }
 
}



 Comments   
Comment by Elena Stepanova [ 2019-12-29 ]

If it's data-dependent, it should be reproducible with the same data. Is it not the case?

Comment by Christophe Migliorini [ 2019-12-30 ]

Well, yes, but the table has:

  • 39 columns, 15M rows
  • 15M rows
  • 42 indexes
  • and last but not least contains RGPD-sensitive data.

Also, we have indeed been able to catch the above case case and reproduce it enough times to produce diagnosis data, but:

  • table is live, so it changes all the time
  • if I replay this case now, as I just did, the query runs fine and returns 0 row without error

As a consequence, and although I'm really willing to help produce a test case, I don't have a clue where to start.

Comment by Elena Stepanova [ 2020-01-12 ]

Is it InnoDB or MyISAM or any other engine?

Comment by Christophe Migliorini [ 2020-01-13 ]

@Elena, InnoDB it is (all our tables are), and we're working to give you a test case – we have another table that is impacted (we have at least 3) and seems easier to work with in that respect.

Comment by Christophe Migliorini [ 2020-01-14 ]

We have the issue on a "dead" table that we can share, and we were able to find test cases on it.

TL;DR

Table DDL is:

CREATE TABLE `event_whois_domain` (
 
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 
  `domain` varchar(255) NOT NULL,
 
  `registrant_name` varchar(255) DEFAULT NULL,
 
  `registrant_organization` varchar(255) DEFAULT NULL,
 
  `registrant_street1` varchar(255) DEFAULT NULL,
 
  `registrant_street2` varchar(255) DEFAULT NULL,
 
  `registrant_street3` varchar(255) DEFAULT NULL,
 
  `registrant_street4` varchar(255) DEFAULT NULL,
 
  `registrant_street5` varchar(255) DEFAULT NULL,
 
  `registrant_city` varchar(255) DEFAULT NULL,
 
  `registrant_postal_code` varchar(255) DEFAULT NULL,
 
  `registrant_country` varchar(255) DEFAULT NULL,
 
  `registrant_email` varchar(255) DEFAULT NULL,
 
  `registrant_telephone` varchar(255) DEFAULT NULL,
 
  `administrative_name` varchar(255) DEFAULT NULL,
 
  `administrative_organization` varchar(255) DEFAULT NULL,
 
  `administrative_street1` varchar(255) DEFAULT NULL,
 
  `administrative_street2` varchar(255) DEFAULT NULL,
 
  `administrative_street3` varchar(255) DEFAULT NULL,
 
  `administrative_street4` varchar(255) DEFAULT NULL,
 
  `administrative_street5` varchar(255) DEFAULT NULL,
 
  `administrative_city` varchar(255) DEFAULT NULL,
 
  `administrative_postal_code` varchar(255) DEFAULT NULL,
 
  `administrative_country` varchar(255) DEFAULT NULL,
 
  `administrative_email` varchar(255) DEFAULT NULL,
 
  `administrative_telephone` varchar(255) DEFAULT NULL,
 
  `technical_name` varchar(255) DEFAULT NULL,
 
  `technical_organization` varchar(255) DEFAULT NULL,
 
  `technical_street1` varchar(255) DEFAULT NULL,
 
  `technical_street2` varchar(255) DEFAULT NULL,
 
  `technical_street3` varchar(255) DEFAULT NULL,
 
  `technical_street4` varchar(255) DEFAULT NULL,
 
  `technical_street5` varchar(255) DEFAULT NULL,
 
  `technical_city` varchar(255) DEFAULT NULL,
 
  `technical_postal_code` varchar(255) DEFAULT NULL,
 
  `technical_country` varchar(255) DEFAULT NULL,
 
  `technical_email` varchar(255) DEFAULT NULL,
 
  `technical_telephone` varchar(255) DEFAULT NULL,
 
  `json` longblob NOT NULL,
 
  `timestamp` timestamp NOT NULL DEFAULT current_timestamp(),
 
  PRIMARY KEY (`id`),
 
  KEY `ixEventWhoisDomainDomain` (`domain`),
 
  KEY `ixEventWhoisDomainTimestamp` (`timestamp`)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8

From my.cnf:

[mariadb]
 
# engine independent statistics and optimizer settings
 
use_stat_tables="PREFERABLY"
 
optimizer_switch="materialization=default,semijoin=default,mrr=on,mrr_sort_keys=on"
 
optimizer_use_condition_selectivity=4
 
join_cache_level=6
 
histogram_size=255
 
histogram_type=DOUBLE_PREC_HB

When we run this query (note that ALL our records are over a month old) for domain.tld

SELECT *
 
FROM event.event_whois_domain
 
WHERE 1 = 1 AND domain = 'domain.tld' AND timestamp >= DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -1 MONTH)
 
ORDER BY timestamp DESC;

In some cases we get an execution plan of this form:

{
 
  "query_block": {
 
    "select_id": 1,
 
    "read_sorted_file": {
 
      "filesort": {
 
        "sort_key": "event_whois_domain.`timestamp` desc",
 
        "table": {
 
          "table_name": "event_whois_domain",
 
          "access_type": "ref",
 
          "possible_keys": [
 
            "ixEventWhoisDomainDomain",
 
            "ixEventWhoisDomainTimestamp"
 
          ],
 
          "key": "ixEventWhoisDomainDomain",
 
          "key_length": "767",
 
          "used_key_parts": ["domain"],
 
          "ref": ["const"],
 
          "rowid_filter": {
 
            "range": {
 
              "key": "ixEventWhoisDomainTimestamp",
 
              "used_key_parts": ["timestamp"]
 
            },
 
            "rows": 1,
 
            "selectivity_pct": 0.0033
 
          },
 
          "rows": 2,
 
          "filtered": 0.0033,
 
          "index_condition": "event_whois_domain.domain = 'domain.tld'",
 
          "attached_condition": "event_whois_domain.domain <=> 'domain.tld' and event_whois_domain.`timestamp` >= <cache>(current_timestamp() + interval -1 month)"
 
        }
 
      }
 
    }
 
  }
 
}

which gives...

[HY000][1032] (conn=45) Can't find record in 'event_whois_domain'

Test data

We have determined that with our initial data set (30k rows):

  • all domains that have at least 3 or more rows (980 domains total) fail
  • no domain associated with 1 row (20386 domains total) fail
  • among the remaining domains, associated with 2 rows (3294 domains total), 105 queries fail.

Now, we reduced our data set to include only those that have 2 entries or more, and now some domains having more than 3 entries actually work. This is the data set we're sending shortly.

Comment by Christophe Migliorini [ 2020-01-14 ]

Test Data (continued)

With the reduced data set attached ( create_minified_table.sql.gz ), which has 9945 rows, we have 3178 unique domains failing.

Sample test cases

  • www.zyxel.com.tw has 2 entries and fails
  • www.aamratechnologies.com has 2 entries and works fine
  • www.mailhost.i-dev.fr has 6 entries and works fine
Comment by Christophe Migliorini [ 2020-01-14 ]

Earlier uploaded file updated – wrong one.

Also, we noted that we could

  • reproduce the issue on Docker image "mariadb:10.4.11-bionic"
  • mitigate the issue on said Docker image by removing optimizer_switch="materialization=default,semijoin=default,mrr=on,mrr_sort_keys=on" in the my.cnf file
Comment by Christophe Migliorini [ 2020-01-14 ]

Based on our tests, the above is 100% deterministic and rather simple in comparison to related issues, we do hope this will help solving them.

As far as we are concerned we will presumably change optimiser settings, as this issue occurs several dozen times per day in production on way more complex tables, so we don't have much choice.

Comment by Christophe Migliorini [ 2020-01-15 ]

/!\ We also found that the problem never arises when rowid_filter is set to off, all other things being equal.

Comment by Elena Stepanova [ 2020-01-15 ]

c_migliorini,
Thanks for all the work you've been doing for this.

Is there anything special I need to do to reproduce it, for example with mariadb:10.4.11-bionic container? It does not seem to be happening to me.

$ sudo docker pull mariadb:10.4.11-bionic
 
10.4.11-bionic: Pulling from library/mariadb
 
Digest: <...>
 
Status: Image is up to date for mariadb:10.4.11-bionic
 
docker.io/library/mariadb:10.4.11-bionic
 
$ sudo docker run --name mdev21356 -e MYSQL_ROOT_PASSWORD=mypass -d mariadb:10.4.11-bionic
 
0ab934b49818ab356464b18d43bf1478020e91278d412235bb39eb5b14b0315a
 
$ sudo docker ps
 
CONTAINER ID        IMAGE                    COMMAND                  CREATED             STATUS              PORTS               NAMES
 
0ab934b49818        mariadb:10.4.11-bionic   "docker-entrypoint.s…"   5 seconds ago       Up 4 seconds        3306/tcp            mdev21356
 
$ sudo docker cp /data/tmp/create_minified_table.sql 0ab934b49818:/
 
$ sudo docker exec -it mdev21356 bash
 
root@0ab934b49818:/# mysql -uroot -pmypass < create_minified_table.sql
 
root@0ab934b49818:/# 
root@0ab934b49818:/# mysql -uroot -pmypass 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
 
Your MariaDB connection id is 10
 
Server version: 10.4.11-MariaDB-1:10.4.11+maria~bionic mariadb.org binary distribution
 
 
 
Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.
 
 
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
 
 
MariaDB [(none)]> SELECT * FROM event.event_whois_domain_minified WHERE 1 = 1 AND domain = 'domain.tld' AND timestamp >= DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -1 MONTH) ORDER BY timestamp DESC;
 
Empty set (0.001 sec)
 
 
 
MariaDB [(none)]>

Comment by Antoine MARTIN [ 2020-01-15 ]

Yes, you need to create a folder called config, and inside this folder, create the file my.cnf

[mariadb]
 
# engine independent statistics and optimizer settings
 
use_stat_tables="PREFERABLY"
 
optimizer_switch="materialization=default,semijoin=default,mrr=on,mrr_sort_keys=on"
 
optimizer_use_condition_selectivity=4
 
join_cache_level=6
 
histogram_size=255
 
histogram_type=DOUBLE_PREC_HB

Then, you can use the following docker run commands with binding of configuration (you've already the good image/tag)

sudo docker run --name mdev21356 -e MYSQL_ROOT_PASSWORD=mypass -v "$(pwd)/config:/etc/mysql/conf.d" -d mariadb:10.4.11-bionic

To reproduce the issue, you need to run the following query, because the issue isn't present on all domain...
Failing query :

SELECT *
 
FROM event.event_whois_domain
 
WHERE 1 = 1 AND domain = 'www.zyxel.com.tw' AND timestamp >= DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -1 MONTH)
 
ORDER BY timestamp DESC;

Non failing queries :

SELECT *
 
FROM event.event_whois_domain
 
WHERE 1 = 1 AND domain = 'www.aamratechnologies.com' AND timestamp >= DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -1 MONTH)
 
ORDER BY timestamp DESC;


SELECT *
 
FROM event.event_whois_domain
 
WHERE 1 = 1 AND domain = 'www.mailhost.i-dev.fr' AND timestamp >= DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -1 MONTH)
 
ORDER BY timestamp DESC;

In the following attachment (failing_domain.txt ), you have all the failing domains

Comment by Elena Stepanova [ 2020-01-15 ]

Thanks. Domain ID was indeed the reason why it wasn't reproducible for me. It is now.

Comment by Elena Stepanova [ 2020-01-15 ]

Here is the test case reduced even further. It can be run via MTR with default MTR settings, or MySQL client on a server with default server options.

--source include/have_innodb.inc
 
 
 
CREATE TABLE event_whois_domain_minified (
 
  id int(11) unsigned NOT NULL AUTO_INCREMENT,
 
  domain varchar(255) NOT NULL,
 
  registrant_name varchar(255) DEFAULT NULL,
 
  registrant_organization varchar(255) DEFAULT NULL,
 
  registrant_street1 varchar(255) DEFAULT NULL,
 
  registrant_street2 varchar(255) DEFAULT NULL,
 
  registrant_street3 varchar(255) DEFAULT NULL,
 
  registrant_street4 varchar(255) DEFAULT NULL,
 
  registrant_street5 varchar(255) DEFAULT NULL,
 
  registrant_city varchar(255) DEFAULT NULL,
 
  registrant_postal_code varchar(255) DEFAULT NULL,
 
  registrant_country varchar(255) DEFAULT NULL,
 
  registrant_email varchar(255) DEFAULT NULL,
 
  registrant_telephone varchar(255) DEFAULT NULL,
 
  administrative_name varchar(255) DEFAULT NULL,
 
  administrative_organization varchar(255) DEFAULT NULL,
 
  administrative_street1 varchar(255) DEFAULT NULL,
 
  administrative_street2 varchar(255) DEFAULT NULL,
 
  administrative_street3 varchar(255) DEFAULT NULL,
 
  administrative_street4 varchar(255) DEFAULT NULL,
 
  administrative_street5 varchar(255) DEFAULT NULL,
 
  administrative_city varchar(255) DEFAULT NULL,
 
  administrative_postal_code varchar(255) DEFAULT NULL,
 
  administrative_country varchar(255) DEFAULT NULL,
 
  administrative_email varchar(255) DEFAULT NULL,
 
  administrative_telephone varchar(255) DEFAULT NULL,
 
  technical_name varchar(255) DEFAULT NULL,
 
  technical_organization varchar(255) DEFAULT NULL,
 
  technical_street1 varchar(255) DEFAULT NULL,
 
  technical_street2 varchar(255) DEFAULT NULL,
 
  technical_street3 varchar(255) DEFAULT NULL,
 
  technical_street4 varchar(255) DEFAULT NULL,
 
  technical_street5 varchar(255) DEFAULT NULL,
 
  technical_city varchar(255) DEFAULT NULL,
 
  technical_postal_code varchar(255) DEFAULT NULL,
 
  technical_country varchar(255) DEFAULT NULL,
 
  technical_email varchar(255) DEFAULT NULL,
 
  technical_telephone varchar(255) DEFAULT NULL,
 
  json longblob NOT NULL,
 
  timestamp timestamp NOT NULL DEFAULT current_timestamp(),
 
  PRIMARY KEY (id),
 
  KEY ixEventWhoisDomainDomain (domain),
 
  KEY ixEventWhoisDomainTimestamp (timestamp)
 
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
 
 
 
INSERT INTO event_whois_domain_minified (id, domain, registrant_name, registrant_organization, registrant_street1, registrant_street2, registrant_street3, registrant_street4, registrant_street5, registrant_city, registrant_postal_code, registrant_country, registrant_email, registrant_telephone, administrative_name, administrative_organization, administrative_street1, administrative_street2, administrative_street3, administrative_street4, administrative_street5, administrative_city, administrative_postal_code, administrative_country, administrative_email, administrative_telephone, technical_name, technical_organization, technical_street1, technical_street2, technical_street3, technical_street4, technical_street5, technical_city, technical_postal_code, technical_country, technical_email, technical_telephone, json, timestamp) VALUES
 
(60380, 'www.mailhost.i-dev.fr', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, '', '2016-12-22 09:18:28'),
 
(60383, 'www.bestwestern.fr', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, '', '2016-12-22 09:27:06'),
 
(80392, 'www.dfinitions.fr', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, '', '2017-01-30 08:02:01'),
 
(80407, 'www.firma.o2.pl', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, 'AZ.pl Sp. z o.o.', 'Al. Papieża Jana Pawła II 19/2', null, null, null, null, '70-453 Szczecin', null, 'POLAND', null, '48914243780', '', '2017-01-30 08:24:51'),
 
(80551, 'www.mailhost.i-dev.fr', null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, null, '', '2017-01-30 10:00:56'),
 
(80560, 'www.blackmer-mouvex.com', 'MARIE-PIERRE PRODEAU', 'MOUVEX', '2 RUE DES CAILLOTES', null, null, null, null, 'AUXERRE', '89000', 'FRANCE', 'PRODEAU@MOUVEX.COM', null, 'MARIE-PIERRE PRODEAU', 'MOUVEX', '2 RUE DES CAILLOTES', null, null, null, null, 'AUXERRE', '89000', 'FRANCE', 'PRODEAU@MOUVEX.COM', '33 386498630', 'LAURENT SOUCHELEAU', 'MOUVEX', '2 RUE DES CAILLOTES', null, null, null, null, 'AUXERRE', '89000', 'FRANCE', 'SOUCHELEAU@MOUVEX.COM', '33 386498643', '', '2017-01-30 10:04:38'),
 
(80566, 'www.inup.com', 'MAXIMILIAN V. KETELHODT', null, 'SUELZBURGSTRASSE 158A', null, null, null, null, 'KOELN', '50937', 'GERMANY', 'ICANN@EXPIRES-2009.WEBCARE24.COM', '492214307580', 'MAXIMILIAN V. KETELHODT', null, 'SUELZBURGSTRASSE 158A', null, null, null, null, 'KOELN', '50937', 'GERMANY', 'ICANN@EXPIRES-2009.WEBCARE24.COM', '492214307580', 'MAXIMILIAN V. KETELHODT', null, 'SUELZBURGSTRASSE 158A', null, null, null, null, 'KOELN', '50937', 'GERMANY', 'ICANN@EXPIRES-2009.WEBCARE24.COM', '492214307580', '', '2017-01-30 10:08:29');
 
 
 
SET optimizer_switch='mrr=on,mrr_sort_keys=on';
 
SELECT * FROM event_whois_domain_minified WHERE 1 = 1 AND domain = 'www.mailhost.i-dev.fr' AND timestamp >= DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -1 MONTH) ORDER BY timestamp DESC;

10.4 2d4b6571 non-debug build

 
MariaDB [test]> SELECT * FROM event_whois_domain_minified WHERE 1 = 1 AND domain = 'www.mailhost.i-dev.fr' AND timestamp >= DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -1 MONTH) ORDER BY timestamp DESC;
 
ERROR 1032 (HY000): Can't find record in 'event_whois_domain_minified'

Execution plan

 
MariaDB [test]> EXPLAIN EXTENDED SELECT * FROM event_whois_domain_minified WHERE 1 = 1 AND domain = 'www.mailhost.i-dev.fr' AND timestamp >= DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -1 MONTH) ORDER BY timestamp DESC;
 
+------+-------------+-----------------------------+------------+------------------------------------------------------+------------------------------------------------------+---------+-------+---------+----------+------------------------------------------------------------------------+
 
| id   | select_type | table                       | type       | possible_keys                                        | key                                                  | key_len | ref   | rows    | filtered | Extra                                                                  |
 
+------+-------------+-----------------------------+------------+------------------------------------------------------+------------------------------------------------------+---------+-------+---------+----------+------------------------------------------------------------------------+
 
|    1 | SIMPLE      | event_whois_domain_minified | ref|filter | ixEventWhoisDomainDomain,ixEventWhoisDomainTimestamp | ixEventWhoisDomainDomain|ixEventWhoisDomainTimestamp | 767|4   | const | 2 (14%) |    14.29 | Using index condition; Using where; Using filesort; Using rowid filter |
 
+------+-------------+-----------------------------+------------+------------------------------------------------------+------------------------------------------------------+---------+-------+---------+----------+------------------------------------------------------------------------+
 
1 row in set, 1 warning (0.001 sec)
 
 
 
MariaDB [test]> show warnings;
 
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Level | Code | Message                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                          |
 
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
| Note  | 1003 | select `test`.`event_whois_domain_minified`.`id` AS `id`,`test`.`event_whois_domain_minified`.`domain` AS `domain`,`test`.`event_whois_domain_minified`.`registrant_name` AS `registrant_name`,`test`.`event_whois_domain_minified`.`registrant_organization` AS `registrant_organization`,`test`.`event_whois_domain_minified`.`registrant_street1` AS `registrant_street1`,`test`.`event_whois_domain_minified`.`registrant_street2` AS `registrant_street2`,`test`.`event_whois_domain_minified`.`registrant_street3` AS `registrant_street3`,`test`.`event_whois_domain_minified`.`registrant_street4` AS `registrant_street4`,`test`.`event_whois_domain_minified`.`registrant_street5` AS `registrant_street5`,`test`.`event_whois_domain_minified`.`registrant_city` AS `registrant_city`,`test`.`event_whois_domain_minified`.`registrant_postal_code` AS `registrant_postal_code`,`test`.`event_whois_domain_minified`.`registrant_country` AS `registrant_country`,`test`.`event_whois_domain_minified`.`registrant_email` AS `registrant_email`,`test`.`event_whois_domain_minified`.`registrant_telephone` AS `registrant_telephone`,`test`.`event_whois_domain_minified`.`administrative_name` AS `administrative_name`,`test`.`event_whois_domain_minified`.`administrative_organization` AS `administrative_organization`,`test`.`event_whois_domain_minified`.`administrative_street1` AS `administrative_street1`,`test`.`event_whois_domain_minified`.`administrative_street2` AS `administrative_street2`,`test`.`event_whois_domain_minified`.`administrative_street3` AS `administrative_street3`,`test`.`event_whois_domain_minified`.`administrative_street4` AS `administrative_street4`,`test`.`event_whois_domain_minified`.`administrative_street5` AS `administrative_street5`,`test`.`event_whois_domain_minified`.`administrative_city` AS `administrative_city`,`test`.`event_whois_domain_minified`.`administrative_postal_code` AS `administrative_postal_code`,`test`.`event_whois_domain_minified`.`administrative_country` AS `administrative_country`,`test`.`event_whois_domain_minified`.`administrative_email` AS `administrative_email`,`test`.`event_whois_domain_minified`.`administrative_telephone` AS `administrative_telephone`,`test`.`event_whois_domain_minified`.`technical_name` AS `technical_name`,`test`.`event_whois_domain_minified`.`technical_organization` AS `technical_organization`,`test`.`event_whois_domain_minified`.`technical_street1` AS `technical_street1`,`test`.`event_whois_domain_minified`.`technical_street2` AS `technical_street2`,`test`.`event_whois_domain_minified`.`technical_street3` AS `technical_street3`,`test`.`event_whois_domain_minified`.`technical_street4` AS `technical_street4`,`test`.`event_whois_domain_minified`.`technical_street5` AS `technical_street5`,`test`.`event_whois_domain_minified`.`technical_city` AS `technical_city`,`test`.`event_whois_domain_minified`.`technical_postal_code` AS `technical_postal_code`,`test`.`event_whois_domain_minified`.`technical_country` AS `technical_country`,`test`.`event_whois_domain_minified`.`technical_email` AS `technical_email`,`test`.`event_whois_domain_minified`.`technical_telephone` AS `technical_telephone`,`test`.`event_whois_domain_minified`.`json` AS `json`,`test`.`event_whois_domain_minified`.`timestamp` AS `timestamp` from `test`.`event_whois_domain_minified` where `test`.`event_whois_domain_minified`.`domain` = 'www.mailhost.i-dev.fr' and `test`.`event_whois_domain_minified`.`timestamp` >= <cache>(current_timestamp() + interval -1 month) order by `test`.`event_whois_domain_minified`.`timestamp` desc |
 
+-------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
 
1 row in set (0.000 sec)

On a debug build, it produces the assertion failure:

10.4 debug 2d4b6571

 
mysqld: /data/src/10.4/storage/innobase/row/row0sel.cc:3942: ICP_RESULT row_search_idx_cond_check(byte*, row_prebuilt_t*, const rec_t*, const offset_t*): Assertion `!prebuilt->index->is_primary()' failed.
 
200115 21:02:58 [ERROR] mysqld got signal 6 ;
 
 
 
#7  0x00007f6be96d2f12 in __GI___assert_fail (assertion=0x55f594a3a868 "!prebuilt->index->is_primary()", file=0x55f594a397a0 "/data/src/10.4/storage/innobase/row/row0sel.cc", line=3942, function=0x55f594a3d8e0 <row_search_idx_cond_check(unsigned char*, row_prebuilt_t*, unsigned char const*, unsigned short const*)::__PRETTY_FUNCTION__> "ICP_RESULT row_search_idx_cond_check(byte*, row_prebuilt_t*, const rec_t*, const offset_t*)") at assert.c:101
 
#8  0x000055f5942320d2 in row_search_idx_cond_check (mysql_rec=0x7f6b90058d58 "\377\377\377\377\377\334\353", prebuilt=0x7f6b901516d8, rec=0x7f6bdf12c084 "", offsets=0x7f6be40a26d0) at /data/src/10.4/storage/innobase/row/row0sel.cc:3942
 
#9  0x000055f5942360a2 in row_search_mvcc (buf=0x7f6b90058d58 "\377\377\377\377\377\334\353", mode=PAGE_CUR_GE, prebuilt=0x7f6b901516d8, match_mode=1, direction=0) at /data/src/10.4/storage/innobase/row/row0sel.cc:5257
 
#10 0x000055f59404652e in ha_innobase::index_read (this=0x7f6b90056b78, buf=0x7f6b90058d58 "\377\377\377\377\377\334\353", key_ptr=0x7f6b90225e1c "\334\353", key_len=4, find_flag=HA_READ_KEY_EXACT) at /data/src/10.4/storage/innobase/handler/ha_innodb.cc:9296
 
#11 0x000055f594047851 in ha_innobase::rnd_pos (this=0x7f6b90056b78, buf=0x7f6b90058d58 "\377\377\377\377\377\334\353", pos=0x7f6b90225e1c "\334\353") at /data/src/10.4/storage/innobase/handler/ha_innodb.cc:9797
 
#12 0x000055f593dfb665 in handler::ha_rnd_pos (this=0x7f6b90056b78, buf=0x7f6b90058d58 "\377\377\377\377\377\334\353", pos=0x7f6b90225e1c "\334\353") at /data/src/10.4/sql/handler.cc:2859
 
#13 0x000055f593c71105 in Mrr_ordered_rndpos_reader::get_next (this=0x7f6b90057030, range_info=0x7f6be40a3688) at /data/src/10.4/sql/multi_range_read.cc:885
 
#14 0x000055f593c72b56 in DsMrr_impl::dsmrr_next (this=0x7f6b90057008, range_info=0x7f6be40a3688) at /data/src/10.4/sql/multi_range_read.cc:1520
 
#15 0x000055f59405a02a in ha_innobase::multi_range_read_next (this=0x7f6b90056b78, range_info=0x7f6be40a3688) at /data/src/10.4/storage/innobase/handler/ha_innodb.cc:20377
 
#16 0x000055f593f8caa8 in QUICK_RANGE_SELECT::get_next (this=0x7f6b901a1690) at /data/src/10.4/sql/opt_range.cc:12160
 
#17 0x000055f593debbdb in find_all_keys (thd=0x7f6b90000af0, param=0x7f6be40a3930, select=0x7f6b9014fa30, fs_info=0x7f6b901a17e0, buffpek_pointers=0x7f6be40a3b30, tempfile=0x7f6be40a39c0, pq=0x0, found_rows=0x7f6b901a19c0) at /data/src/10.4/sql/filesort.cc:764
 
#18 0x000055f593dea13d in filesort (thd=0x7f6b90000af0, table=0x7f6b9018cc20, filesort=0x7f6b9014fd88, tracker=0x7f6b901d2a90, join=0x7f6b90015990, first_table_bit=1) at /data/src/10.4/sql/filesort.cc:267
 
#19 0x000055f593b1e4d7 in create_sort_index (thd=0x7f6b90000af0, join=0x7f6b90015990, tab=0x7f6b9014e4c8, fsort=0x7f6b9014fd88) at /data/src/10.4/sql/sql_select.cc:23479
 
#20 0x000055f593b187b0 in st_join_table::sort_table (this=0x7f6b9014e4c8) at /data/src/10.4/sql/sql_select.cc:21236
 
#21 0x000055f593b1838c in join_init_read_record (tab=0x7f6b9014e4c8) at /data/src/10.4/sql/sql_select.cc:21175
 
#22 0x000055f593b1614d in sub_select (join=0x7f6b90015990, join_tab=0x7f6b9014e4c8, end_of_records=false) at /data/src/10.4/sql/sql_select.cc:20252
 
#23 0x000055f593b1563e in do_select (join=0x7f6b90015990, procedure=0x0) at /data/src/10.4/sql/sql_select.cc:19793
 
#24 0x000055f593aea821 in JOIN::exec_inner (this=0x7f6b90015990) at /data/src/10.4/sql/sql_select.cc:4452
 
#25 0x000055f593ae995e in JOIN::exec (this=0x7f6b90015990) at /data/src/10.4/sql/sql_select.cc:4234
 
#26 0x000055f593aeb073 in mysql_select (thd=0x7f6b90000af0, tables=0x7f6b90013958, wild_num=1, fields=..., conds=0x7f6b900147d8, og_num=1, order=0x7f6b90015810, group=0x0, having=0x0, proc_param=0x0, select_options=2147748608, result=0x7f6b90015968, unit=0x7f6b90004a18, select_lex=0x7f6b90013368) at /data/src/10.4/sql/sql_select.cc:4666
 
#27 0x000055f593adac24 in handle_select (thd=0x7f6b90000af0, lex=0x7f6b90004958, result=0x7f6b90015968, setup_tables_done_option=0) at /data/src/10.4/sql/sql_select.cc:420
 
#28 0x000055f593aa134b in execute_sqlcom_select (thd=0x7f6b90000af0, all_tables=0x7f6b90013958) at /data/src/10.4/sql/sql_parse.cc:6360
 
#29 0x000055f593a969f9 in mysql_execute_command (thd=0x7f6b90000af0) at /data/src/10.4/sql/sql_parse.cc:3899
 
#30 0x000055f593aa5457 in mysql_parse (thd=0x7f6b90000af0, rawbuf=0x7f6b90013198 "SELECT * FROM event_whois_domain_minified WHERE 1 = 1 AND domain = 'www.mailhost.i-dev.fr' AND timestamp >= DATE_ADD(CURRENT_TIMESTAMP, INTERVAL -1 MONTH) ORDER BY timestamp DESC", length=178, parser_state=0x7f6be40a5160, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:7901
 
#31 0x000055f593a905fc in dispatch_command (command=COM_QUERY, thd=0x7f6b90000af0, packet=0x7f6b90137511 "", packet_length=178, is_com_multi=false, is_next_command=false) at /data/src/10.4/sql/sql_parse.cc:1842
 
#32 0x000055f593a8ec89 in do_command (thd=0x7f6b90000af0) at /data/src/10.4/sql/sql_parse.cc:1360
 
#33 0x000055f593c17c51 in do_handle_one_connection (connect=0x55f597252880) at /data/src/10.4/sql/sql_connect.cc:1412
 
#34 0x000055f593c179a0 in handle_one_connection (arg=0x55f597252880) at /data/src/10.4/sql/sql_connect.cc:1316
 
#35 0x000055f59461db0d in pfs_spawn_thread (arg=0x55f5971bef50) at /data/src/10.4/storage/perfschema/pfs.cc:1862
 
#36 0x00007f6beb65b4a4 in start_thread (arg=0x7f6be40a6700) at pthread_create.c:456
 
#37 0x00007f6be978fd0f in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:97

Comment by Christophe Migliorini [ 2020-01-17 ]

wow, that's a reduction! How did you find out? That could help us on another one (we still have the issue on a more complex, GDPR-related, table, and we would be happy to work on a test case for you.

Comment by Elena Stepanova [ 2020-01-17 ]

c_migliorini,

I have some handmade tools, but in your case they aren't necessary and way too complicated for the purpose. Test cases like yours, once you have something that fails deterministically, are very easy to simplify. You need a few-line script which preserves CREATE and SELECT and "bisects" INSERT statements, removing a bunch of them at a time, runs the resulting set of queries, checks the output (or exit code). If the failure still happens, then the removed bunch of INSERT isn't needed, otherwise it's kept. Drop the table, repeat. Leave it run overnight, it will do the trick.
One thing I recommend is enclosing the INSERT part into one big transaction by BEGIN/COMMIT or their analogues. It doesn't make a difference for the result, but will run hundreds times faster.

Comment by Igor Babaev [ 2020-02-11 ]

A fix for this bug was pushed into 10.4

Generated at Thu Feb 08 09:06:31 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.