Troubleshooting Docker Integration of Keycloak with MariaDB Backend

Backend
1

I’m currently working on setting up a Docker container for Keycloak, utilizing a MariaDB container for database storage. Initially, both containers can start without any issues, and Keycloak is able to connect to MariaDB, setting up the necessary database schema. However, I keep encountering character encoding or collation problems resulting in repeated errors logged as:

[Failed SQL: (1194) ALTER TABLE keycloak.CLIENT_SESSION ADD REALM_ID VARCHAR(255) NULL]

After these errors occur, the containers enter a restart loop. Interestingly, transitioning to a PostgreSQL setup resolves these issues entirely.

Here’s the Docker Compose configuration I was attempting:

  keycloak_mariadb:
    container_name: keycloak_mariadb
    image: mariadb:10.7.7-focal
    restart: unless-stopped
    ports:
      - 33306:3306
    environment:
      MARIADB_ROOT_PASSWORD: "${KEYCLOAK_DATABASE_ROOT_PASSWORD}"
      MYSQL_DATABASE: "${KEYCLOAK_DATABASE_PRODUCTION}"
      MYSQL_USER: "${KEYCLOAK_DATABASE_SU_USERNAME}"
      MYSQL_PASSWORD: "${KEYCLOAK_DATABASE_SU_PASSWORD}"
    volumes:
      - ./mysql-keycloak-data:/var/lib/mysql

  keycloak_frontend:
    container_name: keycloak_frontend
    image: quay.io/keycloak/keycloak:20.0.3
    restart: unless-stopped
    ports:
      - 8080:8080
    command: ["start-dev"]
    environment:
      KC_DB: mariadb
      KC_DB_URL: jdbc:mariadb://keycloak_mariadb:3306/su_keycloak_production
      KC_DB_USERNAME: "root"
      KC_DB_PASSWORD: "test"
      KEYCLOAK_ADMIN: admin
      KEYCLOAK_ADMIN_PASSWORD: Pa55w0rd
    depends_on:
      - keycloak_mariadb

networks:
  keycloak_mariadb_network:
    driver: bridge

I aim to stick with MariaDB for consistency in my setup. If anyone has experienced similar issues and found a solution, I would greatly appreciate your insights.

2

hmm, that’s interesting! have you tried setting the charset explicitly in your mariadb config? what keycloak version were you using before? also, did you check the mariadb logs during schema creation to see what’s happening with character encoding?

3

This happens because MariaDB’s default character set clashes with Keycloak’s schema needs. I hit the same issue migrating from H2 to MariaDB. Here’s what fixed it: Add MARIADB_CHARACTER_SET=utf8mb4 and MARIADB_COLLATION=utf8mb4_unicode_ci to your mariadb service environment variables. Then update your JDBC URL: jdbc:mariadb://keycloak_mariadb:3306/su_keycloak_production?characterEncoding=UTF-8&useUnicode=true. The restart loop happens because Keycloak keeps trying to fix the schema on startup, but it can’t until you sort out the encoding mismatch at the database level.

4

had the same problem a few months back. add --character-set-server=utf8mb4 --collation-server=utf8mb4_unicode_ci to your mariadb command or set it in env vars. also, make sure your jdbc url ends with ?characterEncoding=UTF-8. fixed it for me on keycloak 19.x