Bug #70328 MySQL Server crashs while running larger Queries
Submitted: 13 Sep 2013 10:57 Modified: 3 Jan 2014 19:55
Reporter: Sebastian Strzelczyk Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.6.13 OS:Linux (Linux 3.0.74-0.6.10-default x86_64 (Suse Enterprice 11))
Assigned to: CPU Architecture:Any

[13 Sep 2013 10:57] Sebastian Strzelczyk
Description:
MySQL Server is crashing unexpected, after moving the database from 5.3 to 5.6.12 the server process is crashing nearly once a week, but we don't find the reason why. A upgrade to 5.6.13 didn't help.

the crash report don't help to find a repeatable way, so I provide the information I have out of the error.log:

------------------------------
2013-09-13 12:19:02 8466 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 8384528 bytes)
2013-09-13 12:19:02 8466 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 8384528 bytes)

10:21:40 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.
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.

key_buffer_size=524288000
read_buffer_size=8388608
max_used_connections=87
max_threads=151
thread_count=8
connection_count=8
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 3606529 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f07cae2ce20
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 = 7f07d55f6e38 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x900575]
/usr/sbin/mysqld(handle_fatal_signal+0x401)[0x67b411]
/lib64/libpthread.so.0(+0xf7c0)[0x7f101050d7c0]
/usr/sbin/mysqld(_ZN10JOIN_CACHE20skip_record_if_matchEv+0x25)[0x8485b5]
/usr/sbin/mysqld(_ZN10JOIN_CACHE21join_null_complementsEb+0x1ec)[0x84bf8c]
/usr/sbin/mysqld(_ZN10JOIN_CACHE12join_recordsEb+0xec)[0x84872c]
/usr/sbin/mysqld(_ZN10JOIN_CACHE12join_recordsEb+0x1d4)[0x848814]
/usr/sbin/mysqld(_Z13sub_select_opP4JOINP13st_join_tableb+0x3f)[0x6d9f6f]
/usr/sbin/mysqld(_ZN4JOIN4execEv+0x406)[0x6d8d76]
/usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_P10SQL_I_ListI8st_orderESB_S7_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x198)[0x7217e8]
/usr/sbin/mysqld(_Z13handle_selectP3THDP13select_resultm+0x19f)[0x7219ef]
/usr/sbin/mysqld[0x6f982d]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x19d1)[0x6fc191]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x3a5)[0x6ff8c5]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x10b3)[0x700ef3]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x11f)[0x6cc44f]
/usr/sbin/mysqld(handle_one_connection+0x45)[0x6cc525]
/usr/sbin/mysqld(pfs_spawn_thread+0x126)[0x997c86]
/lib64/libpthread.so.0(+0x77b6)[0x7f10105057b6]
/lib64/libc.so.6(clone+0x6d)[0x7f100f27dc5d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (35c55c00): <<was provided but removed for security reasons>>
Connection ID (thread ID): 565401
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.

---------------------

2013-08-23 12:41:06 32542 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 8384528 bytes)
2013-08-30 12:16:07 32542 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 8384528 bytes)
2013-08-30 12:16:07 32542 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 8384528 bytes)
2013-08-30 12:21:16 32542 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 8384528 bytes)
2013-08-30 12:24:06 32542 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 8384528 bytes)
2013-08-30 12:24:06 32542 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 8384528 bytes)
2013-08-30 16:40:25 32542 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 8384528 bytes)
2013-08-30 16:40:25 32542 [ERROR] /usr/sbin/mysqld: Out of memory (Needed 8384528 bytes)
05:46:25 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.
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.

key_buffer_size=524288000
read_buffer_size=8388608
max_used_connections=109
max_threads=151
thread_count=6
connection_count=6
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 3606525 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f44f0018f30
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...
130902 07:46:26 mysqld_safe Number of processes running now: 0

--------------------------

10:08:46 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.
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.

key_buffer_size=524288000
read_buffer_size=8388608
max_used_connections=110
max_threads=151
thread_count=8
connection_count=8
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 3606525 K  bytes of memory

Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x7f54f6994c80
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 = 7f54f9ef4e38 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x35)[0x8fd185]
/usr/sbin/mysqld(handle_fatal_signal+0x401)[0x67d2e1]
/lib64/libpthread.so.0(+0xf7c0)[0x7f5d34a537c0]
/usr/sbin/mysqld(_ZN10JOIN_CACHE20skip_record_if_matchEv+0x25)[0x8468c5]
/usr/sbin/mysqld(_ZN10JOIN_CACHE21join_null_complementsEb+0x22c)[0x84a29c]
/usr/sbin/mysqld(_ZN10JOIN_CACHE12join_recordsEb+0xec)[0x846a3c]
/usr/sbin/mysqld(_ZN10JOIN_CACHE12join_recordsEb+0x1d4)[0x846b24]
/usr/sbin/mysqld(_Z13sub_select_opP4JOINP13st_join_tableb+0x3f)[0x6db75f]
/usr/sbin/mysqld(_ZN4JOIN4execEv+0x3f6)[0x6d80e6]
/usr/sbin/mysqld(_Z12mysql_selectP3THDP10TABLE_LISTjR4ListI4ItemEPS4_P10SQL_I_ListI8st_orderESB_S7_yP13select_resultP18st_select_lex_unitP13st_select_lex+0x198)[0x721e88]
/usr/sbin/mysqld(_Z13handle_selectP3THDP13select_resultm+0x19f)[0x72208f]
/usr/sbin/mysqld[0x6fa7bd]
/usr/sbin/mysqld(_Z21mysql_execute_commandP3THD+0x19a1)[0x6fd0c1]
/usr/sbin/mysqld(_Z11mysql_parseP3THDPcjP12Parser_state+0x3a5)[0x700765]
/usr/sbin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0x10eb)[0x701dbb]
/usr/sbin/mysqld(_Z24do_handle_one_connectionP3THD+0x10f)[0x6cda5f]
/usr/sbin/mysqld(handle_one_connection+0x45)[0x6cdb35]
/usr/sbin/mysqld(pfs_spawn_thread+0x13b)[0x99389b]
/lib64/libpthread.so.0(+0x77b6)[0x7f5d34a4b7b6]
/lib64/libc.so.6(clone+0x6d)[0x7f5d337c3c5d]

Trying to get some variables.
Some pointers may be invalid and cause the dump to abort.
Query (41e1eb60): <<was provided but removed for security reasons>>
Connection ID (thread ID): 1037090
Status: NOT_KILLED

Hopefull it will help you to stablize the Programm and maybe someone that a hint how to get a workarround ...

Tanks

How to repeat:
need some help to find a way to reproduce the bug
[13 Sep 2013 12:23] MySQL Verification Team
process is running out of memory.
please send us output in a text file:

show global variables;
show global status;
show engine innodb status;
show engine performance_schema status;

ps fauxx
free -m
[13 Sep 2013 12:38] Peter Laursen
I am curious and listening!

Are you serious that you upgraded from MySQL 5.3?  Actually I think there was a (very short-lived) release named 5.3.x (along with 5.2.x - they were both out in between MySQL 6.0 and 5.5).  But as Oracle now seems to have truncated the public FTP-mirrors I am not able to verify.

Did you execute 'mysql_upgrade'? Or maybe 'mysql_upgrade has problems with upgrading from this rare version (if you mean what you write)? 

Peter
(not a MySQL/Oracle person)
[14 Sep 2013 16:41] Justin Swanhart
Did you mean you upgraded from MariaDB 5.3?
[16 Sep 2013 9:18] Sebastian Strzelczyk
the previous version don't really matters, we had the software running at 5.1.17 and 5.5.25a and never had problems ... since we migrate to 5.6.x we have the problems on serveral installations. 

The migration was done by dumping from the old database to sql-script and importing the sql-dump to a new server. So problems with with mysql_upgrade could be excluded.

the main change beside the upgrade of the server version is the increase of the memory. The innodb_buffer_size was incresed from 8GB to 30GB and we switched to innodb_file_per_table = 0 to innodb_file_per_table = 1 to save some space on hard disk.

we also increased the linux process limits to avoid some linux side effects belonging process memory limits:

/etc/security/limits.conf
mysql            soft    nofile          32800
mysql            hard    nofile          32800
mysql            soft    rss             83886080
mysql            hard    rss             83886080
mysql            soft    as              83886080
mysql            hard    as              83886080
[16 Sep 2013 9:28] Sebastian Strzelczyk
free -m

Attachment: free_srv1.txt (text/plain), 245 bytes.

[20 Sep 2013 9:46] MySQL Verification Team
I expect mysqld to consume around 34GB at startup then probably 20M per connection at least.  A few wrong queries with tmp_table_size of 300M will cause issues.

o)  performance_schema costs you ~930M
o)  binlog_cache_size=20M is surely a mistake.  Set it to 64k or so.
o)  you need to account memory for innodb fulltext indexes.

It is possible some part of code didn't handle out of memory correctly.
We'd need to see the exact query and EXPLAIN for that..
[20 Sep 2013 13:55] Sebastian Strzelczyk
Thanks for the advice, especially the binlog_cache_size we will reduce, 
that makes sence.

We are still investigate to find the queries which make the problem, but hard to find it in production environment.

we had two crash very close one behind another and we found out that before the crash the memory usage moved sharply to 2.9 GB more ...

have you some tips what indicator we can monitor in the performace-monitor to find the problem?
[25 Sep 2013 18:59] MySQL Verification Team
Are you doing any joins with views involved ?
Try set tmp_table_size and join_buffer_size to their default 
values and see if things stabilize.  At least, make sure no out of memory errors appear in the logs (or spikes on server)
[8 Oct 2013 10:16] Sebastian Strzelczyk
Sorry for the late feedback, I have been on holiday. 

Yes we are working with views, in most of our queries are views involved. 

I will set the adviced variables to default, hopefully it will stablize the server.

We have large, programmatically generated queries, which insert and delete a lot of records to some tables, normaly the server process them, but maybe this queries will bring the server into trouble. I will post them private.
[12 Oct 2013 17:44] Ellis Feigenblatt
I've got the same error.  MySQL crashed due to a long query string with more than 250 unions.

----------------------------------------------------------
Version: 5.6.12-log Community Servel (GPL)
System: Windows 7 SP1
Error: 2013-10-12 13:03:08 660 [ERROR] C:/Program Files/MySQL/MySQL Server 5.6/bin\mysqld: Out of memory (Needed 89804 bytes)
2013-10-12 13:03:26 660 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
----------------------------------------------------------

MySQL is run as a Windows Service.  After the crash the service is fully stopped and MySQL has to be started from the beggining (not a service restart).

Only for your information, the query that caused the crash is an union of more than 250 identical select statements, each one retrieving the last 10 entries from a different general ledger account.  

I believe that any very long query statement with 250 unions or more is prone to cause the crash.

String that produced the error (reduced to fit this bug report):

SELECT contablancamento.cDB_CR ,qBalance ,contabaccount.idcontabaccount ,sAccountDescription ,sSubAccount ,sAccount ,sDescricaoPlano ,contabtransaction.idContabTransaction ,dTransactionDate ,tsCadastro ,contabtransaction.idContabOperacao as idContabOperacao ,contabtransaction.idContabPrototipo as idContabPrototipo ,sDescricaoTransacao ,idTransacaoDeTransferencia ,qValor ,sDescricaoPrototipo ,sUsuario ,sDescricaoCaso ,idContabRecibo FROM contablancamento INNER JOIN contabtransaction	 ON contablancamento.idContabTransaction=contabtransaction.idContabTransaction INNER JOIN contaboperacao	 ON contabtransaction.idContabOperacao=contaboperacao.idContabOperacao INNER JOIN contabaccount	 ON contablancamento.idcontabaccount=contabaccount.idcontabaccount and contabaccount.qBalanceAtual<>0 INNER JOIN contabplano	 ON contabaccount.idContabPlano=contabplano.idContabPlano INNER JOIN contabprototipo	 ON contabtransaction.idContabPrototipo=contabprototipo.idContabPrototipo INNER JOIN processo	 ON contabaccount.idProcesso=processo.idProcesso INNER JOIN caso	 ON processo.idCaso=caso.idCaso INNER JOIN usuario	 ON contaboperacao.idUsuario=usuario.idUsuario LEFT JOIN contabrecibo	 ON contabtransaction.idContabTransaction=contabrecibo.idContabTransaction WHERE contabplano.iNivelSigiloContabil <= '5' AND contabaccount.idcontabaccount='6' AND dTransactionDate<='2013-10-12' AND contabaccount.idcontabaccount='2691'	AND contabplano.sAccount!='1.2.2.3'	AND contabplano.sAccount!='4.1.8.1'	AND contabplano.sAccount!='4.1.8.2' AND contabplano.sAccount!='4.1.8.3' ORDER BY dTransactionDate DESC LIMIT 10)
UNION
(SELECT contablancamento.cDB_CR ,qBalance ,contabaccount.idcontabaccount ,sAccountDescription ,sSubAccount ,sAccount ,sDescricaoPlano ,contabtransaction.idContabTransaction ,dTransactionDate ,tsCadastro ,contabtransaction.idContabOperacao as idContabOperacao ,contabtransaction.idContabPrototipo as idContabPrototipo ,sDescricaoTransacao ,idTransacaoDeTransferencia ,qValor ,sDescricaoPrototipo ,sUsuario ,sDescricaoCaso ,idContabRecibo FROM contablancamento INNER JOIN contabtransaction	 ON contablancamento.idContabTransaction=contabtransaction.idContabTransaction INNER JOIN contaboperacao	 ON contabtransaction.idContabOperacao=contaboperacao.idContabOperacao INNER JOIN contabaccount	 ON contablancamento.idcontabaccount=contabaccount.idcontabaccount and contabaccount.qBalanceAtual<>0 INNER JOIN contabplano	 ON contabaccount.idContabPlano=contabplano.idContabPlano INNER JOIN contabprototipo	 ON contabtransaction.idContabPrototipo=contabprototipo.idContabPrototipo INNER JOIN processo	 ON contabaccount.idProcesso=processo.idProcesso INNER JOIN caso	 ON processo.idCaso=caso.idCaso INNER JOIN usuario	 ON contaboperacao.idUsuario=usuario.idUsuario LEFT JOIN contabrecibo	 ON contabtransaction.idContabTransaction=contabrecibo.idContabTransaction WHERE contabplano.iNivelSigiloContabil <= '5' AND contabaccount.idcontabaccount='2511' AND dTransactionDate<='2013-10-12' AND contabaccount.idcontabaccount='2691'	AND contabplano.sAccount!='1.2.2.3'	AND contabplano.sAccount!='4.1.8.1'	AND contabplano.sAccount!='4.1.8.2'	AND contabplano.sAccount!='4.1.8.3' ORDER BY dTransactionDate DESC LIMIT 10)
UNION

""""... 250 select statements with different account number.""""

UNION
(SELECT contablancamento.cDB_CR ,qBalance ,contabaccount.idcontabaccount ,sAccountDescription ,sSubAccount ,sAccount ,sDescricaoPlano ,contabtransaction.idContabTransaction ,dTransactionDate ,tsCadastro ,contabtransaction.idContabOperacao as idContabOperacao ,contabtransaction.idContabPrototipo as idContabPrototipo ,sDescricaoTransacao ,idTransacaoDeTransferencia ,qValor ,sDescricaoPrototipo ,sUsuario ,sDescricaoCaso ,idContabRecibo FROM contablancamento INNER JOIN contabtransaction	 ON contablancamento.idContabTransaction=contabtransaction.idContabTransaction INNER JOIN contaboperacao	 ON contabtransaction.idContabOperacao=contaboperacao.idContabOperacao INNER JOIN contabaccount	 ON contablancamento.idcontabaccount=contabaccount.idcontabaccount and contabaccount.qBalanceAtual<>0 INNER JOIN contabplano	 ON contabaccount.idContabPlano=contabplano.idContabPlano INNER JOIN contabprototipo	 ON contabtransaction.idContabPrototipo=contabprototipo.idContabPrototipo INNER JOIN processo	 ON contabaccount.idProcesso=processo.idProcesso INNER JOIN caso	 ON processo.idCaso=caso.idCaso INNER JOIN usuario	 ON contaboperacao.idUsuario=usuario.idUsuario LEFT JOIN contabrecibo	 ON contabtransaction.idContabTransaction=contabrecibo.idContabTransaction WHERE contabplano.iNivelSigiloContabil <= '5' AND contabaccount.idcontabaccount='3336' AND dTransactionDate<='2013-10-12' AND contabaccount.idcontabaccount='2691'	AND contabplano.sAccount!='1.2.2.3'	AND contabplano.sAccount!='4.1.8.1'	AND contabplano.sAccount!='4.1.8.2' AND contabplano.sAccount!='4.1.8.3' ORDER BY dTransactionDate DESC LIMIT 10)
ORDER BY sAccount ASC, sSubAccount ASC, dTransactionDate ASC, idContabTransaction ASC LIMIT 0,35;
[3 Dec 2013 19:55] Sveta Smirnova
Sebastian, Ellis,

thank you for the feedback.

Please provide dump or, at least, output of SHOW CREATE TABLE and SHOW TABLE STATUS for all tables, participated in problematic queries and your current configuration files.
[4 Jan 2014 1:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".