[Maria-discuss] New Question: Consistently able to crash MariaDB 10.0.25 and 10.1.4 - Signal 11

2 Jun 2016

      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<TABLE_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...

[Maria-discuss] New Question: Consistently able to crash MariaDB 10.0.25 and 10.1.4 - Signal 11

AskMonty KB