maria-discuss team mailing list archive

[Thomas Plant <thomas@plant.systems>]

Am 03.10.2019 um 10:27 schrieb Thomas Plant:
> Hello,
>
> we have a client VM with MariaDB 10.4.8 which crashes always on the same
> query (it's in the log). Can someone help me to debug the problem, or is
> it already known problem? Server ist a CentOS 7 patched up to date and
> MariaDB 10.4.8 from the MariaDB repositories.
>
> Log contains the following:
>
> 191003  9:43:19 [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.4.8-MariaDB
> key_buffer_size=134217728
> read_buffer_size=131072
> max_used_connections=1
> max_threads=302
> thread_count=7
> It is possible that mysqld could use up to
> key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads =
> 795622 K  bytes of memory
> Hope that's ok; if not, decrease some variables in the equation.
>
> Thread pointer: 0x55d12dc0ed28
> 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 = 0x7ff2fd21dcf0 thread_stack 0x49000
> /usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x55d12b70483e]
> /usr/sbin/mysqld(handle_fatal_signal+0x30f)[0x55d12b19ae0f]
> sigaction.c:0(__restore_rt)[0x7ff3153325f0]
> /usr/sbin/mysqld(handler_index_cond_check+0xbf)[0x55d12b1a56af]
> /usr/sbin/mysqld(+0x59ee80)[0x55d12ae94e80]
> /usr/sbin/mysqld(+0xb340cd)[0x55d12b42a0cd]
> /usr/sbin/mysqld(+0xa52c87)[0x55d12b348c87]
> /usr/sbin/mysqld(+0xa52edf)[0x55d12b348edf]
> /usr/sbin/mysqld(_ZN7handler11ha_rnd_nextEPh+0x10f)[0x55d12b19f8ff]
> /usr/sbin/mysqld(_Z13rr_sequentialP11READ_RECORD+0x1c)[0x55d12b2cde9c]
> /usr/sbin/mysqld(_Z10sub_selectP4JOINP13st_join_tableb+0x1a9)[0x55d12afc7dd9]
> /usr/sbin/mysqld(_ZN4JOIN10exec_innerEv+0xb9c)[0x55d12afea4cc]
> /usr/sbin/mysqld(_ZN4JOIN4execEv+0x33)[0x55d12afea723]
> /usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_jP8st_orderS9_S7_S9_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x186)[0x55d12afe8a26]
>
> /usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x1d7)[0x55d12afe9597]
> /usr/sbin/mysqld(+0x58dd2a)[0x55d12ae83d2a]
> /usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x4591)[0x55d12af90641]
> /usr/sbin/mysqld(_ZN18Prepared_statement7executeEP6Stringb+0x4b6)[0x55d12afa8d06]
> /usr/sbin/mysqld(_ZN18Prepared_statement12execute_loopEP6StringbPhS2_+0x92)[0x55d12afa8ec2]
> /usr/sbin/mysqld(+0x6b3827)[0x55d12afa9827]
> /usr/sbin/mysqld(_Z19mysqld_stmt_executeP3THDPcj+0x27)[0x55d12afa98b7]
> /usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcjbb+0x1625)[0x55d12af97d85]
> /usr/sbin/mysqld(_Z10do_commandP3THD+0x11c)[0x55d12af99aac]
> /usr/sbin/mysqld(_Z24do_handle_one_connectionP7CONNECT+0x1fa)[0x55d12b07718a]
> /usr/sbin/mysqld(handle_one_connection+0x3d)[0x55d12b07726d]
> pthread_create.c:0(start_thread)[0x7ff31532ae65]
> /lib64/libc.so.6(clone+0x6d)[0x7ff3136cb88d]
>
> Trying to get some variables.
> Some pointers may be invalid and cause the dump to abort.
> Query (0x55d12dc24b38): select * from `users` where (`role` = ? or
> `role` = ?) and exists (select * from `email_templates` inner join
> `users_email` on `email_template
> s`.`id` = `users_email`.`email_template_id` where `users`.`id` =
> `users_email`.`user_id` and `email_template_id` = ?) and
> `users`.`deleted_at` is null
> Connection ID (thread ID): 9
> 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_pushdow
> n=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_increme
> ntal=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
>
> 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.
> 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             15028                15028               
> processes
> Max open files            16364                16364                files
> Max locked memory         65536                65536                bytes
> Max address space         unlimited            unlimited            bytes
> Max file locks            unlimited            unlimited            locks
> Max pending signals       15028                15028                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
>
>
> Thanks for any hint.
> Kind Regards,
> Thomas
>
> _______________________________________________
> Mailing list: https://launchpad.net/~maria-discuss
> Post to     : maria-discuss@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~maria-discuss
> More help   : https://help.launchpad.net/ListHelp
Client did some tests and he discovered that the following query will
crash reliably:


SELECT
*
FROM
`users`
WHERE
( `role` = 1 OR `role` = 10 )
AND EXISTS ( SELECT * FROM `email_templates` INNER JOIN `users_email` ON
`email_templates`.`id` = `users_email`.`email_template_id` WHERE
`users`.`id` = `users_email`.`user_id` AND `email_template_id` = 12 )
AND `users`.`deleted_at` IS NULL;


Strange thing is when he changes the `email_template_id` = 12 to
`email_template_id` = 11 (or any other value) the query works.

I checked with "mysqlcheck -ce dbname" and no error resulted.