[MDEV-21883] Server crashes when joining a subselect with 32 tables and GROUP BY - Jira

Details

Type: Bug
Status: Closed (View Workflow)
Priority: Major
Resolution: Fixed
Affects Version/s: 10.3.15, 10.4.12, 10.3(EOL), 10.4(EOL), 10.5
Fix Version/s: 10.3.23, 10.4.13, 10.5.3
Component/s: N/A
Labels:
None
Environment:
$ uname -a
Linux lchabowski 4.15.0-88-generic #88-Ubuntu SMP Tue Feb 11 20:11:34 UTC 2020 x86_64 x86_64 x86_64 GNU/Linux

Description

The following SQL queries crashes the mysqld binary:

CREATE DATABASE crashtest;

USE crashtest;

CREATE TABLE dummy (id INT NOT NULL PRIMARY KEY);

SELECT 1

FROM dummy t

LEFT JOIN (

    SELECT dummy0.id

    FROM dummy AS dummy0

    LEFT JOIN dummy AS dummy1 ON 0

    LEFT JOIN dummy AS dummy2 ON 0

    LEFT JOIN dummy AS dummy3 ON 0

    LEFT JOIN dummy AS dummy4 ON 0

    LEFT JOIN dummy AS dummy5 ON 0

    LEFT JOIN dummy AS dummy6 ON 0

    LEFT JOIN dummy AS dummy7 ON 0

    LEFT JOIN dummy AS dummy8 ON 0

    LEFT JOIN dummy AS dummy9 ON 0

    LEFT JOIN dummy AS dummy10 ON 0

    LEFT JOIN dummy AS dummy11 ON 0

    LEFT JOIN dummy AS dummy12 ON 0

    LEFT JOIN dummy AS dummy13 ON 0

    LEFT JOIN dummy AS dummy14 ON 0

    LEFT JOIN dummy AS dummy15 ON 0

    LEFT JOIN dummy AS dummy16 ON 0

    LEFT JOIN dummy AS dummy17 ON 0

    LEFT JOIN dummy AS dummy18 ON 0

    LEFT JOIN dummy AS dummy19 ON 0

    LEFT JOIN dummy AS dummy20 ON 0

    LEFT JOIN dummy AS dummy21 ON 0

    LEFT JOIN dummy AS dummy22 ON 0

    LEFT JOIN dummy AS dummy23 ON 0

    LEFT JOIN dummy AS dummy24 ON 0

    LEFT JOIN dummy AS dummy25 ON 0

    LEFT JOIN dummy AS dummy26 ON 0

    LEFT JOIN dummy AS dummy27 ON 0

    LEFT JOIN dummy AS dummy28 ON 0

    LEFT JOIN dummy AS dummy29 ON 0

    LEFT JOIN dummy AS dummy30 ON 0

    LEFT JOIN dummy AS dummy31 ON 0

    GROUP BY dummy0.id

) AS subquery

    ON subquery.id = t.id;

Removing the GROUP BY or a LEFT JOIN and the query executes sucessfully.

Tested on 10.3.15, 10.4.12 and on the HEAD of 10.5 (was 23685378bafa38a74957e8f07e2d56b57e90fa53 at the time).

Here is the output of the mysqld process:

$ ./sql/mysqld

2020-03-06 12:52:10 0 [Note] ./sql/mysqld (mysqld 10.5.2-MariaDB-debug) starting as process 11578 ...

2020-03-06 12:52:10 0 [Note] InnoDB: !!!!!!!! UNIV_DEBUG switched on !!!!!!!!!

2020-03-06 12:52:10 0 [Note] InnoDB: Mutexes and rw_locks use GCC atomic builtins

2020-03-06 12:52:10 0 [Note] InnoDB: Uses event mutexes

2020-03-06 12:52:10 0 [Note] InnoDB: Compressed tables use zlib 1.2.11

2020-03-06 12:52:10 0 [Note] InnoDB: Number of pools: 1

2020-03-06 12:52:10 0 [Note] InnoDB: Using SSE2 crc32 instructions

2020-03-06 12:52:10 0 [Note] mysqld: O_TMPFILE is not supported on /tmp (disabling future attempts)

2020-03-06 12:52:10 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728

2020-03-06 12:52:10 0 [Note] InnoDB: Completed initialization of buffer pool

2020-03-06 12:52:11 0 [Note] InnoDB: If the mysqld execution user is authorized, page cleaner thread priority can be changed. See the man page of setpriority().

2020-03-06 12:52:11 0 [Note] InnoDB: 128 rollback segments are active.

2020-03-06 12:52:11 0 [Note] InnoDB: Creating shared tablespace for temporary tables

2020-03-06 12:52:11 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...

2020-03-06 12:52:11 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.

2020-03-06 12:52:11 0 [Note] InnoDB: 10.5.2 started; log sequence number 45617; transaction id 21

2020-03-06 12:52:11 0 [Note] InnoDB: Loading buffer pool(s) from /home/lchabowski/mariadb-dev/dbdata/ib_buffer_pool

2020-03-06 12:52:11 0 [Note] Plugin 'FEEDBACK' is disabled.

2020-03-06 12:52:11 0 [Note] InnoDB: Buffer pool(s) load completed at 200306 12:52:11

2020-03-06 12:52:11 0 [Note] Server socket created on IP: '::'.

2020-03-06 12:52:11 0 [Note] Reading of all Master_info entries succeeded

2020-03-06 12:52:11 0 [Note] Added new Master_info '' to hash table

2020-03-06 12:52:11 0 [Note] ./sql/mysqld: ready for connections.

Version: '10.5.2-MariaDB-debug'  socket: '/tmp/mysql.sock'  port: 3307  Source distribution

mysqld: /home/lchabowski/src/mariadb-server/sql/sql_select.cc:8679: bool greedy_search(JOIN*, table_map, uint, uint, uint): Assertion `join->best_read < double(1.79769313486231570814527423731704357e+308L)' failed.

200306 12:52:31 [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.5.2-MariaDB-debug

key_buffer_size=134217728

read_buffer_size=131072

max_used_connections=1

max_threads=153

thread_count=2

It is possible that mysqld could use up to

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 467885 K  bytes of memory

Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7fb30c000d60

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 = 0x7fb35c117e50 thread_stack 0x49000

addr2line: './sql/mysqld': No such file

./sql/mysqld(my_print_stacktrace+0x4f)[0x555e0fb0393e]

Printing to addr2line failed

./sql/mysqld(handle_fatal_signal+0x3bd)[0x555e0f1b453c]

/lib/x86_64-linux-gnu/libpthread.so.0(+0x12890)[0x7fb365d25890]

/lib/x86_64-linux-gnu/libc.so.6(gsignal+0xc7)[0x7fb364e1de97]

/lib/x86_64-linux-gnu/libc.so.6(abort+0x141)[0x7fb364e1f801]

/lib/x86_64-linux-gnu/libc.so.6(+0x3039a)[0x7fb364e0f39a]

/lib/x86_64-linux-gnu/libc.so.6(+0x30412)[0x7fb364e0f412]

addr2line: './sql/mysqld': No such file

./sql/mysqld(+0x8f33f7)[0x555e0eec03f7]

./sql/mysqld(_Z11choose_planP4JOINy+0x2e5)[0x555e0eebf554]

./sql/mysqld(_ZN13st_join_table21choose_best_splittingEdy+0x482)[0x555e0f0de706]

./sql/mysqld(_Z16best_access_pathP4JOINP13st_join_tableyPK11st_positionjbdPS3_S6_+0x27b)[0x555e0eebbbb4]

./sql/mysqld(+0x8f5404)[0x555e0eec2404]

./sql/mysqld(+0x8f5b58)[0x555e0eec2b58]

./sql/mysqld(+0x8f334e)[0x555e0eec034e]

./sql/mysqld(_Z11choose_planP4JOINy+0x2e5)[0x555e0eebf554]

./sql/mysqld(+0x8e9d02)[0x555e0eeb6d02]

./sql/mysqld(_ZN4JOIN14optimize_innerEv+0x1de3)[0x555e0eeaa87b]

./sql/mysqld(_ZN4JOIN8optimizeEv+0xc6)[0x555e0eea8126]

./sql/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x430)[0x555e0eeb38e5]

./sql/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x19d)[0x555e0eea2ee7]

./sql/mysqld(+0x899695)[0x555e0ee66695]

./sql/mysqld(_Z21mysql_execute_commandP3THD+0x1faa)[0x555e0ee5d465]

./sql/mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x2ed)[0x555e0ee6b7a0]

./sql/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x11fe)[0x555e0ee570f0]

./sql/mysqld(_Z10do_commandP3THD+0x7a5)[0x555e0ee557dc]

./sql/mysqld(_Z24do_handle_one_connectionP7CONNECTb+0x1e0)[0x555e0efffded]

./sql/mysqld(handle_one_connection+0x5b)[0x555e0efffb0d]

/lib/x86_64-linux-gnu/libpthread.so.0(+0x76db)[0x7fb365d1a6db]

/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7fb364f0088f]

Trying to get some variables.

Some pointers may be invalid and cause the dump to abort.

Query (0x7fb30c014888): SELECT 1 FROM dummy t LEFT JOIN (     SELECT dummy0.id     FROM dummy AS dummy0     LEFT JOIN dummy AS dummy1 ON 0     LEFT JOIN dummy AS dummy2 ON 0     LEFT JOIN dummy AS dummy3 ON 0     LEFT JOIN dummy AS dummy4 ON 0     LEFT JOIN dummy AS dummy5 ON 0     LEFT JOIN dummy AS dummy6 ON 0     LEFT JOIN dummy AS dummy7 ON 0     LEFT JOIN dummy AS dummy8 ON 0     LEFT JOIN dummy AS dummy9 ON 0     LEFT JOIN dummy AS dummy10 ON 0     LEFT JOIN dummy AS dummy11 ON 0     LEFT JOIN dummy AS dummy12 ON 0     LEFT JOIN dummy AS dummy13 ON 0     LEFT JOIN dummy AS dummy14 ON 0     LEFT JOIN dummy AS dummy15 ON 0     LEFT JOIN dummy AS dummy16 ON 0     LEFT JOIN dummy AS dummy17 ON 0     LEFT JOIN dummy AS dummy18 ON 0     LEFT JOIN dummy AS dummy19 ON 0     LEFT JOIN dummy AS dummy20 ON 0     LEFT JOIN dummy AS dummy21 ON 0     LEFT JOIN dummy AS dummy22 ON 0     LEFT JOIN dummy AS dummy23 ON 0     LEFT JOIN dummy AS dummy24 ON 0     LEFT JOIN dummy AS dummy25 ON 0     LEFT JOIN dummy AS dummy26 ON 0     LEFT JOIN dummy AS dummy27 ON 0     LEFT JOIN dummy AS dummy28 ON 0     LEFT JOIN dummy AS dummy29 ON 0     LEFT JOIN dummy AS dummy30 ON 0     LEFT JOIN dummy AS dummy31 ON 0     GROUP BY dummy0.id ) AS subquery     ON subquery.id = t.id

Connection ID (thread ID): 3

Status: NOT_KILLED

Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=on,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on,condition_pushdown_for_subquery=on,rowid_filter=on,condition_pushdown_from_having=on,not_null_range_scan=off

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains

information that should help you find out what is causing the crash.

Originally the query was more complex (there are NDAs in place, cant share it, sorry) but I reduced it to the above query. Although the original case crashed with a SIGSEGV and not with a failed assertion:

200306 13:11:51 [ERROR] mysqld got signal 11 ;

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.3.15-MariaDB-1:10.3.15+maria~bionic

key_buffer_size=134217728

read_buffer_size=2097152

max_used_connections=4

max_threads=10002

thread_count=9

It is possible that mysqld could use up to

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 61808382 K  bytes of memory

Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7fb020000c08

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 = 0x7fb08062fdd8 thread_stack 0x49000

mysqld(my_print_stacktrace+0x2e)[0x559f00b0606e]

mysqld(handle_fatal_signal+0x515)[0x559f005a26d5]

/lib/x86_64-linux-gnu/libpthread.so.0(+0x12890)[0x7fb09bb4e890]

mysqld(_Z45fix_semijoin_strategies_for_picked_join_orderP4JOIN+0x88)[0x559f004d7198]

mysqld(_ZN4JOIN20get_best_combinationEv+0xba)[0x559f003f8fca]

mysqld(_ZN4JOIN15optimize_stage2Ev+0x143)[0x559f00416553]

mysqld(_ZN4JOIN8optimizeEv+0x75)[0x559f0041b0c5]

mysqld(_Z22mysql_derived_optimizeP3THDP3LEXP10TABLE_LIST+0xef)[0x559f0038f8ff]

mysqld(_Z27mysql_handle_single_derivedP3LEXP10TABLE_LISTj+0xb1)[0x559f0038f1b1]

mysqld(_ZN13st_select_lex14handle_derivedEP3LEXj+0x47)[0x559f003aa2f7]

mysqld(_ZN4JOIN15optimize_stage2Ev+0x16c)[0x559f0041657c]

mysqld(_ZN4JOIN14optimize_innerEv+0xe69)[0x559f00419619]

mysqld(_ZN4JOIN8optimizeEv+0x37)[0x559f0041b087]

mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x886)[0x559f0041ce86]

mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x14d)[0x559f0041d0bd]

mysqld(+0x5651d1)[0x559f003be1d1]

mysqld(_Z21mysql_execute_commandP3THD+0x6015)[0x559f003caac5]

mysqld(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x20a)[0x559f003cd42a]

mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x1cd7)[0x559f003cfe57]

mysqld(_Z10do_commandP3THD+0x178)[0x559f003d0bf8]

mysqld(_Z24do_handle_one_connectionP7CONNECT+0x212)[0x559f004a1532]

mysqld(handle_one_connection+0x3d)[0x559f004a170d]

/lib/x86_64-linux-gnu/libpthread.so.0(+0x76db)[0x7fb09bb436db]

/lib/x86_64-linux-gnu/libc.so.6(clone+0x3f)[0x7fb09b14588f]

Trying to get some variables.

Some pointers may be invalid and cause the dump to abort.

Query (0x7fb02000f5d0): [[ redacted ]]

Connection ID (thread ID): 84

Status: NOT_KILLED

Optimizer switch: index_merge=on,index_merge_union=on,index_merge_sort_union=on,index_merge_intersection=on,index_merge_sort_intersection=off,engine_condition_pushdown=off,index_condition_pushdown=on,derived_merge=on,derived_with_keys=on,firstmatch=on,loosescan=on,materialization=on,in_to_exists=on,semijoin=on,partial_match_rowid_merge=on,partial_match_table_scan=on,subquery_cache=on,mrr=off,mrr_cost_based=off,mrr_sort_keys=off,outer_join_with_cache=on,semijoin_with_cache=on,join_cache_incremental=on,join_cache_hashed=on,join_cache_bka=on,optimize_join_buffer_size=off,table_elimination=on,extended_keys=on,exists_to_in=on,orderby_uses_equalities=on,condition_pushdown_for_derived=on,split_materialized=on

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains

information that should help you find out what is causing the crash.

Attachments

Issue Links

relates to

MDEV-19132 Strange combination of JOIN with NULL crash the server

Confirmed

Server crashes when joining a subselect with 32 tables and GROUP BY

Details

Description

Attachments

Issue Links

Activity

People

Dates

Git Integration