1150075 - Affiliates Integrity Error -- Duplicate keys error on -dev

Status: RESOLVED WONTFIX

(Firefox Affiliates Graveyard :: affiliates.mozilla.org, defect)

Description

[Chris More [:cmore]]

I continue to get these DB errors emailed to me:

IntegrityError: (1062, "Duplicate entry '1-link_clicks' for key 'links_leaderboardstanding_ranking_629414cbea77acb7_uniq'")
Error with /usr/bin/env python -W ignore /data/genericrhel6-dev/src/affiliates-dev.allizom.org/affiliates-app/manage.py update_leaderboard

Comment 1

[Sheeri Cabral [:sheeri]]

Hi Chris,

It looks to me like this might be expected - a guess based on the fact that "ignore" is in the arguments?

Also based on hunches, I looked at the database. This is the links_leaderboardstanding table, with a unique key of (ranking,metric).

Here's a data sample:
mysql> select * from links_leaderboardstanding limit 5;
+---------+---------+-------+-------------+----------+
| ranking | user_id | value | metric      | id       |
+---------+---------+-------+-------------+----------+
|       1 |       1 |   177 | link_clicks | 81517138 |
|       2 |       3 |    47 | link_clicks | 81517139 |
|       3 |       8 |    27 | link_clicks | 81517140 |
|       4 |    1037 |    13 | link_clicks | 81517141 |
|       5 |     100 |    10 | link_clicks | 81517142 |
+---------+---------+-------+-------------+----------+
5 rows in set (0.00 sec)

If your code is trying to update the table by inserting the row, instead of updating it, that may be where your problem is. e.g. if ranking #1 is now user_id 3, and you try to insert it, it's going to send that error.

What you want in this case is called an "upsert" - basically, update the row if it exists, if not, insert.

There are 2 ways to do upserts in MySQL:
INSERT...ON DUPLICATE KEY UPDATE http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html

REPLACE INTO (similar syntax as INSERT INTO, but REPLACE) http://dev.mysql.com/doc/refman/5.6/en/replace.html

The first way tries an insert, and if the row exists, it will update the row. The 2nd way tries an insert, and if the row exists, deletes the row and inserts again. 

I'm not sure if this helps, because I'm not sure how to do upserts using an ORM.

Comment 2

[Osmose [:osmose, :mkelly]]

The leaderboard update starts by deleting all the rows in the table before inserting: https://github.com/mozilla/affiliates/blob/master/affiliates/links/management/commands/update_leaderboard.py#L39

So in _theory_ we shouldn't have any issues with existing rows breaking the unique constraint.

When I was last attempting to fix this issue I tried a bunch of different Django tricks to see if maybe it wasn't committing that before trying to insert the new data, but to no avail. It's a really weird issue, and IIRC we've never seen it on prod and only occasionally on stage.

Comment 3

[Ben (:bensternthal)]

All work on Firefox Affiliates is on hold as the Affiliates program is slated to be phased out in 2014.