Reconciling software titles fails due to ON DUPLICATE KEY UPDATE issue

💥 Actual behavior

In some cases cron_vulnerabilities schedule is failing at the cron_reconcile_software_titles job, due to an issue with the insert query.

This error is frequent enough that it's blocking https://app.zenhub.com/workspaces/help-engineering-672a4556609a0d000f391584/issues/gh/fleetdm/fleet/19930

🧑‍💻 Steps to reproduce

Not sure how to reproduce this locally, as I'm not sure of the root cause (likely something to do with how software_title rows are inserted, leading to a collision with auto-incremented IDs). But the error is:

upsert software titles: Error 1869 (HY000): Auto-increment value in UPDATE conflicts with internally generated values

🕯️ More info (optional)

This job runs after the main vulnerability scan jobs have already completed, but before updating host counts for software titles and issues. So it's probably not causing issues with reporting vulnerabilities on individual hosts, but may be causing incorrect results when querying across hosts.

🛠️ To fix

I think we can just update this query to not set the id when duplicate key is detected, but set everything else instead (this strategy is already used in similar queries in this file), e.g.:

ON DUPLICATE KEY UPDATE
    software_titles.name = VALUES(name),
    software_titles.source = VALUES(source),
    software_titles.browser = VALUES(browser),
    software_titles.bundle_identifier = VALUES(bundle_identifier)

fleetdm / fleet