[MDEV-6743] MariaDB server crash signal 11 on ORDER BY 1 when using GROUP_CONCAT(IF Created: 2014-09-15  Updated: 2015-01-28  Resolved: 2014-09-23

Status: Closed
Project: MariaDB Server
Component/s: Optimizer
Affects Version/s: 5.1.67, 5.2.14, 5.3.12, 5.5.39, 10.0.13, 10.1.0
Fix Version/s: 5.5.40, 10.0.14

Type: Bug Priority: Critical
Reporter: Stoykov (Inactive) Assignee: Michael Widenius
Resolution: Fixed Votes: 0
Labels: group_concat, upstream
Environment:

Red Hat Enterprise Linux 2.6.32-220.el6.x86_64

Attachments: File real.products_3104_error.log     File show_variables.log    

 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



 Comments   
Comment by Elena Stepanova [ 2014-09-15 ]

Hi Ivan,

Can you provide a dump (at least table structures if not the data itself) and cnf file(s)?

Thanks

Comment by Stoykov (Inactive) [ 2014-09-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;

Comment by Stoykov (Inactive) [ 2014-09-15 ]

file with the SHOW GLOBAL VARIABLES; output

Comment by Stoykov (Inactive) [ 2014-09-15 ]

The data dump is ~ 40Gb ...

Comment by Elena Stepanova [ 2014-09-15 ]

Ivan,

There are about a dozen tables in the query:
marketvenues
market
markettemplate
marketinstruments
instrument
debtinstrument
product
producttype
venue
tradingplatform

Comment by Stoykov (Inactive) [ 2014-09-15 ]

The listed tables structures will be provided, I am checking now if I can get the data dumped as well.

Comment by Elena Stepanova [ 2014-09-15 ]

40Gb is a lot, lets go with the structures first, maybe we'll be able to reproduce it without the whole dump.

Comment by Elena Stepanova [ 2014-09-19 ]

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.

Comment by Michael Widenius [ 2014-09-23 ]

Fix pushed into 5.5.
Will be in 10.0 at next merge.

Generated at Thu Feb 08 07:14:15 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.