MySQL Bugs: #1865: Intermittent Server Crash

Bug #1865	Intermittent Server Crash
Submitted:	17 Nov 2003 17:50	Modified:	30 Oct 2004 9:39
Reporter:	Marc Riehm	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	mysql-4.0.12 (Official MySQL RPM)	OS:	Linux (Linux 2.4.18-14smp)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
Occasionally a large query that we run triggers mysqld to crash. This appears
to be under heavy load conditions. We use InnoDB exclusively. Our application
has a wide variety of concurrent transactions: many small update transactions
(several per second for extended periods of time), some large update
transactions (BLOBs 100s of KBs, say 1 every 10 seconds), some large, complex
queries. Problem occurs under SMP but also on a non-SMP server.

There are not too many concurrent connections: perhaps 30 max.

Some perhaps pertinent info: the stack trace from /var/log/mysqld.log shows
signs of what might be stack corruption and/or nested SEGV. There are only two levels of stack; third fp is nil.

resolve_stack_dump -s mysqld.sym -n marc_frames
0x80741ea handle_segfault + 450
0x8286058 pthread_sighandler + 184

We have not tuned mysql significantly; running mostly under default settings
(max_connections, various innodb parms, etc.)

I will attach more info if given the choice later in this bug report system...

How to repeat:
Unfortunately, the problem is intermittent. Definitely related to load.
Definitely occurs when the above query executes. However, the offending query
succeeds most of the time (when load is more modest).

Info from mysqld.log

Attachment: grays_mysqld.log (application/octet-stream, text), 11.97 KiB.

Note that the schema is not the whole thing going on in the database server.
These tables get big queries and occasional big updates. A different database
on the same server gets a high volume of small update transactions.

The text size of the query that (intermittently) triggers the crash is
more than 2400 bytes. This is the second large query in the attached query
file.  One suspicion is that this might be overrunning some
fixed-size buffer on the stack.

Thank you for taking the time to report a problem.  Unfortunately
you are not using a current version of the product your reported a
problem with -- the problem might already be fixed. Please download
a new version from http://www.mysql.com/downloads/

If you are able to reproduce the bug with one of the latest versions,
please change the version on this bug report to the version you
tested and change the status back to "Open".  Again, thank you for
your continued support of MySQL.

MySQL-4.0.16 has many bug fixes and since your version of mysql.  This bug fits the description of the ones that have been fixed.  Could you please try the latest released version of MySQL.

As Boyd suggested, this morning I upgraded the mysql server to 4.0.16. The
problem still occurs in the same manner.
How do I set the status of the bug back to Open?

Thanks for help so far; it is appreciated.

Doing some reading and digging yesterday, I came across references to the
thread_stack/thread-stack mysqld setting. Because we suspect stack frame
problems, and because we do not have a huge number of connections, I tried
bumping the value of this variable up to 512K from the default value
(bizarrely, the default seems to be 124K in 4.0.16 (yes, 124, not 128);
it used to be 192K in 4.0.12). I tried doing this in /etc/my.cnf, but
SHOW VARIABLES LIKE '%stack%' did not show any change to this value, no
matter what I did. Here's what I tried in the [mysqld] section of my.cnf:

set-variable = thread-stack=512K
set-variable = thread_stack=512K
thread_stack=512K
thread-stack=512K

Your docs are a little unclear about whether thread stack size is a
kernel setting or a compile-time setting or a runtime setting.

This (thread-stack) seems like a worthwhile avenue of investigation; but
how can I change the value?

Hi!

Since you do not get a proper stack trace, then the way to debug this is to run

mysqld --core-file

I am not sure if you have to configure Linux in some way to get it to produce a core file. In my computer I have:

heikki@hundin:~/mysql-standard-4.0.15-pc-linux-i686/bin> ulimit -a
core file size (blocks)     0
data seg size (kbytes)      unlimited
file size (blocks)          unlimited
max locked memory (kbytes)  unlimited
max memory size (kbytes)    unlimited
open files                  1024
pipe size (512 bytes)       8
stack size (kbytes)         unlimited
cpu time (seconds)          unlimited
max user processes          16383
virtual memory (kbytes)     unlimited

and I am able to produce core files.

An even better way to debug is to compile MySQL with

CXXFLAGS="-g" CFLAGS="-g" ./configure
make

and run mysqld inside gdb:

gdb mysqld

(gdb) run

<when it crashes>

(gdb) info threads

(gdb) thread 1

(gdb) bt full

etc. to get the stacks of all threads.

Regards,

Heikki

Several bug fixes were introduced since version 4.0.12, could you
please test the current release 4.0.17.