https://github.com/osquery/osquery logo
Join Slack
Powered by
Hello, I just tried to run an update from 4.55.1 &...
# fleet
j
Hello, I just tried to run an update from 4.55.1 > 4.56.0 and i'm getting a migration error MYSQL_8_0_18.R20240527.01_00 Running on Alpine
Copy code
FAIL 20240826160025_AddRemovedToInstalls.go (failed to find host_software_installs to remove: Error 1054 (42S22): Unknown column 'st.id' in 'on clause'), quitting migration.
Copy code
DEFAULT 2024-09-11T18:35:00.775395628Z [resource.labels.instanceId: fleetdm-corp-sj0p] ################################################################################
DEFAULT 2024-09-11T18:35:00.775943474Z [resource.labels.instanceId: fleetdm-corp-sj0p] # WARNING:
DEFAULT 2024-09-11T18:35:00.776012768Z [resource.labels.instanceId: fleetdm-corp-sj0p] # This will perform Fleet database migrations. Please back up your data before
DEFAULT 2024-09-11T18:35:00.776060059Z [resource.labels.instanceId: fleetdm-corp-sj0p] # continuing.
DEFAULT 2024-09-11T18:35:00.776096673Z [resource.labels.instanceId: fleetdm-corp-sj0p] #
DEFAULT 2024-09-11T18:35:00.776141915Z [resource.labels.instanceId: fleetdm-corp-sj0p] # Missing migrations: tables=[20240815000000 20240815000001 20240816103247 20240820091218 20240826111228 20240826160025 20240829165448 20240829165605 20240829165715 20240829165930 20240829170023 20240829170033], data=[].
DEFAULT 2024-09-11T18:35:00.776206291Z [resource.labels.instanceId: fleetdm-corp-sj0p] #
DEFAULT 2024-09-11T18:35:00.776259190Z [resource.labels.instanceId: fleetdm-corp-sj0p] # Press Enter to continue, or Control-c to exit.
DEFAULT 2024-09-11T18:35:00.776312639Z [resource.labels.instanceId: fleetdm-corp-sj0p] ################################################################################
DEFAULT 2024-09-11T18:35:00.777344238Z [resource.labels.instanceId: fleetdm-corp-sj0p] 2024/09/11 11:35:00 [2024-08-15] Add Jobs Index
DEFAULT 2024-09-11T18:35:00.830177310Z [resource.labels.instanceId: fleetdm-corp-sj0p] 2024/09/11 11:35:00 [2024-08-15] Add Self Service To VPP Apps Teams
DEFAULT 2024-09-11T18:35:00.870295532Z [resource.labels.instanceId: fleetdm-corp-sj0p] 2024/09/11 11:35:00 [2024-08-16] Add Index To Nano Users
DEFAULT 2024-09-11T18:35:00.926619706Z [resource.labels.instanceId: fleetdm-corp-sj0p] 2024/09/11 11:35:00 [2024-08-20] Add Host MDM Commands
DEFAULT 2024-09-11T18:35:00.959376490Z [resource.labels.instanceId: fleetdm-corp-sj0p] 2024/09/11 11:35:00 [2024-08-26] Adjust Host Indexes
DEFAULT 2024-09-11T18:35:07.337812685Z [resource.labels.instanceId: fleetdm-corp-sj0p] 2024/09/11 11:35:07 [2024-08-26] Add Removed To Installs
DEFAULT 2024-09-11T18:35:07.439586772Z [resource.labels.instanceId: fleetdm-corp-sj0p] 2024/09/11 11:35:07 FAIL 20240826160025_AddRemovedToInstalls.go (failed to find host_software_installs to remove: Error 1054 (42S22): Unknown column 'st.id' in 'on clause'), quitting migration.
DEFAULT 2024-09-11T18:35:07.570130386Z [resource.labels.instanceId: fleetdm-corp-sj0p] ################################################################################
k
I'm sorry you're running in to issues with migrations @Jason Roberts! Can you share some additional information? 1. What version of MySQL are you running? 2. Have you ever had issues with migrations before? 3. When running your migrations, is the Fleet server completely offline? 4. Are migrations running on multiple Fleet instances, or a single Fleet instance? 5. Have you attempted re-running the migrations?
I see the MySQL version listed, sorry I missed that hte first time!
j
2. Yes when multiple instances spun up at the same time. That is not the case anymore. 3. No old version instances are still running 4. No Single instance. 5. Yes it keeps restarting and failing
k
Can you try again with all Fleet instances offline? Migrations should be run while Fleet is offline to avoid data collisions.
j
no difference
k
Do you have access to query the database? I'd like to see the schema for the software_titles table: 
DESCRIBE software_titles;
j
image.png
id int(10) unsigned NO PRI auto_increment name varchar(255) NO MUL source varchar(64) NO browser varchar(255) NO bundle_identifier varchar(255) YES MUL additional_identifier tinyint(3) unsigned YES VIRTUAL GENERATED
image.png
i can create the title_id column
Copy code
ALTER TABLE software ADD COLUMN title_id INT(10) UNSIGNED DEFAULT NULL
seems like thats what makes it from a previous upgrade
oh wait thats software not sofware_titles
k
Yeah, it's 
software_titles.id
that's causing the error.
j
that column exists
k
Thanks for checking!
j
image.png
this is the query thats failing
Copy code
SELECT DISTINCT hsi.id
	FROM host_software_installs hsi
	INNER JOIN software_installers si ON hsi.software_installer_id = si.id
	INNER JOIN software_titles st ON si.title_id = st.id
	-- software is installed on some host
	INNER JOIN software s ON s.title_id = st.id
	INNER JOIN hosts h ON hsi.host_id = h.id
	WHERE NOT EXISTS (
		-- software is not installed on this specific host
		SELECT 1 FROM host_software hs
		INNER JOIN software s2 ON hs.software_id = s2.id AND s2.title_id = st.id
		WHERE hs.host_id = hsi.host_id
	) AND (
		-- software status is: Installed
		(hsi.post_install_script_exit_code IS NOT NULL AND hsi.post_install_script_exit_code = 0) OR
		(hsi.post_install_script_exit_code IS NULL AND hsi.install_script_exit_code IS NOT NULL AND hsi.install_script_exit_code = 0)
	) AND
		-- software was refetched after it was installed
		hsi.updated_at < h.detail_updated_at
k
Yep, I was just looking at that and I think I see the potential issue. I'm reaching out to the team to verify a potential workaround and raise the red flag. Bear with me just a bit.
Good deal. Apologies for missing this on the first go round, but this is a known issue with MySQL 8.0.18, the minimum required version of MySQL for fleet is 8.0.36 as of Fleet v4.55.0
j
uh
gcp does not support that
oh no wait
it does
k
Great! I thought I saw it when I was looking at their docs, but I wasn't positive.
j
image.png
barely supported though
they don't go higher than 8.0
k
I'll make sure I keep an eye on that. I know we're planning to add support for 8.4, but I'd be surprised if we moved the minimum required version up again any time soon, especially if it wouldn't be supported by the major cloud providers.
j
also feature request db version check for migrations
also version update worked
k
Excellent! That's a great FR. I'm happy to bring it to the team, but it's always nice to see stuff coming from the community if you'd like to submit it yourself: https://github.com/fleetdm/fleet/issues/new?assignees=&amp;labels=~feature+fest%2C%3Aproduct&amp;projects=&amp;template=feature-request.md&amp;title=
j
11 Views
Open in Slack
PreviousNext
Powered by