upgrade from 4.54.1 to 4.55.0 failed - DB Migration - updating primary key in vpp_apps failed

[daniejstriata]

Fleet version: → fleetctl --version fleetctl - version 4.55.0 branch: HEAD revision: 11aaa4fed9082aefd905d5872a49c53a7824c74e build date: 2024-08-09 build user: runner go version: go1.22.4

💥  Actual behavior

################################################################################
# WARNING:
#   This will perform Fleet database migrations. Please back up your data before
#   continuing.
#
#   Missing migrations: tables=[20240723102712 20240725152735 20240725182118 20240726100517 20240730171504 20240730174056 20240730215453 20240730374423 20240801115359 20240802101043 20240802113716], data=[].
#
#   Press Enter to continue, or Control-c to exit.
################################################################################

2024/08/12 09:19:03 [2024-07-23] VPPDB Updates
2024/08/12 09:19:03 [2024-07-25] Enforce File Vault Setup Assistant
2024/08/12 09:19:03 [2024-07-25] Add Additional Identifier To Software Titles
2024/08/12 09:19:03 [2024-07-26] Nano Queue Timestamp Precision
2024/08/12 09:19:04 [2024-07-30] Add Global Stat To Vuln Host Counts
2024/08/12 09:19:04 [2024-07-30] Add Global Stat To Software Host Counts
2024/08/12 09:19:06 [2024-07-30] Add Global Stat To Software Title Host Counts
2024/08/12 09:19:07 [2024-07-30] Add Platform To VPP Apps
2024/08/12 09:19:07 FAIL 20240730374423_AddPlatformToVPPApps.go (updating primary key in vpp_apps: Error 1553 (HY000): Cannot drop index 'PRIMARY': needed in a foreign key constraint), quitting migration.

🧑‍💻  Steps to reproduce

1. upgrade from 4.54.1 to 4.55.0
2. prepare database

[daniejstriata]

Running the prepare process now gives me:

################################################################################
# WARNING:
#   This will perform Fleet database migrations. Please back up your data before
#   continuing.
#
#   Missing migrations: tables=[20240730374423 20240801115359 20240802101043 20240802113716], data=[].
#
#   Press Enter to continue, or Control-c to exit.
################################################################################

2024/08/12 09:19:16 [2024-07-30] Add Platform To VPP Apps
2024/08/12 09:19:16 FAIL 20240730374423_AddPlatformToVPPApps.go (adding platform to vpp_apps: Error 1060 (42S21): Duplicate column name 'platform'), quitting migration.

MySQL [fleet]> describe vpp_apps;
+-------------------+--------------+------+-----+-------------------+-----------------------------------------------+
| Field             | Type         | Null | Key | Default           | Extra                                         |
+-------------------+--------------+------+-----+-------------------+-----------------------------------------------+
| adam_id           | varchar(16)  | NO   | PRI | NULL              |                                               |
| title_id          | int unsigned | YES  | MUL | NULL              |                                               |
| bundle_identifier | varchar(255) | NO   |     |                   |                                               |
| icon_url          | varchar(255) | NO   |     |                   |                                               |
| name              | varchar(255) | NO   |     |                   |                                               |
| latest_version    | varchar(255) | NO   |     |                   |                                               |
| created_at        | timestamp    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED                             |
| updated_at        | timestamp    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
| platform          | varchar(10)  | NO   |     | NULL              |                                               |
+-------------------+--------------+------+-----+-------------------+-----------------------------------------------+

[lucasmrod]

Hi @daniejstriata!

What's your MySQL server and version?

[daniejstriata]

@lucasmrod I have recently upgraded the server providing the database to MySQL 8.4.2 LTS.

[getvictor]

@daniejstriata Unfortunately we don't yet support MySQL 8.4. I just tried MySQL 8.4, and I hit other issues with migrations before getting to your specific one.

I filed #21270 to add support.

Any specific reason you need 8.4.2 vs 8.0?

Workaround may be to:

1. Dump DB.
2. Load DB into MySQL 8.0 server.
3. Do migration.
4. Dump DB.
5. Load DB into MySQL 8.4.2 server.

[daniejstriata]

The DB Sever hosts other Databases too and I missed the fact that FleetDM is running on it too and not working with 8.4 yet. I was able to roll-back the upgrade of 8.4.

[xpkoala]

@daniejstriata I just wanted to confirm that your Fleet instance is now working since you had rolled back your MySQL version.

[getvictor]

@daniejstriata MySQL 8.4.2 support should be in the next 4.56.0 release.

[daniejstriata]

@daniejstriata I just wanted to confirm that your Fleet instance is now working since you had rolled back your MySQL version.

I had to rollback to a previous backup of the database. All is working again. thanks.

[fleet-release]

Failed DB migration,
Fleet version upgrade stumbles.
Brighter paths await.

[daniejstriata]

I upgraded to the latest version without any issues. Thank you! @xpkoala @lukeheath