MySql fails to update

Question

In the past two years we use MySql for keeping data for nightly builds of our C++ projects. DB is fairly small, and has a few triggers on it. Recently it began failing on updates rather frequently. We deleted some unnecessary old records and situation improved a little, however failure still happens.

We've tried to look at the error logs, nothing significant there (unless error logging isn't configured correctly).

Can someone give a hint as to where we should look? Are there any diagnostic modes that can help us track the problems?

I'd be happy to elaborate on missing info..

Answer 1

You didn't mention the server type, so I'm going to assume that it's some flavour of Linux. If that's the case, you can enable debug logging in your my.cnf file, usually found somewhere like /etc/mysql/my.cnf. Here's the relevant snippet from mine:

# you need the debug USE flag enabled to use the following directives,
# if needed, uncomment them, start the server and issue 
# #tail -f /tmp/mysqld.sql /tmp/mysqld.trace
# this will show you *exactly* what's happening in your server ;)

#log                                            = /tmp/mysqld.sql
#gdb
#debug                                          = d:t:i:o,/tmp/mysqld.trace
#one-thread

Note that the debug USE flag mentioned above is a reference to the distribution I use (Gentoo) and that other distros may or may not install MySQL with debugging support. If they don't though, they usually have it as a separate package. On Ubuntu for example, I think this package is called libmysqld-pic.

Anyway, make sure that debugging is enabled in my.cnf, restart the server and watch /tmp/mysqld.sql and /tmp/mysqld.trace to see what's going on. Remember not to leave this on permanently though as it's a big performance hit.