MySQL Bugs: #94778: Mysqld process meet message with "mysqld got signal 11" and crashing

Bug #94778	Mysqld process meet message with "mysqld got signal 11" and crashing
Submitted:	26 Mar 2019 10:55	Modified:	29 Mar 2019 14:01
Reporter:	sun jarod	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	5.7.21	OS:	Linux
Assigned to:		CPU Architecture:	x86

Description:
our production database meet issue with msg "mysqld got signal 11" and mysqld process crash many times(>5) in last 12 hours.

16:40:23 UTC - 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.
Attempting to collect some information that could help diagnose the problem.
As this is a crash and something is definitely wrong, the information
collection process might fail.

key_buffer_size=8388608
read_buffer_size=1986560
max_used_connections=23
max_threads=1400
thread_count=74
connection_count=20
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 8180495 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f275002c940
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 = 7f279a511e80 thread_stack 0x40000
/data/mysqlbase/mysql3306/bin/mysqld(my_print_stacktrace+0x2c)[0xec1a6c]
/data/mysqlbase/mysql3306/bin/mysqld(handle_fatal_signal+0x451)[0x7afb61]
/lib64/libpthread.so.0(+0xf370)[0x7f2c0977b370]
/data/mysqlbase/mysql3306/bin/mysqld(_ZN10Field_blob15copy_blob_valueEP11st_mem_root+0x25)[0x7dcc55]
/data/mysqlbase/mysql3306/bin/mysqld(_Z25mysql_prepare_blob_valuesP3THDR4ListI4ItemEP11st_mem_root+0x298)[0xe0ec38]
/data/mysqlbase/mysql3306/bin/mysqld(_Z12write_recordP3THDP5TABLEP9COPY_INFOS4_+0x999)[0xe0f909]
/data/mysqlbase/mysql3306/bin/mysqld(_ZN19Query_result_insert9send_dataER4ListI4ItemE+0xaf)[0xe10d1f]
/data/mysqlbase/mysql3306/bin/mysqld[0xc710b0]
/data/mysqlbase/mysql3306/bin/mysqld[0xc6d905]
/data/mysqlbase/mysql3306/bin/mysqld(_Z10sub_selectP4JOINP7QEP_TABb+0x18c)[0xc72d8c]
/data/mysqlbase/mysql3306/bin/mysqld(_ZN4JOIN4execEv+0x237)[0xc6bba7]
/data/mysqlbase/mysql3306/bin/mysqld(_Z12handle_queryP3THDP3LEXP12Query_resultyy+0x17d)[0xcd7e8d]
/data/mysqlbase/mysql3306/bin/mysqld(_ZN21Sql_cmd_insert_select7executeEP3THD+0x3d7)[0xe0e6c7]
/data/mysqlbase/mysql3306/bin/mysqld(_Z21mysql_execute_commandP3THDb+0x5b0)[0xc99e60]
/data/mysqlbase/mysql3306/bin/mysqld(_ZN13sp_instr_stmt9exec_coreEP3THDPj+0x50)[0xc23fd0]
/data/mysqlbase/mysql3306/bin/mysqld(_ZN12sp_lex_instr23reset_lex_and_exec_coreEP3THDPjb+0x434)[0xc25b44]
/data/mysqlbase/mysql3306/bin/mysqld(_ZN12sp_lex_instr29validate_lex_and_execute_coreEP3THDPjb+0xab)[0xc2649b]
/data/mysqlbase/mysql3306/bin/mysqld(_ZN13sp_instr_stmt7executeEP3THDPj+0x120)[0xc275f0]
/data/mysqlbase/mysql3306/bin/mysqld(_ZN7sp_head7executeEP3THDb+0x4e4)[0xc1f894]
/data/mysqlbase/mysql3306/bin/mysqld(_ZN7sp_head17execute_procedureEP3THDP4ListI4ItemE+0x757)[0xc23267]
/data/mysqlbase/mysql3306/bin/mysqld(_Z21mysql_execute_commandP3THDb+0x1de2)[0xc9b692]
/data/mysqlbase/mysql3306/bin/mysqld(_Z11mysql_parseP3THDP12Parser_state+0x38d)[0xca017d]
/data/mysqlbase/mysql3306/bin/mysqld(_Z16dispatch_commandP3THDPK8COM_DATA19enum_server_command+0xa1c)[0xca0c7c]
/data/mysqlbase/mysql3306/bin/mysqld(_Z10do_commandP3THD+0x177)[0xca2557]
/data/mysqlbase/mysql3306/bin/mysqld(handle_connection+0x270)[0xd5d4b0]
/data/mysqlbase/mysql3306/bin/mysqld(pfs_spawn_thread+0x1b4)[0x12237a4]
/lib64/libpthread.so.0(+0x7dc5)[0x7f2c09773dc5]
/lib64/libc.so.6(clone+0x6d)[0x7f2c0823073d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (7f2750fd2d50): is an invalid pointer
Connection ID (thread ID): 62
Status: NOT_KILLED

The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
2019-03-26T00:40:23.744266+08:00 0 [Warning] 'NO_AUTO_CREATE_USER' sql mode was not set.
2019-03-26T00:40:23.744381+08:00 0 [Note] --secure-file-priv is set to NULL. Operations related to importing and exporting data are disabled
2019-03-26T00:40:23.744423+08:00 0 [Note] /data/mysqlbase/mysql3306/bin/mysqld (mysqld 5.7.21) starting as process 103347 ...

How to repeat:
after restart the MySQL daemon process for a while the issue repeated.

Hi,

There is not enough information here to reproduce / verify the bug. 

What I can see 
 - you have network issues
 - you have RAM issues 
 - the crash happens around BLOB's

We need more data in order to proceed, from what I see here, there's no bug, you need to better configure your server and for that, if you can't do it yourself, you can contact our Support team and they will be happy to help you out.

kind regards
Bogdan

Thanks for you reply. We try to enable Linux core dump file function for next crashing to gather more details info. and We still try to figure out our configuration meets the best practice config for well tuning method. but we still think the strange thing about our crashing is which type blob column relative with our issue? BLOB or TEXT?  There a app version change before the issue happen. but we still have no clue how to find out the root cause, because the version change related with many source code. so we hope to get some useful from core dump.

Hi,

Well, as you can see in your crash dump:

/data/mysqlbase/mysql3306/bin/mysqld(my_print_stacktrace+0x2c)[0xec1a6c]
/data/mysqlbase/mysql3306/bin/mysqld(handle_fatal_signal+0x451)[0x7afb61]
/lib64/libpthread.so.0(+0xf370)[0x7f2c0977b370]
/data/mysqlbase/mysql3306/bin/mysqld(_ZN10Field_blob15copy_blob_valueEP11st_mem_root+0x25)[0x7dcc55]
/data/mysqlbase/mysql3306/bin/mysqld(_Z25mysql_prepare_blob_valuesP3THDR4ListI4ItemEP11st_mem_root+0x298)[0xe0ec38]
/data/mysqlbase/mysql3306/bin/mysqld(_Z12write_recordP3THDP5TABLEP9COPY_INFOS4_+0x999)[0xe0f909]
/data/mysqlbase/mysql3306/bin/mysqld(_ZN19Query_result_insert9send_dataER4ListI4ItemE+0xaf)[0xe10d1f]

You had INSERT into some table
MySQL started the write_record()
then prepare_blob_values() -> so you are inserting a BLOB when you get the crash

and then you get crash in copy_blob_value()

copy_blob_value() will crash in case of memory issues .. so I assume you are inserting some huge blob and you don't have enough ram to handle this or you have missconfigured mysql server to handle it. In any case I don't think this is a bug, but if you can make reproducible test case we can look into it. On the other hand, I do suggest you contact support team and get that server properly configured

kind regards
Bogdan

hi, we can finally reproduce the issue and crash our instance.

the procedure name "sp_f_lcinsured" will crash the instance in a huge quantit multiple parallel situation, but we can be safety execute this procedure single time or just a few parallel job. 

we found the last sql before crashing is row number 174-177 via binlog query. so we doubt the sql that cause the crashing focus on the  below：

the row number 181-189

insert into f_lcinsured(GRPCONTNO,CONTNO,INSUREDNO,PRTNO,APPNTNO,MANAGECOM,EXECUTECOM,FAMILYID,RELATIONTOMAININSURED,RELATIONTOAPPNT,ADDRESSNO,SEQUENCENO,NAME,SEX,BIRTHDAY,IDTYPE,IDNO,NATIVEPLACE,NATIONALITY,RGTADDRESS,MARRIAGE,MARRIAGEDATE,HEALTH,STATURE,AVOIRDUPOIS,DEGREE,CREDITGRADE,BANKCODE,BANKACCNO,ACCNAME,JOINCOMPANYDATE,STARTWORKDATE,POSITION,SALARY,OCCUPATIONTYPE,OCCUPATIONCODE,WORKTYPE,PLURALITYTYPE,SMOKEFLAG,CONTPLANCODE,OPERATOR,INSUREDSTAT,MAKEDATE,MAKETIME,MODIFYDATE,MODIFYTIME,UWFLAG,UWCODE,UWDATE,UWTIME,BMI,INSUREDPEOPLES,LICENSE,LICENSETYPE,CUSTOMERSEQNO,SOCIALINSUNO,SENDMSGFLAG,SENDMAILFLAG,JOINCONTDATE,WORKNO,CERTIFYCODE,STARTCODE,ENDCODE,SOCIALINSUFLAG,IDEXPDATE,GRPNO,ORGANCOMCODE,LASTNAME,FIRSTNAME,LASTNAMEEN,FIRSTNAMEEN,NAMEEN,LASTNAMEPY,FIRSTNAMEPY,LANGUAGE,MAINCUSTOMERNO,INSUREDTYPE,ISLONGVALID,IDSTARTDATE,INSUREDAPPAGE,SENIORITY,WORKCOMPNAME,PLANCODE,DEATHDATE,BLACKLISTFLAG,VIPVALUE,SENDQQFLAG,SENDMSNFLAG,SERVERAREA,SUBCUSTOMERNO,SUBSTANDARD,REMARK,PAKAGECODE,DESTINATION,PURPOSE,EMERGENCYCONTACT,EMERCONTPHONE,APPLYTODAY,MATERNITYFLAG,RESCUETYPE,DEPTCODE,SUBCOMPANYCODE,INSURECODE,WORKADDRESS,SOCIALINSUADDRESS,ORIGINCONTNO,ORIGININSUREDNO,COMCODE,MODIFYOPERATOR,IsSocialInsuFlag,ResidentsType,Ismotorcyclelicense,update_time,InsuredTaxType,create_time,MODIFY_time,redis_insert_flag,redis_update_flag,redis_del_flag)
	select GRPCONTNO,CONTNO,INSUREDNO,PRTNO,APPNTNO,MANAGECOM,EXECUTECOM,FAMILYID,RELATIONTOMAININSURED,RELATIONTOAPPNT,ADDRESSNO,SEQUENCENO,NAME,SEX,BIRTHDAY,IDTYPE,IDNO,NATIVEPLACE,NATIONALITY,RGTADDRESS,MARRIAGE,MARRIAGEDATE,HEALTH,STATURE,AVOIRDUPOIS,DEGREE,CREDITGRADE,BANKCODE,BANKACCNO,ACCNAME,JOINCOMPANYDATE,STARTWORKDATE,POSITION,SALARY,OCCUPATIONTYPE,OCCUPATIONCODE,WORKTYPE,PLURALITYTYPE,SMOKEFLAG,CONTPLANCODE,OPERATOR,INSUREDSTAT,MAKEDATE,MAKETIME,MODIFYDATE,MODIFYTIME,UWFLAG,UWCODE,UWDATE,UWTIME,BMI,INSUREDPEOPLES,LICENSE,LICENSETYPE,CUSTOMERSEQNO,SOCIALINSUNO,SENDMSGFLAG,SENDMAILFLAG,JOINCONTDATE,WORKNO,CERTIFYCODE,STARTCODE,ENDCODE,SOCIALINSUFLAG,IDEXPDATE,GRPNO,ORGANCOMCODE,LASTNAME,FIRSTNAME,LASTNAMEEN,FIRSTNAMEEN,NAMEEN,LASTNAMEPY,FIRSTNAMEPY,LANGUAGE,MAINCUSTOMERNO,INSUREDTYPE,ISLONGVALID,IDSTARTDATE,INSUREDAPPAGE,SENIORITY,WORKCOMPNAME,PLANCODE,DEATHDATE,BLACKLISTFLAG,VIPVALUE,SENDQQFLAG,SENDMSNFLAG,SERVERAREA,SUBCUSTOMERNO,SUBSTANDARD,REMARK,PAKAGECODE,DESTINATION,PURPOSE,EMERGENCYCONTACT,EMERCONTPHONE,APPLYTODAY,MATERNITYFLAG,RESCUETYPE,DEPTCODE,SUBCOMPANYCODE,INSURECODE,WORKADDRESS,SOCIALINSUADDRESS,ORIGINCONTNO,ORIGININSUREDNO,COMCODE,MODIFYOPERATOR,IsSocialInsuFlag,ResidentsType,Ismotorcyclelicense,update_time,InsuredTaxType,create_time,MODIFY_time,'N' redis_insert_flag,'N' redis_update_flag,'N' redis_del_flag
	from f_lcinsured_tmp t
	 where EVENT_FLAG=@event
	on duplicate key 
	update GRPCONTNO=values(GRPCONTNO),CONTNO=values(CONTNO),APPNTNO=values(APPNTNO),MANAGECOM=values(MANAGECOM),EXECUTECOM=values(EXECUTECOM),FAMILYID=values(FAMILYID),RELATIONTOMAININSURED=values(RELATIONTOMAININSURED),RELATIONTOAPPNT=values(RELATIONTOAPPNT),ADDRESSNO=values(ADDRESSNO),SEQUENCENO=values(SEQUENCENO),NAME=values(NAME),SEX=values(SEX),BIRTHDAY=values(BIRTHDAY),IDTYPE=values(IDTYPE),IDNO=values(IDNO),NATIVEPLACE=values(NATIVEPLACE),NATIONALITY=values(NATIONALITY),RGTADDRESS=values(RGTADDRESS),MARRIAGE=values(MARRIAGE),MARRIAGEDATE=values(MARRIAGEDATE),HEALTH=values(HEALTH),STATURE=values(STATURE),AVOIRDUPOIS=values(AVOIRDUPOIS),DEGREE=values(DEGREE),CREDITGRADE=values(CREDITGRADE),BANKCODE=values(BANKCODE),BANKACCNO=values(BANKACCNO),ACCNAME=values(ACCNAME),JOINCOMPANYDATE=values(JOINCOMPANYDATE),STARTWORKDATE=values(STARTWORKDATE),POSITION=values(POSITION),SALARY=values(SALARY),OCCUPATIONTYPE=values(OCCUPATIONTYPE),OCCUPATIONCODE=values(OCCUPATIONCODE),WORKTYPE=values(WORKTYPE),PLURALITYTYPE=values(PLURALITYTYPE),SMOKEFLAG=values(SMOKEFLAG),CONTPLANCODE=values(CONTPLANCODE),OPERATOR=values(OPERATOR),INSUREDSTAT=values(INSUREDSTAT),MAKEDATE=values(MAKEDATE),MAKETIME=values(MAKETIME),MODIFYDATE=values(MODIFYDATE),MODIFYTIME=values(MODIFYTIME),UWFLAG=values(UWFLAG),UWCODE=values(UWCODE),UWDATE=values(UWDATE),UWTIME=values(UWTIME),BMI=values(BMI),INSUREDPEOPLES=values(INSUREDPEOPLES),LICENSE=values(LICENSE),LICENSETYPE=values(LICENSETYPE),CUSTOMERSEQNO=values(CUSTOMERSEQNO),SOCIALINSUNO=values(SOCIALINSUNO),SENDMSGFLAG=values(SENDMSGFLAG),SENDMAILFLAG=values(SENDMAILFLAG),JOINCONTDATE=values(JOINCONTDATE),WORKNO=values(WORKNO),CERTIFYCODE=values(CERTIFYCODE),STARTCODE=values(STARTCODE),ENDCODE=values(ENDCODE),SOCIALINSUFLAG=values(SOCIALINSUFLAG),IDEXPDATE=values(IDEXPDATE),GRPNO=values(GRPNO),ORGANCOMCODE=values(ORGANCOMCODE),LASTNAME=values(LASTNAME),FIRSTNAME=values(FIRSTNAME),LASTNAMEEN=values(LASTNAMEEN),FIRSTNAMEEN=values(FIRSTNAMEEN),NAMEEN=values(NAMEEN),LASTNAMEPY=values(LASTNAMEPY),FIRSTNAMEPY=values(FIRSTNAMEPY),LANGUAGE=values(LANGUAGE),MAINCUSTOMERNO=values(MAINCUSTOMERNO),INSUREDTYPE=values(INSUREDTYPE),ISLONGVALID=values(ISLONGVALID),IDSTARTDATE=values(IDSTARTDATE),INSUREDAPPAGE=values(INSUREDAPPAGE),SENIORITY=values(SENIORITY),WORKCOMPNAME=values(WORKCOMPNAME),PLANCODE=values(PLANCODE),DEATHDATE=values(DEATHDATE),BLACKLISTFLAG=values(BLACKLISTFLAG),VIPVALUE=values(VIPVALUE),SENDQQFLAG=values(SENDQQFLAG),SENDMSNFLAG=values(SENDMSNFLAG),SERVERAREA=values(SERVERAREA),SUBCUSTOMERNO=values(SUBCUSTOMERNO),SUBSTANDARD=values(SUBSTANDARD),REMARK=values(REMARK),PAKAGECODE=values(PAKAGECODE),DESTINATION=values(DESTINATION),PURPOSE=values(PURPOSE),EMERGENCYCONTACT=values(EMERGENCYCONTACT),EMERCONTPHONE=values(EMERCONTPHONE),APPLYTODAY=values(APPLYTODAY),MATERNITYFLAG=values(MATERNITYFLAG),RESCUETYPE=values(RESCUETYPE),DEPTCODE=values(DEPTCODE),SUBCOMPANYCODE=values(SUBCOMPANYCODE),INSURECODE=values(INSURECODE),WORKADDRESS=values(WORKADDRESS),SOCIALINSUADDRESS=values(SOCIALINSUADDRESS),ORIGINCONTNO=values(ORIGINCONTNO),ORIGININSUREDNO=values(ORIGININSUREDNO),COMCODE=values(COMCODE),MODIFYOPERATOR=values(MODIFYOPERATOR),IsSocialInsuFlag=values(IsSocialInsuFlag),ResidentsType=values(ResidentsType),Ismotorcyclelicense=values(Ismotorcyclelicense),update_time=values(update_time),InsuredTaxType=values(InsuredTaxType)
	,redis_update_flag=values(redis_update_flag),MODIFY_time =values(MODIFY_time)
	;
   commit;

Not sure our error stack meet bug #79243 or a new bug ?

HI,

Please, always try to use latest release for the version that you are using.

Seems that you are hit by a bug that is already fixed:

Bug  from the internal database
-------------------------------
Fixed in 5.7.22, 8.0.5.
.
INSERT ... ON DUPLICATE KEY UPDATE could be handled improperly if a
source table produced no rows.

Can you try upgrading to 5.7.25 and see whether you can still reproduce the bug ?????

thanks for your help to confirm that we hit this bug.

This is our production db. for this reason we will try to split the SQL into two parts and test the result. if the issue is still exists. then we will try to consider the newest version of 5.7

Hi,

Let us know of your findings ........

Hi, we can re-procedure the crashing when source table produced no rows on SQL like 'INSERT select on DUPLICATE KEY UPDATE' on our production db version 5.7.21.So we changed the implementation of those SQL like 'INSERT .. select .. on DUPLICATE KEY UPDATE', the issue resolved.

thanks for your help.you can close this case.

Sun,

Thank you very much for your feedback.

This bug report is now closed.