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:
|
1 2 3 4 5 |
CREATE TABLE foreign_key_errors ( ts datetime NOT NULL, error text NOT NULL, PRIMARY KEY (ts) ); |
2- Run the tool as a daemon and store it on that recently created table
|
1 |
# pt-fk-error-logger --daemonize --run-time=3600 --interval=10 --dest h=10.0.0.5,D=test,t=foreign_key_errors h=127.0.0.1 |
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:
|
1 |
ERROR 1025 (HY000): Error on rename of './employees10/#sql-3da_2a' to './employees10/employees' (errno: 150) |
Ok, not very informative. It's not very clear where is the error. Let's see the explanation with perror:
|
1 2 |
# perror 150 MySQL error code 150: Foreign key constraint is incorrectly formed |
Still difficult to know what the problem is.
4- Let's check what the tool has logged:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
mysql> select * from test.foreign_key_errors\G *************************** 1. row *************************** ts: 2012-09-26 14:28:31 error: Error in foreign key constraint of table employees10/dept_emp: there is no index in referenced table which would contain the columns as the first columns, or the data types in the referenced table do not match the ones in table. Constraint: , CONSTRAINT "dept_emp_ibfk_1" FOREIGN KEY ("emp_no") REFERENCES "employees" ("emp_no") ON DELETE CASCADE The index in the foreign key in table is "PRIMARY" See http://dev.mysql.com/doc/refman/5.5/en/innodb-foreign-key-constraints.html for correct foreign key definition. InnoDB: Renaming table `employees10`.`#sql-3da_2b` to `employees10`.`employees` failed! |
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:
|
1 |
mysql> ALTER TABLE employees DROP PRIMARY KEY; |
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 the Author
Miguel Angel NietoMiguel 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.
