[keycloak-user] MySQL and UTF8

[Sven Beauprez]

Hi,

Searching the internet, I noticed that I am not alone struggling with this and the things I found did not work for my particular test, unless I missed something, hence this mail. When trying to use UTF-8, I get the exception “Row size too large”.

I am starting MySQL (8.0.3) and Keycloak (4.7.0-Final) respectively via docker as follows (just a test env, not the most secure setup). I am aware of following MySQL configuration https://www.keycloak.org/docs/latest/server_installation/index.html#mysql-database

But it seems I am doing something wrong when using the containerized version. Do

docker volume create mysql-volume
docker network create mysql-network

docker run --name mysql --mount source=mysql-volume,target=/var/lib/mysql --net mysql-network -p 3306:3306 -e MYSQL_USER=keycloak -e MYSQL_DATABASE=keycloak -e MYSQL_PASSWORD=password -e MYSQL_ROOT_PASSWORD=password -d mysql:8.0.13 --character-set-server=utf8


and run keycloak

docker run --name keycloak --net mysql-network -e KEYCLOAK_USER=admin -e KEYCLOAK_PASSWORD=password -e JDBC_PARAMS='connectTimeout=30&useSSL=false&allowPublicKeyRetrieval=true&characterEncoding=UTF-8' -p 8080:8080 jboss/keycloak:4.7.0.Final


And I get the following error (as described above)


...
Caused by: liquibase.exception.MigrationFailedException: Migration failed for change set META-INF/jpa-changelog-1.9.1.xml::1.9.1::keycloak:
     Reason: liquibase.exception.DatabaseException: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs [Failed SQL: ALTER TABLE keycloak.REALM MODIFY CERTIFICATE VARCHAR(4000)]
                at liquibase.changelog.ChangeSet.execute(ChangeSet.java:619)
                at liquibase.changelog.visitor.UpdateVisitor.visit(UpdateVisitor.java:51)
                at liquibase.changelog.ChangeLogIterator.run(ChangeLogIterator.java:79)
                at liquibase.Liquibase.update(Liquibase.java:214)
                at liquibase.Liquibase.update(Liquibase.java:192)
                at liquibase.Liquibase.update(Liquibase.java:188)
                at org.keycloak.connections.jpa.updater.liquibase.LiquibaseJpaUpdaterProvider.updateChangeSet(LiquibaseJpaUpdaterProvider.java:182)
                at org.keycloak.connections.jpa.updater.liquibase.LiquibaseJpaUpdaterProvider.update(LiquibaseJpaUpdaterProvider.java:102)
                ... 57 more
Caused by: liquibase.exception.DatabaseException: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs [Failed SQL: ALTER TABLE keycloak.REALM MODIFY CERTIFICATE VARCHAR(4000)]
                at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:309)
                at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:55)
                at liquibase.executor.jvm.JdbcExecutor.execute(JdbcExecutor.java:113)
                at liquibase.database.AbstractJdbcDatabase.execute(AbstractJdbcDatabase.java:1277)
                at liquibase.database.AbstractJdbcDatabase.executeStatements(AbstractJdbcDatabase.java:1259)
                at liquibase.changelog.ChangeSet.execute(ChangeSet.java:582)
                ... 64 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Row size too large. The maximum row size for the used table type, not counting BLOBs, is 65535. This includes storage overhead, check the manual. You have to change some columns to TEXT or BLOBs
...




Regards,

Sven