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
|
Attachments
Activity
| Field | Original Value | New Value |
|---|---|---|
| Due Date | 2014-10-15 |
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;
|
| Attachment | show_variables.log [ 34101 ] |
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.
| Assignee | Elena Stepanova [ elenst ] |
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 Version/s | 5.5.40 [ 17100 ] | |
| Fix Version/s | 10.0 [ 16000 ] | |
| Affects Version/s | 10.1.0 [ 12200 ] | |
| Affects Version/s | 10.0.13 [ 16300 ] | |
| Affects Version/s | 5.5.39 [ 16301 ] | |
| Affects Version/s | 5.3.12 [ 12000 ] | |
| Affects Version/s | 5.2.14 [ 12101 ] | |
| Affects Version/s | 5.1.67 [ 12100 ] | |
| Affects Version/s | 5.5.35 [ 14000 ] | |
| Affects Version/s | 10.0.12 [ 15201 ] | |
| Assignee | Elena Stepanova [ elenst ] | Michael Widenius [ monty ] |
| Due Date | 2014-10-15 | |
| Labels | Mariadb group_concat | group_concat upstream |
| Priority | Major [ 3 ] | Critical [ 2 ] |
| Fix Version/s | 10.0.15 [ 17300 ] | |
| Fix Version/s | 10.0 [ 16000 ] | |
| Resolution | Fixed [ 1 ] | |
| Status | Open [ 1 ] | Closed [ 6 ] |
| Fix Version/s | 10.0.14 [ 17101 ] | |
| Fix Version/s | 10.0.15 [ 17300 ] |
| Component/s | Optimizer [ 10200 ] | |
| Component/s | OTHER [ 10125 ] |
| Workflow | MariaDB v2 [ 54000 ] | MariaDB v3 [ 62104 ] |
| Workflow | MariaDB v3 [ 62104 ] | MariaDB v4 [ 148225 ] |
Hi Ivan,
Can you provide a dump (at least table structures if not the data itself) and cnf file(s)?
Thanks