Unable to migrate from 7.8.2->7.9.X

[profijoeln]

Hello,

I am unable to migrate from 7.8.2 to 7.9.X version because of missing columns. Some of these errors only appear once actions like new user creation are triggered.

Example:

Unhandled rejection SequelizeDatabaseError: Unknown column 'disable_2fa_key' in 'field list'

Is there a migration process within the component itself, or a guide with steps to migrating already existing MySQL IDM database to version 7.9+?

[jason-fox]

I have come across the same issue whilst updating to 8.0.0. I have a workaround as follows:

1. Download the following tutorial for a mininal Keyrock set-up: https://github.com/fiware/tutorials.Identity-Management
2. Amend the .env to show your version of Keyrock .e.g 7.8.2
3. Run ./services start to start Keyrock and the tutorial will seed the 7.8.2 database format..
4. Create a backup running docker exec db-mysql /usr/bin/mysqldump -u root --password=secret idm > mine.sql
5. Revert the .env to show their version of Keyrock .e.g 8.0.0
6. Rerun ./services start to start Keyrock and the tutorial will seed the updated database format..
7. Create a backup running docker exec db-mysql /usr/bin/mysqldump -u root --password=secret idm > theirs.sql

Use a diff tool to analyse the difference between mine.sql and theirs.sql.

@@ -35,7 +35,7 @@ CREATE TABLE `SequelizeMeta` (
 
 LOCK TABLES `SequelizeMeta` WRITE;
 /*!40000 ALTER TABLE `SequelizeMeta` DISABLE KEYS */;
-INSERT INTO `SequelizeMeta` VALUES ('201802190000-CreateUserTable.js'),('201802190003-CreateUserRegistrationProfileTable.js'),('201802190005-CreateOrganizationTable.js'),('201802190008-CreateOAuthClientTable.js'),('201802190009-CreateUserAuthorizedApplicationTable.js'),('201802190010-CreateRoleTable.js'),('201802190015-CreatePermissionTable.js'),('201802190020-CreateRoleAssignmentTable.js'),('201802190025-CreateRolePermissionTable.js'),('201802190030-CreateUserOrganizationTable.js'),('201802190035-CreateIotTable.js'),('201802190040-CreatePepProxyTable.js'),('201802190045-CreateAuthZForceTable.js'),('201802190050-CreateAuthTokenTable.js'),('201802190060-CreateOAuthAuthorizationCodeTable.js'),('201802190065-CreateOAuthAccessTokenTable.js'),('201802190070-CreateOAuthRefreshTokenTable.js'),('201802190075-CreateOAuthScopeTable.js'),('20180405125424-CreateUserTourAttribute.js'),('20180612134640-CreateEidasTable.js'),('20180727101745-CreateUserEidasIdAttribute.js'),('20180730094347-CreateTrustedApplicationsTable.js'),('20180828133454-CreatePasswordSalt.js'),('20180921104653-CreateEidasNifColumn.js'),('20180922140934-CreateOauthTokenType.js'),('20181022103002-CreateEidasTypeAndAttributes.js'),('20181108144720-RevokeToken.js'),('20181113121450-FixExtraAndScopeAttribute.js'),('20181203120316-FixTokenTypesLength.js'),('20190116101526-CreateSignOutUrl.js'),('20190316203230-CreatePermissionIsRegex.js'),('20190429164755-CreateUsagePolicyTable.js'),('20190507112246-CreateRoleUsagePolicyTable.js'),('20190507112259-CreatePtpTable.js'),('20191019153205-UpdateUserAuthorizedApplicationTable.js'),('20200107102154-CreatePermissionFiwareService.js'),('20200107102154-CreatePermissionUseFiwareService.js'),('20200928134556-AddDisable2faKey.js'),('20210422214057-init-visible_attributes.js'),('20210423161823-AddOidcNonce.js.js'),('20210603073911-hashed-access-tokens.js'),('20210607162019-CreateDelegationEvidenceTable.js');
+INSERT INTO `SequelizeMeta` VALUES ('201802190000-CreateUserTable.js'),('201802190003-CreateUserRegistrationProfileTable.js'),('201802190005-CreateOrganizationTable.js'),('201802190008-CreateOAuthClientTable.js'),('201802190009-CreateUserAuthorizedApplicationTable.js'),('201802190010-CreateRoleTable.js'),('201802190015-CreatePermissionTable.js'),('201802190020-CreateRoleAssignmentTable.js'),('201802190025-CreateRolePermissionTable.js'),('201802190030-CreateUserOrganizationTable.js'),('201802190035-CreateIotTable.js'),('201802190040-CreatePepProxyTable.js'),('201802190045-CreateAuthZForceTable.js'),('201802190050-CreateAuthTokenTable.js'),('201802190060-CreateOAuthAuthorizationCodeTable.js'),('201802190065-CreateOAuthAccessTokenTable.js'),('201802190070-CreateOAuthRefreshTokenTable.js'),('201802190075-CreateOAuthScopeTable.js'),('20180405125424-CreateUserTourAttribute.js'),('20180612134640-CreateEidasTable.js'),('20180727101745-CreateUserEidasIdAttribute.js'),('20180730094347-CreateTrustedApplicationsTable.js'),('20180828133454-CreatePasswordSalt.js'),('20180921104653-CreateEidasNifColumn.js'),('20180922140934-CreateOauthTokenType.js'),('20181022103002-CreateEidasTypeAndAttributes.js'),('20181108144720-RevokeToken.js'),('20181113121450-FixExtraAndScopeAttribute.js'),('20181203120316-FixTokenTypesLength.js'),('20190116101526-CreateSignOutUrl.js'),('20190316203230-CreatePermissionIsRegex.js'),('20190429164755-CreateUsagePolicyTable.js'),('20190507112246-CreateRoleUsagePolicyTable.js'),('20190507112259-CreatePtpTable.js');
 /*!40000 ALTER TABLE `SequelizeMeta` ENABLE KEYS */;
 UNLOCK TABLES;
 
@@ -97,31 +97,6 @@ LOCK TABLES `authzforce` WRITE;
 /*!40000 ALTER TABLE `authzforce` ENABLE KEYS */;
 UNLOCK TABLES;
 
---
--- Table structure for table `delegation_evidence`
---
-
-DROP TABLE IF EXISTS `delegation_evidence`;
-/*!40101 SET @saved_cs_client     = @@character_set_client */;
-/*!40101 SET character_set_client = utf8 */;
-CREATE TABLE `delegation_evidence` (
-  `policy_issuer` varchar(255) NOT NULL,
-  `access_subject` varchar(255) NOT NULL,
-  `policy` json NOT NULL,
-  PRIMARY KEY (`policy_issuer`,`access_subject`),
-  UNIQUE KEY `policy_issuer_access_subject_unique` (`policy_issuer`,`access_subject`)
-) ENGINE=InnoDB DEFAULT CHARSET=latin1;
-/*!40101 SET character_set_client = @saved_cs_client */;
-
---
--- Dumping data for table `delegation_evidence`
---
-
-LOCK TABLES `delegation_evidence` WRITE;
-/*!40000 ALTER TABLE `delegation_evidence` DISABLE KEYS */;
-/*!40000 ALTER TABLE `delegation_evidence` ENABLE KEYS */;
-UNLOCK TABLES;
-
 --
 -- Table structure for table `eidas_credentials`
 --
@@ -198,7 +173,7 @@ DROP TABLE IF EXISTS `oauth_access_token`;
 /*!40101 SET @saved_cs_client     = @@character_set_client */;
 /*!40101 SET character_set_client = utf8 */;
 CREATE TABLE `oauth_access_token` (
-  `access_token` text NOT NULL,
+  `access_token` varchar(255) NOT NULL,
   `expires` datetime DEFAULT NULL,
   `scope` varchar(2000) DEFAULT NULL,
   `refresh_token` varchar(255) DEFAULT NULL,
@@ -208,9 +183,8 @@ CREATE TABLE `oauth_access_token` (
   `user_id` varchar(36) DEFAULT NULL,
   `iot_id` varchar(255) DEFAULT NULL,
   `authorization_code` varchar(255) DEFAULT NULL,
-  `hash` char(64) NOT NULL,
-  PRIMARY KEY (`hash`),
-  UNIQUE KEY `oauth_access_token_hash_uk` (`hash`),
+  PRIMARY KEY (`access_token`),
+  UNIQUE KEY `access_token` (`access_token`),
   KEY `oauth_client_id` (`oauth_client_id`),
   KEY `user_id` (`user_id`),
   KEY `iot_id` (`iot_id`),
@@ -249,7 +223,6 @@ CREATE TABLE `oauth_authorization_code` (
   `extra` json DEFAULT NULL,
   `oauth_client_id` varchar(36) DEFAULT NULL,
   `user_id` varchar(36) DEFAULT NULL,
-  `nonce` varchar(255) DEFAULT NULL,
   PRIMARY KEY (`authorization_code`),
   UNIQUE KEY `authorization_code` (`authorization_code`),
   KEY `oauth_client_id` (`oauth_client_id`),
@@ -440,8 +413,6 @@ CREATE TABLE `permission` (
   `xml` text,
   `oauth_client_id` varchar(36) DEFAULT NULL,
   `is_regex` tinyint(1) NOT NULL DEFAULT '0',
-  `authorization_service_header` varchar(255) DEFAULT NULL,
-  `use_authorization_service_header` tinyint(1) NOT NULL DEFAULT '0',
   PRIMARY KEY (`id`),
   UNIQUE KEY `id` (`id`),
   KEY `oauth_client_id` (`oauth_client_id`),
@@ -455,7 +426,7 @@ CREATE TABLE `permission` (
 
 LOCK TABLES `permission` WRITE;
 /*!40000 ALTER TABLE `permission` DISABLE KEYS */;
-INSERT INTO `permission` VALUES ('1','Get and assign all internal application roles',NULL,1,NULL,NULL,NULL,'idm_admin_app',0,NULL,0),('2','Manage the application',NULL,1,NULL,NULL,NULL,'idm_admin_app',0,NULL,0),('3','Manage roles',NULL,1,NULL,NULL,NULL,'idm_admin_app',0,NULL,0),('4','Manage authorizations',NULL,1,NULL,NULL,NULL,'idm_admin_app',0,NULL,0),('5','Get and assign all public application roles',NULL,1,NULL,NULL,NULL,'idm_admin_app',0,NULL,0),('6','Get and assign only public owned roles',NULL,1,NULL,NULL,NULL,'idm_admin_app',0,NULL,0);
+INSERT INTO `permission` VALUES ('1','Get and assign all internal application roles',NULL,1,NULL,NULL,NULL,'idm_admin_app',0),('2','Manage the application',NULL,1,NULL,NULL,NULL,'idm_admin_app',0),('3','Manage roles',NULL,1,NULL,NULL,NULL,'idm_admin_app',0),('4','Manage authorizations',NULL,1,NULL,NULL,NULL,'idm_admin_app',0),('5','Get and assign all public application roles',NULL,1,NULL,NULL,NULL,'idm_admin_app',0),('6','Get and assign only public owned roles',NULL,1,NULL,NULL,NULL,'idm_admin_app',0);
 /*!40000 ALTER TABLE `permission` ENABLE KEYS */;
 UNLOCK TABLES;
 
@@ -703,7 +674,7 @@ CREATE TABLE `user` (
 
 LOCK TABLES `user` WRITE;
 /*!40000 ALTER TABLE `user` DISABLE KEYS */;
-INSERT INTO `user` VALUES ('admin','admin',NULL,NULL,'default',0,'[email protected]','7df8504bbd55c718b7c5d5ccbd4f5559c8813f2d','2021-06-11 08:30:34',1,1,'{\"visible_attributes\": [\"username\", \"description\"]}',NULL,0,NULL,'5b560ea85c3b1013');
+INSERT INTO `user` VALUES ('admin','admin',NULL,NULL,'default',0,'[email protected]','2dc958a12768918587cb0d4dd40c34c6eca9e314','2021-06-11 12:39:10',1,1,NULL,NULL,0,NULL,'6a8fd1ece6e0cd10');
 /*!40000 ALTER TABLE `user` ENABLE KEYS */;
 UNLOCK TABLES;
 
@@ -718,8 +689,6 @@ CREATE TABLE `user_authorized_application` (
   `id` int(11) NOT NULL AUTO_INCREMENT,
   `user_id` varchar(36) DEFAULT NULL,
   `oauth_client_id` varchar(36) DEFAULT NULL,
-  `shared_attributes` varchar(255) DEFAULT NULL,
-  `login_date` datetime DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `user_id` (`user_id`),
   KEY `oauth_client_id` (`oauth_client_id`),
@@ -782,8 +751,6 @@ CREATE TABLE `user_registration_profile` (
   `verification_key` varchar(255) DEFAULT NULL,
   `verification_expires` datetime DEFAULT NULL,
   `user_email` varchar(255) DEFAULT NULL,
-  `disable_2fa_key` varchar(255) DEFAULT NULL,
-  `disable_2fa_expires` datetime DEFAULT NULL,
   PRIMARY KEY (`id`),
   KEY `user_email` (`user_email`),
   CONSTRAINT `user_registration_profile_ibfk_1` FOREIGN KEY (`user_email`) REFERENCES `user` (`email`) ON DELETE CASCADE ON UPDATE CASCADE
@@ -808,4 +775,4 @@ UNLOCK TABLES;
 /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
 /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
 
--- Dump completed on 2021-06-11  8:34:31
+-- Dump completed on 2021-06-11 12:42:16

You can see disable_2fa_key and disable_2fa_expires marked as missing from the diff and since they have a default value it is easy to add the missing columns using SQL.

[jason-fox]

Migrating from 7.9 to 8.0 has an additional problem in that hash of the token is now mandatory

 LOCK TABLES `oauth_access_token` WRITE;
 /*!40000 ALTER TABLE `oauth_access_token` DISABLE KEYS */;
 INSERT INTO `oauth_access_token` VALUES 
-('15682667caa4bb5ac15056fee3836b2980288bf2','2016-07-30 12:14:21',NULL,NULL,NULL,NULL,'8ca60ce9-32f9-42d6-a013-a19b3af0c13d','admin',NULL,NULL),
-('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','2016-07-30 12:14:21',NULL,NULL,NULL,NULL,'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa','alice',NULL,NULL),
-('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb','2016-07-30 12:14:21',NULL,NULL,NULL,NULL,'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb','bob',NULL,NULL),
-('cccccccccccccccccccccccccccccccccccccccc','2016-07-30 12:14:21',NULL,NULL,NULL,NULL,'cccccccc-cccc-cccc-cccc-cccccccccccc','charlie',NULL,NULL),
-('d1d1d1d1d1d1d1d1d1d1d1d1d1d1d1d1d1d1d1d1','2016-07-30 12:14:21',NULL,NULL,NULL,NULL,'d1d1d1d1-dddd-dddd-dddd-d1d1d1d1d1d1','detective1',NULL,NULL),
-('d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2','2016-07-30 12:14:21',NULL,NULL,NULL,NULL,'d2d2d2d2-dddd-dddd-dddd-d2d2d2d2d2d2','detective2',NULL,NULL),
-('m1m1m1m1m1m1m1m1m1m1m1m1m1m1m1m1m1m1m1m1','2016-07-30 12:14:21',NULL,NULL,NULL,NULL,'m1m1m1m1-mmmm-mmmm-mmmm-m1m1m1m1m1m1','manager1',NULL,NULL),
-('m2m2m2m2m2m2m2m2m2m2m2m2m2m2m2m2m2m2m2m2','2016-07-30 12:14:21',NULL,NULL,NULL,NULL,'m2m2m2m2-mmmm-mmmm-mmmm-m2m2m2m2m2m2','manager2',NULL,NULL);
+('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa','2016-07-30 12:14:21',NULL,NULL,NULL,NULL,'aaaaaaaa-aaaa-aaaa-aaaa-aaaaaaaaaaaa','alice',NULL,NULL, '12661599e24923dc17384a28644fbd2c0e30fa1cc7295772470d22729b054c8b'),
+('bbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbbb','2016-07-30 12:14:21',NULL,NULL,NULL,NULL,'bbbbbbbb-bbbb-bbbb-bbbb-bbbbbbbbbbbb','bob',NULL,NULL, '8d94b35f8eea7e1577e30fc75646dfeb4dd0982a083635028998d53ef590c7ec'),
+('cccccccccccccccccccccccccccccccccccccccc','2016-07-30 12:14:21',NULL,NULL,NULL,NULL,'cccccccc-cccc-cccc-cccc-cccccccccccc','charlie',NULL,NULL, 'f57858edab011913ac0a5d92f04987f4b34eab0d702c8198c1900871d7d87198'),
+('d1d1d1d1d1d1d1d1d1d1d1d1d1d1d1d1d1d1d1d1','2016-07-30 12:14:21',NULL,NULL,NULL,NULL,'d1d1d1d1-dddd-dddd-dddd-d1d1d1d1d1d1','detective1',NULL,NULL, '18a4605f12def28bbbbab7bbef23fe6e204d73432d9aee8514fc168037945221'),
+('d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2d2','2016-07-30 12:14:21',NULL,NULL,NULL,NULL,'d2d2d2d2-dddd-dddd-dddd-d2d2d2d2d2d2','detective2',NULL,NULL, '1df5d6346470cc81d7a533f67a8399c052b5fc608b94972557138e10a335c5e1'),
+('m1m1m1m1m1m1m1m1m1m1m1m1m1m1m1m1m1m1m1m1','2016-07-30 12:14:21',NULL,NULL,NULL,NULL,'m1m1m1m1-mmmm-mmmm-mmmm-m1m1m1m1m1m1','manager1',NULL,NULL, '853d6a374a92501e3e93d28184f9217941793ff646b636c04b35d20169c0d3b7'),
+('m2m2m2m2m2m2m2m2m2m2m2m2m2m2m2m2m2m2m2m2','2016-07-30 12:14:21',NULL,NULL,NULL,NULL,'m2m2m2m2-mmmm-mmmm-mmmm-m2m2m2m2m2m2','manager2',NULL,NULL, '5603ade3a9d2303dbf3f28a35023a53c28297dc7db955784ac09b4c294ecae8b');

I needed to generate each hash using:

const crypto = require('crypto');
const hash = crypto.createHash("sha3-256").update(token_id).digest('hex');