Fleet fail updating to 4.48

[kuzakov]

Fleet version: 4.47.3

Web browser and operating system: Chrome 123.0.6312.107 / MacOS 14.4.1

---

💥  Actual behavior

Get an error when update Fleet from 4.47.3 to 4.48.0/4.48.1(I tested both):

2024/04/09 12:42:07 [2024-03-27] Add DDM Tables
2024/04/09 12:42:07 [2024-03-27] Create Table Nano DDM Requests
2024/04/09 12:42:07 FAIL 20240327115617_CreateTableNanoDDMRequests.go (creating mdm_apple_declarative_requsts: Error 3780 (HY000): Referencing column 'enrollment_id' and referenced column 'id' in foreign key constraint 'mdm_apple_declarative_requests_enrollment_id' are incompatible.), quitting migration.

🧑‍💻  Steps to reproduce

1. Update to Fleet 4.47.3 (fleet prepare db)
2. Update to Fleet 4.48.0 or 4.48.1 (fleet prepare db)

🕯️ More info (optional)

Database: mysql-wsrep: 8.0.35-26.16 Galera Cluster (3 nodes) Runtime OS: Ubuntu 22.04.4 LTS

[mna]

Hey @kuzakov ,

Thanks for reporting this, we believe this is because your default database collation is not utf8mb4_unicode_ci. Would you be comfortable running the following statements in your Fleet database?

First, you can check the default character set and collation, we believe those will NOT be utf8mb4 and utf8mb4_unicode_ci for you:

mysql> SELECT * FROM information_schema.SCHEMATA  WHERE schema_name = "fleet";
+--------------+-------------+----------------------------+------------------------+----------+
| CATALOG_NAME | SCHEMA_NAME | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH |
+--------------+-------------+----------------------------+------------------------+----------+
| def          | fleet       | utf8mb4                    | utf8mb4_unicode_ci     | NULL     |
+--------------+-------------+----------------------------+------------------------+----------+
1 row in set (0.00 sec)

If that's the case, and if you're ok running this, you can change the defaults using this statement:

ALTER DATABASE fleet CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

After that, the migration should succeed. We'll patch the migration, but this should get you unblocked right away (please let us know if that's not the case).

[lukeheath]

@kuzakov Thank you for reporting this. As mentioned by @mna above, we have figured out the root cause of the issue. Because this blocks upgrades for some users, we are escalating this to a P1 critical bug and will issue a patch release with a fix later today.

[kuzakov]

Thank you guys for your extremely quick answer and reaction. All are true, we have had a following collation:

+--------------+------------------+----------------------------+------------------------+----------+--------------------+
| CATALOG_NAME | SCHEMA_NAME      | DEFAULT_CHARACTER_SET_NAME | DEFAULT_COLLATION_NAME | SQL_PATH | DEFAULT_ENCRYPTION |
+--------------+------------------+----------------------------+------------------------+----------+--------------------+
| def          | fleetdm_09042024 | utf8mb4                    | utf8mb4_0900_ai_ci     |     NULL | NO                 |
+--------------+------------------+----------------------------+------------------------+----------+--------------------+

And after that when I switched to utf8mb4_unicode_ci - updating to 4.48.1 was performed successfully. Thanks a lot!

[fleet-release]

Update falters, still, Smooth migration will bring peace, In the cloud's glass city.

[y34r-z3r0]

ALTER DATABASE fleet CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

It works. Thanks a lot. In my case there was the following difference:

MySQL 8.0.33

DEFAULT_COLLATION_NAME = utf8mb4_unicode_ci

MySQL 8.4.0

DEFAULT_COLLATION_NAME = utf8mb4_0900_ai_ci