Mssql migrations issue.

[grinay]

Hello i've build the latest v0.23 version and tried to use in keyclock 21.0.2 which connected to mssql database. On run it fails to apply migrations: jpa-changelog-phasetwo-20211207.xml

Caused by: liquibase.exception.MigrationFailedException: Migration failed for changeset META-INF/jpa-changelog-phasetwo-20211207.xml::org-role-3::garth:
     Reason: liquibase.exception.DatabaseException: Column 'ORGANIZATION_ROLE.ID' is not the same length or scale as referencing column 'USER_ORGANIZATION_ROLE_MAPPING.ROLE_ID' in foreign key 'FK_123g9nf5findslfksadifu277'. Columns participating in a foreign key relationship must be defined with the same length and scale. [Failed SQL: (1753) ALTER TABLE USER_ORGANIZATION_ROLE_MAPPING ADD CONSTRAINT FK_123g9nf5findslfksadifu277 FOREIGN KEY (ROLE_ID) REFERENCES ORGANIZATION_ROLE (ID)]

jpa-changelog-phasetwo-20211208.xml

Caused by: liquibase.exception.MigrationFailedException: Migration failed for changeset META-INF/jpa-changelog-phasetwo-20211208.xml::drop-column-user-org-role-org-1::garth:
     Reason: liquibase.exception.DatabaseException: The object 'FK_HGF6S4UUNYWDKP4244YTNGBAD' is dependent on column 'ORGANIZATION_ID'. [Failed SQL: (5074) ALTER TABLE USER_ORGANIZATION_ROLE_MAPPING DROP COLUMN ORGANIZATION_ID]

To fix that I had to manually apply some queries to tables:

ALTER TABLE USER_ORGANIZATION_ROLE_MAPPING
DROP CONSTRAINT UK_S5FNFVSRU9I4QPH9GB8V4FXFM;

ALTER TABLE USER_ORGANIZATION_ROLE_MAPPING
ALTER COLUMN ROLE_ID VARCHAR(36);

ALTER TABLE USER_ORGANIZATION_ROLE_MAPPING
ADD CONSTRAINT UK_S5FNFVSRU9I4QPH9GB8V4FXFM UNIQUE ([ORGANIZATION_ID], [USER_ID], [ROLE_ID]);

ALTER TABLE USER_ORGANIZATION_ROLE_MAPPING
DROP CONSTRAINT FK_HGF6S4UUNYWDKP4244YTNGBAD;

After that migrations finished.

[xgp]

We don't (currently) support mssql in our migrations, but we'd take PRs. A model for creating a PR is here for the team that added MySQL compatibility. https://github.com/p2-inc/keycloak-orgs/pull/41

[grinay]

@xgp so fast response. Ok, I'm not java developer. Hope someone helps to make these migrations.

[xgp]

@grinay Understood, but I think you're 99% of the way there by just knowing the fixes. There is no Java required to make the migrations work. Everything is done in the Liquibase changelog files (https://github.com/p2-inc/keycloak-orgs/tree/main/src/main/resources/META-INF). Look at an example from how the team that added MySQL did it: https://github.com/p2-inc/keycloak-orgs/pull/41/files#diff-426463610480e06ccaf5eb2286686991c4a9e62f6b1b9ade38f437e3f4f13099R4

TBH, I knew nothing about Liquibase until I built this extension for Keycloak, but I found it not to hard to figure out, as basically everything is just a wrapper on top of SQL.

Hope someone helps to make these migrations.

Given what I've seen, that is low likelihood. Companies that use this extension either build and contribute them (e.g. like Fastly for the MySQL compatibility), or our paying customers require them.

[grinay]

@xgp here it is https://github.com/p2-inc/keycloak-orgs/pull/57

[xgp]

Amazing! Thank you so much @grinay ! I will test these in the next few days. I need to test incremental migrations with the other supported database types to make sure there will not be an error/regression when existing deployments upgrade.

Also, for testing mssql, do you have a method that uses docker images for sql server?

[xgp]

Looks like it's possible to use these https://mcr.microsoft.com/en-us/product/mssql/server/about

[grinay]

@xgp I'm using https://hub.docker.com/_/microsoft-azure-sql-edge it's the same mssql. And also original version https://hub.docker.com/_/microsoft-mssql-server. The only thing is the last one doesn't work on ARM. But edge version is working.

[xgp]

@grinay what do your connection parameters look like? I'm getting the following error:

Could not obtain connection to query metadata: com.microsoft.sqlserver.jdbc.SQLServerException: The driver could not establish a secure connection to SQL Server by using Secure Sockets Layer (SSL) encryption. Error: "PKIX path building failed: sun.security.provider.certpath.SunCertPathBuilderException: unable to find valid certification path to requested target

[grinay]

@xgp I had similar issue, Something to do with encryption. ENV KC_DB_URL=jdbc:sqlserver://$KC_DB_HOSTNAME:1433;databaseName=$KC_DB_NAME;characterEncoding=UTF-8;encrypt=true;trustServerCertificate=true;trustStore=/etc/pki/java/cacerts;trustStorePassword=changeit So we have to add generated certificates. Also build with this command RUN /opt/keycloak/bin/kc.sh build --db=mssql --transaction-xa-enabled=false

[xgp]

Fixed in https://github.com/p2-inc/keycloak-orgs/pull/57