https://github.com/osquery/osquery logo
Join Slack
Powered by
Hey! I am getting a failed migration when running ...
# fleet
j
Hey! I am getting a failed migration when running the migrations for upgrading from 4.74.0 to 4.75.1 (and same if I try 4.75.0)
Copy code
fleet-migration 2025/10/22 10:48:21 [2025-10-15] Add Name To Software Checkum Calculation                                                                                                                                                                                                                             ││ fleet-migration 2025/10/22 10:48:21 FAIL 20251015103505_AddNameToSoftwareCheckumCalculation.go (updating software checksums: Error 1062 (23000): Duplicate entry '\x9Ct \x01m)7\xF5\xA5\xD0\xA4\x00\xFFt\xB4I' for key 'software.idx_software_checksum'), quitting migration.
Please advise 😅
g
Hi Jarle, Looking at that error, you may have some duplicate entries on your software table. But I could be wrong. I'll check with someone else and get back to you as soon as I can! 🙂
🙌 1
u
Hey Jarle,This does seem to be pointing towards my initial thought earlier about there being duplicate entries on your software table. I'm trying to get some more information on the best way to resolve for you.
j
Ok. Thanks. Right now I am stuck at 4.74.0
u
After speaking with the team a little more, this error generally comes up if a Fleet instance was online during the migration.- Was Fleet a instance running at the time?- If YES, make sure Fleet is offline first- Roll back your database to the snapshot before the upgrade- Prepare the DB- Bring Fleet back online
j
Nope. I took down Fleet before the upgrade. I can do a database rollback anyways to see if this resolves the issue....
No dice. The problem still persists even after rolling back to before my migration attempt.
👀 1
g
Oh no! I'm sorry to hear that didn't resolve it. And thank you for confirming that Fleet was down during the previous upgrade. Is it the same duplicate causing the issue again? I believe our next step would have to be to locate that duplicate, and remove it from the table. Make sure you've got a backup before making any changes. This should help you locate any existing duplicates on the table:
Copy code
SELECT checksum, COUNT(*) as count
FROM software
GROUP BY checksum
HAVING COUNT(*) > 1;
This should help you locate any potential duplicates during the preparation:
Copy code
SELECT
  MD5(CONCAT_WS(CHAR(0),
    version,
    source,
    bundle_identifier,
    `release`,
    arch,
    vendor,
    browser,
    extension_id,
    name
  )) AS future_checksum,
  COUNT(*) as count
FROM software
WHERE source = 'apps'
  AND bundle_identifier IS NOT NULL
  AND bundle_identifier != ''
GROUP BY future_checksum
HAVING COUNT(*) > 1;
Let me know if that helps/resolves it.
👀 1
j
Ok, so. Currently no duplicates: 
Empty set
Future, a lot:
Copy code
+----------------------------------+-------+
| future_checksum                  | count |
+----------------------------------+-------+
| 2d061e47d037d7f5115a312c002a616c |     2 |
| 04a8b01287479374118491d6655e8a7b |     2 |
| 66185d9ea0ed6d94c72738546483f246 |     2 |
| f6efbb77af391a564027148cef2ba882 |     2 |
| d9fd8076ab95419dd6d7606ba3db072b |     2 |
| 79ea0ed14542395ac9cf84fb90641a6b |     2 |
| 48c76abd089413964e3a229b49619dcc |     2 |
| 8b9d01810847b825c7bdbd261023a943 |     2 |
| 520b35a9434bf5af3add2e4c2c1c6f8d |     2 |
| 4a31b5ffb14da918803745422af3bcb4 |     2 |
| c4b2bb76524ab8e42996afe372aa5d83 |     2 |
| e4133aa570b401b3ddeaffd6fa27a7ee |     2 |
| ae20431c723795dd2c5f76ce7983ca0e |     2 |
| 475801e66765fff0e2363abfa2ba824d |     2 |
| 939b9991ae370f6c62467bc91a183f42 |     2 |
| 8b28c73a8f390a65b1bbce3aebf25319 |     2 |
| 1914155a8e94016e95cdb540862c94bf |     2 |
| 5c70779e821b2f6c7874a88f10afdf51 |     2 |
| cc44702dc157d5f1262b44c66af7c35d |     2 |
| 255788e30b846daf6aeed000de0b324b |     2 |
| 5ccb182582ce6437cfd06b9c97fb35eb |     2 |
| 23f678a46d6a187be4c858108acb99dd |     2 |
| 4c2e548bc9a7d2764ed29ee88862bcce |     2 |
| 73aa8fb4b6c8507687a249e6af102c5b |     2 |
| 9c7420016d2937f5a5d0a400ff74b449 |     2 |
| 1168c0d3814fa9742af8794f0792b34f |     2 |
+----------------------------------+-------+
👀 1
They're all MacOS stuff: 
+-------------------------------+---------+----------------------------------+-------+
| name                          | version | future_checksum                  | count |
+-------------------------------+---------+----------------------------------+-------+
| Add Printer                   | 607     | 2d061e47d037d7f5115a312c002a616c |     2 |
| Speech Service                | 9.2.22  | 04a8b01287479374118491d6655e8a7b |     2 |
| SpotlightService              | 3.0     | 66185d9ea0ed6d94c72738546483f246 |     2 |
| com.apple.FontRegistryUIAgent | 81.0    | f6efbb77af391a564027148cef2ba882 |     2 |
| AirPlay Screen Mirroring      | 2.0     | d9fd8076ab95419dd6d7606ba3db072b |     2 |
| Single Sign-On                | 1.0     | 79ea0ed14542395ac9cf84fb90641a6b |     2 |
| Screen Time                   | 4.1     | 48c76abd089413964e3a229b49619dcc |     2 |
| Keyboard Setup Assistant      | 1.0     | 8b9d01810847b825c7bdbd261023a943 |     2 |
| Cisco Secure Client NVM Agent | 1.0     | 520b35a9434bf5af3add2e4c2c1c6f8d |     2 |
| RegisterPluginIM              | 26.200  | 4a31b5ffb14da918803745422af3bcb4 |     2 |
| OpenSpell                     | 1.0     | c4b2bb76524ab8e42996afe372aa5d83 |     2 |
| Sync Services UI Daemon       | 8.1     | e4133aa570b401b3ddeaffd6fa27a7ee |     2 |
| Quick Look Helper             | 5.0     | ae20431c723795dd2c5f76ce7983ca0e |     2 |
| Accessibility Services        | 1.0     | 475801e66765fff0e2363abfa2ba824d |     2 |
| Japanese Kana Palette         | 1.1.0   | 939b9991ae370f6c62467bc91a183f42 |     2 |
| Xcode Previews                | 16.0    | 8b28c73a8f390a65b1bbce3aebf25319 |     2 |
| Control Strip                 | 1.0     | 1914155a8e94016e95cdb540862c94bf |     2 |
| Sync Server                   | 8.1     | 5c70779e821b2f6c7874a88f10afdf51 |     2 |
| Family Request                | 1.0     | cc44702dc157d5f1262b44c66af7c35d |     2 |
| Calendar / Reminders          | 8.0     | 255788e30b846daf6aeed000de0b324b |     2 |
| Emoji & Symbols               | 2.0.1   | 5ccb182582ce6437cfd06b9c97fb35eb |     2 |
| Tamil                         | 1.6     | 23f678a46d6a187be4c858108acb99dd |     2 |
| Quick Look UI Helper          | 5.0     | 4c2e548bc9a7d2764ed29ee88862bcce |     2 |
| AppleSpell                    | 2.4     | 73aa8fb4b6c8507687a249e6af102c5b |     2 |
| AssistiveControl              | 2.0     | 9c7420016d2937f5a5d0a400ff74b449 |     2 |
| Ainu Input Method             | 1.0     | 1168c0d3814fa9742af8794f0792b34f |     2 |
+-------------------------------+---------+----------------------------------+-------+
And looking at e.g. "Add Printer" I see that it currently has two entries, with different checksum:
Copy code
+-------+-------------+---------+--------+---------------------+---------+------------+------+--------+---------+--------------+----------+------------------------------------+-------------+
| id    | name        | version | source | bundle_identifier   | release | vendor_old | arch | vendor | browser | extension_id | title_id | checksum                           | name_source |
+-------+-------------+---------+--------+---------------------+---------+------------+------+--------+---------+--------------+----------+------------------------------------+-------------+
| 10144 | Add Printer | 607     | apps   | com.apple.print.add |         |            |      |        |         |              |      396 | 0xAADBF0C8B8CDA8F06A53CB1E8069DE73 | basic       |
| 11047 | Add Printer | 607     | apps   | com.apple.print.add |         |            |      |        |         |              |      396 | 0x670E744BBC4D5C8643BF9A895ADABA6F | basic       |
+-------+-------------+---------+--------+---------------------+---------+------------+------+--------+---------+--------------+----------+------------------------------------+-------------+
👀 1
g
Thanks, Jarle. That's ... more than expected. If you delete the duplicate rows related to those, that should let the prepare db stage complete. If you need it, try this for returning the rows you need to work with:
Copy code
SELECT id, name, version, source, bundle_identifier, `release`, arch, vendor, browser, extension_id
FROM software
WHERE MD5(CONCAT_WS(CHAR(0),
  version, source, bundle_identifier, `release`, arch, vendor, browser, extension_id, name
)) = '2d061e47d037d7f5115a312c002a616c';
Do that for each one. Again, make sure you've got a backup before doing anything 🙂. Try it for one first to make sure it no longer exists in the future_checksum results. The software table will repopulate once devices report in.
j
OK. I will try that.
👍 1
g
Thank you for your patience and working with us to resolve!
j
Alright. That was a tedious job, but the migration went through and we are now on 4.75.1 🎉 Thank you 🙌
🙌 1
g
That's fantastic news, glad you're updated now. I'll close this off and grab myself a cup of tea to celebrate. Let me know if you need anything else. Have an awesome day 🙂!
41 Views
Open in Slack
PreviousNext
Powered by