September 20, 2014

Logging Foreign Key errors

In the last blog post I wrote about how to log deadlock errors using Percona Toolkit. Foreign key errors have the same problems. InnoDB only logs the last error in the output of SHOW ENGINE INNODB STATUS, so we need another similar tool in order to have historical data.

pt-fk-error-logger

This is a tool very similar to pt-deadlock-logger that will help us to log those errors. It can log them to a table, file or just show them on STDOUT. Let’s see how to use it:

1- Create the table

First we create the table where we are going to store the information:

2- Run the tool as a daemon and store it on that recently created table

We are monitoring the MySQL on host 127.0.0.1 and storing all the errors on the host 10.0.05, database test and table foreign_key_errors. It will run as a daemon for 3600 seconds and will check the last error every 10 seconds.

3- Cause an error

I run a SQL command that shows this error:

Ok, not very informative. It's not very clear where is the error. Let's see the explanation with perror:

Still difficult to know what the problem is.

4- Let's check what the tool has logged:

So, we have the timestamp when the error happened and the text of the error. We can see here that it was caused by an index that was referenced by the FK, a PRIMARY KEY. So, the command that causes the error was:

Conclusion

If you are having too many deadlock or foreign key errors log them. Just reading the last error happened on the databases is not enough information to dig into the code and solve the problem. These tools are easy to use and make your life easier.

About Miguel Angel Nieto

Miguel joined Percona in October 2011. He has worked as a System Administrator for a Free Software consultant and in the supporting area of the biggest hosting company in Spain. His current focus is improving MySQL and helping the community of Free Software to grow. Miguel's roles inside Percona are Senior Support Engineer and Manager of EMEA Support Team.

Speak Your Mind

*