Hello,
A new question has been asked in "MariaDB Community" by gaurav_sharma_g1. Please answer it at http://mariadb.com/kb/en/consistently-able-to-crash-mariadb-10025-and-1014-s... as the person asking the question may not be subscribed to the mailing list.
--------------------------------
We are able to consistently reproduce the crash of MariaDB with the following query:
{{{
drop procedure if exists sp_getServerAndAgentHeartbeats;
DELIMITER $$
CREATE PROCEDURE sp_getServerAndAgentHeartbeats()
begin
select allof.*
from (select bothof.Name fullname, bothof.instanceid name, bothof.DMSUrl id, bothof.ts heartbeat, bothof.tz, bothof.local_time, bothof.pdfagentsetup, bothof.origin, bothof.dmsid, bothof.DealerID dealerid, bothof.server_group, bothof.hasNotes
from
(
select d.Name, dlrurl.DealerID, DMSUrl, DMSSuff, psh.*
, (select OptionValue from DealerSetupOption where DealerID = d.ID and OptionKey = 'Dealer.timezone.id') tz
, (select timestampadd(minute, (480 + OptionValue), current_timestamp()) from DealerSetupOption where DealerID = d.ID and OptionKey = 'Dealer.timezone' ) local_time
, (select OptionValue from DealerSetupOption where DealerID = d.ID and OptionKey = 'pdfserver.settings') pdfagentsetup
, "server" origin
, (select DMSID from DealerDMS where DealerID = d.ID) dmsid
, (select guid from dms_staging_db.server_groups sg where sg.ip = DMSUrl and find_in_set(DMSSuff, sg.suffix) > 0 limit 1) server_group
, IF((select count(*) from dms_staging_db.server_notes sn where sn.uuid = server_group) >= 1,'true','false') hasNotes
from (select DealerID, OptionValue as DMSUrl
from DealerSetupOption dso1
where OptionKey = 'Dealer.Pdf.Source.Url') dlrurl
inner join (select DealerID, OptionValue as DMSSuff
from DealerSetupOption dso1
where OptionKey = 'ADP.SessionSuffix') dlrsuff
on dlrsuff.DealerID = dlrurl.DealerID
inner join Dealer d on dlrurl.DealerID = d.ID
inner join PdfServerHeartbeat psh on psh.dealerurl = DMSUrl and psh.instanceid = concat_ws('', '_', DMSSuff)
where coalesce(DMSUrl, '') !='' and coalesce(DMSSuff, '') !=''
) bothof
union
(
select d.Name fullname
, aih.DealerIdentifier name
, aih.DealerMarker id
, aih.lastgotdata heartbeat
, (select OptionValue from DealerSetupOption where DealerID = d.ID and OptionKey = 'Dealer.timezone.id') tz
, (select timestampadd(minute, (480 + OptionValue), current_timestamp()) from DealerSetupOption where DealerID = d.ID and OptionKey = 'Dealer.timezone' ) local_time
, (select OptionValue from DealerSetupOption where DealerID = d.ID and OptionKey = 'pdfserver.settings') pdfagentsetup
, "agent" origin
, (select DMSID from DealerDMS where DealerID = d.ID) dmsid
, d.ID DealerID
, (select guid from dms_staging_db.server_groups sg where sg.ip = aih.DealerMarker and find_in_set(aih.DealerIdentifier, sg.suffix) > 0 limit 1) server_group
, IF((select count(*) from dms_staging_db.server_notes sn where sn.uuid = server_group) >= 1,'true','false') hasNotes
from (select DealerMarker, DealerIdentifier, count(1) pcount, max(ts) lastgotdata
from AgentInstanceHeartbeat
where Instance != 'pdf_server'
group by DealerMarker, DealerIdentifier
) aih
inner join (
select dlrurl.DealerID, DMSUrl, DMSSuff
from (select DealerID, OptionValue as DMSUrl
from DealerSetupOption dso1
where OptionKey = 'Dealer.Pdf.Source.Url') dlrurl
inner join (select DealerID, OptionValue as DMSSuff
from DealerSetupOption dso1
where OptionKey = 'ADP.SessionSuffix') dlrsuff
on dlrsuff.DealerID = dlrurl.DealerID
where coalesce(DMSUrl, '') !='' and coalesce(DMSSuff, '') !=''
) dsodata on (aih.DealerMarker = dsodata.DMSUrl and aih.DealerIdentifier = dsodata.DMSSuff)
inner join Dealer d on (dsodata.DealerID = d.ID)
where d.isValid = 1
)
order by 1 asc) allof;
end$$
}}}
**Our developer wrote this query as a modification to an existing query and upon executing the above SP, we consistently see the following stack trace in the MariaDB logs:**
{{{
Thread pointer: 0x0x7f301afa0008
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 = 0x7f2fdce88140 thread_stack 0x48400
/usr/sbin/mysqld(my_print_stacktrace+0x2b)[0x7f48c34c197b]
/usr/sbin/mysqld(handle_fatal_signal+0x475)[0x7f48c3020825]
/lib64/libpthread.so.0(+0xf7e0)[0x7f48c26267e0]
/usr/sbin/mysqld(_ZN17Dependency_marker11visit_fieldEP10Item_field+0x4b)[0x7f48c304765b]
/usr/sbin/mysqld(_ZN10Item_field30enumerate_field_refs_processorEPh+0x12)[0x7f48c302d612]
mysys/stacktrace.c:247(my_print_stacktrace)[0x7f48c3043c43]
sql/signal_handler.cc:160(handle_fatal_signal)[0x7f48c30a2690]
sql/item.cc:6707(Dependency_marker::visit_field(Item_field*))[0x7f48c303ddc4]
sql/item_subselect.cc:329(Item_subselect::enumerate_field_refs_processor(unsigned char*))[0x7f48c303ef5e]
sql/item.cc:5054(Item_field::fix_fields(THD*, Item**))[0x7f48c3040098]
/usr/sbin/mysqld(_ZN9Item_func10fix_fieldsEP3THDPP4Item+0x1a7)[0x7f48c30732a7]
sql/item_func.cc:208(Item_func::fix_fields(THD*, Item**))[0x7f48c2e713cb]
sql/sql_base.cc:8629(setup_conds(THD*, TABLE_LIST*, List&, Item**))[0x7f48c2ef5160]
sql/sql_select.cc:646(setup_without_group)[0x7f48c30a5b44]
sql/item_subselect.cc:259(Item_subselect::fix_fields(THD*, Item**))[0x7f48c30a9a0d]
sql/item_func.cc:208(Item_func::fix_fields(THD*, Item**))[0x7f48c30732a7]
sql/item_func.cc:208(Item_func::fix_fields(THD*, Item**))[0x7f48c30732a7]
sql/sql_base.cc:7901(setup_fields(THD*, Item**, List<Item>&, enum_mark_columns, List<Item>*, bool))[0x7f48c2e70c13]
sql/sql_select.cc:791(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*))[0x7f48c2ef50a2]
sql/sql_union.cc:454(st_select_lex_unit::prepare(THD*, select_result*, unsigned long))[0x7f48c2f400ec]
sql/sql_derived.cc:671(mysql_derived_prepare(THD*, LEX*, TABLE_LIST*))[0x7f48c2e8e570]
sql/sql_derived.cc:195(mysql_handle_single_derived(LEX*, TABLE_LIST*, unsigned int))[0x7f48c2e8f327]
sql/sql_lex.cc:3820(st_select_lex::handle_derived(LEX*, unsigned int))[0x7f48c2ea46b7]
sql/table.cc:7126(TABLE_LIST::handle_derived(LEX*, unsigned int))[0x7f48c2f55767]
sql/sql_lex.cc:3820(st_select_lex::handle_derived(LEX*, unsigned int))[0x7f48c2ea46b7]
sql/sql_select.cc:711(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*))[0x7f48c2ef4e31]
sql/sql_select.cc:3429(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*))[0x7f48c2f01aed]
sql/sql_select.cc:384(handle_select(THD*, LEX*, select_result*, unsigned long))[0x7f48c2f04bfd]
/usr/sbin/mysqld(+0x451102)[0x7f48c2ea7102]
sql/sql_parse.cc:5895(execute_sqlcom_select)[0x7f48c2eb33bf]
sql/sp_head.cc:3215(sp_instr_stmt::exec_core(THD*, unsigned int*))[0x7f48c311c9be]
sql/sp_head.cc:2979(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x7f48c312243e]
sql/sp_head.cc:3129(sp_instr_stmt::execute(THD*, unsigned int*))[0x7f48c312293d]
sql/sp_head.cc:1319(sp_head::execute(THD*, bool))[0x7f48c311f91c]
sql/sp_head.cc:2104(sp_head::execute_procedure(THD*, List<Item>*))[0x7f48c3120cc6]
sql/sql_parse.cc:2405(do_execute_sp)[0x7f48c2eb3b30]
sql/sp_head.cc:3215(sp_instr_stmt::exec_core(THD*, unsigned int*))[0x7f48c311c9be]
sql/sp_head.cc:2979(sp_lex_keeper::reset_lex_and_exec_core(THD*, unsigned int*, bool, sp_instr*))[0x7f48c312243e]
sql/sp_head.cc:3129(sp_instr_stmt::execute(THD*, unsigned int*))[0x7f48c312293d]
sql/sp_head.cc:1319(sp_head::execute(THD*, bool))[0x7f48c311f91c]
sql/sp_head.cc:2104(sp_head::execute_procedure(THD*, List<Item>*))[0x7f48c3120cc6]
sql/sql_parse.cc:2405(do_execute_sp)[0x7f48c2eb3b30]
sql/sql_parse.cc:7314(mysql_parse(THD*, char*, unsigned int, Parser_state*))[0x7f48c2eb68fd]
sql/sql_parse.cc:1486(dispatch_command(enum_server_command, THD*, char*, unsigned int))[0x7f48c2eb955b]
sql/sql_parse.cc:1109(do_command(THD*))[0x7f48c2eb9b0b]
sql/sql_connect.cc:1350(do_handle_one_connection(THD*))[0x7f48c2f7569f]
sql/sql_connect.cc:1264(handle_one_connection)[0x7f48c2f757f7]
/lib64/libpthread.so.0(+0x7aa1)[0x7f48c261eaa1]
/lib64/libc.so.6(clone+0x6d)[0x7f48c0b03aad]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (0x7f302e2a8020): is an invalid pointer
Connection ID (thread ID): 264
Status: NOT_KILLED
}}}
After this MariaDB crashes and restarts.
**For now we have reverted the changes back to the old code which is shown below:**
{{{
drop procedure if exists sp_getServerAndAgentHeartbeats;
DELIMITER $$
CREATE PROCEDURE sp_getServerAndAgentHeartbeats()
begin
select allof.*, IF((select count(*) from dms_staging_db.server_notes sn where sn.uuid = concat(allof.dealerid,'-notes')) >= 1,'true','false') hasNotes from (select bothof.Name fullname, bothof.instanceid name, bothof.DMSUrl id, bothof.ts heartbeat, bothof.tz, bothof.local_time, bothof.pdfagentsetup, bothof.origin, bothof.dmsid, bothof.DealerID dealerid
from
(
(
select d.Name, dlrurl.DealerID, DMSUrl, DMSSuff, psh.*
, (select OptionValue from DealerSetupOption where DealerID = d.ID and OptionKey = 'Dealer.timezone.id') tz
, (select timestampadd(minute, (480 + OptionValue), current_timestamp()) from DealerSetupOption where DealerID = d.ID and OptionKey = 'Dealer.timezone' ) local_time
, (select OptionValue from DealerSetupOption where DealerID = d.ID and OptionKey = 'pdfserver.settings') pdfagentsetup
, "server" origin
, (select DMSID from DealerDMS where DealerID = d.ID) dmsid
from (select DealerID, OptionValue as DMSUrl
from DealerSetupOption dso1
where OptionKey = 'Dealer.Pdf.Source.Url') dlrurl
inner join (select DealerID, OptionValue as DMSSuff
from DealerSetupOption dso1
where OptionKey = 'ADP.SessionSuffix') dlrsuff
on dlrsuff.DealerID = dlrurl.DealerID
inner join Dealer d on dlrurl.DealerID = d.ID
inner join PdfServerHeartbeat psh on psh.dealerurl = DMSUrl and psh.instanceid = concat_ws('', '_', DMSSuff)
where coalesce(DMSUrl, '') !='' and coalesce(DMSSuff, '') !=''
)
union
(
select d.Name, data.*
, (select OptionValue from DealerSetupOption where DealerID = d.ID and OptionKey = 'Dealer.timezone.id') tz
, (select timestampadd(minute, (480 + OptionValue), current_timestamp()) from DealerSetupOption where DealerID = d.ID and OptionKey = 'Dealer.timezone' ) local_time
, (select OptionValue from DealerSetupOption where DealerID = d.ID and OptionKey = 'pdfserver.settings') pdfagentsetup
, "server" origin
, (select DMSID from DealerDMS where DealerID = d.ID) dmsid
from (
select replace(dealerurl, 'dealer-', '') DealerID, dealerurl DMSUrl, null DMSSuff, psh1.*
from PdfServerHeartbeat psh1
where dealerurl like 'dealer-%'
) data
inner join Dealer d on data.DealerID = d.ID
)
) bothof
union
(
select d.Name fullname
, aih.DealerIdentifier name
, aih.DealerMarker id
, aih.lastgotdata heartbeat
, (select OptionValue from DealerSetupOption where DealerID = d.ID and OptionKey = 'Dealer.timezone.id') tz
, (select timestampadd(minute, (480 + OptionValue), current_timestamp()) from DealerSetupOption where DealerID = d.ID and OptionKey = 'Dealer.timezone' ) local_time
, (select OptionValue from DealerSetupOption where DealerID = d.ID and OptionKey = 'pdfserver.settings') pdfagentsetup
, "agent" origin
, (select DMSID from DealerDMS where DealerID = d.ID) dmsid
, d.ID DealerID
from (select DealerMarker, DealerIdentifier, count(1) pcount, max(ts) lastgotdata
from AgentInstanceHeartbeat
where Instance != 'pdf_server'
group by DealerMarker, DealerIdentifier
) aih
inner join (
select dlrurl.DealerID, DMSUrl, DMSSuff
from (select DealerID, OptionValue as DMSUrl
from DealerSetupOption dso1
where OptionKey = 'Dealer.Pdf.Source.Url') dlrurl
inner join (select DealerID, OptionValue as DMSSuff
from DealerSetupOption dso1
where OptionKey = 'ADP.SessionSuffix') dlrsuff
on dlrsuff.DealerID = dlrurl.DealerID
where coalesce(DMSUrl, '') !='' and coalesce(DMSSuff, '') !=''
) dsodata on (aih.DealerMarker = dsodata.DMSUrl and aih.DealerIdentifier = dsodata.DMSSuff)
inner join Dealer d on (dsodata.DealerID = d.ID)
where d.isValid = 1
)
order by 1 asc) allof;
end$$
}}}
--------------------------------
To view or answer this question please visit: http://mariadb.com/kb/en/consistently-able-to-crash-mariadb-10025-and-1014-s...
AskMonty KB