Before reporting an issue

[X] I have read and understood the above terms for submitting issues, and I understand that my issue may be closed without action if I do not follow them.

Area

core

Describe the bug

While deploying the update to 26.0.5 (updating from 25.0.6) an unexpected database migration issue was encountered.
This did not happen on my test environment, but it did happen with both production environments.

I believe this has to do with a small collation difference between those two environments: in test my database is create schema keycloak collate utf8mb4_unicode_ci;, where the production database use utf8mb3_unicode_ci.

However, only three tables are shown to use the mb4 collation and charset in my test environment:

• ORG
• ORG_DOMAIN
• REVOKED_TOKEN

Looking at https://www.keycloak.org/server/db#_configuring_unicode_support_for_a_mysql_database, utf8mb4 is not supported, which would mean that my test environment is running out of spec. But that was the only environment without issues.

Then I found this thread: https://groups.google.com/g/keycloak-user/c/9MhUfXwla3s?pli=1. Looks like utf8mb4 should be used now with MySQL 8, and that the documentation needs to be updated?

Version

26.0.5

Regression

[ ] The issue is a regression

Expected behavior

The migration to be completed without issue.

Actual behavior

```
keycloak-1 | 2024-11-15 13:43:56,257 ERROR [liquibase.changelog.ChangeSet] (main) ChangeSet META-INF/jpa-changelog-26.0.0.xml::26.0.0-org-group::keycloak encountered an exception.: liquibase.exception.DatabaseException: Illegal mix of collations (utf8mb3_general_ci,IMPLICIT) and (utf8mb3_unicode_ci,IMPLICIT) for operation '=' [Failed SQL: (1267) UPDATE keycloak.KEYCLOAK_GROUP SET TYPE = 1 WHERE NAME IN (SELECT ID FROM keycloak.ORG)]
```

```
keycloak-1 | 2024-11-15 13:44:11,230 ERROR [liquibase.changelog.ChangeSet] (main) ChangeSet META-INF/jpa-changelog-26.0.0.xml::26.0.0-org-group::keycloak encountered an exception.: liquibase.exception.DatabaseException: Duplicate column name 'TYPE' [Failed SQL: (1060) ALTER TABLE keycloak.KEYCLOAK_GROUP ADD TYPE INT DEFAULT 0 NULL]
```

How to Reproduce?

I believe it should be reproducable when upgrading a 25 instance to 26 while using MySQL 8 where the complete database is using the utf8mb3_unicode_ci collation and utf8mb3 character set.

Anything else?

For now I fixed it by manually marking the migration as completed in the db and adding the TYPE column manually. As the specific table was empty, there was no need to run https://github.com/keycloak/keycloak/blob/cec081961b7116577dea764adc826b7005b23981/model/jpa/src/main/java/org/keycloak/connections/jpa/updater/liquibase/custom/JpaUpdate26_0_0_OrganizationGroupType.java manually.