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

MariaDB SELECT query ends with lost connection

    XMLWordPrintable

Details

    • Bug
    • Status: Closed (View Workflow)
    • Major
    • Resolution: Fixed
    • 10.2, 10.3
    • 10.2.15, 10.3.7
    • Server
    • None

    Description

      In table with approx 1.6 million lines simple SELECT with one subquery fails and client looses connection to database. Error occurs in Galera cluster and in stand-alone MariaDB server.

      For testing we installed Galera cluster on Mariadb 10.1.31 (from Ubuntu repo version 10.1.31+maria-1~xenial), and got result to query with ~ 50 seconds.

      The same query failed with MariaDB versions 10.2.11+maria~xenial, 10.2.14+maria~xenial, and 10.3.5+maria~xenial.

      analyze output:

      ANALYZE SELECT b.reserv_num, 
          DATEDIFF(b.drop_off_time,b.pick_up_time) AS duration, 
          DAYNAME(b.pick_up_time) AS booking_start, 
          DAYNAME(b.drop_off_time) AS booking_end 
      FROM b 
      WHERE b.email IN 
          (SELECT b.email
           FROM b
           LEFT JOIN a on a.id = b.car_id 
           WHERE b.top_agency_id = 12 
               AND b.email NOT LIKE '%HIDDEN%' 
               AND b.email NOT LIKE '%HIDDEN%' 
               AND b.email NOT LIKE '%HIDDEN%' 
               AND b.email NOT LIKE '%HIDDEN%' 
               AND b.email NOT LIKE '%HIDDEN%' 
               AND b.firstname NOT LIKE '%HIDDEN%' 
               AND b.lastname NOT LIKE '%HIDDEN%' 
               AND b.version NOT IN ('Q','0') 
           GROUP BY b.email 
           HAVING COUNT(DISTINCT b.reserv_num) > 3 );
       
      ----+---------------+---------+-------------------+--------+----------+----------+------------+-----------------------------+
      | id   | select_type  | table       | type | possible_keys         | key           | key_len | ref               | rows   | r_rows   | filtered | r_filtered | Extra                       |
      +------+--------------+-------------+------+-----------------------+---------------+---------+-------------------+--------+----------+----------+------------+-----------------------------+
      |    1 | PRIMARY      | <subquery2> | ALL  | distinct_key          | NULL          | NULL    | NULL              | 174652 |   343.00 |   100.00 |     100.00 |                             |
      |    1 | PRIMARY      | b           | ref  | email                 | email         | 386     | <subquery2>.email |      1 |     7.99 |   100.00 |     100.00 |                             |
      |    2 | MATERIALIZED | b           | ref  | version,top_agency_id | top_agency_id | 4       | const             | 174652 | 64575.00 |   100.00 |     100.00 | Using where; Using filesort |
      +------+--------------+-------------+------+-----------------------+---------------+---------+-------------------+--------+----------+----------+------------+-----------------------------+
      3 rows in set (46.52 sec)
      

      Attachments

        Issue Links

          Activity

            People

              Unassigned Unassigned
              Nõmme Margus
              Votes:
              0 Vote for this issue
              Watchers:
              2 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.