EmergencyEMERGENCY? Get 24/7 Help Now!

Logging Foreign Key errors

 | September 27, 2012 |  Posted In: MySQL

PREVIOUS POST
NEXT POST

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.

PREVIOUS POST
NEXT POST
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.

Leave a Reply

 
 

Percona’s widely read Percona Data Performance blog highlights our expertise in enterprise-class software, support, consulting and managed services solutions for both MySQL® and MongoDB® across traditional and cloud-based platforms. The decades of experience represented by our consultants is found daily in numerous and relevant blog posts.

Besides specific database help, the blog also provides notices on upcoming events and webinars.
Want to get weekly updates listing the latest blog posts? Subscribe to our blog now! Submit your email address below and we’ll send you an update every Friday at 1pm ET.

No, thank you. Please do not ask me again.