keycloak · ahus1 · Jun 11, 2025 · May 27, 2025 · Jun 2, 2025 · Jun 10, 2025
@@ -0,0 +1,91 @@
+package org.keycloak.connections.jpa.updater.liquibase.custom;
+
+import liquibase.exception.CustomChangeException;
+import liquibase.statement.core.DeleteStatement;
+import liquibase.structure.core.Column;
+
+import java.sql.PreparedStatement;
+import java.sql.ResultSet;
+import java.util.HashSet;
+import java.util.Set;
+import java.util.concurrent.atomic.AtomicInteger;
+import java.util.stream.Collectors;
+
+/**
+ * Cleanup script for removing duplicated migration model versions in the MIGRATION_MODEL table
+ * See: <a href="https://github.com/keycloak/keycloak/issues/39866">keycloak#39866</a>
+ */
+public class JpaUpdate26_2_6_RemoveDuplicateMigrationModelVersion extends CustomKeycloakTask {
+
+    private final static String MIGRATION_MODEL_TABLE = "MIGRATION_MODEL";
+
+    @Override
+    protected String getTaskId() {
+        return "Delete duplicated records for DB version in MIGRATION_MODEL table";
+    }
+
+    @Override
+    protected void generateStatementsImpl() throws CustomChangeException {
+        Set<String> idsToDelete = new HashSet<>();
+
+        final String tableName = getTableName(MIGRATION_MODEL_TABLE);
+        final String colId = database.correctObjectName("ID", Column.class);
+        final String colVersion = database.correctObjectName("VERSION", Column.class);
+        final String colUpdateTime = database.correctObjectName("UPDATE_TIME", Column.class);
+
+        //noinspection SqlSourceToSinkFlow
+        try (PreparedStatement ps = connection.prepareStatement(getOlderDuplicatedRecords(tableName, colId, colVersion, colUpdateTime))) {
+            ResultSet resultSet = ps.executeQuery();
+            while (resultSet.next()) {
+                idsToDelete.add(resultSet.getString(1));
+            }
+        } catch (Exception e) {
+            throw new CustomChangeException(getTaskId() + ": Failed to detect duplicate MIGRATION_MODEL rows", e);
+        }
+
+        AtomicInteger i = new AtomicInteger();
+        idsToDelete.stream()
+                .collect(Collectors.groupingByConcurrent(id -> i.getAndIncrement() / 20, Collectors.toList())) // Split into chunks of at most 20 items
+                .values().stream()
+                .map(ids -> new DeleteStatement(null, null, MIGRATION_MODEL_TABLE)
+                        .setWhere(":name IN (" + ids.stream().map(id -> "?").collect(Collectors.joining(",")) + ")")
+                        .addWhereColumnName(colId)
+                        .addWhereParameters(ids.toArray())
+                )
+                .forEach(statements::add);
+    }
+
+    /**
+     * Get duplicated records
+     * <p>
+     * If there is VERSION duplication, choose:
+     * <p>
+     * - If UPDATE_TIME is: different -> pick more recent
+     * <p>
+     * - If UPDATE_TIME is: equal -> pick some random
+     */
+    private String getOlderDuplicatedRecords(String tableName, String colId, String colVersion, String colUpdateTime) {
+        return """
+                SELECT m1.%s
+                FROM %s m1
+                WHERE EXISTS (
+                    SELECT m2.%s
+                    FROM %s m2
+                    WHERE m2.%s = m1.%s
+                    AND (
+                        m2.%s > m1.%s
+                        OR (m2.%s = m1.%s AND m2.%s > m1.%s)
+                    )
+                )
+                """.formatted(
+                colId,                  // SELECT m1.%s         => SELECT m1.ID
+                tableName,              // FROM %s m1           => FROM MIGRATION_MODEL m1
+                colId,                  // SELECT m2.%s         => SELECT m2.ID
+                tableName,              // FROM %s              => FROM MIGRATION_MODEL m2
+                colVersion, colVersion, // WHERE m2.%s = m1.%s  => WHERE m2.VERSION = m1.VERSION
+                colUpdateTime, colUpdateTime, // m2.%s > m1.%s  => m2.UPDATE_TIME > m1.UPDATE_TIME
+                // OR (m2.%s = m1.%s AND m2.%s > m1.%s)         => OR (m2.UPDATE_TIME = m1.UPDATE_TIME AND m2.ID > m1.ID)
+                colUpdateTime, colUpdateTime, colId, colId);
+    }
+
+}
@@ -0,0 +1,28 @@
+<?xml version="1.0" encoding="UTF-8" standalone="no"?>
+<!--
+  ~ * Copyright 2025 Red Hat, Inc. and/or its affiliates
+  ~ * and other contributors as indicated by the @author tags.
+  ~ *
+  ~ * Licensed under the Apache License, Version 2.0 (the "License");
+  ~ * you may not use this file except in compliance with the License.
+  ~ * You may obtain a copy of the License at
+  ~ *
+  ~ * http://www.apache.org/licenses/LICENSE-2.0
+  ~ *
+  ~ * Unless required by applicable law or agreed to in writing, software
+  ~ * distributed under the License is distributed on an "AS IS" BASIS,
+  ~ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+  ~ * See the License for the specific language governing permissions and
+  ~ * limitations under the License.
+  -->
+<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
+
+    <changeSet author="keycloak" id="26.2.6-39866-duplicate">
+        <customChange class="org.keycloak.connections.jpa.updater.liquibase.custom.JpaUpdate26_2_6_RemoveDuplicateMigrationModelVersion"/>
+    </changeSet>
+
+    <changeSet author="keycloak" id="26.2.6-39866-uk">
+        <addUniqueConstraint tableName="MIGRATION_MODEL" columnNames="VERSION" constraintName="UK_MIGRATION_VERSION"/>
+    </changeSet>
+
+</databaseChangeLog>
@@ -86,6 +86,7 @@
     <include file="META-INF/jpa-changelog-26.0.0.xml"/>
     <include file="META-INF/jpa-changelog-26.1.0.xml"/>
     <include file="META-INF/jpa-changelog-26.2.0.xml"/>
+    <include file="META-INF/jpa-changelog-26.2.6.xml"/>
     <include file="META-INF/jpa-changelog-26.3.0.xml"/>
 
 </databaseChangeLog>
@@ -29,6 +29,7 @@
 import org.keycloak.models.DeploymentStateProvider;
 import org.keycloak.models.KeycloakSession;
 import org.keycloak.models.ModelException;
+import org.keycloak.models.utils.KeycloakModelUtils;
 import org.keycloak.representations.idm.RealmRepresentation;
 import org.keycloak.storage.datastore.DefaultMigrationManager;
 import org.keycloak.testsuite.AbstractKeycloakTest;
@@ -49,52 +50,56 @@ public void addTestRealms(List<RealmRepresentation> testRealms) {
      */
     @Test
     @ModelTest
-    public void testMigrationDeniedWithDBSnapshotAndServerNonSnapshot(KeycloakSession session) {
-        MigrationModel model = session.getProvider(DeploymentStateProvider.class).getMigrationModel();
-        String databaseVersion = model.getStoredVersion();
-        Assert.assertNotNull("Stored DB version was null", model.getStoredVersion());
+    public void testMigrationDeniedWithDBSnapshotAndServerNonSnapshot(KeycloakSession s) {
+        KeycloakModelUtils.runJobInTransaction(s.getKeycloakSessionFactory(), (session) -> {
+            MigrationModel model = session.getProvider(DeploymentStateProvider.class).getMigrationModel();
+            String databaseVersion = model.getStoredVersion();
+            Assert.assertNotNull("Stored DB version was null", model.getStoredVersion());
 
-        String currentVersion = Version.VERSION;
-        try {
-            // Simulate to manually set runtime version of KeycloakServer to 23. Migration should fail as the version is lower than DB version.
-            Version.VERSION = "23.0.0";
-            model.setStoredVersion(Constants.SNAPSHOT_VERSION);
+            String currentVersion = Version.VERSION;
+            try {
+                // Simulate to manually set runtime version of KeycloakServer to 23. Migration should fail as the version is lower than DB version.
+                Version.VERSION = "23.0.0";
+                model.setStoredVersion(Constants.SNAPSHOT_VERSION);
 
-            new DefaultMigrationManager(session, false).migrate();
-            Assert.fail("Not expected to successfully run migration. DB version was " + databaseVersion + ". Keycloak version was " + currentVersion);
-        } catch (ModelException expected) {
-            Assert.assertTrue(expected.getMessage().startsWith("Incorrect state of migration. You are trying to run server version"));
-        } finally {
-            // Revert both versions to the state before the test
-            Version.VERSION = currentVersion;
-            model.setStoredVersion(databaseVersion);
-        }
+                new DefaultMigrationManager(session, false).migrate();
+                Assert.fail("Not expected to successfully run migration. DB version was " + databaseVersion + ". Keycloak version was " + currentVersion);
+            } catch (ModelException expected) {
+                Assert.assertTrue(expected.getMessage().startsWith("Incorrect state of migration. You are trying to run server version"));
+            } finally {
+                // Revert version to the state before the test
+                Version.VERSION = currentVersion;
+                session.getTransactionManager().rollback();
+            }
+        });
     }
 
     /**
      * Tests migration should not be allowed when DB version is set to non-snapshot version like "23.0.0", but Keycloak server version is snapshot version "999.0.0"
      */
     @Test
     @ModelTest
-    public void testMigrationDeniedWithDBNonSnapshotAndServerSnapshot(KeycloakSession session) {
-        MigrationModel model = session.getProvider(DeploymentStateProvider.class).getMigrationModel();
-        String databaseVersion = model.getStoredVersion();
-        Assert.assertNotNull("Stored DB version was null", model.getStoredVersion());
+    public void testMigrationDeniedWithDBNonSnapshotAndServerSnapshot(KeycloakSession s) {
+        KeycloakModelUtils.runJobInTransaction(s.getKeycloakSessionFactory(), (session) -> {
+            MigrationModel model = session.getProvider(DeploymentStateProvider.class).getMigrationModel();
+            String databaseVersion = model.getStoredVersion();
+            Assert.assertNotNull("Stored DB version was null", model.getStoredVersion());
 
-        String currentVersion = Version.VERSION;
-        try {
-            // Simulate to manually set DB version to 23 when server version is SNAPSHOT. Migration should fail as it is an attempt to run production DB with the development server
-            Version.VERSION = Constants.SNAPSHOT_VERSION;
-            model.setStoredVersion("23.0.0");
+            String currentVersion = Version.VERSION;
+            try {
+                // Simulate to manually set DB version to 23 when server version is SNAPSHOT. Migration should fail as it is an attempt to run production DB with the development server
+                Version.VERSION = Constants.SNAPSHOT_VERSION;
+                model.setStoredVersion("23.0.0");
 
-            new DefaultMigrationManager(session, false).migrate();
-            Assert.fail("Not expected to successfully run migration. DB version was " + databaseVersion + ". Keycloak version was " + currentVersion);
-        } catch (ModelException expected) {
-            Assert.assertTrue(expected.getMessage().startsWith("Incorrect state of migration. You are trying to run nightly server version"));
-        } finally {
-            // Revert both versions to the state before the test
-            Version.VERSION = currentVersion;
-            model.setStoredVersion(databaseVersion);
-        }
+                new DefaultMigrationManager(session, false).migrate();
+                Assert.fail("Not expected to successfully run migration. DB version was " + databaseVersion + ". Keycloak version was " + currentVersion);
+            } catch (ModelException expected) {
+                Assert.assertTrue(expected.getMessage().startsWith("Incorrect state of migration. You are trying to run nightly server version"));
+            } finally {
+                // Revert version to the state before the test
+                Version.VERSION = currentVersion;
+                session.getTransactionManager().rollback();
+            }
+        });
     }
 }