mysql suddenly shut itself down

any clues as to why?

[jamie]

hi,

woke up this morning to find mysql had shut itself down yesterday evening?

here is the /var/log/messages:

May 5 21:59:43 eul0000533 mysqld: Stopping MySQL: succeeded
May 5 21:59:43 eul0000533 mysqld: Starting MySQL: succeeded

here is the /var/log/mysqld.log:

030505 21:59:43 /usr/libexec/mysqld: Normal shutdown
A mysqld process already exists at Mon May 5 21:59:43 GMT 2003
030505 21:59:44 /usr/libexec/mysqld: Shutdown Complete
030505 21:59:44 mysqld ended

can anyone point me in the right direction by looking at these logs? does it have something to do with the mysql process which already existed?

many thanks

[rayvd]

What version are you running? Was there any debugging information? Generally, MySQL will dump a backtrace of some sort into mysqld.log before it shuts down. Another good thing to check is if you have some sort of cron job running that might kill the server for maintenance... check /etc/crontab /etc/cron.* and crontab -l for any user that might have access to the shutdown function.

Kind of an extreme case, but our MySQL server was shutting down randomly for quite a while. Turned out to be a result of ECC not being enabled in BIOS (actually that was the cause of a _lot_ of other problems as well :))

[bonanza]

This happened to me a few times -- no idea why.
Thankfully, I noticed pretty quickly in each case.
But if I was on vacation? *shudder*

Way down on my list of things to do (apparently behind reading endless threads about google) is to set up a simple detection script that watches to make sure mysql is running.

If anyone has done that (on *nix), I'd love to see it.

[rayvd]

Something like this would probably work

#!/bin/sh 
# 

if (mysqladmin ping &>/dev/null); then
 echo "running"
else
 echo "not running"
 safe_mysqld &
fi
 

This would require that you have a ~/.my.cnf file in the directory of the user calling the script (whoever you run mysqld as).

Just call it from cron every 5 minutes or so.

[jamie]

hi rayvd,

thanks for tips. i didn't quite understand 'dump a backtrace' though?

re cronjobs: i only have standard ones running, and they have been running for months now - this is the first time this has happened.

i do have mysqld in /etc/logrotate.d/

/var/log/mysqld.log {
missingok
create 0640 mysql mysql
prerotate
[ -e /var/lock/subsys/mysqld ] && /usr/bin/mysqladmin flush-logs ¦¦ /bin/true
endscript
postrotate
[ -e /var/lock/subsys/mysqld ] && /usr/bin/mysqladmin flush-logs ¦¦ /bin/true
endscript

re users: i am the only user how has access to this server i am puzzled.

i was working on a new mysql database yesterday evening, i reloaded mysql a couple of times, but i don't think this would cause mysql to freak out and shut down.

i do like the idea of your mysql check script. it would probably be possible to modify that and automatically restart the service if it detected that it was not running?

will have to look into that a bit more deeply if this happens again.

cheers

p.s. *shudder* - ditto ;D

[rayvd]

Here is an excerpt from my logfile from back in the day when mysql was crashing a _lot_... the debugging information can be somewhat helpful if it's actually a problem with MySQL itself.

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 agaist 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=8388600 
record_buffer=131072 
sort_buffer=2097144 
max_used_connections=19 
max_connections=100 
threads_connected=5 
It is possible that mysqld could use up to 
key_buffer_size + (record_buffer + sort_buffer)*max_connections = 225791 K 
bytes of memory 
Hope that's ok, if not, decrease some variables in the equation 
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 range sanity check OK, backtrace follows: 
0x80d51bf 
0x40031f75 
0x8304c55 
0x8304bd5 
0x80ed6c3 
0x80ee5de 
0x80ee885 
0x80db96b 
0x80ded65 
0x80dacef 
0x80da2a9 
Stack trace seems successful - bottom reached 
Please read [mysql.com...]  and follow instructions on how to res 
olve the stack trace. Resolved 
stack trace is much more helpful in diagnosing the problem, so please do 
resolve it 
Trying to get some variables. 
Some pointers may be invalid and cause the dump to abort... 
thd->query at 0x84d13e0 = SELECT * FROM xmb_threads WHERE fid='7' AND pid='24097' AND dateline > '102 
4679532' ORDER BY topped DESC,dateline DESC 
thd->thread_id=51117 
Successfully dumped variables, if you ran with --log, take a look at the 
details of what thread 51117 did to cause the crash. In some cases of really 
bad corruption, the values shown above may be invalid 
The manual page at [mysql.com...]  contains 
information that should help you find out what is causing the crash 
Number of processes running now: 0 
020820 10:12:13 mysqld restarted 

If you're not getting this information, perhaps you need to increase the debug level MySQL runs at? Check out [mysql.com...] for a list of parameters you can start mysqld with.

Incidentally, that script I posted did make a call to safe_mysqld which would restart the server if it wasn't running. :)

[jamie]

Incidentally, that script I posted did make a call to safe_mysqld which would restart the server if it wasn't running. :)

thanks very much rayvd!

still a lot for me to learn ;-)

have to check that debug level.

btw, in your script where does the echo "running" echo out to? where will you see it?

cheers

[rayvd]

It echos it to stdout... which would display it on your console if you ran it manually. However, cron sends the output of stdout/stderr via email to the user who ran the program. You may want to edit out those echo statements once you confirm that the script works otherwise you will end up with a _lot_ of email :)

[jamie]

thanks for the help rayvd will enjoy trying this out :-)

Jamie

[jamie]

doh!

on that day i ran the scheduled errata update for the latest mysql fix from the RH network - i am sure that is why mysql turned itself off!

how stupid am I?

still thanks loads for help :-)