MySQL Bugs: #55376: MySQL crashes with signal 11 (out of memory) above certain batch size

Bug #55376	MySQL crashes with signal 11 (out of memory) above certain batch size
Submitted:	19 Jul 2010 18:41	Modified:	20 Jul 2010 18:02
Reporter:	Vitaly Galushkin	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	5.1.48	OS:	Linux (SLE-10-x86_64-SP2)
Assigned to:		CPU Architecture:	Any
Tags:	crash, multi statements, Signal 11

Description:
We have production system sending multiple UPDATE statements in a single batch from Windows box using libmysql.dll (version 5.1.48 Win32). Update statements are separated by semicolon within the batch. When the batch size exceeds ~10,000 statements or ~1-1.5MB (in some cases that threshold could be lower of higher with different SQLs) the server crashes with the message below. Adjustments for various MySQL buffers including max_allowed_packet don't help to resolve that issue. The problem appeared with MySQL server versions 5.1.28, 5.1.34, 5.1.42 and 5.1.48; we haven't had that problem with 5.0.41.

100719 13:50:47 [ERROR] /hostname/mysql-5.1.48-linux-x86_64-glibc23/bin/mysqld: Out of memory (Needed 4390080 bytes)
100719 13:50:47 [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
100719 13:50:47 - 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=268435456
read_buffer_size=33554432
max_used_connections=1
max_threads=500
threads_connected=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 49419296 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd: 0x47a2230
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 = 0x4488a190 thread_stack 0x40000
/hostname/mysql-5.1.48-linux-x86_64-glibc23/bin/mysqld(my_print_stacktrace+0x2e)[0x8b10be]
/hostname/mysql-5.1.48-linux-x86_64-glibc23/bin/mysqld(handle_segfault+0x322)[0x5e29e2]
/lib64/libpthread.so.0[0x2b49ff229c00]
/hostname/mysql-5.1.48-linux-x86_64-glibc23/bin/mysqld(_Z8MYSQLlexPvS_+0xee1)[0x519401]
/hostname/mysql-5.1.48-linux-x86_64-glibc23/bin/mysqld(_Z10MYSQLparsePv+0x5e8)[0x60c6c8]
/hostname/mysql-5.1.48-linux-x86_64-glibc23/bin/mysqld(_Z11mysql_parseP3THDPKcjPS2_+0x217)[0x5f8907]
/hostname/mysql-5.1.48-linux-x86_64-glibc23/bin/mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xfe5)[0x5f9a45]
/hostname/mysql-5.1.48-linux-x86_64-glibc23/bin/mysqld(_Z10do_commandP3THD+0xe6)[0x5fa256]
/hostname/mysql-5.1.48-linux-x86_64-glibc23/bin/mysqld(handle_one_connection+0x246)[0x5ecb76]
/lib64/libpthread.so.0[0x2b49ff222143]
/lib64/libc.so.6(__clone+0x6d)[0x2b49ff89e8cd]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x2aab07c178be is an invalid pointer
thd->thread_id=5
thd->killed=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.

How to repeat:
I came up with the test program which simulates our system's behavior; compiled with MSVC 2008. The batch size could be specified as the parameter for the executable; that way you can determine the threshold when MySQL started to crash.

#include <stdio.h> 
#include <stdlib.h>
#include <string.h> 
#include <config-win.h>
#include <mysql.h>

int main(int argc, char **args) 
{ 
   MYSQL mysql; 
   char const sql1[] = "DROP TABLE IF EXISTS s11test";
   char const sql2[] = "CREATE TABLE s11test (id int, note varchar(100))";
   char const sql3[] = "UPDATE s11test SET note='QQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQQ' WHERE id=";
   char * buffer;
   char buffer2[2000];
   long buf_size;
   long sql_size = 0;
   int i = 0;
   int r;

   buf_size = (argc > 1) ? atoi(args[1]) : 2000000;
   printf("Buffer size: %li\n", buf_size);

   buffer = (char*) malloc (sizeof(char)*buf_size);
   if (buffer == NULL) {fputs ("Memory error",stderr); exit (2);}
   *buffer = '\0';

   while (sql_size < (buf_size - 200))
   {
     sprintf(buffer2, "%s %i;\n", sql3, i++);
     strcat(buffer, buffer2); 
	 sql_size += strlen(buffer2);
   }

   mysql_init(&mysql);
   mysql_real_connect(&mysql,"mysqlhost","user","pass","test",3306,NULL,CLIENT_MULTI_STATEMENTS); 
   printf("Server: %s\n", mysql_get_server_info(&mysql));
   
   r = mysql_real_query(&mysql, sql1, sizeof(sql1));
   printf("SQL1 result: %i\n", r);
   r = mysql_real_query(&mysql, sql2, sizeof(sql2)); 
   printf("SQL2 result: %i\n", r);
   r = mysql_real_query(&mysql, buffer, sql_size); 
   printf("SQL3 result: %i  SQL3 statements: %i\n  SQL3 size: %li\n", r, i, sql_size);

   free (buffer);
   return 0; 
}

Thank you for the report.

I can not repeat described behavior. Please indicate accurate package name of MySQL server you use (file name you downloaded), send us configuration file and indicate how much physical RAM do you have on box where MySQL server is running.

MySQL package
-------------
Generic Linux (glibc 2.3) (x86, 64-bit), Compressed TAR Archive 5.1.48 (mysql-5.1.48-linux-x86_64-glibc23.tar.gz) : http://dev.mysql.com/downloads/mirror.php?id=390206

Config file
-----------
[mysqld]
port=3309
socket=/hostname/mysqltest/test.sock
basedir=/hostname/mysql-5.1.48-linux-x86_64-glibc23
datadir=/hostname/mysqltest
log-error=/hostname/mysqltest/test.err
pid-file=/hostname/mysqltest/test.pid
tmpdir=/hostname/mysqltest

max_connections=500
key_buffer=256M
innodb_buffer_pool_size=1G
innodb_log_file_size=512M
max_allowed_packet=32M

OS,Memory usage
---------------
$ SPident

CONCLUSION: System is up-to-date!
  found    SLE-10-x86_64-SP2 + "online updates"

$ top -n 1
Tasks: 113 total,   1 running, 112 sleeping,   0 stopped,   0 zombie
Cpu(s):  0.7%us,  0.2%sy,  0.0%ni, 99.0%id,  0.1%wa,  0.0%hi,  0.0%si,  0.0%st
Mem:   3890772k total,  2334040k used,  1556732k free,    50328k buffers
Swap:  4208976k total,      140k used,  4208836k free,  1925488k cached

-------------------------------------------------------------------------
I have reproduced that bug on 2 SuSE servers: one has 4GB RAM (as shown above), another server was SLES-9-x86_64-SP4 with 8GB RAM. During the test execution, the "free mem" didn't drop below 1550000k.

are you sure this is not the same as bug #27863 which is fixed in 5.5.5 ?

Yes, this is the same bug as #27863. I've tested 5.5.5 and it looks good. 
I'm really surprised the bug #27863 had been closed but 5.1 GA release was not patched. Are there any plans to fix 5.1 code too?

Thank you for the feedback.

Bug closed as duplicate of bug #27863.

Regarding to push to 5.1 this is unlikely as bug #27863 marked as closed.