The Ultimate Guide to High Availability with MariaDB
This is a read-only copy of the MariaDB Knowledgebase generated on 2024-11-17. For the latest, interactive version please visit https://mariadb.com/kb/.

Consistently able to crash MariaDB 10.0.25 and 10.1.4 - Signal 11

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$$

Answer Answered by Elena Stepanova in this comment.

We actually have a bug report like this, MDEV-9208. If you can provide a test case (including tables, data and everything needed to get the crash), please add it as a comment to the bug report, so that whoever fixes the bug would make sure the fix covers your variation of the problem as well.

Content reproduced on this site is the property of its respective owners, and this content is not reviewed in advance by MariaDB. The views, information and opinions expressed by this content do not necessarily represent those of MariaDB or any other party.