MySQL: Find Duplicate Entries in a Table

Here is a little background for this issue. The database used to run on ancient copy of MySQL until very recently we upgraded it to 5.0. Not without some headaches of migrating the database, but it worked. Common problem we had with this database was duplicate entries in printed reports. Why did these happen? Mostly because of user error combined with a lack of foreign key constraints.

The two MyIsam tables that were causing the issue were: report and proforma. Both were used for tracking documents through the review process. Report was the actual field audit report that would be submitted, reviewed and sent to the clients, while proforma was the documentation of expenses that our employees were supposed to submit for each assignment. Both would store dates, comments, notes and other info regarding said documents. The proforma had a 1-1 relationship with report (each proforma was tied to a single report entry). But since the old MySQL version did not support Foreign Key constraints this was not enforced (or rather only enforced by the PHP front end).

However users would continuously find ways to submit multiple proformas for the same report – for example by clicking the “submit” button 7 times. Other fun trick for re-submitting proforma was to send it in as TBA (to be annouced). Upon seeing the TBA the clerical staff in the office would manually associate it with appropriate report, usually without checking if another copy is already in the database. And when we locked that down, they would just continuously send bug reports about it forcing us to sort this out at the DB level.

What happened if the non-existent FK constraint was violated? One of the main reporting sections of the site used a complex join across these two tables. If there were two or more proformas per report, that report would show up on the list multiple times. What do people do when they see duplicates on the list? They start deleting them. Problem is – these were not real duplicates, but the same entry repeated several times. Deleting one copy would hose all of them, causing yet more support request for restoring the entry from the nightly DB dump.

Fun times. So after we switched to a DB engine that was actually developed in this century I immediately switched the tables to InnoDB and put a freaking foreign key constraint on that relationship. This way if someone finds a new loophole in the PHP code they will end up seeing a nice MySQL error instead of creating new duplicates.

But guess what – I just got more bug reports about duplicate entries. Apparently the FK constraint is not retroactive, and MySQL will gleefully allow duplicate keys to exist in your FK column when you apply the constraint. I figured that it would start trashing about and keep giving me errors if that was the case, but no – all it cared about was an index on the key column. So I was left with a task to track down all the duplicate entries in the foreign key column in my proforma table. How do I do this? This problem actually got me scratching my head for a bit, until I had a sudden epiphany:

SELECT 
	id, report_id, count(*)
FROM
	proforma
HAVING
	count(*)>1

I found around 17 separate instances of the foreign key (here report_id) being repeated anywhere from 2 to 8 times. All of these were for older entries that no longer show up on the first page of results, so naturally no one noticed. Still, it shows that the problem was more prevalent than we initially suspected.

What I’m really trying to say here is this: use database constraints to enforce table relationships. This is the only proper and effective way to do it. Trying to enforce constraints in software is just asking for trouble as you create multiple points of failure. Different parts of the code will update any given table at different times, and all of them must check this. Failing to implement proper checks in every piece of code that touches that table may lead to duplicate entries in FK columns and similar undesired side effects. A simple constraint on a column will stop this from happening much more effectively, with much less effort.

[tags]mysql, sql, database, database design, foreign key, key, constraint, php[/tags]