Page MenuHomePhabricator
Create Task
Maniphest T356736

Duplicate entry 'X.X.X.X' for key 'ip' sql: INSERT INTO actor_data (ip,org,client_count,types,conc_city,conc_state,conc_country,countries,location_country,risks
Closed, ResolvedPublicBUG REPORT
Actions

Description

First seen on February 3 at 8:39 UTC

There are a variety of IP addresses that throw the "Duplicate entry" error. There are 19,983 instances of the error appearing since February 3.

In subsequent imports, we see "Found failed batches from a previous run; attempting to re-import batches from an update from 20240202 to 20240203" but this ends up triggering the same errors again.

In practice, I think that means the production DB contains a mix of data newer than February 3rd, and stale entries.

See also:

Details

Related Changes in Gerrit:
SubjectRepoBranchLines +/-
ipoid: Bump versionoperations/deployment-chartsmaster+1 -1
ipoid: Bump versionoperations/deployment-chartsmaster+1 -1
ipoid: Bump versionoperations/deployment-chartsmaster+1 -1
ipoid: Bump versionoperations/deployment-chartsmaster+1 -1
Customize query in gerrit
Related Changes in GitLab:
TitleReferenceAuthorSource BranchDest Branch
main: Use "yesterday" as the current import daterepos/mediawiki/services/ipoid!230kharlanmain-I5b39cb19101158085eb9db461c0665aace81d5ebmain
Prefere the original statement on retryrepos/mediawiki/services/ipoid!229stranmain-I17cda9998017cf572d324ff914e1f535f028ab24main
update-db: Use for statement instead of .everyrepos/mediawiki/services/ipoid!228kharlanmain-I2a1afffeaefd2ef0e3d9cfd6554a38a321cbed20main
update-db: Log error object seen in duplicate entry handlingrepos/mediawiki/services/ipoid!225kharlanmain-I0dc5702adced78c5728babf3b08cbf13fafd8ba7main
Recover from duplicate IP key SQL errorsrepos/mediawiki/services/ipoid!222stranmain-I56a6fceb221ec492067ac0d04a40ea7550ea553dmain
Customize query in GitLab

Related Objects

Event Timeline

kostajh created this task.Feb 6 2024, 8:12 AM
Restricted Application added a subscriber: Aklapper. · View Herald TranscriptFeb 6 2024, 8:12 AM
kostajh changed the subtype of this task from "Task" to "Bug Report".Feb 6 2024, 8:12 AM
kostajh added a comment.Feb 6 2024, 8:17 AM

T354944: Ensure database is up-to-date with latest data file would help with this, in the longer term.

In the shorter term, I wonder if ON DUPLICATE KEY UPDATE would be a reasonable option. It's not an ideal solution, because in theory the SQL statement generation shouldn't result in INSERT queries that generate duplicate entries.

kostajh updated the task description. (Show Details)Feb 6 2024, 8:25 AM
kostajh added a comment.Feb 6 2024, 8:45 AM
In T356736#9516124, @kostajh wrote:

T354944: Ensure database is up-to-date with latest data file would help with this, in the longer term.

In the shorter term, I wonder if ON DUPLICATE KEY UPDATE would be a reasonable option. It's not an ideal solution, because in theory the SQL statement generation shouldn't result in INSERT queries that generate duplicate entries.

Another thought would be to modify generateInsertActorQueries to do a SELECT before generating the insert query. If it finds a matching IP, it would instead return an UPDATE, and would also give us the opportunity to log some more details about the stack trace in which this scenario occurs.

STran claimed this task.Feb 6 2024, 8:46 AM
Tchanders mentioned this in T354944: Ensure database is up-to-date with latest data file.Feb 6 2024, 7:55 PM
STran moved this task from Priority Backlog to In Progress on the Trust and Safety Product Sprint (Sprint Kazoo (Jan 29 - Feb 9 2024)) board.Feb 7 2024, 11:18 AM
CodeReviewBot added a project: Patch-For-Review.Feb 7 2024, 11:20 AM

stran opened https://gitlab.wikimedia.org/repos/mediawiki/services/ipoid/-/merge_requests/222

Recover from duplicate IP key SQL errors

STran added a comment.Feb 7 2024, 11:41 AM

It seems like the feed file can be updated over the course of the day. I happened to have an older copy of the feed file and downloaded a new copy while investigating and noticed that my file was 1.01GB and the new file was 1.02GB. Checking the old file vs the new file, I saw that the IP exists in the new (and presumably now-static) file but wasn't in the feed that the db probably used:

% grep {IP} 20240202.new.json

% grep {IP} 20240202.old.json
{"ip":{IP},"Kar-Tel LLC"}

Checking the lc, the two are definitely different:

% wc -l 20240202.new.json
 36949570 20240202.new.json

% wc -l 20240202.old.json
 36598906 20240202.old.json

We discussed the following fix:

I downloaded the file for the date again today while writing this up and can see that the line count for this and the "final" file from the other day are the same:

% wc -l 20240202.new.json
 36949570 20240202.new.json

% wc -l 20240202.latest.json         
 36949570 20240202.latest.json

I'm running some diffs and will update this investigation if/when they succeed.

STran moved this task from In Progress to Needs Review on the Trust and Safety Product Sprint (Sprint Kazoo (Jan 29 - Feb 9 2024)) board.Feb 8 2024, 11:58 AM
CodeReviewBot added a comment.Feb 9 2024, 9:35 AM

kharlan merged https://gitlab.wikimedia.org/repos/mediawiki/services/ipoid/-/merge_requests/222

Recover from duplicate IP key SQL errors

Maintenance_bot removed a project: Patch-For-Review.Feb 9 2024, 10:31 AM
gerritbot added a comment.Feb 9 2024, 11:57 AM

Change 999638 had a related patch set uploaded (by STran; author: STran):

[operations/deployment-charts@master] ipoid: Bump version

https://gerrit.wikimedia.org/r/999638

gerritbot added a project: Patch-For-Review.Feb 9 2024, 11:57 AM
gerritbot added a comment.Feb 9 2024, 12:32 PM

Change 999638 merged by jenkins-bot:

[operations/deployment-charts@master] ipoid: Bump version

https://gerrit.wikimedia.org/r/999638

Maintenance_bot removed a project: Patch-For-Review.Feb 9 2024, 1:30 PM
CodeReviewBot added a project: Patch-For-Review.Feb 9 2024, 2:12 PM

kharlan opened https://gitlab.wikimedia.org/repos/mediawiki/services/ipoid/-/merge_requests/225

update-db: Log IP address used with duplicate entry handling

dom_walden mentioned this in T344941: Respond to data update and import errors.Feb 12 2024, 7:57 AM
kostajh moved this task from Needs Review to In Progress on the Trust and Safety Product Sprint (Sprint Kazoo (Jan 29 - Feb 9 2024)) board.Feb 12 2024, 1:24 PM

It seems like this is still an issue. Looking at the logs from today (example) we are stuck on importing February 2 - February 3. This is occurring despite us having 38,960 log entries for "Duplicate entry found for ip, attempting to delete and re-insert" in the last week.

CodeReviewBot added a comment.Feb 13 2024, 8:58 AM

stran merged https://gitlab.wikimedia.org/repos/mediawiki/services/ipoid/-/merge_requests/225

update-db: Log error object seen in duplicate entry handling

gerritbot added a comment.Feb 13 2024, 9:15 AM

Change 1002924 had a related patch set uploaded (by STran; author: STran):

[operations/deployment-charts@master] ipoid: Bump version

https://gerrit.wikimedia.org/r/1002924

gerritbot added a comment.Feb 13 2024, 9:18 AM

Change 1002924 merged by jenkins-bot:

[operations/deployment-charts@master] ipoid: Bump version

https://gerrit.wikimedia.org/r/1002924

Maintenance_bot removed a project: Patch-For-Review.Feb 13 2024, 9:30 AM
CodeReviewBot added a project: Patch-For-Review.Feb 13 2024, 11:39 AM

kharlan opened https://gitlab.wikimedia.org/repos/mediawiki/services/ipoid/-/merge_requests/228

update-db: Use for statement instead of .every

CodeReviewBot added a comment.Feb 13 2024, 1:02 PM

stran merged https://gitlab.wikimedia.org/repos/mediawiki/services/ipoid/-/merge_requests/228

update-db: Use for statement instead of .every

Maintenance_bot removed a project: Patch-For-Review.Feb 13 2024, 1:30 PM
CodeReviewBot added a project: Patch-For-Review.Feb 14 2024, 6:40 AM

stran opened https://gitlab.wikimedia.org/repos/mediawiki/services/ipoid/-/merge_requests/229

Prefere the original statement on retry

CodeReviewBot added a comment.Feb 14 2024, 7:23 AM

kharlan merged https://gitlab.wikimedia.org/repos/mediawiki/services/ipoid/-/merge_requests/229

Prefere the original statement on retry

Maintenance_bot removed a project: Patch-For-Review.Feb 14 2024, 7:30 AM
gerritbot added a comment.Feb 14 2024, 7:35 AM

Change 1003360 had a related patch set uploaded (by Kosta Harlan; author: STran):

[operations/deployment-charts@master] ipoid: Bump version

https://gerrit.wikimedia.org/r/1003360

gerritbot added a project: Patch-For-Review.Feb 14 2024, 7:35 AM

Change 1003360 merged by jenkins-bot:

[operations/deployment-charts@master] ipoid: Bump version

https://gerrit.wikimedia.org/r/1003360

Maintenance_bot removed a project: Patch-For-Review.Feb 14 2024, 8:30 AM
kostajh mentioned this in T355051: Observability: Emit counts in data pipeline.Feb 14 2024, 9:09 AM
CodeReviewBot added a project: Patch-For-Review.Feb 14 2024, 5:04 PM

kharlan opened https://gitlab.wikimedia.org/repos/mediawiki/services/ipoid/-/merge_requests/230

main: Use "yesterday" as the current import date

STran moved this task from In Progress to Needs Review on the Trust and Safety Product Sprint (Sprint Kazoo (Jan 29 - Feb 9 2024)) board.Feb 15 2024, 8:47 AM
kostajh mentioned this in T357616: Logs from containers sometimes not visible in logstash.Feb 15 2024, 11:10 AM
Tchanders edited projects, added: Trust and Safety Product Sprint (Sprint Piano (Feb 19th - 1st March)); removed: Trust and Safety Product Sprint (Sprint Kazoo (Jan 29 - Feb 9 2024)).Feb 21 2024, 10:19 AM
Tchanders moved this task from Priority Backlog to Needs review on the Trust and Safety Product Sprint (Sprint Piano (Feb 19th - 1st March)) board.
CodeReviewBot added a comment.Feb 21 2024, 10:45 AM

stran merged https://gitlab.wikimedia.org/repos/mediawiki/services/ipoid/-/merge_requests/230

main: Use "yesterday" as the current import date

gerritbot added a comment.Feb 21 2024, 10:55 AM

Change 1005457 had a related patch set uploaded (by STran; author: STran):

[operations/deployment-charts@master] ipoid: Bump version

https://gerrit.wikimedia.org/r/1005457

gerritbot added a comment.Feb 21 2024, 11:03 AM

Change 1005457 merged by jenkins-bot:

[operations/deployment-charts@master] ipoid: Bump version

https://gerrit.wikimedia.org/r/1005457

Maintenance_bot removed a project: Patch-For-Review.Feb 21 2024, 11:30 AM
Dreamy_Jazz subscribed.Feb 27 2024, 4:53 PM

@STran are there other patches that need review for this ticket? If not, can this be moved into QA?

STran moved this task from Needs review to Needs QA on the Trust and Safety Product Sprint (Sprint Piano (Feb 19th - 1st March)) board.Feb 29 2024, 12:07 PM
dom_walden moved this task from Needs QA to Done on the Trust and Safety Product Sprint (Sprint Piano (Feb 19th - 1st March)) board.EditedMar 7 2024, 9:36 AM
dom_walden subscribed.

We appear to be recovering from duplicate IP errors without interrupting the import completely (i.e. other IPs in the feed are imported).

However, I have found a few puzzling issues with the accuracy of the data on update when there are duplicate IPs. Raised as T359384, T359181, T359157.

EDIT: I have found an issue that happens even without duplicate IPs: T359517.

Further testing of this should be done in those bugs.

Latest ipoid commit tested: 3fc04e78cb82ac031188446aed0aa1210d1200f0

Dreamy_Jazz closed this task as Resolved.Mar 11 2024, 3:19 PM

Marking as resolved as I think there isn't anything else to do in this ticket. Please re-open if I am wrong.

Content licensed under Creative Commons Attribution-ShareAlike (CC BY-SA) 4.0 unless otherwise noted; code licensed under GNU General Public License (GPL) 2.0 or later and other open source licenses. By using this site, you agree to the Terms of Use, Privacy Policy, and Code of Conduct. · Wikimedia Foundation · Privacy Policy · Code of Conduct · Terms of Use · Disclaimer · CC-BY-SA · GPL · Credits