Random crashes

[azurit]

Our MySQL server is crashing randomly (sometimes several times per day, sometimes once per month). From syslog:

Feb 27 12:23:37 server mysqld[2917]: 100227 12:23:37 - mysqld got signal 11 ;
Feb 27 12:23:37 server mysqld[2917]: This could be because you hit a bug. It is also possible that this binary
Feb 27 12:23:37 server mysqld[2917]: or one of the libraries it was linked against is corrupt, improperly built,
Feb 27 12:23:37 server mysqld[2917]: or misconfigured. This error can also be caused by malfunctioning hardware.
Feb 27 12:23:37 server mysqld[2917]: We will try our best to scrape up some info that will hopefully help diagnose
Feb 27 12:23:37 server mysqld[2917]: the problem, but since we have already crashed, something is definitely wrong
Feb 27 12:23:37 server mysqld[2917]: and this may fail.
Feb 27 12:23:37 server mysqld[2917]:
Feb 27 12:23:37 server mysqld[2917]: key_buffer_size=1073741824
Feb 27 12:23:37 server mysqld[2917]: read_buffer_size=131072
Feb 27 12:23:37 server mysqld[2917]: max_used_connections=201
Feb 27 12:23:37 server mysqld[2917]: max_connections=200
Feb 27 12:23:37 server mysqld[2917]: threads_connected=13
Feb 27 12:23:37 server mysqld[2917]: It is possible that mysqld could use up to
Feb 27 12:23:37 server mysqld[2917]: key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 3122176 K
Feb 27 12:23:37 server mysqld[2917]: bytes of memory
Feb 27 12:23:37 server mysqld[2917]: Hope that's ok; if not, decrease some variables in the equation.
Feb 27 12:23:37 server mysqld[2917]:
Feb 27 12:23:37 server mysqld[2917]: thd=0x7fdbc88ae5a0
Feb 27 12:23:37 server mysqld[2917]: Attempting backtrace. You can use the following information to find out
Feb 27 12:23:37 server mysqld[2917]: where mysqld died. If you see no messages after this, something went
Feb 27 12:23:37 server mysqld[2917]: terribly wrong...
Feb 27 12:23:37 server mysqld[2917]: Cannot determine thread, fp=0x7fdbc88ae5a0, backtrace may not be correct.
Feb 27 12:23:37 server mysqld[2917]: Bogus stack limit or frame pointer, fp=0x7fdbc88ae5a0, stack_bottom=0x4c460000, thread_stack=131072, aborting backtrace.
Feb 27 12:23:37 server mysqld[2917]: Trying to get some variables.
Feb 27 12:23:37 server mysqld[2917]: Some pointers may be invalid and cause the dump to abort...
Feb 27 12:23:37 server mysqld[2917]: thd->query at 0x4cde8d0 = SET NAMES 'cp1250'
Feb 27 12:23:37 server mysqld[2917]: thd->thread_id=1243328
Feb 27 12:23:37 server mysqld[2917]: The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
Feb 27 12:23:37 server mysqld[2917]: information that should help you find out what is causing the crash.
Feb 27 12:23:37 server mysqld[2917]: pure virtual method called
Feb 27 12:23:37 server mysqld[2917]: terminate called without an active exception
Feb 27 12:23:37 server mysqld[2917]: Fatal signal 6 while backtracing
Feb 27 12:23:37 server mysqld[2917]: pure virtual method called
Feb 27 12:23:37 server mysqld[2917]: terminate called recursively
Feb 27 12:23:37 server mysqld_safe[27213]: Number of processes running now: 0
Feb 27 12:23:37 eserver mysqld_safe[27215]: restarted

We are using version 5.0.87-d10-ourdelta65, Debian Lenny, 64bit system, SMP. I'm also attaching my.cnf. It's quite busy server, user_statistics are enabled. Just tell if you need any other info.

[Arjen Lentz (arjen-lentz)]

Invalid stack... hmm. Can you please try without user_statistics enabled, i.e. userstat_running = 0

By the way tmp_table_size = 700M makes no sense, you wouldn't have enough memory; and tmp tables get limited by max_heap_table_size anyway, so that's why this incorrect setting hasn't hurt you yet.

[azurit (azurit)]

Today I got 3 crashes and two of them were with user statistics DISABLED. I got the last previous crash when I was reporting this issue.

Thank you for your suggestions about my configuration, I will try to fix it.

This bug is really annoing :( I'm getting lots of 'crashed tables' every time this happend and some of them are really big and repair takes about 30 minutes (=30 minutes of downtime).

Just tell if you need any other info. Thank you.

[azurit (azurit)]

Today I got crash on different server while I was restoring one database from dump (1.1 GB). I think this bug is appearing only when MySQL server is under bigger load.

[azurit (azurit)]

Looks like this software is unmaintained, looking for replacement.. thnks

[Arjen Lentz (arjen-lentz)]

We're working on a new build in the 5.0 series, to update the last available upstream version.
Do note that the 5.0 series is also end-of-lifed upstream (Sun/Oracle).
See also the 5.1 (MariaDB based) builds/packages.

Did that other crash also occur while doing the "SET NAMES 'cp1250'" command? You may find it's related to that. Which would very likely make it a general MySQL bug not OurDelta build specific.

[azurit (azurit)]

I have never got crash with original Debian MySQL server package but, of course, I cannot tell where exactly the problem is.

I'm not sure what was MySQL doing right before crash. In second case (restoring DB from dump) it was inserting data or generating/enabling keys.

[Toby Thain (qu1j0t3)]

1) did the Debian server have the same memory configuration, and similar stress/workload?
2) have you ruled out hardware causes? (including an overnight run of memtest86)

Bad memory can cause unpredictable segfaults, and (especially if the configurations had been different) it's possible you could see that with one server and not the other.

[azurit (azurit)]

1.) Yes, it was the same SW and HW, I just replaced MySQL server.

2.) No, I wasn't doing any tests. Only MySQL server is crashing and I'm experiencing this problem o 3 different servers. The server which is under biggest load is crashing more often. The one which crash while restoring DB from dump is under very low load and I got crash only two times on it (the second one was with that dump). This is why I think that crash is related to the load of MySQL server.

Yesterday I changed configuration based on Arjen Lentz suggestion:
tmp_table_size = 32M
max_heap_table_size = 32M

This is the only change I made and now I cannot tell if it will make any difference.

[azurit (azurit)]

It crashed again so changes in config has no effect.