If you upgrade the Axon Ivy Engine from one minor release to the next or even to the next major release, it is very likely that the system database will have to be migrated. I have just done this when upgrading from 11.1 to 11.2 (nightly). Unfortunately, the migration failed. No problem, I made a backup and can now simply restore it.
The error is as follows:
ErrorError 'Referencing column 'ReceiverId' and referenced column 'SecurityMemberId' in foreign key constraint 'IWA_Notification_ibfk_1' are incompatible.' while executing statement 'CREATE TABLE IWA_Notification ( NotificationId VARCHAR(36) NOT NULL, Kind VARCHAR(100) NOT NULL, Payload TEXT NOT NULL, ReceiverId VARCHAR(210) NOT NULL, CreatedAt DATETIME(3) NOT NULL, PRIMARY KEY (NotificationId), FOREIGN KEY (ReceiverId) REFERENCES IWA_SecurityMember(SecurityMemberId) ON DELETE CASCADE, INDEX IWA_Notification_KindIndex (Kind), INDEX IWA_Notification_ReceiverIdIndex (ReceiverId), INDEX IWA_Notification_CreatedAtIndex (CreatedAt) ) ENGINE=InnoDB'
It cannot create a foreign key because the data types are incompatible. The problem here is the CHARSET and COLLATION. That doesn't just pop up. Let me see what the table to be referenced looks like:
show create table IWA_SecurityMember;
CREATE TABLE `IWA_SecurityMember` (
`SecurityMemberId` varchar(210) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
`Name` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
`MemberName` varchar(201) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci NOT NULL,
`DisplayName` varchar(200) CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci DEFAULT NULL,
`Enabled` bit(1) NOT NULL DEFAULT b'0',
`Type` int NOT NULL,
`SecuritySystemId` bigint NOT NULL,
PRIMARY KEY (`SecurityMemberId`),
KEY `IWA_SecurityMemberNameIdx` (`Name`),
KEY `IWA_SecurityMemberMemberNameIdx` (`MemberName`),
KEY `IWA_SecurityMemberDisplayNameIdx` (`DisplayName`),
KEY `IWA_SecurityMemberEnabledIdx` (`Enabled`),
KEY `IWA_SecurityMemberTypeIdx` (`Type`),
KEY `IWA_SecurityMember_SecuritySystemIdIndex` (`SecuritySystemId`),
CONSTRAINT `IWA_SecurityMember_ibfk_1` FOREIGN KEY (`SecuritySystemId`) REFERENCES `IWA_SecuritySystem` (`SecuritySystemId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_unicode_ci
I see that SecurityMemberId has the following definition:
CHARACTER SET utf8mb3 COLLATE utf8mb3_unicode_ci
When creating tables, we do not always define the CHARSET and COLLATION but use the DEFAULT of the database. This seems to have changed in the meantime, as we regularly update the MySQL database server to the latest version. Let's have a look at the database defaults:
mysql> SHOW VARIABLES LIKE 'character_set_database';
+------------------------+---------+
| Variable_name | Value |
+------------------------+---------+
| character_set_database | utf8mb4 |
+------------------------+---------+
1 row in set (0.00 sec)
mysql> SHOW VARIABLES LIKE 'collation_database';
+--------------------+--------------------+
| Variable_name | Value |
+--------------------+--------------------+
| collation_database | utf8mb4_0900_ai_ci |
+--------------------+--------------------+
1 row in set (0.01 sec)
uiiii. They are actually no longer the same. So I now set the default for this database fixed, as it was before:
ALTER DATABASE AxonIvySystemDatabase
CHARACTER SET utf8mb3
COLLATE utf8mb3_unicode_ci;
Then I can perform the migration successfully. 👍