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
-
Activity
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?
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.
Closing as fixed in scope of MDEV-15755, based on the comment above.
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 binaryor 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-bugsWe will try our best to scrape up some info that will hopefully helpdiagnose 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-logkey_buffer_size=16777216read_buffer_size=131072max_used_connections=514max_threads=3002thread_count=142It is possible that mysqld could use up tokey_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 12760884 K bytes of memoryHope that's ok; if not, decrease some variables in the equation.Thread pointer: 0x7fc2bc0b9118Attempting backtrace. You can use the following information to find outwhere mysqld died. If you see no messages after this, something wentterribly 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): 37799778Status: NOT_KILLEDAt the first glance, it looks similar to
MDEV-15065.