Hello, I just tried to upgrade to 4.30.1 and ran into the error # Missing migrations: tables=[20230315104937 20230317173844 20230320133602 20230330100011 20230330134823 20230405232025 20230408084104], data=[]. 2023/04/13 182519 FAIL 20230315104937_EnsureUniformCollation.go (fixing software table: aggregating dupes: Error 1253: COLLATION 'utf8mb4_unicode_ci' is not valid for CHARACTER SET 'utf8'), quitting migration. Running Database version is MySQL 8.0.18 in GCP CloudSql From version": "4.28.1

this was recently asked and answered in a few places in this slack. E.g. https://osquery.slack.com/archives/C01DXJL16D8/p1681221652183249

uh im not using maria db?

There's a different thread you can find. search for

utf8mb4_unicode_ci

- good luck.

So none of those threads really cover this issue. The database set im running

Collation utf8mb4_unicode_ci
Character set utf8mb4

Is the same called out in your gcp fleetdm terraform example https://github.com/fleetdm/fleet/blob/73827717d65e6f291161e2f53a1aa84f357dba9e/infrastructure/dogfood/terraform/gcp/mysql.tf#L40

I'm sorry you're experiencing this. We introduced a migration that tries to standardize the mysql character encodings (we noticed that many deployments had different settings), and that has been causing pain for some folks. Can you please try the following queries?

ALTER TABLE software CONVERT TO CHARACTER SET utf8mb4;
ALTER TABLE host_users CONVERT TO CHARACTER SET utf8mb4;
ALTER TABLE operating_systems CONVERT TO CHARACTER SET utf8mb4;

If those work, you should hopefully be able to re-run the migration successfully. If not, you may need to follow the manual duplicate removal as described in https://github.com/fleetdm/fleet/issues/10911#issuecomment-1496293883.

Yup that fixed it. Thank You!

Glad to hear it!