Problems upgrading from 17.0.1 to 18.0.0

[Lucio Crusca]

This is a crosspost from https://serverfault.com/questions/1099487 since the post there did not receive any answers.

I'm trying to understand if I should open a new issue or if it is my fault.

Original post:

I have a KeyCloak 17.0.1 that is apparently working without issues on my server, configured to use MariaDB. I say "apparently" because, as of today, it's not in production yet, albeit it starts in production mode, but it is on a development server and it is actually there only to let us developers play with it. I start it with this command:

    bin/kc.sh -v start --hostname=my.real.hostname --https-certificate-file=/etc/letsencrypt/live/my.real.hostname/cert.pem --https-certificate-key-file=/etc/letsencrypt/live/my.real.hostname/privkey.pem --db-url-host localhost --db-username root --db-password my-real-password --proxy=reencrypt --db-schema=KEYCLOAK

It's running on a Debian 11 system with a Debian-packaged MariaDB server. In order to have it running, I had to move MariaDB data on a case insensitive ext4 filesystem and configure MariaDB to ignore case in table names (see [my post here][1]). Before that, it was complaining with `Schema "KEYCLOAK" not found` error message.

Now I'm trying to upgrade KC 17.0.1 to KC 18, following [this guide][2], but when I start KC 18, I get [this error message][3] (in short `Schema "KEYCLOAK" not found`).

Since KC 17.0.1 was complaining with the same error message and the problem was solved by moving MariaDB on a casefolding ext4 filesystem, I wanted to ensure that MariaDB was still  ignoring case. So I tried manually executing, from the MariaDB console, the same SQL statement that was causing the KC error message:

    MariaDB [(none)]> CREATE TABLE KEYCLOAK.DATABASECHANGELOGLOCK (ID INT NOT NULL, LOCKED BOOLEAN NOT NULL, LOCKGRANTED TIMESTAMP, LOCKEDBY VARCHAR(255), CONSTRAINT PK_DATABASECHANGELOGLOCK PRIMARY KEY (ID));

which replied with a different error message than what KC reports in the logs:

    ERROR 1050 (42S01): Table 'databasechangeloglock' already exists

so KC 18, during the upgrade process, is trying to create a table that already exists. Maybe it thinks it does not exist because it can't find the `KEYCLOAK` schema for some reason and it tries to create it, but then again how did KC 18 understood it needed to upgrade the database, if it could not find it? I'm not really looking for an answer to this: I'd be happy with just a workaround.

Just to make sure MariaDB is actually casefolding both schemas and tables names, here are some other things I tried:

    # mysqladmin -u root -p variables | grep lower_case_table_names
    | lower_case_table_names                                   | 2  
    # mysql
    MariaDB [(none)]> create database TESTDB;
    Query OK, 1 row affected (0.000 sec)
   
    MariaDB [(none)]> drop database testdb;
    Query OK, 0 rows affected (0.001 sec)
   
    MariaDB [(none)]> drop database nonexistingschemaname;
    ERROR 1008 (HY000): Can't drop database 'nonexistingschemaname'; database doesn't exist
   
    MariaDB [(none)]> create database TESTDB;
    Query OK, 1 row affected (0.000 sec)

    MariaDB [(none)]> use testdb;
    Database changed

So MariaDB seems to work correctly (at least from the casefold point of view), but still KC 18 crashes on startup, while KC 17 works. Any clues?

  [1]: https://groups.google.com/g/keycloak-user/c/3hemmPcTcsk/m/6v7VkDIeAAAJ
  [2]: https://www.keycloak.org/docs/latest/upgrading/index.html
  [3]: https://www.toptal.com/developers/hastebin/quyosudego.sql