Ryan
06/21/2023, 1:43 PM2023/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.
Ryan
06/21/2023, 1:48 PMRyan
06/21/2023, 1:54 PMfleet
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?roberto
06/21/2023, 6:17 PMRyan
06/22/2023, 8:44 AMRyan
06/26/2023, 11:34 AMroberto
06/26/2023, 12:33 PMRyan
06/27/2023, 2:35 PMroberto
07/06/2023, 1:24 PMshow table status where name in ('nano_commands', 'host_mdm_apple_bootstrap_packages');
Ryan
07/06/2023, 2:14 PMmysql> 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)
roberto
07/06/2023, 2:21 PMCollation
column and seems to be of the right type. Can I ask you to run:
describe nano_commands;
Ryan
07/06/2023, 3:06 PMmysql> 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)
Ryan
07/12/2023, 2:47 PMRyan
07/12/2023, 2:48 PMRyan
07/12/2023, 2:52 PMRyan
07/13/2023, 9:43 AMRyan
07/13/2023, 10:09 AMRyan
07/13/2023, 10:10 AMroberto
07/13/2023, 2:39 PMSELECT COLLATION_NAME
FROM information_schema.columns
WHERE TABLE_SCHEMA = 'fleet'
AND TABLE_NAME = 'nano_commands'
AND COLUMN_NAME = 'command_uuid';
roberto
07/13/2023, 2:41 PMSHOW CREATE TABLE nano_commands
🙏 . Sorry to annoy you with this, I haven't been able to reproduce myselfRyan
07/13/2023, 2:44 PMRyan
07/13/2023, 2:44 PMmysql> 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)
Ryan
07/13/2023, 2:45 PMmysql> 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)
roberto
07/13/2023, 4:30 PMSELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA
WHERE schema_name = "fleet";
Ryan
07/14/2023, 10:36 AMmysql> 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)
roberto
07/14/2023, 1:49 PMroberto
07/14/2023, 1:50 PMroberto
07/14/2023, 1:52 PMRyan
07/14/2023, 2:00 PMRyan
07/14/2023, 2:00 PMRyan
07/14/2023, 2:00 PMroberto
07/14/2023, 2:15 PMRyan
07/14/2023, 4:04 PMroberto
07/14/2023, 6:52 PMRyan
08/01/2023, 1:52 PMRyan
08/10/2023, 9:19 AMRyan
08/10/2023, 9:53 AMRyan
08/11/2023, 9:59 AMcommand_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/
mysql> ALTER DATABASE fleet CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
Query OK, 1 row affected (0.04 sec)