Hi there, I’m working on a long overdue upgrade for a Fleet server, trying to go from 4.13.2 to current which is 4.37.0, ran into a weird error during a migration what I think was added in the 4.22.0 version? Here’s the error:

ERROR 1062 (23000): Duplicate entry '' for key 'hosts.hosts_search'

Wondering if anyone has seen it before specifically for the migration

20220915165116_HostDisplayName

? I saw a few mentions in this slack about editing rows in order make things work e.g. this thread, been testing with manually dedupping the table (on a snapshot of the real db) but still ran into the error after clearing all duplicates for (hostname, uuid, computer_name) which appears to be the index that the migration is trying to add. Thanks for any help or advice!

Hi @Andrew Zick! Is the Fleet server running during migrations?

Yes, and I saw that mentioned elsewhere as an issue, but I’m not sure how to “turn off” a fleet server that running in EKS while still being able to run the migrations? It’s also being managed by AWS CDK, just to make things more complicated. Watching the K8S pods via kubectl, we saw that a prepare pod would appear during the CDK deployment e.g.

fleet-prepare-db-v4.21.0-62jfq

. Do we maybe need to be pausing certain pods while that pod is executing?

Exactly. The pod running the migrations should be the only one online. Otherwise, duplicate data can be added as hosts keep checking in.

Ah okay. Hmmm. So maybe we try to manually disable the webserver pods e.g.

fleet-webserver-958cb485f-79c2q

, then deploy the upgrade? Wondering if the CDK deploy might restart the pods though, will keep thinking.

Not exactly sure how to accomplish this in K8s ecosystem but effectively: Scale down service running fleet web server to zero. Launch a task that only migrates the database and exits. Scale service that runs fleet webserver back up with new container image matching what was used to migrate

Okay, sounds like that’s the path. I think maybe we run the upgrade to 4.22.0, watch for the prepare pod to fail, then kill the webservers while the prepare pod automatically retries? I will add one thing though. From my original message, I took a snapshot of the db and tried to run the same SQL commands that the migration is running.

ALTER TABLE hosts DROP INDEX hosts_search;

causes a different error

2023/09/12 21:04:16 FAIL 20220915165116_HostDisplayName.go (upHostDisplayName: delete index: Error 1091: Can't DROP 'hosts_search'; check that column/key exists), quitting migration.

but we’ve successfully tested a workaround of manually adding the index for it to then delete. So we think it’s the next line

CREATE FULLTEXT INDEX hosts_search ON hosts(hostname, uuid, computer_name)

that’s causing the error in my question. Here’s the full error btw:

2023/09/12 21:30:03 FAIL 20220915165116_HostDisplayName.go (upHostDisplayName: create index: Error 1062: Duplicate entry '' for key 'hosts.hosts_search'), quitting migration.

And I still get this error when testing this SQL line on my snapshot. So I’m worried that turning off the webservers, while correct and something I’ll do + add to our documentation, won’t address this?