2026-08-26 · run 2026-08-26-s4-mysql-dml-only-db-user

S4 on MySQL: the DML-only failure holds, but the fix is *privileges*, not ownership

Summary

This is the first run against an engine other than Postgres, and it closes the ☐ "holds on MySQL/MariaDB/Oracle" row in S4. Two findings, one expected and one that changes what we tell customers:

1. A DML-only account fails the migration on MySQL, same as Postgres — so the failure mode generalises. It fails even earlier on MySQL than on Postgres: before any Keycloak changeset runs, at Liquibase's own changelog-table setup (ALTER TABLE DATABASECHANGELOG ADD PRIMARY KEY (ID, AUTHOR, FILENAME)), not at the first Keycloak DDL changeset.

2. The fix is different. On Postgres the S4 record found that granting DDL is not enough — DROP INDEX requires ownership, which no GRANT confers. MySQL has no ownership concept: granting the DDL privileges (CREATE, ALTER, DROP, INDEX, REFERENCES, CREATE VIEW, SHOW VIEW on keycloak.*) makes the same migration succeed cleanly. The pre-flight question is therefore engine-specific: on Postgres, "does the account own the objects"; on MySQL, "does the account hold the DDL privileges".

Environment

Procedure

The whole run needed a MySQL toolchain first. Then:

# run A — DML only
CREATE USER 'kc_dml'@'%' IDENTIFIED BY 'kc_dml';
GRANT SELECT, INSERT, UPDATE, DELETE ON keycloak.* TO 'kc_dml'@'%';
# .env: KC_DB=mysql, COMPOSE_FILE=docker-compose.yml:docker-compose.mysql.yml,
#        KC_DB_USERNAME=kc_dml, KC_DB_PASSWORD=kc_dml
./bin/upgrade.sh 26.7.1          # FAILED, 13s

# run B — DML + DDL (no ownership concept to add)
GRANT CREATE, ALTER, DROP, INDEX, REFERENCES, CREATE VIEW, SHOW VIEW
      ON keycloak.* TO 'kc_dml'@'%';
./bin/restore.sh baseline-26.0.0-mysql-typical-1k
./bin/upgrade.sh 26.7.1          # READY

Results

Run A failure — earlier than Postgres, and not a Keycloak changeset

ERROR: liquibase.exception.ChangeLogParseException:
  liquibase.exception.SetupException: liquibase.exception.DatabaseException:
  ALTER command denied to user 'kc_dml'@'172.19.0.3' for table 'DATABASECHANGELOG'
  [Failed SQL: (1142) ALTER TABLE keycloak.DATABASECHANGELOG ADD PRIMARY KEY (ID, AUTHOR, FILENAME)]

Contrast with Postgres S4, where the first failure was a real Keycloak changeset (20.0.0-12964-supported-dbs-edb-migration, DROP INDEX IDX_GROUP_ATT_BY_NAME_VALUE → "must be owner of index"). On MySQL, Liquibase 26.7.1 wants a primary key on its own DATABASECHANGELOG table that the 26.0.0-era Liquibase did not create, and it tries to add it before touching any Keycloak changeset. A DML-only account is therefore rejected at the door, not mid-migration.

Run B — DDL privileges are sufficient

Granting the DDL set (still no ownership concept — MySQL has none) let the same migration run to completion: Updating database → realm migrators 26.1.0 → 26.7.0 (~5s), migration_model 26.7.1, index audit 121 correct / 0 missing / 0 wrong shape, all 1,000 users intact.

Verification