Hiya, I’m trying to upgrade from 4.29.1 to 4.33.1 ...
# fleet
r
Hiya, I’m trying to upgrade from 4.29.1 to 4.33.1 and I’m getting an error running the database migrations:
Copy code
2023/06/21 13:42:57 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.
This has left Fleet in an unusable state.
I was able to force downgrade
fleet
and
fleetctl
packages, and restart the service to get it back up, but not quite sure how to proceed with the upgrade, should I do them one version at a time or is this a bug with this specific DDL?
r
hey, thanks for raising this. I'll take a look and let you know what I find. could you share your MySQL version?
r
Hi @roberto it’s MySQL 8.0.18
Hi @roberto did you manage to find any clues?
r
Hey @Ryan sorry for not updating you on this. So far, I haven't been able to repro (tried multiple upgrade paths,) but I'm still looking at this
r
Ok cool, thanks. Can I provide any more information for you? Thanks.
r
@Ryan sorry for the delay, do you have access to your database instance to run queries? can you run the following?
Copy code
show table status where name in ('nano_commands', 'host_mdm_apple_bootstrap_packages');
r
Hi @roberto I can do indeed:
Copy code
mysql> show table status where name in ('nano_commands', 'host_mdm_apple_bootstrap_packages');
+---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| Name          | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options | Comment |
+---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
| nano_commands | InnoDB |      10 | Dynamic    |    0 |              0 |       16384 |               0 |            0 |         0 |           NULL | 2023-04-03 11:28:14 | NULL        | NULL       | utf8mb4_unicode_ci |     NULL |                |         |
+---------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+----------------+---------+
1 row in set (0.04 sec)
r
thanks Ryan! so far that looks good. I was interested in the
Collation
column and seems to be of the right type. Can I ask you to run:
Copy code
describe nano_commands;
r
Copy code
mysql> describe nano_commands;
+--------------+--------------+------+-----+-------------------+-----------------------------------------------+
| Field        | Type         | Null | Key | Default           | Extra                                         |
+--------------+--------------+------+-----+-------------------+-----------------------------------------------+
| command_uuid | varchar(127) | NO   | PRI | NULL              |                                               |
| request_type | varchar(63)  | NO   |     | NULL              |                                               |
| command      | mediumtext   | NO   |     | NULL              |                                               |
| created_at   | timestamp    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED                             |
| updated_at   | timestamp    | YES  |     | CURRENT_TIMESTAMP | DEFAULT_GENERATED on update CURRENT_TIMESTAMP |
+--------------+--------------+------+-----+-------------------+-----------------------------------------------+
5 rows in set (0.04 sec)
Hi @roberto do you need any more information, or maybe you have a suggestion?
I see there is a 4.34.0 released now, so I can try that?
Oh actually, I see it’s not a release yet, only a tag.
I see 4.34.0 is out now so I will try that in case it has fixed the issue.
No dice either 😄
@roberto Do you have any other ideas we can try before I resort to dropping the whole database and starting again? 😄 Thanks for your help so far!
r
@Ryan thanks so much for your patience, and sorry for the delayed reply. so far, column type looks good, table collation looks good. I wonder what about the column collation. If you're still able to run queries in the db instance, could you share the output of:
Copy code
SELECT COLLATION_NAME
FROM information_schema.columns
WHERE TABLE_SCHEMA = 'fleet'
AND TABLE_NAME = 'nano_commands'
AND COLUMN_NAME = 'command_uuid';
and maybe
SHOW CREATE TABLE nano_commands
🙏 . Sorry to annoy you with this, I haven't been able to reproduce myself
r
No worries 😄
Answers:
Copy code
mysql> SELECT COLLATION_NAME
    -> FROM information_schema.columns
    -> WHERE TABLE_SCHEMA = 'fleet'
    -> AND TABLE_NAME = 'nano_commands'
    -> AND COLUMN_NAME = 'command_uuid';
+--------------------+
| COLLATION_NAME     |
+--------------------+
| utf8mb4_unicode_ci |
+--------------------+
1 row in set (0.03 sec)
Copy code
mysql> SHOW CREATE TABLE nano_commands;
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table         | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| nano_commands | CREATE TABLE `nano_commands` (
  `command_uuid` varchar(127) COLLATE utf8mb4_unicode_ci NOT NULL,
  `request_type` varchar(63) COLLATE utf8mb4_unicode_ci NOT NULL,
  `command` mediumtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`command_uuid`),
  CONSTRAINT `nano_commands_chk_1` CHECK ((`command_uuid` <> _utf8mb4'')),
  CONSTRAINT `nano_commands_chk_2` CHECK ((`request_type` <> _utf8mb4'')),
  CONSTRAINT `nano_commands_chk_3` CHECK ((substr(`command`,1,5) = _utf8mb4'<?xml'))
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci |
+---------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.03 sec)
r
thank you! okay, that also looks good (sorry, there are so many rules on which character set is applied in MySQL that I had to be sure) could you check what's the schema character set?
Copy code
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME  FROM information_schema.SCHEMATA 
WHERE schema_name = "fleet";
r
@roberto :
Copy code
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)
r
@Ryan thanks, I think that's the problem! I thought at some point we included a migration to set the database collation (which is used for new tables)
we're releasing a point version today, let me see if I can squeeze a fix in
if not, it might have to wait until next Friday (we're also doing a release then)
r
ooh brilliant
yeah no need to rush to get it included today, I’m happy that we’ve identified the cause anyway
I’ll keep an eye on the changelog for it 🙂 thanks again for your help!
r
thanks to you for the patience and sorry for the troubles! sounds good, I'll also ping you here
r
no worries, have a good weekend 🙂 👋
r
you too!
r
Hiya @roberto do you know if this fix made it into a release yet, I see 4.34.1 in the changelog, but it doesn’t mention the schema migration fix.
Morning @roberto how’s it going? I tested again with v4.35.0 but no dice yet, I’m wondering if there’s a manual task I can run in MySQL in the meantime to correct this schema anomaly to unblock us so we can start doing upgrades again, what are your thoughts? Thanks 🙂
Same issue with v4.35.1 😄
Ok, so today I spent a bit of time digging, and checked the collation type for the
command_uuid
was correct across all columns and tables in the
fleet
schema - which it was - so I thought would it be enough just to set the default collation to
utf8mb4_unicode_ci
for all future tables, and that did indeed fix the problem, the schema migrations now ran successfully for v4.35.1 \o/
Copy code
mysql> ALTER DATABASE fleet CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 1 row affected (0.04 sec)