https://github.com/osquery/osquery logo
Join Slack
Powered by
Hey Fleet team, We're stuck upgrading from v4.86.1...
# fleet
a
Hey Fleet team, We're stuck upgrading from v4.86.1 to v4.89.1. fleet prepare db fails every time on the CleanupSoftwareLastOpenedAtSentinels step with: 
Error 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
The error references "clearing sentinel host_software.last_opened_at values". Our setup: we have two instances, one is the migration leader that runs fleet prepare db, the other just waits. There are no old Fleet instances running — we've verified zero running EC2 instances. So the only MySQL connections come from fleet prepare db itself. Since Error 1205 means another transaction is holding a conflicting lock, and nothing else is connected to the DB, the contention seems to be internal to fleet prepare db. Have you seen this issue before? Any recommendations on how I can get past it? I am open to the idea of deleting records in our sw inventory tables as it will likely get re-populated as hosts publish new info to Fleet server. Appreciate any input!
u
Hello, I haven't seen this particular error before, but I have seen issues when trying to update between multiple versions at one time. Are you able to try updating to 4.87.1, 4.88.1, then 4.89.1?
a
4.89.1 contains fixes to critical CVEs and that's why we want to get on it as soon as we can. Let me think about doing gradual upgrades as you have suggested.
@Steven Palmesano Could you release a patch 4.87.2 with the CVEs fixed in 4.89.x? Doing gradual upgrades is something we want to avoid as the CVEs have been pending for more than a month now. Let me know if releasing 4.86 or 4.87 with CVE patches is something you can consider.
u
I'm in a block of calls now, and don't know if I'll be able to check on this today. Gradual updates don't take that much time to go through though (just did this a few weeks with another customer). The reason to do it that way is to resolve update issues when going between multiple versions. In the end, you should still be able to land on 4.89.1.
z
Hey @Aditya Oza For community support, we only commit patches to the latest version of Fleet, so we wouldn't be able to backport to 4.87.X at this time.
👍 1
a
@Steven Palmesano We still see the same error when migrating to 4.87.1
Copy code
2026/07/22 18:53:15 FAIL 20260608210432_CleanupSoftwareLastOpenedAtSentinels.go (clearing sentinel host_software.last_opened_at values: Error 1205 (HY000): Lock wait timeout exceeded; try restarting transaction), quitting migration
Do you have any suggestions? Can you think of any other process that might be holding up a lock to this table?
For community support, we only commit patches to the latest version of Fleet
@Zay Hanlon All good here. We are running into the same issue with 4.87.x as well, and so my earlier request isn't relevant anymore.
✅ 1
z
Sounds good and thanks for confirming that you're working through the step by step path!
@Steven Palmesano this would be a good escalation up to @Kathy Satterlee
👀 1
k
I definitely haven't seen that error pop up during migrations. Taking a look at what that particular migration is doing.
ty 1
@Aditya Oza Could you, or someone with database access please run this query? I'd like to see if the issue is just the amount of time it may take to do a full scan of the host_software table:
Copy code
SELECT table_rows, data_length, index_length
FROM information_schema.tables
WHERE table_schema = DATABASE() AND table_name = 'host_software';
a
@Kathy Satterlee
Copy code
mysql> SELECT table_rows, data_length, index_length
    -> FROM information_schema.tables
    -> WHERE table_schema = DATABASE() AND table_name = 'host_software';
+------------+-------------+--------------+
| TABLE_ROWS | DATA_LENGTH | INDEX_LENGTH |
+------------+-------------+--------------+
|  505025579 | 30064787456 |            0 |
+------------+-------------+--------------+
1 row in set (0.00 sec)
The error 
Lock wait timeout exceeded; try restarting transaction
suggests that my process is waiting on something else to release the lock on that table. I'm not sure what might be holding up the lock since we have only one instance performing the DB migration. To me it appears to be a lock contention problem and not a data volume problem. @Kathy Satterlee could you share your thoughts?
k
I think that we've got a huge amount of data in that table, so the updates are locking things up for too long. For manual remediation, we could clear out the software tables, but I could also give you a process for manually completing the migration by stepping through the table rather than doing a full scan. What would work best for you?
a
@Kathy Satterlee thank you. Could you give me queries for both? For the nuclear option for clearing all software tables, let me know which tables need to be cleared.
u
Please make sure to backup the database prior to trying this!
Copy code
`-- =====================================================================`
Copy code
`-- Mitigation for 20260608210432_CleanupSoftwareLastOpenedAtSentinels`
Copy code
`-- Batches the UPDATE by host_id range to keep each transaction's lock`
Copy code
`-- footprint small, instead of one full-table scan/update.`
Copy code
`-- =====================================================================`
Copy code
`DELIMITER $$`
Copy code
`DROP PROCEDURE IF EXISTS cleanup_software_last_opened_at_sentinels$$`
Copy code
`CREATE PROCEDURE cleanup_software_last_opened_at_sentinels(IN batch_size INT)`
Copy code
`BEGIN`
Copy code
`DECLARE min_host_id INT UNSIGNED;`
Copy code
`DECLARE max_host_id INT UNSIGNED;`
Copy code
`DECLARE range_start INT UNSIGNED;`
Copy code
`DECLARE range_end INT UNSIGNED;`
Copy code
`DECLARE rows_affected INT DEFAULT 0;`
Copy code
`DECLARE total_cleared BIGINT DEFAULT 0;`
Copy code
`IF batch_size IS NULL OR batch_size <= 0 THEN`
Copy code
`SET batch_size = 5000;`
Copy code
`END IF;`
Copy code
`SELECT MIN(host_id), MAX(host_id) INTO min_host_id, max_host_id FROM host_software;`
Copy code
`IF min_host_id IS NULL THEN`
Copy code
`SELECT 'host_software is empty; nothing to do.' AS message;`
Copy code
`ELSE`
Copy code
`SET range_start = min_host_id;`
Copy code
`WHILE range_start <= max_host_id DO`
Copy code
`SET range_end = range_start + batch_size - 1;`
Copy code
`UPDATE host_software`
Copy code
`SET last_opened_at = NULL`
Copy code
`WHERE last_opened_at = '1980-01-01 00:00:00'`
Copy code
`AND host_id BETWEEN range_start AND range_end;`
Copy code
`SET rows_affected = ROW_COUNT();`
Copy code
`SET total_cleared = total_cleared + rows_affected;`
Copy code
`IF rows_affected > 0 THEN`
Copy code
`SELECT CONCAT('host_id ', range_start, '-', range_end,`
Copy code
`': cleared ', rows_affected, ' row(s)') AS progress;`
Copy code
`END IF;`
Copy code
`SET range_start = range_end + 1;`
Copy code
`-- Small pause between batches to avoid saturating I/O; drop if unnecessary.`
Copy code
`DO SLEEP(0.05);`
Copy code
`END WHILE;`
Copy code
`SELECT CONCAT('Done. Total rows cleared: ', total_cleared) AS summary;`
Copy code
`END IF;`
Copy code
`END$$`
Copy code
`DELIMITER ;`
Copy code
`-- Adjust batch size (host_id span per iteration) to taste. 5000 is a`
Copy code
`-- reasonable starting point; lower it if you still see contention.`
Copy code
`CALL cleanup_software_last_opened_at_sentinels(5000);`
Copy code
`DROP PROCEDURE cleanup_software_last_opened_at_sentinels;`
Copy code
`-- =====================================================================`
Copy code
`-- Verify no sentinel rows remain before marking the migration applied`
Copy code
`-- =====================================================================`
Copy code
`SELECT COUNT(*) AS remaining_sentinels`
Copy code
`FROM host_software`
Copy code
`WHERE last_opened_at = '1980-01-01 00:00:00';`
Copy code
`-- Only proceed past this point if remaining_sentinels = 0.`
Copy code
`-- =====================================================================`
Copy code
`-- Mark the migration as applied in migration_status_tables`
Copy code
`-- =====================================================================`
Copy code
`INSERT INTO migration_status_tables (version_id, is_applied, tstamp)`
Copy code
`SELECT 20260608210432, 1, NOW()`
Copy code
`WHERE NOT EXISTS (`
Copy code
`SELECT 1 FROM migration_status_tables WHERE version_id = 20260608210432`
Copy code
`);`
Copy code
`UPDATE migration_status_tables`
Copy code
`SET is_applied = 1, tstamp = NOW()`
Copy code
`WHERE version_id = 20260608210432 AND is_applied = 0;`
Copy code
`-- Confirm`
Copy code
`SELECT * FROM migration_status_tables WHERE version_id = 20260608210432;`
k
And for fully clearing the table:
Copy code
sql
-- =====================================================================
-- Full reset of host_software and dependent aggregate/derived data.
-- Forces a complete software inventory re-collection on next check-in
-- for every host. Run only with servers stopped.
-- =====================================================================

-- 1. Per-host installed-path records are keyed by (host_id, software_id)
--    pairs that live in host_software. Once host_software is cleared,
--    these become orphaned rows with no corresponding inventory entry.
TRUNCATE TABLE host_software_installed_paths;

-- 2. The main host <-> software association table.
--    TRUNCATE (not DELETE) deliberately here — it's a DDL-style
--    operation, not a row-by-row scan/delete, so it avoids the exact
--    lock/scan cost problem from the sentinel migration entirely.
TRUNCATE TABLE host_software;

-- 3. Aggregate host counts per software_id (global + per-team) are
--    now all stale/zero. These have a CHECK (hosts_count > 0)
--    constraint, so the correct reset is to remove the rows entirely
--    rather than try to zero them out. Fleet's periodic host-count
--    calculation cron repopulates these once host_software fills
--    back in from re-collection.
TRUNCATE TABLE software_host_counts;

-- 4. Same idea, but per software TITLE rather than per exact version.
TRUNCATE TABLE software_titles_host_counts;

-- =====================================================================
-- Optional: force a fresh diff cycle rather than relying on osquery's
-- normal schedule to notice the gap on its own.
-- =====================================================================
-- software_updated_at is the diff-gate Fleet uses to decide whether a
-- host's software query results need reprocessing. Resetting it to
-- NULL is a nudge to make sure the next check-in re-ingests fully
-- rather than assuming "nothing changed" if it were to compare against
-- a stale timestamp. I'm flagging this as an assumption on ingestion
-- behavior, not something I've verified against the ingestion code —
-- worth confirming before running if you want to be precise about it.
-- UPDATE hosts SET software_updated_at = NULL;

-- =====================================================================
-- Verification
-- =====================================================================
SELECT
  (SELECT COUNT(*) FROM host_software)                  AS host_software_rows,
  (SELECT COUNT(*) FROM host_software_installed_paths)  AS installed_paths_rows,
  (SELECT COUNT(*) FROM software_host_counts)           AS software_host_counts_rows,
  (SELECT COUNT(*) FROM software_titles_host_counts)    AS software_titles_host_counts_rows;
-- All four should read 0.
Better formatted version of the mitigation script:
Copy code
-- =====================================================================
-- Mitigation for 20260608210432_CleanupSoftwareLastOpenedAtSentinels
-- Batches the UPDATE by host_id range to keep each transaction's lock
-- footprint small, instead of one full-table scan/update.
-- =====================================================================

DELIMITER $$

DROP PROCEDURE IF EXISTS cleanup_software_last_opened_at_sentinels$$

CREATE PROCEDURE cleanup_software_last_opened_at_sentinels(IN batch_size INT)
BEGIN
    DECLARE min_host_id INT UNSIGNED;
    DECLARE max_host_id INT UNSIGNED;
    DECLARE range_start INT UNSIGNED;
    DECLARE range_end INT UNSIGNED;
    DECLARE rows_affected INT DEFAULT 0;
    DECLARE total_cleared BIGINT DEFAULT 0;

    IF batch_size IS NULL OR batch_size <= 0 THEN
        SET batch_size = 5000;
    END IF;

    SELECT MIN(host_id), MAX(host_id) INTO min_host_id, max_host_id FROM host_software;

    IF min_host_id IS NULL THEN
        SELECT 'host_software is empty; nothing to do.' AS message;
    ELSE
        SET range_start = min_host_id;

        WHILE range_start <= max_host_id DO
            SET range_end = range_start + batch_size - 1;

            UPDATE host_software
            SET last_opened_at = NULL
            WHERE last_opened_at = '1980-01-01 00:00:00'
              AND host_id BETWEEN range_start AND range_end;

            SET rows_affected = ROW_COUNT();
            SET total_cleared = total_cleared + rows_affected;

            IF rows_affected > 0 THEN
                SELECT CONCAT('host_id ', range_start, '-', range_end,
                               ': cleared ', rows_affected, ' row(s)') AS progress;
            END IF;

            SET range_start = range_end + 1;

            -- Small pause between batches to avoid saturating I/O; drop if unnecessary.
            DO SLEEP(0.05);
        END WHILE;

        SELECT CONCAT('Done. Total rows cleared: ', total_cleared) AS summary;
    END IF;
END$$

DELIMITER ;

-- Adjust batch size (host_id span per iteration) to taste. 5000 is a
-- reasonable starting point; lower it if you still see contention.
CALL cleanup_software_last_opened_at_sentinels(5000);

DROP PROCEDURE cleanup_software_last_opened_at_sentinels;

-- =====================================================================
-- Verify no sentinel rows remain before marking the migration applied
-- =====================================================================
SELECT COUNT(*) AS remaining_sentinels
FROM host_software
WHERE last_opened_at = '1980-01-01 00:00:00';

-- Only proceed past this point if remaining_sentinels = 0.

-- =====================================================================
-- Mark the migration as applied in migration_status_tables
-- =====================================================================
INSERT INTO migration_status_tables (version_id, is_applied, tstamp)
SELECT 20260608210432, 1, NOW()
WHERE NOT EXISTS (
    SELECT 1 FROM migration_status_tables WHERE version_id = 20260608210432
);

UPDATE migration_status_tables
SET is_applied = 1, tstamp = NOW()
WHERE version_id = 20260608210432 AND is_applied = 0;

-- Confirm
SELECT * FROM migration_status_tables WHERE version_id = 20260608210432;
a
@Kathy Satterlee Thank you. I appreciate this!
u
No problem! If you go the mitigation route, I included marking the migration as complete so it'll be skipped when you run prepare db again.
11 Views
Open in Slack
PreviousNext
Powered by