MySQL error upgrading from 25.0.6 to 26.0.0

swow · October 22, 2024, 10:51pm

I’m attempting to upgrade my Keycloak from 25.0.6 to 26.0.0. When starting the new version with the config file from the old version, it’s failing to update the MySQL database.

To do the update I stopped Keycloak, unzipped version 26.0.0, copied the keycloak.conf file to the new version, then ran kc.sh build --db=mysql --http-relative-path /auth which succeeds, followed by kc.sh start --optimized

Checking the log files I see this error line
Oct 22 15:39:05 da-sso-02 kc.sh[1637838]: 2024-10-22 15:39:05,618 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]

I haven’t done any customization to the MySQL database. I just created the DB and gave Keycloak the credentials for it, letting Keycloak create/alter all its tables to get started.

Any idea what might be going wrong here and how to get it back on track?

ben.overcash · October 23, 2024, 1:10pm

I’ve not had this issue, but I can try to add some details to help narrow down the problem.

Keycloak’s schema evolution is performed by a tool called Liquidbase

Here’s the schema update that failed:

<changeSet author="keycloak" id="26.0.0-org-group">
        <addColumn tableName="KEYCLOAK_GROUP">
            <column name="TYPE" type="INT" defaultValueNumeric="0"/>
        </addColumn>
        <update tableName="KEYCLOAK_GROUP">
            <column name="TYPE" valueNumeric="0" type="INT"/>
        </update>
        <addNotNullConstraint tableName="KEYCLOAK_GROUP" columnName="TYPE" columnDataType="INT"/>
        <customChange class="org.keycloak.connections.jpa.updater.liquibase.custom.JpaUpdate26_0_0_OrganizationGroupType"/>
    </changeSet>

The schema evolution is recorded in the table DATABASECHANGELOG

Try querying that table and see what’s in there;

SELECT * FROM DATABASECHANGELOG
ORDER BY DATEEXECUTED DESC

See what returns for this entry;

SELECT * FROM DATABASECHANGELOG
WHERE ID = '26.0.0-org-group'

I suspect that the schema evolution updated the KEYCLOAK_GROUP table but failed to write the update to the DATABASECHANGELOG table; and now you’re stuck because it thinks it needs to run it again but cannot. You may need to restore a backup of your database from prior to the update.

If this is a production instance, I’d advice against trying to bypass the tooling.

swow · October 23, 2024, 11:56pm

I decided to restore from a recent backup (before the attempt to update) and try again. It was possible that previous database was dirty from a different failed attempt to upgrade. This time it was on the server that had never had anything but the initial install and gradual upgrades with no issues.

Unfortunately it failed. But with a different message in the logs:

Oct 23 14:24:04 da-sso-02 kc.sh[7678]: 2024-10-23 14:24:04,548 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Failed to update database
Oct 23 14:24:04 da-sso-02 kc.sh[7678]: 2024-10-23 14:24:04,548 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for changeset META-INF/jpa-changelog-26.0.0.xml::26.0.0-org-group::keycloak:
Oct 23 14:24:04 da-sso-02 kc.sh[7678]:     Reason: 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)]
Oct 23 14:24:04 da-sso-02 kc.sh[7678]: 2024-10-23 14:24:04,548 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: liquibase.exception.MigrationFailedException: Migration failed for changeset META-INF/jpa-changelog-26.0.0.xml::26.0.0-org-group::keycloak:
Oct 23 14:24:04 da-sso-02 kc.sh[7678]:     Reason: 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)]
Oct 23 14:24:04 da-sso-02 kc.sh[7678]: 2024-10-23 14:24:04,550 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Migration failed for changeset META-INF/jpa-changelog-26.0.0.xml::26.0.0-org-group::keycloak:
Oct 23 14:24:04 da-sso-02 kc.sh[7678]:     Reason: 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)]
Oct 23 14:24:04 da-sso-02 kc.sh[7678]: 2024-10-23 14:24:04,551 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: 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)]
Oct 23 14:24:04 da-sso-02 kc.sh[7678]: 2024-10-23 14:24:04,551 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Illegal mix of collations (utf8mb3_general_ci,IMPLICIT) and (utf8mb3_unicode_ci,IMPLICIT) for operation '='
Oct 23 14:24:04 da-sso-02 kc.sh[7678]: 2024-10-23 14:24:04,551 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) For more details run the same command passing the '--verbose' option. Also you can use '--help' to see the details about the usage of the particular command.

So referencing the same table but failing to update the column. Collations are getting a bit outside my understanding of MySQL. Hopefully that might be possible to fix? Any insight?

ben.overcash · October 24, 2024, 12:12pm

Sounds like there could be an encoding conflict with your database.

I found this in the Keycloak documentation;

=== Configuring Unicode support for a MySQL database

Unicode characters are supported in a MySQL database if the database was created with Unicode support in the VARCHAR and CHAR fields when using the CREATE DATABASE command.

Note that the utf8mb4 character set is not supported due to different storage requirements for the utf8 character set. See MySQL documentation for details. In that situation, the length restriction on non-special fields does not apply because columns are created to accommodate the number of characters, not bytes.  If the database default character set does not allow Unicode storage, only the special fields allow storing Unicode values.

. Start MySQL Server.
. Under JDBC driver settings, locate the *JDBC connection settings*.
. Add this connection property: `characterEncoding=UTF-8`

Can you restore from backup again and try again with an updated connection string?

jdbc:mysql://yourdatabase:3306/yourkeycoakdb?characterEncoding=UTF-8

If you’re running a newer version of MySQL take a look at this post on StackOverflow about other ways to set this configuration: JDBC url for MySQL configuration to use utf8 character encoding - Stack Overflow

arozmarin · October 28, 2024, 4:11am

I have same issues.

2024-10-28 14:53:39,159 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Failed to start server in (production) mode
2024-10-28 14:53:39,159 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Failed to update database
2024-10-28 14:53:39,160 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for changeset META-INF/jpa-changelog-26.0.0.xml::26.0.0-org-group::keycloak:
Reason: liquibase.exception.DatabaseException: Duplicate column name ‘TYPE’ [Failed SQL: (1060) ALTER TABLE keycloak.KEYCLOAK_GROUP ADD TYPE INT DEFAULT 0 NULL]
2024-10-28 14:53:39,160 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: liquibase.exception.MigrationFailedException: Migration failed for changeset META-INF/jpa-changelog-26.0.0.xml::26.0.0-org-group::keycloak:
Reason: liquibase.exception.DatabaseException: Duplicate column name ‘TYPE’ [Failed SQL: (1060) ALTER TABLE keycloak.KEYCLOAK_GROUP ADD TYPE INT DEFAULT 0 NULL]
2024-10-28 14:53:39,161 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Migration failed for changeset META-INF/jpa-changelog-26.0.0.xml::26.0.0-org-group::keycloak:
Reason: liquibase.exception.DatabaseException: Duplicate column name ‘TYPE’ [Failed SQL: (1060) ALTER TABLE keycloak.KEYCLOAK_GROUP ADD TYPE INT DEFAULT 0 NULL]
2024-10-28 14:53:39,161 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Duplicate column name ‘TYPE’ [Failed SQL: (1060) ALTER TABLE keycloak.KEYCLOAK_GROUP ADD TYPE INT DEFAULT 0 NULL]
2024-10-28 14:53:39,161 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Duplicate column name ‘TYPE’
2024-10-28 14:53:39,161 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) For more details run the same command passing the ‘–verbose’ option. Also you can use ‘–help’ to see the details about the usage of the particular command.

I tried options above and no success.

For test I create new DB and run start, it worked. But update from old fails. I’m using MySQL 8.0.32

arozmarin · October 28, 2024, 4:23am

Hi guys, I found fix, I don’t know if is correct but it worked for me.

I DB look for . In my case

mysql> SELECT MAX(ORDEREXECUTED) AS max_order FROM DATABASECHANGELOG;
±----------+
| max_order |
±----------+
| 135 |
±----------+

And than I run

INSERT INTO DATABASECHANGELOG (ID, AUTHOR, FILENAME, DATEEXECUTED, ORDEREXECUTED, EXECTYPE) VALUES (‘26.0.0-org-group’, ‘keycloak’, ‘META-INF/jpa-changelog-26.0.0.xml’, NOW(), 135, ‘EXECUTED’);

That worked for me in my lab environment, I didn’t upgrade production yet.

swow · October 29, 2024, 11:06pm

I tried that. Restored from backup, everything’s ticking along happily under 25.0.6. Updated the db-url string in the .conf file - still works fine under 25.0.6. Updated to 26.0.0 and started it and got the same collation errors:

	2024-10-29 15:08:38,386 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Migration failed for changeset META-INF/jpa-changelog-26.0.0.xml::26.0.0-org-group::keycloak:
      Reason: 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)]

I poked back through my notes and found how I created the database in the first place:

CREATE DATABASE keycloakdb CHARACTER SET utf8 COLLATE utf8_unicode_ci;

If I recall, I was following some guides online, since I couldn’t fnd any official guidance from the Keycloak docs on creating the DB in MySQL. Perhaps I shouldn’t have had the COLLATE option on there? Do I need to change the COLLATE option on the DB, if that’s even possible?

ben.overcash · October 30, 2024, 11:34am

By doing this you’re bypassing the schema update process which could have other unforeseen side-affects. If the database schema does not match what the Keycloak code expects you could get lots of bad errors. I’d advise against doing this in a production environment.

ben.overcash · October 30, 2024, 11:37am

Sounds worth a try; we used a Postgres DB and didn’t have any issues so I’m unable to use our system for comparison.

swow · October 30, 2024, 11:35pm

Well, it’s working now. Upgraded to 26.0.0 just now. Updating the thread in case anyone else has an issue like this.

I ended up updating the COLLATE setting for various columns in the database. Most columns had “utf8mb3_unicode_ci” as expected. About 20 or so were using the utf8_general_ci collation. I’m not sure why/how they got set that way as the DB was created with the utf8mb3_unicode_ci collation specified as default.

But after I updated the COLLATE setting for those columns, the upgrade went fine and everything is working well.

arozmarin · November 14, 2024, 3:45am

Hi Ben,

I tried all the options and I’mm still getting:

2024-11-14 14:41:30,850 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Failed to update database
2024-11-14 14:41:30,850 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: liquibase.exception.LiquibaseException: liquibase.exception.MigrationFailedException: Migration failed for changeset META-INF/jpa-changelog-26.0.0.xml::26.0.0-org-group::keycloak:
Reason: liquibase.exception.DatabaseException: Duplicate column name ‘TYPE’ [Failed SQL: (1060) ALTER TABLE keycloak.KEYCLOAK_GROUP ADD TYPE INT DEFAULT 0 NULL]
2024-11-14 14:41:30,851 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: liquibase.exception.MigrationFailedException: Migration failed for changeset META-INF/jpa-changelog-26.0.0.xml::26.0.0-org-group::keycloak:
Reason: liquibase.exception.DatabaseException: Duplicate column name ‘TYPE’ [Failed SQL: (1060) ALTER TABLE keycloak.KEYCLOAK_GROUP ADD TYPE INT DEFAULT 0 NULL]
2024-11-14 14:41:30,851 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Migration failed for changeset META-INF/jpa-changelog-26.0.0.xml::26.0.0-org-group::keycloak:
Reason: liquibase.exception.DatabaseException: Duplicate column name ‘TYPE’ [Failed SQL: (1060) ALTER TABLE keycloak.KEYCLOAK_GROUP ADD TYPE INT DEFAULT 0 NULL]
2024-11-14 14:41:30,852 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Duplicate column name ‘TYPE’ [Failed SQL: (1060) ALTER TABLE keycloak.KEYCLOAK_GROUP ADD TYPE INT DEFAULT 0 NULL]
2024-11-14 14:41:30,853 ERROR [org.keycloak.quarkus.runtime.cli.ExecutionExceptionHandler] (main) ERROR: Duplicate column name ‘TYPE’

I’m using “mysql Ver 8.0.32 for Linux on x86_64 (Source distribution)”

I’m able only if I INSERT INTO DATABASECHANGELOG. Any idea how could I upgrade any other way? Thanks

arozmarin · November 14, 2024, 4:45am

Hi Ben,

After a bit more research and testing, I managed to upgrade like that:

First I ALTER

to utf8mb3_unicode_ci

Second think I had to do was,

ALTER TABLE keycloak.KEYCLOAK_GROUP DROP COLUMN TYPE;

After this changes I managed to upgrade to 26. What is you opinion, is this OK?

ben.overcash · November 14, 2024, 6:39pm

Updating the encoding types is definitely what I was would recommend. I’m not a DBA but for sure that seems like the underlying culprit here.

Dropping the column feels gross but, yeah, that’s probably what i’d have done too. After the upgrade is the column re-added correctly? As long as that’s the case I think it would be fine.

My other thought is that, since in your initial run the column was added but not rolled back either your driver or database aren’t configured to support transactions correctly – ideally if any part of the ChangeSet fails everything would be rolled back. Maybe look into using a XA driver? Just a thought to help prevent this from happening again in the future.

carstenlenz · November 15, 2024, 8:53am

I am having the exact same issue. Did anyone filed a bug for this? Really strange. We have multiple (test) instances of KC an all of them are affected by this.

@ben.overcash

MySQL does not support transactional DDL - so no alter table … things can be rolled back. That is why this failure will always leave the DB in an inconsistent state regarding migrations.

ben.overcash · November 15, 2024, 2:09pm

I did not know that, thanks for the insight! I almost always go with postgres so I’m really unfamiliar with mysql configurations.

From what I’ve gathered, the root cause is that the database was created with the wrong default encoding; if you add characterEncoding=UTF-8 to your connection string and also update existing tables to use utf8mb3_unicode_ci that’s solved the issue for others.

You’d also need to drop / unalter any columns that are in that halfway state to deal with the non-transactional update…

col-panic · November 27, 2024, 2:11pm

github.com/keycloak/keycloak

MySQL database migration issue

opened 02:41PM - 15 Nov 24 UTC

bobvandevijver

kind/bug area/core status/triage team/core-iam

### 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.

Topic		Replies	Views
Upgrading Keycloak from 18.0.0 to 20.0.3 with MySQL upgrading	0	993	January 30, 2023
Keycloak migration from legacy to newer version Configuring the server	3	1642	September 1, 2022
Error configuring mysql with keycloak 20.0.3? Configuring the server	0	759	February 3, 2023
Upgrading 14.0->15.1. MSSQL issue Getting advice upgrading	6	2705	October 14, 2021
Keycloak database upgrade to 9.0.1+ Failed Getting advice	1	1998	April 22, 2020

MySQL error upgrading from 25.0.6 to 26.0.0

Related topics