Logging Foreign Key errors

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.


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 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:


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.


Share this post

Leave a Reply