Migrating Database from 3.4.3 to new Version

Configuring the server
1

Hello,
i have problems migrating my old database (3.4.3) to the current version. Im starting the keycloak container with the old database and then the migrating scripts fails with:

17:12:35,203 ERROR [org.keycloak.connections.jpa.updater.liquibase.conn.DefaultLiquibaseConnectionProvider] (ServerService Thread Pool -- 68) 
Change Set META-INF/jpa-changelog-4.0.0.xml::4.0.0-KEYCLOAK-5579-fixed::mposolda@redhat.com failed.
Error: (conn=176273) Can't create table `keycloak`.`CLIENT_SCOPE_CLIENT` (errno: 150 "Foreign key constraint is incorrectly formed") 
[Failed SQL: ALTER TABLE keycloak.CLIENT_SCOPE_CLIENT ADD CONSTRAINT FK_C_CLI_SCOPE_CLIENT FOREIGN KEY (CLIENT_ID) REFERENCES keycloak.CLIENT (ID)]

I can’t find the problem because the tables look good for me.

Table CLIENT_SCOPE_CLIENT:
CLIENT_ID | varchar(36) | NO | PRI | NULL | |

Table CLIENT:
ID | varchar(36) | NO | PRI | NULL | |

show create tables:

CREATE TABLE `CLIENT` (
  `ID` varchar(36) COLLATE utf8_unicode_ci NOT NULL,
 <cutted>
  `USE_TEMPLATE_MAPPERS` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`ID`),
  UNIQUE KEY `UK_<cutted>` (`REALM_ID`,`CLIENT_ID`),
  CONSTRAINT `FK_<cutted>` FOREIGN KEY (`REALM_ID`) REFERENCES `REALM` (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci 

CREATE TABLE `CLIENT_SCOPE_CLIENT` (
  `CLIENT_ID` varchar(36) NOT NULL,
  `SCOPE_ID` varchar(36) NOT NULL,
  `DEFAULT_SCOPE` bit(1) NOT NULL DEFAULT b'0',
  PRIMARY KEY (`CLIENT_ID`,`SCOPE_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Im using following mariadb:
mariadb Ver 15.1 Distrib 10.4.11-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Im starting from keycloak db version 3.4.3 and a few changesets succeed. The last one is:

| 4.0.0-KEYCLOAK-6228                                               | bburke@redhat.com   | META-INF/jpa-changelog-4.0.0.xml           | 2020-11-30 17:12:34 |            60 | EXECUTED | 7:c6538c29b9c9a08f9e9ea2de5c2b6375 | dropUniqueConstraint constraintName=UK_JKUWUVD56ONTGSUHOGM8UEWRT, tableName=USER_CONSENT; dropNotNullConstraint columnName=CLIENT_ID, tableName=USER_CONSENT; addColumn tableName=USER_CONSENT; addUniqueConstraint constraintName=UK_JKUWUVD56ONTGSUHO... |          | NULL | 3.5.4     | NULL     | NULL   | 6756353405    |

Is there a possibilty to ignore this error maybe?

If someone give me a hint to find a solution i would be very thankful

2

Recently i saw in my own post that the charset is configured wrong.
As i configured the database with the correct default charset it works now.

Now i am hanging on another issue:

11:23:25,982 ERROR [org.keycloak.connections.jpa.updater.liquibase.conn.DefaultLiquibaseConnectionProvider] (ServerService Thread Pool -- 69) Change Set META-INF/jpa-changelog-9.0.1.xml::9.0.1-KEYCLOAK-12579-add-not-null-constraint::keycloak failed. 
Error: (conn=189378) Duplicate entry 'appmgmt-heriad' for key 'SIBLING_NAMES' 
Failed SQL: UPDATE keycloak.KEYCLOAK_GROUP SET PARENT_GROUP = ' ' WHERE PARENT_GROUP IS NULL]
11:23:25,990 FATAL [org.keycloak.services] (ServerService Thread Pool -- 69) java.lang.RuntimeException: Failed to update database
3

i manually removed the duplicate entries and the issue is now resolved.

Well sometimes it goes like this.
Issue could be removed.