Capturing Foreign Key Errors

Looking further into capturing certain error conditions such as yesterday’s Capturing Deadlocks post, next up was looking for foreign key errors.

MySQL

Similar to capturing Deadlocks in MySQL, the most recent foreign key error can be viewed in SHOW ENGINE INNODB STATUS.  Similarly, the Percona Toolkit can be used to capture & log the foreign key errors using pt-fk-error-logger.  The setup again is similar to capturing deadlocks.

• Install the Percona toolkit using your standard process
• For the initial setup, I create a new tools database & tools user for this setup
• The below block will create the percona.deadlocks table if it does not exist, and run the logger in daemon mode, checking every 60 seconds.
• 1. Additional options

     pt-fk-error-logger h=<hostname>,u=perconaadm,P=3306 --ask-pass --dest D=percona,t=foreign_key_errlog --daemonize --interval 60s

  • Additional options notes
    • –ask-pass will cause a prompt for the password to be entered twice, password will not be visible in the process list.
    • –run-time 5m – This will run the tool in Daemon mode for 5 minutes, then exit.
    • –create-dest-table is optional (if you’ve already created the table!)
• I have scripted this out to check the daemon is running.

The logging of the error contains the timestamp of the error and the error message including the constraints violated and the failing statement.

 SQL Server

I was not able to find a corresponding automatic capture for foreign key errors, we rely on the application to catch the foreign key errors and log for further review.