MySQL crashes every morning at 5:00:01

Snapdragon · Unread post by **Snapdragon** » Tue Dec 02, 2008 6:43 pm

Obviously there's a cron of some sort.

I just found this today, and it's been happening since June 14. I'm thinking it coincides with the upgrade to PHP 5.2.6 that I did from here.

I still get the good ol' "Your PHP MySQL library version 4.1.22 differs from your MySQL server version 5.0.62." And in the log it hints that part of the problem may be due to one of the libraries it was linked against is corrupt, improperly built, or misconfigured.

All my crons run at 4AM.. so I can't explain why it crashes at 5AM. It's wrecked a few tables but I managed to repair them with myisamchk.

Maybe my variables are out of whack? I've configured them many times ot get the best speed and keep the bad things out.

The server has 12 GB of memory.

Any thoughts?

Code: Select all

081202  5:00:01 - 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=536870912
read_buffer_size=131072
max_used_connections=17
max_connections=100
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 741887 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x2ad7b0b410
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...
Cannot determine thread, fp=0x45394fe8, backtrace may not be correct.
Bogus stack limit or frame pointer, fp=0x45394fe8, stack_bottom=0x45390000, thread_stack=262144, aborting backtrace.
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x2ad90e4c90  is invalid pointer
thd->thread_id=646685
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.

Number of processes running now: 0
081202 05:00:01  mysqld restarted
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
081202  5:00:01  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
081202  5:00:01  InnoDB: Started; log sequence number 0 2450975268
081202  5:00:01 [Note] /usr/libexec/mysqld: ready for connections.
Version: '5.0.62-log'  socket: '/var/lib/mysql/mysql.sock'  port: 3306  Source distribution

Snapdragon · Unread post by **Snapdragon** » Tue Dec 02, 2008 7:14 pm

Looks like my hunch was correct. It is 100% to do with the mismatched library. I posted last on here on June 13 with the error of the mismatch, and June 14 was the first crash.

So I need to go back to figuring out how to rid myself of MySQL and PHP and installing them from scratch so they match.

Unread post by **scott** » Wed Dec 03, 2008 11:49 am

Just rebuild the PHP src.rpm on your system, that would link it against whatever native client libraries you're using.

Snapdragon · Unread post by **Snapdragon** » Wed Dec 03, 2008 3:58 pm

Unfortunately I never learned how to do that. If it's a quick command you can supply, I'd appreciate it, otherwise I'll have to do some research later.

Oddly enough it did not crash this morning. I caught it with it's pants down. Frustrating.

Unread post by **scott** » Wed Dec 03, 2008 7:41 pm

Sure, Ive been putting my notes up on it here:

http://www.atomicorp.com/wiki/index.php/RPMS

Snapdragon · Unread post by **Snapdragon** » Wed Dec 03, 2008 9:31 pm

Alright, but if I installed php originally from the RPM on the DVD, and then upgraded using ART.. would I even have a src file for it?

Unread post by **scott** » Thu Dec 04, 2008 1:21 pm

Theres a link to all the SRPM's off the download page

Snapdragon · Unread post by **Snapdragon** » Thu Dec 04, 2008 3:01 pm

I see that.

I wonder if I was crashing for another reason. When I changed to MySQL 5, I didn't do a Repair on all the tables to upgrade them. I think when I was running my daily optimize table/backup DB script, that's what was actually crashing MySQL.

After doing a mysqlcheck -A -e --auto-repair, a whole whack of tables were upgraded and now MySQL hasn't crashed for two days.

Unread post by **scott** » Thu Dec 04, 2008 5:38 pm

Hey thats some great followup info. I never considered that part of the upgrade from 4->5 causing that kind of instability. Definitely keep updating this thread, its really great info that I know other folks will be able to take advantage of.

Snapdragon · Unread post by **Snapdragon** » Thu Dec 04, 2008 9:23 pm

Alright, well here's the full background for my MySQL environment at the moment.

I upgraded from 4 to 5 on June 13. On June 14 MySQL started crashing every night when my cron jobs ran. (see below)

As a result, many of my tables needed repair, mostly ones with session information. One table was beyond repair, an xcart_stats_pages_views was marked as crashed. Granted, it took almost six solid months of abuse before finally actually 'crashing' three days ago.

mysqlcheck/Repair could not fix this table. It did call my attention to a pile of other tables crying out for upgrading though.

So I ran:

Code: Select all

mysqlcheck -A -a -c -e --auto-repair -uadmin -p

This checks all databases, analyzes and checks all tables, uses extended mode to ensure 100% consistency at the expense of time (4 GB of databases took about 5 minutes), and auto repairs and upgrades any issues.

To fix the crashed table:

Code: Select all

myisamchk --safe-recover /var/lib/mysql/<databasename>/<table>

It uses the .FRM file to rebuild the index. It took about 20 seconds and I revived a 1.06 million record table. There's a warning about running this program on tables that were created via previous versions of MySQL, the --safe switch is supposed to avoid issues.

Reference link that has more info:
http://patchlog.com/databases/repair-a-mysql-table/

My nightly cron runs this:

Code: Select all

mysqlcheck -A --optimize -u<backup user that has read privs on all dbs> -p<password>

This optimizes all databases before backup, as well to remove the daily session/cookie/search results pile up in all my forums. Running a nightly optimize can also repair minor table issues.

Then:

Code: Select all

for i in *; do mysqlhotcopy $i /mnt/backup/var/lib/mysql --user=XXX --password=XXX --allowold --keepold; done

48 hour rotating database backup.

Hope that helps someone else.

Snapdragon · Unread post by **Snapdragon** » Tue Dec 09, 2008 12:49 pm

Code: Select all

This MySQL server has been running for 7 days, 4 hours, 48 minutes and 8 seconds. It started up on Dec 02, 2008 at 05:00 AM.

At this point it's a solid confirmation that the table upgrade was required and was the source of my crash issues. It should be added to any documentation regarding V4 to V5 upgrades.

Kalimari · Unread post by **Kalimari** » Tue Dec 09, 2008 3:15 pm

That's great info Snapdragon - glad your problem is resolved.

I too run nightly --optimize of databases, but will have to give mysqlhotcopy a look, thanks for the head's up - why I've not come across it before, I'll never know.

In case you've not discovered it, I use mysqltuner to ensure MySQL settings/cache/queries etc are optimal - it's an invaluable tool: http://wiki.mysqltuner.com/MySQLTuner

Snapdragon · Unread post by **Snapdragon** » Tue Dec 30, 2008 10:37 pm

I'll look into that, thanks. I'm dropping a few clients for 2009, so it will be time to tweak again to the few larger ones that are left.

As well, nothing in my log since the table upgrade, so it's a definite requirement from MySQL 4 to 5 ---- upgrade your tables! Running 28 days and 75M queries since the fix.