Details
-
Bug
-
Status: Closed (View Workflow)
-
Critical
-
Resolution: Fixed
-
5.1.67, 5.2.14, 5.3.12, 5.5.39, 10.0.13, 10.1.0
-
Red Hat Enterprise Linux 2.6.32-220.el6.x86_64
Description
MySQL 5.5.35 and MariaDB 10.0.12 crash when ORDER BY 1 when using GROUP_CONCAT(IF
SQL that causes the crash:
INSERT INTO tempdb.tt_markets
|
SELECT mv.marketId,
|
NULL,
|
GROUP_CONCAT(IF (tp.overridesExchangeCode, IFNULL(tp.symbolCode,v.symbolCode), v.symbolCode) ORDER BY 1 SEPARATOR ',' ),
|
GROUP_CONCAT(v.`permissionsCode` ORDER BY 1 SEPARATOR ',' ),
|
GROUP_CONCAT(IF (tp.overridesExchangeCode, IFNULL(tp.tradingPlatformName, v.venueAcronym), v.venueAcronym) ORDER BY 1 SEPARATOR ',' )
|
FROM marketvenues mv
|
JOIN market mkt ON mv.marketId = mkt.marketId
|
JOIN markettemplate mt ON mkt.marketTemplateId = mt.marketTemplateId
|
JOIN marketinstruments mi ON mkt.marketId = mi.marketId
|
JOIN instrument i ON i.instrumentId = mi.instrumentId
|
JOIN debtinstrument di ON i.instrumentId = di.instrumentId
|
JOIN product p ON i.productId = p.productId
|
JOIN producttype pt ON p.productTypeId = pt.productTypeId
|
JOIN venue v ON mv.venueId = v.venueId
|
LEFT JOIN tradingplatform tp ON tp.tradingPlatformId = v.tradingPlatformId
|
JOIN tempdb.tt_MktVenIds ttmvIds
|
ON mv.marketVenueId=ttmvIds.ttv_MarketVenueId
|
|
|
WHERE mi.marketSequence = 1 -- Limit the marketinstrument join to one record
|
AND p.rowStatusId < compareStatus -- Limits it to active or expired products
|
AND pt.productTypeCode = 'DBT' -- Limits it to debt products
|
AND i.rowStatusId < compareStatus -- Limits it to active or expired instruments
|
AND mkt.rowStatusId < compareStatus -- Limits it to active or expired markets
|
AND mt.marketTemplateCode = "OUT" -- Limits it to outright markets
|
AND di.maturityDate >= IF (IncludeInActive='Y', 0, CONVERT(CURDATE(),UNSIGNED)) -- Limits it to unexpired/expired instruments
|
GROUP BY mv.marketId;
|
Crash is caused by "ORDER BY 1", if I replace it by one of the value of IF it stops crashing. For example:
GROUP_CONCAT(IF (tp.overridesExchangeCode, IFNULL(tp.symbolCode,v.symbolCode), v.symbolCode) ORDER BY v.symbolCode SEPARATOR ',' ),
|
GROUP_CONCAT(v.`permissionsCode` ORDER BY 1 SEPARATOR ',' ),
|
GROUP_CONCAT(IF (tp.overridesExchangeCode, IFNULL(tp.tradingPlatformName, v.venueAcronym), v.venueAcronym) ORDER BY v.venueAcronym SEPARATOR ',' )
|
But that's not equivalent.
'IF' returned value cannot have an alias assigned, e.g: IF(1,2,3) as if_output.
I guess it would crash anyway since following is crashing too:
GROUP_CONCAT(IF (tp.overridesExchangeCode, IFNULL(tp.symbolCode,v.symbolCode), v.symbolCode) ORDER BY IF (tp.overridesExchangeCode, IFNULL(tp.symbolCode,v.symbolCode), v.symbolCode) SEPARATOR ',' )
|
error log:
140821 18:10:35 [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.12-MariaDB-log
|
key_buffer_size=33554432
|
read_buffer_size=262144
|
max_used_connections=33
|
max_threads=1002
|
thread_count=25
|
It is possible that mysqld could use up to
|
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 823012 K bytes of memory
|
Hope that's ok; if not, decrease some variables in the equation.
|
|
|
Thread pointer: 0x0x7f39eac4d008
|
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 = 0x7f3ba0706de0 thread_stack 0x48000
|
mysys/stacktrace.c:247(my_print_stacktrace)[0xbb674e]
|
sql/signal_handler.cc:153(handle_fatal_signal)[0x7152ec]
|
/lib64/libpthread.so.0[0x30ece0f4a0]
|
sql/item_sum.cc:3428(Item_func_group_concat::fix_fields(THD*, Item**))[0x7c6186]
|
sql/sql_base.cc:7778(setup_fields(THD*, Item**, List<Item>&, enum_mark_columns, List<Item>*, bool))[0x54a459]
|
sql/sql_select.cc:781(JOIN::prepare(Item***, TABLE_LIST*, unsigned int, Item*, unsigned int, st_order*, bool, st_order*, Item*, st_order*, st_select_lex*, st_select_lex_unit*))[0x5c4386]
|
sql/sql_select.cc:3281(mysql_select(THD*, Item***, TABLE_LIST*, unsigned int, List<Item>&, Item*, unsigned int, st_order*, st_order*, Item*, st_order*, unsigned long long, select_result*, st_select_lex_unit*, st_select_lex*))[0x5e738a]
|
sql/sql_select.cc:372(handle_select(THD*, LEX*, select_result*, unsigned long))[0x5ea71d]
|
sql/sql_parse.cc:3529(mysql_execute_command(THD*))[0x59a5e5]
|
sql/sp_head.cc:3191(sp_instr_stmt::exec_core(THD*, unsigned int*))[0x83b56e]
|
sql/sp_head.cc:2959(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x83b70c]
|
sql/sp_head.cc:3108(sp_instr_stmt::execute(THD*, unsigned int*))[0x841902]
|
sql/sp_head.cc:1366(sp_head::execute(THD*, bool))[0x83f71c]
|
sql/sp_head.cc:2156(sp_head::execute_procedure(THD*, List<Item>*))[0x840ef3]
|
sql/sql_parse.cc:4690(mysql_execute_command(THD*))[0x5954f5]
|
sql/sp_head.cc:3191(sp_instr_stmt::exec_core(THD*, unsigned int*))[0x83b56e]
|
sql/sp_head.cc:2959(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x83b70c]
|
sql/sp_head.cc:3108(sp_instr_stmt::execute(THD*, unsigned int*))[0x841902]
|
sql/sp_head.cc:1366(sp_head::execute(THD*, bool))[0x83f71c]
|
sql/sp_head.cc:2156(sp_head::execute_procedure(THD*, List<Item>*))[0x840ef3]
|
sql/sql_parse.cc:4690(mysql_execute_command(THD*))[0x5954f5]
|
sql/sp_head.cc:3191(sp_instr_stmt::exec_core(THD*, unsigned int*))[0x83b56e]
|
sql/sp_head.cc:2959(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x83b70c]
|
sql/sp_head.cc:3108(sp_instr_stmt::execute(THD*, unsigned int*))[0x841902]
|
sql/sp_head.cc:1366(sp_head::execute(THD*, bool))[0x83f71c]
|
sql/sp_head.cc:2156(sp_head::execute_procedure(THD*, List<Item>*))[0x840ef3]
|
sql/sql_parse.cc:4690(mysql_execute_command(THD*))[0x5954f5]
|
sql/sp_head.cc:3191(sp_instr_stmt::exec_core(THD*, unsigned int*))[0x83b56e]
|
sql/sp_head.cc:2959(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x83b70c]
|
sql/sp_head.cc:3108(sp_instr_stmt::execute(THD*, unsigned int*))[0x841902]
|
sql/sp_head.cc:1366(sp_head::execute(THD*, bool))[0x83f71c]
|
sql/sp_head.cc:2156(sp_head::execute_procedure(THD*, List<Item>*))[0x840ef3]
|
sql/sql_parse.cc:4690(mysql_execute_command(THD*))[0x5954f5]
|
sql/sql_parse.cc:6409(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x59b2da]
|
sql/sql_parse.cc:1311(dispatch_command(enum_server_command, THD*, char*, unsigned int))[0x59d087]
|
sql/sql_parse.cc:1006(do_command(THD*))[0x59d7a5]
|
sql/sql_connect.cc:1379(do_handle_one_connection(THD*))[0x664874]
|
sql/sql_connect.cc:1295(handle_one_connection)[0x6649b2]
|
perfschema/pfs.cc:1863(pfs_spawn_thread)[0x8b6739]
|
/lib64/libpthread.so.0[0x30ece077f1]
|
/lib64/libc.so.6(clone+0x6d)[0x30ec6e5ccd]
|
|
|
Trying to get some variables.
|
Some pointers may be invalid and cause the dump to abort.
|
Query (0x7f39de6398d0): INSERT INTO tempdb.tt_markets SELECT mv.marketId, NULL, GROUP_CONCAT(IF (tp.overridesExchangeCode, IFNULL(tp.symbolCode,v.symbolCode), v.symbolCode) ORDER BY 1 SEPARATOR ',' ), GROUP_CONCAT(v.`permissionsCode` ORDER BY 1 SEPARATOR ',' ), GROUP_CONCAT(IF (tp.overridesExchangeCode, IFNULL(tp.tradingPlatformName, v.venueAcronym), v.venueAcronym) ORDER BY 1 SEPARATOR ',' ) FROM marketvenues mv JOIN market mkt ON mv.marketId = mkt.marketId JOIN markettemplate mt ON mkt.marketTemplateId = mt.marketTemplateId JOIN marketinstruments mi ON mkt.marketId = mi.marketId JOIN instrument i ON i.instrumentId = mi.instrumentId JOIN debtinstrument di ON i.instrumentId = di.instrumentId JOIN product p ON i.productId = p.productId JOIN producttype pt ON p.productTypeId = pt.productTypeId JOIN venue v ON mv.venueId = v.venueId LEFT JOIN tradingplatform tp ON tp.tradingPlatformId = v.tradingPlatformId JOIN tempdb.tt_MktVenIds ttmvIds ON mv.marketVenueId=ttmvIds.ttv_MarketVenueId WHERE mi.marketSequence = 1 AND p.rowStatusId < NAME_CONST('compareStatus',2) AND pt.productTypeCode = 'DBT' AND i.rowStatusId < NAME_CONST('compareStatus',2) AND mkt.rowStatusId < NAME_CONST('compareStatus',2) AND mt.marketTemplateCode = "OUT" AND di.maturityDate >= IF ( NAME_CONST('IncludeInActive',_utf8'N' COLLATE 'utf8_general_ci')='Y', 0, CONVERT(CURDATE(),UNSIGNED)) GROUP BY mv.marketId
|
Connection ID (thread ID): 323
|
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
|