Details
Description
Hi-
I'm experiencing occasionally crashes in a query that joins a few tables, including a join to a view. From what I can tell from the stack trace, the problem may have something to do with the view. This query normally works fine, and when I run it in the command line client it is very fast.
However, under relatively heavy load, especially when I configure my connection pool to contain only a single connection, I get a crash - always on the same query. The stack trace is below, please let me know if there is any additional information I can provide.
Server version: 10.1.14-MariaDB-1~trusty
|
key_buffer_size=134217728
|
read_buffer_size=2097152
|
max_used_connections=22
|
max_threads=102
|
thread_count=2
|
It is possible that mysqld could use up to
|
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 759828 K bytes of memory
|
Hope that's ok; if not, decrease some variables in the equation.
|
|
Thread pointer: 0x0x7ff9153a2008
|
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 = 0x7ff9482d51f0 thread_stack 0x48400
|
/usr/sbin/mysqld(my_print_stacktrace+0x2e)[0x7ff951040b5e]
|
/usr/sbin/mysqld(handle_fatal_signal+0x38d)[0x7ff950b6cabd]
|
/lib/x86_64-linux-gnu/libpthread.so.0(+0x10330)[0x7ff94f0c2330]
|
/usr/sbin/mysqld(_Z17create_view_fieldP3THDP10TABLE_LISTPP4ItemPKc+0x1bc)[0x7ff950a971ac]
|
/usr/sbin/mysqld(_Z23find_field_in_table_refP3THDP10TABLE_LISTPKcjS4_S4_S4_PP4ItembbPjbPS2_+0x13f)[0x7ff9509abe1f]
|
/usr/sbin/mysqld(_Z20find_field_in_tablesP3THDP10Item_identP10TABLE_LISTS4_PP4Item27find_item_error_report_typebb+0x143)[0x7ff9509ac903]
|
/usr/sbin/mysqld(_ZN10Item_field10fix_fieldsEP3THDPP4Item+0x332)[0x7ff950b8c1c2]
|
/usr/sbin/mysqld(_ZN9Item_func10fix_fieldsEP3THDPP4Item+0x19f)[0x7ff950bc714f]
|
/usr/sbin/mysqld(_ZN9Item_cond10fix_fieldsEP3THDPP4Item+0xb9)[0x7ff950b9ce59]
|
/usr/sbin/mysqld(_Z11setup_condsP3THDP10TABLE_LISTR4ListIS1_EPP4Item+0x18b)[0x7ff9509aee3b]
|
/usr/sbin/mysqld(_ZN4JOIN7prepareEPPP4ItemP10TABLE_LISTjS1_jP8st_orderbS7_S1_S7_P13st_select_lexP18st_select_lex_unit+0x3c4)[0x7ff950a367f4]
|
/usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x924)[0x7ff950a41b54]
|
/usr/sbin/mysqld(_Z13handle_selectP3THDP3LEXP13select_resultm+0x245)[0x7ff950a41e15]
|
/usr/sbin/mysqld(+0x428331)[0x7ff9509e4331]
|
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x61b9)[0x7ff9509f0a19]
|
/usr/sbin/mysqld(_ZN18Prepared_statement7executeEP6Stringb+0x4b9)[0x7ff950a074e9]
|
/usr/sbin/mysqld(+0x44b633)[0x7ff950a07633]
|
/usr/sbin/mysqld(_Z19mysqld_stmt_executeP3THDPcj+0x17e)[0x7ff950a07b2e]
|
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xfbf)[0x7ff9509f5f2f]
|
/usr/sbin/mysqld(_Z10do_commandP3THD+0x169)[0x7ff9509f7b89]
|
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x18a)[0x7ff950abb24a]
|
/usr/sbin/mysqld(handle_one_connection+0x40)[0x7ff950abb420]
|
/lib/x86_64-linux-gnu/libpthread.so.0(+0x8184)[0x7ff94f0ba184]
|
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7ff94e5d937d]
|
|
Trying to get some variables.
|
Some pointers may be invalid and cause the dump to abort.
|
Query (0x7ff91798b020): is an invalid pointer
|
Connection ID (thread ID): 2544
|
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
|
Thanks,
Kevin
schmeic,
Can you provide the query since you have already located it, and
or
for all involved tables and views?
If you don't want to share it publicly, you can upload the output to ftp.askmonty.org/private.
If you can provide the database dump, it would be even better.
Please also attach your cnf file(s).
Regarding reproducing it locally on your machine, if you can afford experimenting, please try to execute the query via a prepared statement, and if it does not crash at the first attempt, execute it twice more. That is, do the following: