[MDEV-15928] MariaDB SELECT query ends with lost connection Created: 2018-04-19  Updated: 2018-08-06  Resolved: 2018-08-06

Status: Closed
Project: MariaDB Server
Component/s: Server
Affects Version/s: 10.2, 10.3
Fix Version/s: 10.2.15, 10.3.7

Type: Bug Priority: Major
Reporter: Margus Assignee: Unassigned
Resolution: Fixed Votes: 0
Labels: None

Attachments: File galera_select_error.log    
Issue Links:
Relates
relates to MDEV-15065 Item_subselect::cleanup double free o... Closed
relates to MDEV-15755 Query crashing MariaDB in cleanup_aft... Closed

 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)



 Comments   
Comment by Elena Stepanova [ 2018-04-19 ]

From the error log:

*** Error in `/usr/sbin/mysqld': double free or corruption (out): 0x00007fc2bc32db70 ***
 
======= Backtrace: =========
 
/lib/x86_64-linux-gnu/libc.so.6(+0x777e5)[0x7fc5993f17e5]
 
/lib/x86_64-linux-gnu/libc.so.6(+0x8037a)[0x7fc5993fa37a]
 
/lib/x86_64-linux-gnu/libc.so.6(cfree+0x4c)[0x7fc5993fe53c]
 
/usr/sbin/mysqld(_ZN14Item_subselect7cleanupEv+0x7e)[0x55ab21861fae]
 
/usr/sbin/mysqld(_ZN11Query_arena10free_itemsEv+0x2d)[0x55ab215fba0d]
 
/usr/sbin/mysqld(_ZN3THD19cleanup_after_queryEv+0x11b)[0x55ab215fe04b]
 
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x20e)[0x55ab21635a8e]
 
/usr/sbin/mysqld(+0x4c233f)[0x55ab2163633f]
 
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x1069)[0x55ab216379e9]
 
/usr/sbin/mysqld(_Z10do_commandP3THD+0x13c)[0x55ab2163914c]
 
/usr/sbin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x24a)[0x55ab21700e3a]
 
/usr/sbin/mysqld(handle_one_connection+0x3d)[0x55ab21700fad]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba)[0x7fc599dd66ba]
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7fc59948141d]
 
======= Memory map: ========
 
...
 
ffffffffff600000-ffffffffff601000 r-xp 00000000 00:00 0                  [vsyscall]
 
180411  6:07:09 [ERROR] mysqld got signal 6 ;
 
This could be because you hit a bug. It is also possible that this binary
 
or one of the libraries it was linked against is corrupt, improperly built,
 
or misconfigured. This error can also be caused by malfunctioning hardware.
 
 
 
To report this bug, see https://mariadb.com/kb/en/reporting-bugs
 
 
 
We will try our best to scrape up some info that will hopefully help
 
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.
 
 
 
Server version: 10.2.11-MariaDB-10.2.11+maria~xenial-log
 
key_buffer_size=16777216
 
read_buffer_size=131072
 
max_used_connections=514
 
max_threads=3002
 
thread_count=142
 
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 12760884 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x7fc2bc0b9118
 
Attempting backtrace. You can use the following information to find out
 
where mysqld died. If you see no messages after this, something went
 
terribly wrong...
 
stack_bottom = 0x7fc2c0f7fcf8 thread_stack 0x40000
 
(my_addr_resolve failure: fork)
 
/usr/sbin/mysqld(my_print_stacktrace+0x2e) [0x55ab21d2f6de]
 
/usr/sbin/mysqld(handle_fatal_signal+0x305) [0x55ab217cac35]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x11390) [0x7fc599de0390]
 
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x38) [0x7fc5993af428]
 
/lib/x86_64-linux-gnu/libc.so.6(abort+0x16a) [0x7fc5993b102a]
 
/lib/x86_64-linux-gnu/libc.so.6(+0x777ea) [0x7fc5993f17ea]
 
/lib/x86_64-linux-gnu/libc.so.6(+0x8037a) [0x7fc5993fa37a]
 
/lib/x86_64-linux-gnu/libc.so.6(cfree+0x4c) [0x7fc5993fe53c]
 
/usr/sbin/mysqld(Item_subselect::cleanup()+0x7e) [0x55ab21861fae]
 
/usr/sbin/mysqld(Query_arena::free_items()+0x2d) [0x55ab215fba0d]
 
/usr/sbin/mysqld(THD::cleanup_after_query()+0x11b) [0x55ab215fe04b]
 
/usr/sbin/mysqld(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool)+0x20e) [0x55ab21635a8e]
 
/usr/sbin/mysqld(+0x4c233f) [0x55ab2163633f]
 
/usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool)+0x1069) [0x55ab216379e9]
 
/usr/sbin/mysqld(do_command(THD*)+0x13c) [0x55ab2163914c]
 
/usr/sbin/mysqld(do_handle_one_connection(CONNECT*)+0x24a) [0x55ab21700e3a]
 
/usr/sbin/mysqld(handle_one_connection+0x3d) [0x55ab21700fad]
 
/lib/x86_64-linux-gnu/libpthread.so.0(+0x76ba) [0x7fc599dd66ba]
 
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d) [0x7fc59948141d]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7fc2bc17e2a0): 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 '%test%' AND b.email NOT LIKE '%happycar%'  AND b.email NOT LIKE '%titanium%'  AND b.email NOT LIKE '%wisecars%' AND b.email NOT LIKE '%carrentalgateway%'  AND b.firstname NOT LIKE '%test%'  AND b.lastname NOT LIKE '%test%'  AND b.version NOT IN ('Q','0')  GROUP BY b.email HAVING COUNT(DISTINCT b.reserv_num) > 3 )
 
Connection ID (thread ID): 37799778
 
Status: NOT_KILLED

At the first glance, it looks similar to MDEV-15065.

Comment by Elena Stepanova [ 2018-07-19 ]

There was another similar bug MDEV-15755, which was fixed in 10.2.15 and 10.3.7:

commit 445ac662c369756c7465d687e40225ffcce28e74
 
Author: Sergei Golubchik
 
Date:   Mon May 14 16:44:03 2018 +0200
 
 
 
    MDEV-15755 Query crashing MariaDB in cleanup_after_query
 
    
    set the pointer to NULL to avoid double-free
 
    when the item is cleaned up many times
 
    
    (once in JOIN_TAB::cleanup(): tmp->jtbm_subselect->cleanup()
 
    and once at the end of the query, with all other items)

Nõmme, have you already had a chance to upgrade to 10.2.15+ or 10.3.7+? Can you confirm that the problem is gone?

Comment by Margus [ 2018-08-06 ]

Tested the query with version 10.2.15-MariaDB-10.2.15+maria~xenial-log and it really seems to be OK. No crash or other anomalies occurred. Thanks for note.

Comment by Elena Stepanova [ 2018-08-06 ]

Closing as fixed in scope of MDEV-15755, based on the comment above.

Generated at Thu Feb 08 08:25:04 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.