Error upgrading from v4.30.1 in `prepare db` stage

[mdtro]

Fleet version: v4.30.1 Operating system: GCP Cloud Run

---

🧑‍💻  Expected behavior

The upgrade process successfully runs the database migrations and my instance is running v4.32.0.

💥  Actual behavior

I'm receiving an error when the prepare db occurs.

2023/06/09 18:33:15 FAIL 20230411102858_CreateHostBootstrapPackagesTable.go (create host_mdm_apple_bootstrap_packages: Error 3780: Referencing column 'command_uuid' and referenced column 'command_uuid' in foreign key constraint 'host_mdm_apple_bootstrap_packages_ibfk_1' are incompatible.), quitting migration.

👣 Reproduction steps

I'm using a slightly modified version of your Terraform GCP dogfood. My modifications are primarily around some extra logging requirements and special DNS requirements.

I'm afraid that the upgrade path I took might have broken things. 🙂 I started at v4.29.0 and tried to go to v4.32.0 directly. I should have known better and should have read the release notes about the migration bug in v4.29.0 first.

1. Start with a v4.29.0 install on MySQL 5.7.
2. Update to MySQL 8.0 and adjust collation "utf8mb4_unicode_ci" -> "utf8mb4_general_ci".
3. Bump to v4.32.0. This will fail with a migration error:

   2023/06/09 18:02:10 FAIL 20230411102858_CreateHostBootstrapPackagesTable.go (create host_mdm_apple_bootstrap_packages: Error 3780: Referencing column 'command_uuid' and referenced column 'command_uuid' in foreign key constraint 'host_mdm_apple_bootstrap_packages_ibfk_1' are incompatible.), quitting migration.

4. Upgrade from v4.29.0 to v4.29.1 (database is still at MySQL 8.0 at this point).
5. Upgrade from v4.29.1 to v4.30.1.
6. Attempt upgrades to any later versions. I receive the same error when attempting v4.30.1 > v4.31.0, v4.30.1 > v4.31.1, and v4.30.1 > v4.32.0.

More info

I also have this in my output when attempting to go from v4.30.1 to v4.32.0.

#   Missing migrations: tables=[20230411102858 20230421155932 20230425082126 20230425105727 20230501154913 20230503101418 20230515144206 20230517140952 20230517152807 20230518114155 20230520153236], data=[].

[xpkoala]

@mdtro I'm sorry for the delay in getting you a response. I have passed on this ticket to the engineering team and I should have some more detailed instructions soon on getting those migration issues sorted.

[roperzh]

hey @mdtro , sorry for the delay getting back to you, I have been trying to reproduce for a while but I couldn't.

are you able to run queries in your database? can you share with us the output of:

show table status where name in ('nano_commands', 'host_mdm_apple_bootstrap_packages');

[roperzh]

Ryan in the community slack was having the same problem: https://osquery.slack.com/archives/C01DXJL16D8/p1687355031304579

we pinpointed the issue to be the default collation set at the schema level, which is used for new tables:

mysql> SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME  FROM information_schema.SCHEMATA  WHERE schema_name = "fleet";
+----------------------------+------------------------+
| DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME |
+----------------------------+------------------------+
| utf8mb4                    | utf8mb4_0900_ai_ci     |
+----------------------------+------------------------+
1 row in set (0.03 sec)

[roperzh]

we did set the default character set for the schema in a prior migration:

https://github.com/fleetdm/fleet/blob/9c2cc92d9b407c90e7c480a2bf0b19711759ec34/server/datastore/mysql/migrations/tables/20230315104937_EnsureUniformCollation.go#L193

[xpkoala]

@mdtro We are going to close this ticket to keep our bug views tidy, but please feel free to re-open this ticket if you are experiencing any more issues with the original problem.

[fleet-release]

Upgrade path in the cloud, Smooth as the river's flow, Fleet shines, no error shroud.

[roperzh]

@xpkoala IMO, I think it would be good to add a fix for this, we probably need to tweak the current migration to specify the character set. I volunteer to tackle it

[xpkoala]

@roperzh I must have misunderstood that this was still an issue and not addressed in an earlier fix. Apologies for closing the ticket!

[mdtro]

@xpkoala - no worries! I misunderstood as well. I'll run the query and get the results back to you if that'll help. 🙂

[xpkoala]

@mdtro Thank you for the offer! If you are able to provide the output that might give us a bit more insight into the issue.

@roperzh when you have a chance could you add any additional information on what a fix for this would entail. We can then send it to off for prioritization, thanks!

[oioki]

hi everyone, I'm @mdtro's colleague, and we are looking into this issue together. Here are the results of the above mentioned queries:

Server version: 8.0.26-google (Google)
...
MySQL [fleet]> show table status where name in ('nano_commands', 'host_mdm_apple_bootstrap_packages')\G
*************************** 1. row ***************************
           Name: nano_commands
         Engine: InnoDB
        Version: 10
     Row_format: Dynamic
           Rows: 0
 Avg_row_length: 0
    Data_length: 16384
Max_data_length: 0
   Index_length: 0
      Data_free: 0
 Auto_increment: NULL
    Create_time: 2023-06-09 17:36:34
    Update_time: NULL
     Check_time: NULL
      Collation: utf8mb4_unicode_ci
       Checksum: NULL
 Create_options:
        Comment:
1 row in set (0.004 sec)

MySQL [fleet]> SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME  FROM information_schema.SCHEMATA  WHERE schema_name = "fleet"\G
*************************** 1. row ***************************
DEFAULT_CHARACTER_SET_NAME: utf8mb4
    DEFAULT_COLLATION_NAME: utf8mb4_general_ci
1 row in set (0.001 sec)

[xpkoala]

@oioki Thank you for the results from that query! @roperzh is out for the next few business days but I'll make sure he sees this upon his return.

[oioki]

@roperzh @xpkoala Hi folks, any updates on this issue? I see it was mentioned in another PR, but seems like it does not really addresses this.

[ireedy]

Bug has aged out. Moved back to drafting

[lukeheath]

@roperzh @sabrinabuckets Would y'all please follow up on the status of this issue this week?

1. Try to reproduce the issue reported.
2. If we can't reproduce, it sounds like @roperzh may have an idea:

we probably need to tweak the current migration to specify the character set.

Let's try that. If we can't reproduce (after trying and documenting our steps), please ask the bug reporter to test. If that doesn't work, and we don't have other ideas, and we can't reproduce after trying all options, we'll need to close this issue.

[roperzh]

👋 hey folks (@oioki @mdtro), for some reason your databases don't have the right character set. Would you be comfortable running the following statement in your Fleet database?

ALTER DATABASE fleet CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

afterwards the migrations should succeed. We'll patch the migration, but this should get you unblocked right away.

[oioki]

Hey @roperzh We ran this query, migrations went through and we were able to upgrade. Thank you!

[fleet-release]

Upgrade path errors solved, Fleet flows like river's current, In cloud city's pulse.