Why is mysql constantly exiting with signal 11?

December 21, 2005 10:07 AM

I run a fairly large Guild Wars fansite, using MediaWiki. Lately we've been having some database problems. Basically, mysql refuses to stay running for more than 10 seconds at a time, and occasionally complains of an InnoDB error, then exits with a signal 11. I've shut it down, run myisamchk, repaired, restarted, but it keeps coming back. Anyone care to lend a hand? After the repair, myisamchk doesn't show any errors on the table, or any others, but it always crops back up. We've tried everything from replacing the RAM in the machine, to completely rebuilding mySQL. Help! (last lines of mysql error log inside)

Here's the last couple lines of the .err file. It repeats this messages every single time it goes down:

051221 13:07:27InnoDB: Assertion failure in thread 7176 in file fsp0fsp.c line 3288
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html
InnoDB: about forcing recovery.
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=1044480
max_used_connections=0
max_connections=80
threads_connected=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 425663 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

051221 13:07:27 mysqld restarted
051221 13:07:27 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...
051221 13:07:30 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 1188694775.
InnoDB: Doing recovery: scanned up to log sequence number 0 1188694775
InnoDB: 3 transaction(s) which must be rolled back or cleaned up
InnoDB: in total 3 row operations to undo
InnoDB: Trx id counter is 0 26100736
InnoDB: Starting rollback of uncommitted transactions
InnoDB: Cleaning up trx with id 0 22057111
InnoDB: Cleaning up trx with id 0 22053814
InnoDB: Cleaning up trx with id 0 22052656
InnoDB: Rollback of uncommitted transactions completed
051221 13:07:30 InnoDB: Flushing modified pages from the buffer pool...
051221 13:07:30 InnoDB: Started; log sequence number 0 1188694775
051221 13:07:30 [ERROR] Failed to open the relay log './freebsd-relay-bin.000001' (relay_log_pos 4)
051221 13:07:30 [ERROR] Could not find target log during relay log initialization
051221 13:07:30 [ERROR] Failed to initialize the master info structure
/usr/local/libexec/mysqld: ready for connections.
Version: '4.1.15' socket: '/tmp/mysql.sock' port: 3306 FreeBSD port: mysql-server-4.1.15

posted by zerolives to Technology (7 answers total)

MySQL, on good hardware and built properly, is extremely stable software, so those are the first two options I'd exhaust first. I see you've replaced the RAM; can you duplicate the problem with the same data on an entirely different machine?

I also see that you're testing against your own MySQL build, and a build from ports. Don't do this! MySQL is very sensitive to compilation options. In my experience, the only ready-for-production MySQL is the binary distribution from MySQL AB. Get one for FreeBSD here.
posted by mendel at 10:39 AM on December 21, 2005

Oh, also, this is the sort of thing that benefits strongly from very domain-specific knowledge, and exposure to people that work at MySQL AB. I'd post what you posted here to the MySQL mailing list too.
posted by mendel at 10:41 AM on December 21, 2005

Also try mysqldump-ing and loading that into a freshly created database just to be sure. But yes, the mysql mailinglist is probably the better place to get answers on this.
posted by fvw at 10:48 AM on December 21, 2005

Response by poster: I can't mysqldump the database. MySQL crashes literally every few seconds. That's not enough time to dump a 300mb database.
posted by zerolives at 12:37 PM on December 21, 2005

Response by poster: Also, I always custom build mysql. -02 is good when you're running a server that has lots of traffic.
posted by zerolives at 1:15 PM on December 21, 2005

Can you run another instance of the daemon?
posted by The Jesse Helms at 2:03 PM on December 21, 2005

One other thought. The

051221 13:07:30 [ERROR] Failed to open the relay log './freebsd-relay-bin.000001' (relay_log_pos 4)

line looks weird to me. A relay log with a name that ends in 000001 says to me that this mysql instance thinks it's a slave. Nothing in your post makes me think it's a slave. See http://dev.mysql.com/doc/refman/5.0/en/slave-logs.html for details on the relay log.

"Failed to initialize the master info structure" is also an error you commonly see on slaves. What happens if within the 10 seconds you have the daemon you do a SHOW SLAVE STATUS;? What about SHOW MASTER STATUS;?

Anyway, it looks to me like your server thinks it's a misconfigured slave rather than a standalone server. http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html is a good place to see how to set up replication (and consequently should tell you where to look for wonkiness).
posted by amery at 6:34 PM on December 21, 2005