MySQL Duplicate Entry, But Not Really

Written by Anna Filina - - Aggregated on Thursday August 25, 2016
Tags: mysql

I chased an interesting bug today. MySQL was complaining about “Integrity constraint violation: 1062 Duplicate entry”. I had all the necessary safeguards in my code to prevent duplicates in tha column.

I gave up on logic and simply dumped the contents of the problematic column for every record. I found that there was a record with and without an accent on one of the characters. PHP saw each as a unique value, but MySQL did not make a distinction, which is why it complained about a duplicate value. It’s a good thing too, because based on my goal, these should have been treated as duplicates.

My solution was to substitute accented characters before filtering duplicates in the code. This way, similar records were rejected before they were sent to the database.

Happy coding!

[Edit 2016-08-26] As pointed out in the comments, a more robust and versatile solution would be to check the collation on the column. Thanks!

« SymfonyLive London 2016 is in 3 weeks! - Symfony blog

Sitepoint - How to Create a Pokemon Spawn Locations … »