https://github.com/osquery/osquery logo
Join Slack
Powered by
I am trying to migrate from fleet 4.64.2 to 4.75.1...
# fleet
a
I am trying to migrate from fleet 4.64.2 to 4.75.1. I am seeing errors similar to the one described here. https://github.com/fleetdm/fleet/issues/33562 The number of missing migrations in my case is much larger.
Copy code
Missing migrations: tables=[20250410104321 20250421085116 20250422095806 20250424153059 20250430103833 20250430112622 20250501162727 20250502154517 20250502222222 20250507170845 20250513162912 20250519161614 20250519170000 20250520153848 20250528115932 20250529102706 20250603105558 20250609102714 20250609112613 20250613103810 20250616193950 20250624140757 20250626130239 20250629131032 20250701155654 20250707095725 20250716152435 20250718091828 20250728122229 20250731122715 20250731151000 20250803000000 20250805083116 20250807140441 20250808000000 20250811155036 20250813205039 20250814123333 20250815130115 20250816115553 20250817154557 20250825113751 20250827113140 20250828120836 20250902112642 20250904091745 20250905090000 20250922083056 20250923120000 20250926123048 20251015103505]
Interestingly, we have two production environments and only one of them has this problem. The migration completed smoothly from 4.64.2-> 4.73.0 -> 4.75.1 on one of them. But for the other migrations keep getting stuck on 4.64.2 with the above error. Any input to resolve this is much appreciated!
k
Hi @Aditya Oza! Can you share the full output when running 
fleet prepare db
?
a
@Kathy Satterlee thank you! Here are logs from our deployment. The interesting part to me is this one
Copy code
=================== Running Fleet DB Setup / Migration ===================

2025/11/06 19:49:25 [2025-04-10] Update Mac OS Software Names

2025/11/06 19:49:27 FAIL 20250410104321_UpdateMacOSSoftwareNames.go (adding temporary index to host_software_installed_paths: Error 1061 (42000): Duplicate key name 'software_id'), quitting migration.
logs.txt
u
Was Fleet completely offline while these migrations were running?
a
I had scaled down all ASG/EC2 to 0 before triggering the deployment. Multiple instances were spun up during the deployment. One of the instances obtained a lock to proceed with the migration, the others were waiting on the migration to complete. If it helps, these are our 
entrypoint.sh
/startup scripts
entrypoint.shfleet_setup.sh
k
We usually only see errors like this when multiple instances try to run migrations at the same time. It doesn't look like that's likely to be happening given your workflow, but would it be possible to scale Fleet down and try manually running the migration?
Do you happen to have the output handy for your first attempt at running migrations as well?
a
we first attempted the version upgrade/DB migration on 21st October. Logs from then..
logs-10-21.txt
@Kathy Satterlee If I want to drop the 
software_id
index from host_software_installed_paths, is below a good command to run before 
fleet prepare db
Copy code
use kolide; ALTER TABLE host_software_installed_paths DROP INDEX software_id;
I came across this issue which talks about dropping some tables/data and figured whether dropping 
software_id
index would be sufficient in this case. https://github.com/fleetdm/fleet/issues/32576 Thank you for your support!
💯 1
k
You may also need to clean up some duplicate software. I've got a query for that, one sec.
Copy code
DELETE 
FROM software s1
JOIN (
    SELECT
        MIN(id) AS keep_id,  -- keep the row with the smallest id
        CONCAT_WS(CHAR(0),
            version,
            source,
            bundle_identifier,
            `release`,
            arch,
            vendor,
            browser,
            extension_id,
            name
        ) AS software_item
    FROM software
    WHERE source = 'apps'
      AND bundle_identifier IS NOT NULL
      AND bundle_identifier != ''
    GROUP BY future_checksum
    HAVING COUNT(*) > 1
) s2 ON CONCAT_WS(CHAR(0),
            s1.version,
            s1.source,
            s1.bundle_identifier,
            s1.`release`,
            s1.arch,
            s1.vendor,
            s1.browser,
            s1.extension_id,
            s1.name
        ) = s2.software_item
WHERE s1.id != s2.keep_id;
16 Views
Open in Slack
PreviousNext
Powered by