Details
-
Bug
-
Status: Closed (View Workflow)
-
Major
-
Resolution: Fixed
-
10.0.14
-
Linux 2.6.32-504.el6.x86_64
Description
In our production database the following statement crashes the query optimizer:
explain select b.info1 |
from
|
(
|
select distinct info1 |
from state s1 |
where s1.status='FR' and s1.timestamp>'20141101' and s1.timestamp<'20141105' |
) b
|
left outer join state s |
on s.timestamp>='20141201' and s.timestamp < '20141201230000' and s.info1 = b.info1 |
where s.`status`='FR' |
This statement is a reduced one, that is the real statement is much larger, but this seems to be the part that crashes the optimizer. When we select slightly different date values, the query can be optimized with the following execution plan:
'id','select_type','table','type','possible_keys','key','key_len','ref','rows','Extra','1','PRIMARY','<derived2>','ALL',\N,\N,\N,\N,'5650','Using where','1','PRIMARY','s','ref','timestamp,iStatus,iStatusNext,info1','info1','63','b.info1','27','Using index condition; Using where','2','DERIVED','s1','range','timestamp,iStatus,iStatusNext','iStatus','312',\N,'5650','Using index condition; Using temporary',
|
If we replace the left outer join by inner join, the statement works. If we drop the subselect and replace it with something static, it works, too. If we remove the "where" clause and write this condition into the "on" clause (which of course changes the statement), it works, too.
In the log file of the server we find the following error report:
150102 12:51:22 [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 http://kb.askmonty.org/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.0.14-MariaDB-log
|
key_buffer_size=134217728
|
read_buffer_size=131072
|
max_used_connections=13
|
max_threads=502
|
thread_count=11
|
It is possible that mysqld could use up to
|
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1233792 K bytes of memory
|
Hope that's ok; if not, decrease some variables in the equation.
|
|
|
Thread pointer: 0x0x7ffa59f0b008
|
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 = 0x7ffc93195d00 thread_stack 0x48000
|
/usr/sbin/mysqld(my_print_stacktrace+0x2b)[0xb6b85b]
|
/usr/sbin/mysqld(handle_fatal_signal+0x398)[0x723f08]
|
/lib64/libpthread.so.0[0x36d1a0f710]
|
/usr/sbin/mysqld(_Z16best_access_pathP4JOINP13st_join_tableyjbdP11st_positionS4_+0x158b)[0x5ff87b]
|
/usr/sbin/mysqld[0x6012a8]
|
/usr/sbin/mysqld[0x601663]
|
/usr/sbin/mysqld[0x60198b]
|
/usr/sbin/mysqld(_Z11choose_planP4JOINy+0xf5)[0x601ef5]
|
/usr/sbin/mysqld[0x52a437]
|
/usr/sbin/mysqld(_ZN4JOIN14optimize_innerEv+0x6de)[0x61d3de]
|
/usr/sbin/mysqld(_Z12mysql_selectP3THDPPP4ItemP10TABLE_LISTjR4ListIS1_ES2_jP8st_orderSB_S2_SB_yP13select_resultP18st_select_lex_unitP13st_select_lex+0xd8)[0x61fab8]
|
/usr/sbin/mysqld(_Z19mysql_explain_unionP3THDP18st_select_lex_unitP13select_result+0x117)[0x6205c7]
|
/usr/sbin/mysqld[0x5cad47]
|
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x4c97)[0x5d58d7]
|
/usr/sbin/mysqld[0x5d7402]
|
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x1b20)[0x5d95c0]
|
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x453)[0x6946a3]
|
/usr/sbin/mysqld(handle_one_connection+0x42)[0x694772]
|
/usr/sbin/mysqld[0xa6137d]
|
/lib64/libpthread.so.0[0x36d1a079d1]
|
/lib64/libc.so.6(clone+0x6d)[0x36d16e89dd]
|
|
|
Trying to get some variables.
|
Some pointers may be invalid and cause the dump to abort.
|
Query (0x7ffa41c22020): is an invalid pointer
|
Connection ID (thread ID): 25
|
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
|
Now I hope, that this is enough information for you to find out, what is happening here. If not, please feel free to ask for anything missing.
Best regards,
Thomas Mischke
Attachments
Issue Links
- is blocked by
-
MDEV-7453 10.0.16 merge
-
- Closed
-