Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.2(EOL), 10.3(EOL)
-
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
- relates to
-
-
- Closed
-
-
-
- Closed
-