[MDEV-24953] 10.5.9 crashes with large IN() list Created: 2021-02-23  Updated: 2021-05-03  Resolved: 2021-02-26

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 10.5.9
Fix Version/s: 10.5.10

Type: Bug Priority: Major
Reporter: Larry David Assignee: Sergei Petrunia
Resolution: Fixed Votes: 1
Labels: None
Environment:

Centos 8 and Centos 7, phpbb 3.3.x


Issue Links:
Duplicate
is duplicated by MDEV-25269 Crash: exception 0xc0000005 Closed
is duplicated by MDEV-25504 System Segfaults On A Specific Query Closed
is duplicated by MDEV-25578 table with too much redundant key coz... Closed
Relates
relates to MDEV-24995 Server crash on query Closed

 Description   

I upgraded from 10.5.8 to 10.5.9 and a particular delete statement for phpbb 3.3.x cause MariaDB to crash.

This is the statement:

DELETE FROM phpbb3_notification_emails
                        WHERE notification_type_id IN (3, 4, 5, 6, 23) AND user_id = '5044' AND item_parent_id IN ('544', '53', '1939')

(Note that the IN list is actually 4642 values long, the above has been truncated to 3 for ease of viewing)

This only started happening once MariaDB was upgraded from 10.5.8 to 10.5.9. I believe that 10.5.8 had a bug fix itself to a similar issue

MDEV-24117

So I wonder if 10.5.9 has a regression error?

Version: '10.5.9-MariaDB-log'  socket: '/var/lib/mysql/mysql.sock'  port: 0  MariaDB Server
210223 13:31:32 [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.9-MariaDB-log
key_buffer_size=1048576
read_buffer_size=1048576
max_used_connections=3
max_threads=50
thread_count=3
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 104669 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
 
Thread pointer: 0x7f658814ee68
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 = 0x7f65c0410c90 thread_stack 0x49000
??:0(my_print_stacktrace)[0x55d51a88c7fe]
??:0(handle_fatal_signal)[0x55d51a290a37]
sigaction.c:0(__restore_rt)[0x7f65c5bdc630]
??:0(SEL_ARG::tree_delete(SEL_ARG*))[0x55d51a3bf2cd]
??:0(Item_bool_func::get_mm_parts(RANGE_OPT_PARAM*, Field*, Item_func::Functype, Item*))[0x55d51a3c862c]
??:0(SEL_IMERGE::and_sel_tree(RANGE_OPT_PARAM*, SEL_TREE*, SEL_IMERGE*))[0x55d51a3ca92a]
??:0(Item_cond_and::get_mm_tree(RANGE_OPT_PARAM*, Item**))[0x55d51a3cf01f]
??:0(SQL_SELECT::test_quick_select(THD*, Bitmap<64u>, unsigned long long, unsigned long long, bool, bool, bool, bool))[0x55d51a3cb5b1]
??:0(mysql_delete(THD*, TABLE_LIST*, Item*, SQL_I_List<st_order>*, unsigned long long, unsigned long long, select_result*))[0x55d51a3eea73]
??:0(mysql_execute_command(THD*))[0x55d51a0916f6]
??:0(mysql_parse(THD*, char*, unsigned int, Parser_state*, bool, bool))[0x55d51a094db5]
??:0(dispatch_command(enum_server_command, THD*, char*, unsigned int, bool, bool))[0x55d51a096f6c]
??:0(do_command(THD*))[0x55d51a09837b]
??:0(do_handle_one_connection(CONNECT*, bool))[0x55d51a182ac2]
??:0(handle_one_connection)[0x55d51a182d84]
??:0(MyCTX_nopad::finish(unsigned char*, unsigned int*))[0x55d51a4de11d]
pthread_create.c:0(start_thread)[0x7f65c5bd4ea5]
??:0(__clone)[0x7f65c3cfd9fd]
 
 
 
Connection ID (thread ID): 1507
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 /data/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             127953               127953               processes
Max open files            16384                16384                files
Max locked memory         65536                65536                bytes
Max address space         unlimited            unlimited            bytes
Max file locks            unlimited            unlimited            locks
Max pending signals       127953               127953               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: core
 
2021-02-23 13:31:38 0 [Note] mariadbd: Aria engine: starting recovery
recovered pages: 0% 10% 20% 30% 40% 50% 60% 73% 84% 94% 100% (0.0 seconds); tables to flush: 7 6 5 4 3 2 1 0
 (0.0 seconds);
2021-02-23 13:31:38 0 [Note] mariadbd: Aria engine: recovery done
2021-02-23 13:31:38 0 [Note] Plugin 'InnoDB' is disabled.
2021-02-23 13:31:38 0 [Note] Plugin 'FEEDBACK' is disabled.
2021-02-23 13:31:38 0 [Note] Reading of all Master_info entries succeeded
2021-02-23 13:31:38 0 [Note] Added new Master_info '' to hash table
2021-02-23 13:31:38 0 [Note] /usr/sbin/mariadbd: ready for connections.



 Comments   
Comment by Sergei Petrunia [ 2021-02-23 ]

Ok, the issue was introduced by the fix for MDEV-9750.

The fix is really basic:

diff --git a/sql/opt_range.cc b/sql/opt_range.cc
index 3684db40242..c0df503de2f 100644
--- a/sql/opt_range.cc
+++ b/sql/opt_range.cc
@@ -9813,7 +9813,8 @@ and_all_keys(RANGE_OPT_PARAM *param, SEL_ARG *key1, SEL_ARG *key2,
        continue;
       }
       next->next_key_part=tmp;
-      new_weight += 1 + tmp->weight;
+      if (tmp)
+        new_weight += 1 + tmp->weight;
       if (use_count)
        next->increment_use_count(use_count);
       if (param->alloced_sel_args > SEL_ARG::MAX_SEL_ARGS)

A workaround is to set optimizer_max_sel_arg_weight to a value lower than SEL_ARG::MAX_SEL_ARGS (which is 16000).

Comment by Larry David [ 2021-02-23 ]

Confirmed setting

optimizer_max_sel_arg_weight

to a value less than 16000 in my.cnf has removed the problem.

Thank you for your quick response and assitance with this.

Comment by Sergei Petrunia [ 2021-02-24 ]

Patch: http://lists.askmonty.org/pipermail/commits/2021-February/014481.html

Generated at Thu Feb 08 09:33:57 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.