Web lists-archives.com

Re: duplicate rows in spite of multi-column unique constraint




Hi Chris,

On 3/24/2015 10:07 AM, Chris Hornung wrote:
Thanks for the suggestions regarding non-printing characters, definitely
makes sense as a likely culprit!

However, the data really does seem to be identical in this case:

mysql> select id, customer_id, concat('-', group_id, '-') from
app_customergroupmembership  where customer_id ='ajEiQA';
+-------------+-------------+----------------------------+
| id          | customer_id | concat('-', group_id, '-') |
+-------------+-------------+----------------------------+
| 20279608258 | ajEiQA      | -ddH6Ev-                   |
| 20279608269 | ajEiQA      | -ddH6Ev-                   |
+-------------+-------------+----------------------------+
2 rows in set (0.00 sec)


I also ran the data through hexdump as a secondary check, also looks
identical:

  mysql  --defaults-extra-file=~/.customers_mysql.cnf app -s -e "select
id, customer_id, group_id from app_customergroupmembership  where
customer_id ='ajEiQA';"  | hexdump -c
0000000   2   0   2   7   9   6   0   8   2   5   8  \t   a   j   E   i
0000010   Q   A  \t   d   d   H   6   E   v  \n   2   0   2   7   9   6
0000020   0   8   2   6   9  \t   a   j   E   i   Q   A  \t   d   d   H
0000030   6   E   v  \n


Any other suggestions given this info?



This reminded me of something so I went digging. Turns out to be a bug introduced by a fix applied to a different bug in 5.6.12. We hate creating regressions but they do sometimes happen.

http://bugs.mysql.com/bug.php?id=73170

The fix was published in 5.5.40, 5.6.21, and 5.7.5. You will need to upgrade to that release (or any later release) to avoid this happening in the future.

For now, manually resolve the duplication by deciding which id value you want to keep and discard the other copy of the row.

--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql