[MDEV-31240] Crash with condition pushable into derived and containing outer reference Created: 2023-05-11  Updated: 2024-01-23  Resolved: 2023-05-12

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 11.1.1, 10.11.3, 10.4.29, 10.5.20, 10.6.13, 10.8.8, 10.9.6, 10.10.4
Fix Version/s: 10.4.30, 10.5.21, 10.6.14, 10.9.7, 10.10.5, 10.11.4

Type: Bug Priority: Blocker
Reporter: Nathan Assignee: Sergei Petrunia
Resolution: Fixed Votes: 0
Labels: regression
Environment:

Issue noted in cPanel with Almalinux 8.7 and CentOS 7.9.2009

Issue Links:
Blocks
Duplicate
duplicates MDEV-31241 Crashes in subselects in choose_best_... Closed
duplicates MDEV-31394 Mariadb 10.5 crushed with specific query Closed
duplicates MDEV-31418 One SQL query crashes the server Closed
duplicates MDEV-31419 SEGV in mysqld`best_access_path Closed
is duplicated by MDEV-31295 Program terminated with signal SIGSEG... Closed
is duplicated by MDEV-31320 Database crashing after upgrade to 10... Closed
is duplicated by MDEV-31324 Crash by subselect with unused columns Closed
PartOf
is part of MDEV-31376 prepare Q2.1 release (10.5–11.1) Closed
Problem/Incident
is caused by MDEV-26301 Split optimization refills temporary ... Closed

 Description   

Hello,

We could see the below error with which mariadb server crashes and reloads

230511  7:38:01 [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.5.20-MariaDB source revision: b735ca47738a1d2e995a429f40afd620eb7d8843
 
key_buffer_size=134217728
 
read_buffer_size=131072
 
max_used_connections=1
 
max_threads=602
 
thread_count=1
 
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1456372 K  bytes of memory
 
Hope that's ok; if not, decrease some variables in the equation.
 
 
 
Thread pointer: 0x7f8e74000c58
 
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 = 0x7f90580aebd8 thread_stack 0x49000
 
/usr/sbin/mariadbd(my_print_stacktrace+0x2e)[0x555b3770932e]
 
/usr/sbin/mariadbd(handle_fatal_signal+0x485)[0x555b37186825]
 
/lib64/libpthread.so.0(+0x12cf0)[0x7f905cb89cf0]
 
/usr/sbin/mariadbd(_ZN13st_join_table21choose_best_splittingEjyPy+0x531)[0x555b37109b41]
 
/usr/sbin/mariadbd(_Z16best_access_pathP4JOINP13st_join_tableyPK8POSITIONjbdPS3_S6_+0x12b)[0x555b36fbbd2b]
 
/usr/sbin/mariadbd(+0x7b440d)[0x555b36fbf40d]
 
/usr/sbin/mariadbd(_Z11choose_planP4JOINy+0x262)[0x555b36fbfe32]
 
/usr/sbin/mariadbd(_ZN4JOIN14optimize_innerEv+0x344e)[0x555b36fe753e]
 
/usr/sbin/mariadbd(_ZN4JOIN8optimizeEv+0xa0)[0x555b36fe7be0]
 
/usr/sbin/mariadbd(_ZN13st_select_lex31optimize_unflattened_subqueriesEb+0x12d)[0x555b36f63e1d]
 
/usr/sbin/mariadbd(_Z12mysql_updateP3THDP10TABLE_LISTR4ListI4ItemES6_PS4_jP8st_orderybPySA_+0x651)[0x555b3703b2b1]
 
/usr/sbin/mariadbd(_Z21mysql_execute_commandP3THD+0x31cf)[0x555b36f8cef1]
 
/usr/sbin/mariadbd(_Z11mysql_parseP3THDPcjP12Parser_statebb+0x247)[0x555b36f7cb0a]
 
/usr/sbin/mariadbd(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x1dc2)[0x555b36f880a7]
 
/usr/sbin/mariadbd(_Z10do_commandP3THD+0x11e)[0x555b36f89265]
 
/usr/sbin/mariadbd(_Z24do_handle_one_connectionP7CONNECTb+0x501)[0x555b370805b1]
 
/usr/sbin/mariadbd(handle_one_connection+0x5d)[0x555b3708090d]
 
/usr/sbin/mariadbd(+0xba7ea3)[0x555b373b2ea3]
 
/lib64/libpthread.so.0(+0x81cf)[0x7f905cb7f1cf]
 
/lib64/libc.so.6(clone+0x43)[0x7f905becfe73]
 
 
 
Trying to get some variables.
 
Some pointers may be invalid and cause the dump to abort.
 
Query (0x7f8e74012d10): UPDATE mdlov_report_coursesize rc
 
                    SET backupsize = (SELECT bf.filesize FROM (SELECT id AS course, SUM(filesize) AS filesize
 
              FROM (SELECT c.id, f.filesize
 
                      FROM mdlov_course c
 
                      JOIN mdlov_context cx ON cx.contextlevel = 50 AND cx.instanceid = c.id
 
                      JOIN mdlov_files f ON f.contextid = cx.id AND f.component = 'backup') x
 
            GROUP BY id) bf WHERE bf.course = rc.course)
 
 
 
Connection ID (thread ID): 4
 
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 https://mariadb.com/kb/en/how-to-produce-a-full-stack-trace-for-mysqld/ contains
 
information that should help you find out what is causing the crash.
 
Writing a core file...
 
Working directory at /var/lib/mysql
 
Resource Limits:
 
Limit                     Soft Limit           Hard Limit           Units     
Max cpu time              unlimited            unlimited            seconds   
Max file size             unlimited            unlimited            bytes     
Max data size             unlimited            unlimited            bytes     
Max stack size            8388608              unlimited            bytes     
Max core file size        0                    unlimited            bytes     
Max resident set          unlimited            unlimited            bytes     
Max processes             256596               256596               processes 
Max open files            40000                40000                files     
Max locked memory         65536                65536                bytes     
Max address space         unlimited            unlimited            bytes     
Max file locks            unlimited            unlimited            locks     
Max pending signals       256596               256596               signals   
Max msgqueue size         819200               819200               bytes     
Max nice priority         0                    0                    
Max realtime priority     0                    0                    
Max realtime timeout      unlimited            unlimited            us        
Core pattern: |/usr/lib/systemd/systemd-coredump %P %u %g %s %t %c %h %e



 Comments   
Comment by Daniel Black [ 2023-05-11 ]

Can you install the debug symbols with sudo yum install gdb MariaDB-server-debuginfo and then include the output from:

On the assumption that the core dump was saved, can you try:

$ coredumpctl debug
 
 
 
set pagination off
 
set print frame-arguments all
 
bt full

And include this output.

Comment by Nathan [ 2023-05-11 ]

I downgraded the MariaDB version back to 10.5.19 since all the sites were down. This is a production server so won't be able to generate the log now

Comment by Sergei Petrunia [ 2023-05-11 ]

A testcase:

 
 
create table t1 (
 
  a int
 
);
 
insert into t1 select seq from seq_1_to_1000;
 
 
 
create table t2 (
 
  a int,
 
  b int,
 
  key (a)
 
);
 
 
 
create table one (a int);
 
insert into one values (1);
 
 
 
insert into t2 select mod(seq,10), mod(seq,10) from seq_1_to_1000;
 
analyze table t2 persistent for all;
 
 
 
explain
 
select 
  a,
 
  (select
 
     concat(one.a,'=',T2_GRP.SUM_B)
 
   from 
     (select a, sum(b) as SUM_B from t2 group by a) as T2_GRP,
 
     one
 
   where
 
      T2_GRP.a=t1.a
 
  )
 
from t1;


  Thread 34 "mysqld" received signal SIGSEGV, Segmentation fault.
 
  [Switching to Thread 0x7fffcafb4700 (LWP 7734)]
 
  0x0000555555ffaec6 in st_join_table::choose_best_splitting (this=0x7fff740bd1e8, idx=1, remaining_tables=1, spl_pd_boundary=0x7fffcafb1340) at /home/psergey/   dev-git2/10.4-look/sql/opt_split.cc:1049
 
(gdb) wher
 
  #0  0x0000555555ffaec6 in st_join_table::choose_best_splitting (this=0x7fff740bd1e8, idx=1, remaining_tables=1, spl_pd_boundary=0x7fffcafb1340) at /home/       psergey/dev-git2/10.4-look/sql/opt_split.cc:1049
 
  #1  0x0000555555df5ad3 in best_access_path (join=0x7fff740b5fe8, s=0x7fff740bd1e8, remaining_tables=1, join_positions=0x7fff740bdb58, idx=1,                    disable_jbuf=false, record_count=1, pos=0x7fff740bdc98, loose_scan_pos=0x7fffcafb1700) at /home/psergey/dev-git2/10.4-look/sql/sql_select.cc:7468
 
  #2  0x0000555555dfc75b in best_extension_by_limited_search (join=0x7fff740b5fe8, remaining_tables=1, idx=1, record_count=1, read_time=1.2, search_depth=61,     prune_level=1, use_cond_selectivity=4) at /home/psergey/dev-git2/10.4-look/sql/sql_select.cc:9778
 
  #3  0x0000555555dfcecf in best_extension_by_limited_search (join=0x7fff740b5fe8, remaining_tables=3, idx=0, record_count=1, read_time=0, search_depth=62,       prune_level=1, use_cond_selectivity=4) at /home/psergey/dev-git2/10.4-look/sql/sql_select.cc:9867
 
  #4  0x0000555555dfa5b9 in greedy_search (join=0x7fff740b5fe8, remaining_tables=3, search_depth=62, prune_level=1, use_cond_selectivity=4) at /home/psergey/dev- git2/10.4-look/sql/sql_select.cc:8948
 
  #5  0x0000555555df97cb in choose_plan (join=0x7fff740b5fe8, join_tables=3) at /home/psergey/dev-git2/10.4-look/sql/sql_select.cc:8510
 
  #6  0x0000555555df0f31 in make_join_statistics (join=0x7fff740b5fe8, tables_list=@0x7fff74016150: {<base_list> = {<Sql_alloc> = {<No data fields>}, first =     0x7fff740b6eb0, last = 0x7fff740b6ec0, elements = 2}, <No data fields>}, keyuse_array=0x7fff740b62d8) at /home/psergey/dev-git2/10.4-look/sql/sql_select.cc:5711
 
  #7  0x0000555555de4a71 in JOIN::optimize_inner (this=0x7fff740b5fe8) at /home/psergey/dev-git2/10.4-look/sql/sql_select.cc:2368
 
  #8  0x0000555555de2324 in JOIN::optimize (this=0x7fff740b5fe8) at /home/psergey/dev-git2/10.4-look/sql/sql_select.cc:1711
 
  #9  0x0000555555d64fef in st_select_lex::optimize_unflattened_subqueries (this=0x7fff74015920, const_only=false) at /home/psergey/dev-git2/10.4-look/sql/       sql_lex.cc:4236
 
  #10 0x0000555555f950b8 in JOIN::optimize_unflattened_subqueries (this=0x7fff740b55e0) at /home/psergey/dev-git2/10.4-look/sql/opt_subselect.cc:5602
 
  #11 0x0000555555de6c4b in JOIN::optimize_stage2 (this=0x7fff740b55e0) at /home/psergey/dev-git2/10.4-look/sql/sql_select.cc:2942
 
  #12 0x0000555555de4b70 in JOIN::optimize_inner (this=0x7fff740b55e0) at /home/psergey/dev-git2/10.4-look/sql/sql_select.cc:2394 
  #13 0x0000555555de2324 in JOIN::optimize (this=0x7fff740b55e0) at /home/psergey/dev-git2/10.4-look/sql/sql_select.cc:1711
 
  #14 0x0000555555dedb0d in mysql_select (thd=0x7fff74000d50, tables=0x7fff7401a0f8, wild_num=0, fields=@0x7fff74015a80: {<base_list> = {<Sql_alloc> = {<No data  fields>}, first = 0x7fff74015ef0, last = 0x7fff74019fb0, elements = 2}, <No data fields>}, conds=0x0, og_num=0, order=0x0, group=0x0, having=0x0, proc_param=0x0, select_options=2147748612, result=0x7fff7401b1b0, unit=0x7fff74004c88, select_lex=0x7fff74015920) at /home/psergey/dev-git2/10.4-look/sql/sql_select.cc:4812


 

(gdb) p pos->table
$7 = (JOIN_TAB *) 0xa5a5a5a5a5a5a5a5
(gdb) p pos - this->join->positions
$9 = -1
(gdb) p refills
$11 = 1
(gdb) print last_found
$13 = 2
(gdb) p/x best_param_tables
$14 = 0x4000000000000000

{gdb}
Comment by Sergei Petrunia [ 2023-05-11 ]

A patch: https://github.com/MariaDB/server/tree/bb-10.4-mdev31240

Comment by Igor Babaev [ 2023-05-12 ]

See also bb-10.4-igor

Comment by Sergei Petrunia [ 2023-05-12 ]

Ok to push. (Now looking which branch should this go into)

Comment by Daniel Black [ 2023-05-17 ]

I've verified that optimizer_switch="split_materialized=off" would avoid a crash for those on an affected version.

Comment by Daniel Lenski [ 2024-01-23 ]

danblack wrote:

I've verified that optimizer_switch="split_materialized=off" would avoid a crash for those on an affected version.

This (SET SESSION optimizer_switch='split_materialized=off') appears to also avoid a crash on 10.4.29 for the related crash described in MDEV-31403.

Generated at Thu Feb 08 10:22:20 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.