Cannot upgrade fleetdm database to version 4.65.0

[spkis]

Fleet version:

Web browser and operating system:

---

💥  Actual behavior

Trying to update, it throws an error. Coming from 4.63.0:

2025/03/20 14:22:32 [2025-02-17] Migrate Pending Upcoming Activities 2025/03/20 14:22:32 FAIL 20250217093329_MigratePendingUpcomingActivities.go (failed to insert pending vpp app installs: Error 1045 (28000): Access denied for user 'itops'@'%' (using password: YES)), quitting migration. Permissions are checked, the user has ALL permissions to the database!

🧑‍💻  Steps to reproduce

1. Change to fleetdm version 4.65.0
2. execute the container setup with prepare db
3. error

🕯️ More info (optional)

N/A

[sharon-fdm]

@xpkoala , @lucasmrod , this could potentially be severe. Can you give it a glance please?

[lucasmrod]

Hi @spkis!

Is it possible the DB user itops set to Fleet does not have the necessary permissions?

[lucasmrod]

Sorry @spkis it was just pointed out at me that you clarified "Permissions are checked, the user has ALL permissions to the database!" :)

Double checking: Could you try using a MySQL client and log in to the MySQL with the user+password that Fleet is using and try to CREATE a dummy table on the database configured in Fleet? (e.g. maybe the password was recently changed, which would explain the "Access denied" error)

[spkis]

@lucasmrod I have done all of that. I have a phpmyadmin instance, which i tried to login etc. before. I also made a new dummy table now:

So no this is not the issue :-)

[castrapel]

I have a similar problem with one of my Fleet deployments, but one of my other Fleet deployments works. For me, the issue was with the nano_view_queue view. DESCRIBE nano_view_queue; in my non-working deployment's DB gave me an error, but it works fine in my working deployment DB.

Details: Error 1356: View 'fleet.nano_view_queue' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

My view was empty, so I just deleted it and recreated it with the following:

CREATE ALGORITHM=UNDEFINED DEFINER=`fleet`@`` SQL SECURITY DEFINER VIEW `nano_view_queue` AS select `q`.`id` AS `id`,`q`.`created_at` AS `created_at`,`q`.`active` AS `active`,`q`.`priority` AS `priority`,`c`.`command_uuid` AS `command_uuid`,`c`.`request_type` AS `request_type`,`c`.`command` AS `command`,`r`.`updated_at` AS `result_updated_at`,`r`.`status` AS `status`,`r`.`result` AS `result` from ((`nano_enrollment_queue` `q` join `nano_commands` `c` on((`q`.`command_uuid` = `c`.`command_uuid`))) left join `nano_command_results` `r` on(((`r`.`command_uuid` = `q`.`command_uuid`) and (`r`.`id` = `q`.`id`)))) order by `q`.`priority` desc,`q`.`created_at`

Then I ran fleet prepare db to fix my non-working deployment.

[sgress454]

IIRC there is anecdotal evidence that this can happen if the migrations are run while the Fleet server is also running. I think a possible solve here would be to be provide a script that is auto-generated (similar to our test schema dump) that takes a mysql user and grants them permissions on all tables/views. I'm not sure how much luck we'll have reproducing this reliably, but we can certainly create and test such a script.

[spkis]

Hi, Well could NOT be my problem, since e I run everything containered with startup parameters. So it will run BEFORE it starts the fleet server.

Ricardo Kissinger Head of IT Infrastructure and IT Security

[spkis]

Figured out the issue for us is, that the user who owns or has permissions on the view is "fleet" while we use a different user now for working. We will also drop the view and create it from scratch! hope it helps!

Though, I think that this is the initial problem since we don't use the fleet user (anymore).

[lucasmrod]

@spkis Thanks for the additional information!

@xpkoala @sgress454 When time allows we could try reproducing by running Fleet server with a different FLEET_MYSQL_USER than the default fleet.

[juan-fdz-hawa]

This looks like it was a permissions issue on the DB.

Seems like Fleet was initially bootstrapped using the default fleet DB user and then switched for the itops DB user. The itops DB user did not have the proper SELECT privileges on the nano_view_queue view when the migration was trying to run which caused the failure - but just to be safe, I double checked the 4.64.0 to 4.65.0 update.

[fleet-release]

Upgrade path now clear, Access granted, no more fear, Fleet's vision is near.

[spkis]

Thanks! Could in fact be yes, that we switched it between but great it works +1