Details
-
Type:
Bug
-
Status: Closed
-
Priority:
Critical
-
Resolution: Fixed
-
Affects Version/s: 5.1.67, 5.2.14, 5.3.12, 5.5.39, 10.0.13, 10.1.0
-
Component/s: Optimizer
-
Labels:
-
Environment: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
Gliffy Diagrams
Attachments
Activity
- All
- Comments
- Work Log
- History
- Activity
- Transitions
table:
CREATE TEMPORARY TABLE tempdb.tt_markets ( marketId BIGINT(20) NULL, otcMarketVenueId BIGINT(20) NULL, Exchange VARCHAR(150) NULL, ExchangeCommonCode VARCHAR(150) NULL, TSExchange VARCHAR(150) NULL )ENGINE=MEMORY; CREATE TEMPORARY TABLE IF NOT EXISTS tempdb.tt_MarketVenueIds (marketvenueId BIGINT(20) NULL)ENGINE = MEMORY;
file with the SHOW GLOBAL VARIABLES; output
The data dump is ~ 40Gb ...
Ivan,
There are about a dozen tables in the query:
marketvenues
market
markettemplate
marketinstruments
instrument
debtinstrument
product
producttype
venue
tradingplatform
The listed tables structures will be provided, I am checking now if I can get the data dumped as well.
40Gb is a lot, lets go with the structures first, maybe we'll be able to reproduce it without the whole dump.
Test case:
CREATE TABLE t1 (pk INT, t2_id INT, t5_id INT, PRIMARY KEY (pk)); INSERT INTO t1 VALUES (1,3,12),(2,3,15); CREATE TABLE t2 (pk INT, PRIMARY KEY (pk)); INSERT INTO t2 VALUES (4),(5); CREATE TABLE t3 (t2_id INT, t4_id INT); INSERT INTO t3 VALUES (6,11),(7,12); CREATE TABLE t4 (id INT); INSERT INTO t4 VALUES (13),(14); CREATE TABLE t5 (pk INT, f VARCHAR(50), t6_id INT, PRIMARY KEY (pk)); INSERT INTO t5 VALUES (9,'FOO',NULL); CREATE TABLE t6 (pk INT, f VARCHAR(120), b TINYINT(4), PRIMARY KEY (pk)); PREPARE stmt FROM " SELECT t1.t2_id, GROUP_CONCAT(IF (t6.b, t6.f, t5.f) ORDER BY 1) FROM t1 JOIN t2 ON t1.t2_id = t2.pk JOIN t3 ON t2.pk = t3.t2_id JOIN t4 ON t4.id = t3.t4_id JOIN t5 ON t1.t5_id = t5.pk LEFT JOIN t6 ON t6.pk = t5.t6_id GROUP BY t1.t2_id "; EXECUTE stmt; EXECUTE stmt; EXECUTE stmt;
Stack trace from 5.5 revno 4292:
#3 <signal handler called> #4 0x000000000089025c in Item_func_group_concat::fix_fields (this=0x390ce88, thd=0x385d760, ref=0x390ca68) at 5.5/sql/item_sum.cc:3417 #5 0x00000000005de668 in setup_fields (thd=0x385d760, ref_pointer_array=0x3912a98, fields=..., mark_used_columns=MARK_COLUMNS_READ, sum_func_list=0x38dbc10, allow_sum_func=true) at 5.5/sql/sql_base.cc:8166 #6 0x000000000065fb90 in JOIN::prepare (this=0x38db8e0, rref_pointer_array=0x390b7f8, tables_init=0x390d2c0, wild_num=0, conds_init=0x38db6a8, og_num=1, order_init=0x0, skip_order_by=false, group_init=0x3912a38, having_init=0x0, proc_param_init=0x0, select_lex_arg=0x390b588, unit_arg=0x390aeb0) at 5.5/sql/sql_select.cc:723 #7 0x00000000006682df in mysql_select (thd=0x385d760, rref_pointer_array=0x390b7f8, tables=0x390d2c0, wild_num=0, fields=..., conds=0x38db6a8, og_num=1, order=0x0, group=0x3912a38, having=0x0, proc_param=0x0, select_options=2416184064, result=0x3912a78, unit=0x390aeb0, select_lex=0x390b588) at 5.5/sql/sql_select.cc:3074 #8 0x000000000065ee61 in handle_select (thd=0x385d760, lex=0x390ae00, result=0x3912a78, setup_tables_done_option=0) at 5.5/sql/sql_select.cc:319 #9 0x0000000000638413 in execute_sqlcom_select (thd=0x385d760, all_tables=0x390d2c0) at 5.5/sql/sql_parse.cc:4688 #10 0x00000000006315f5 in mysql_execute_command (thd=0x385d760) at 5.5/sql/sql_parse.cc:2233 #11 0x0000000000652465 in Prepared_statement::execute (this=0x3908bf0, expanded_query=0x7fd3859e2cc0, open_cursor=false) at 5.5/sql/sql_prepare.cc:3928 #12 0x000000000065157c in Prepared_statement::execute_loop (this=0x3908bf0, expanded_query=0x7fd3859e2cc0, open_cursor=false, packet=0x0, packet_end=0x0) at 5.5/sql/sql_prepare.cc:3587 #13 0x000000000064f6a0 in mysql_sql_stmt_execute (thd=0x385d760) at 5.5/sql/sql_prepare.cc:2737 #14 0x0000000000631623 in mysql_execute_command (thd=0x385d760) at 5.5/sql/sql_parse.cc:2243 #15 0x000000000063ab22 in mysql_parse (thd=0x385d760, rawbuf=0x38db488 "EXECUTE stmt", length=12, parser_state=0x7fd3859e3650) at 5.5/sql/sql_parse.cc:5799 #16 0x000000000062eb6d in dispatch_command (command=COM_QUERY, thd=0x385d760, packet=0x38d3c71 "EXECUTE stmt", packet_length=12) at 5.5/sql/sql_parse.cc:1079 #17 0x000000000062dcf9 in do_command (thd=0x385d760) at 5.5/sql/sql_parse.cc:793 #18 0x000000000072fd5a in do_handle_one_connection (thd_arg=0x385d760) at 5.5/sql/sql_connect.cc:1266 #19 0x000000000072f819 in handle_one_connection (arg=0x385d760) at 5.5/sql/sql_connect.cc:1181 #20 0x0000000000b64569 in pfs_spawn_thread (arg=0x396afe0) at 5.5/storage/perfschema/pfs.cc:1015 #21 0x00007fd38a010b50 in start_thread (arg=<optimized out>) at pthread_create.c:304 #22 0x00007fd388b6020d in clone () at ../sysdeps/unix/sysv/linux/x86_64/clone.S:112
Also reproducible on all of MySQL 5.1-5.7.
Fix pushed into 5.5.
Will be in 10.0 at next merge.
Hi Ivan,
Can you provide a dump (at least table structures if not the data itself) and cnf file(s)?
Thanks