Uploaded image for project: 'Red Hat build of Keycloak'
  1. Red Hat build of Keycloak
  2. RHBK-1636

Realm cannot be deleted if there are tons of consents

XMLWordPrintable

    • Icon: Bug Bug
    • Resolution: Done
    • Icon: Major Major
    • None
    • 22.0.11, 24.0.5
    • None

      When deleting a realm with a large number of USER_CONSENT_CLIENT_SCOPE records, following slow query logs are recorded and the realm deletion operation failed with transaction timeout error:

      # Time: 2024-05-12T01:23:45.000000Z
# User@Host: root[root] @  [127.0.0.1]  Id: 13895
# Query_time: 31.325039  Lock_time: 0.000002 Rows_sent: 0  Rows_examined: 14200000
SET timestamp=1715499224;
delete from USER_CONSENT_CLIENT_SCOPE where SCOPE_ID='1bb77779-8416-5f39-38ec-c741a7955262';

      This is because there is no index for that table in column scope_id:

      create index IDX_USCONSENT_SCOPEID on USER_CONSENT_CLIENT_SCOPE(SCOPE_ID);

      After that there is another slow query because of the insidious bug of mariadb/mysql of not using indexes with inner queries:

      # Time: 2024-06-01T05:43:21.000000Z
# User@Host: root[root] @  [127.0.0.1]  Id:   101
# Query_time: 570.563298  Lock_time: 0.000002 Rows_sent: 0  Rows_examined: 2110000
use root;
SET timestamp=1719463552;
delete from USER_CONSENT_CLIENT_SCOPE where USER_CONSENT_ID in (select userconsen1_.ID from USER_CONSENT userconsen1_ where userconsen1_.USER_ID in (select userentity2_.ID from USER_ENTITY userentity2_ where userentity2_.REALM_ID='dummy'));

      This last one is similar to other issues like this PR: https://github.com/keycloak/keycloak/pull/12611

              rhn-support-rmartinc Ricardo Martin Camarero
              rhn-support-rmartinc Ricardo Martin Camarero
              Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

                Created:
                Updated:
                Resolved: