https://github.com/osquery/osquery logo
Title
j

Jason Roberts

04/13/2023, 6:32 PM
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 18:25:19 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
j

John Speno

04/13/2023, 6:34 PM
this was recently asked and answered in a few places in this slack. E.g. https://osquery.slack.com/archives/C01DXJL16D8/p1681221652183249
j

Jason Roberts

04/13/2023, 6:36 PM
uh im not using maria db?
Running Database version is MySQL 8.0.18 in GCP CloudSql
This is also running an upgrade on a fleetdm deployment thats been working for the last 2 years.
j

John Speno

04/13/2023, 6:38 PM
There's a different thread you can find. search for
utf8mb4_unicode_ci
- good luck.
j

Jason Roberts

04/13/2023, 6:51 PM
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
z

zwass

04/13/2023, 7:19 PM
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.
j

Jason Roberts

04/13/2023, 7:35 PM
Yup that fixed it. Thank You!
z

zwass

04/13/2023, 7:36 PM
Glad to hear it!