keycloak12+mysql5.7 初次启动报错处理

现象

启动报错

ERROR [org.keycloak.connections.jpa.updater.liquibase.LiquibaseJpaUpdaterProvider] (ServerService Thread Pool – 65) Error has occurred while updating the database: 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)]

可以看见keycloak使用了liquibase管理数据库版本
修改表REALEM字段CERTIFICATE为VARCHAR(4000)时,导致行大小超过了MYSQL上限65535

解决

将表编码类型改为utf8（原本utf8mb4字符长度是4个字节，utf8是3个字节）

源码

查看源码发现，其实REALM这个表中的CERTIFICATE等几个大文本字段在后来的版本中都删除了，但是liquibase需要顺序执行变更集，导致执行到1.9.1这个版本时过不去了，真的尴尬

• jpa-changelog-1.9.1.xml

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">
    <changeSet author="keycloak" id="1.9.1">
        <preConditions onSqlOutput="TEST" onFail="MARK_RAN">
            <not>
                <dbms type="db2" />
            </not>
        </preConditions>

        <modifyDataType tableName="REALM" columnName="PRIVATE_KEY" newDataType="VARCHAR(4000)"/>
        <modifyDataType tableName="REALM" columnName="PUBLIC_KEY" newDataType="VARCHAR(4000)"/>
        <modifyDataType tableName="REALM" columnName="CERTIFICATE" newDataType="VARCHAR(4000)"/>
    </changeSet>
</databaseChangeLog>

• jpa-changelog-2.3.0.xml

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
<!--
  ~ Copyright 2016 Red Hat, Inc. and/or its affiliates
  ~ and other contributors as indicated by the @author tags.
  ~
  ~ Licensed under the Apache License, Version 2.0 (the "License");
  ~ you may not use this file except in compliance with the License.
  ~ You may obtain a copy of the License at
  ~
  ~ http://www.apache.org/licenses/LICENSE-2.0
  ~
  ~ Unless required by applicable law or agreed to in writing, software
  ~ distributed under the License is distributed on an "AS IS" BASIS,
  ~ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
  ~ See the License for the specific language governing permissions and
  ~ limitations under the License.
  -->

<databaseChangeLog xmlns="http://www.liquibase.org/xml/ns/dbchangelog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.liquibase.org/xml/ns/dbchangelog http://www.liquibase.org/xml/ns/dbchangelog/dbchangelog-3.1.xsd">

     <changeSet author="bburke@redhat.com" id="2.3.0">
        <createTable tableName="FEDERATED_USER">
            <column name="ID" type="VARCHAR(255)">
                <constraints nullable="false"/>
            </column>
            <column name="STORAGE_PROVIDER_ID" type="VARCHAR(255)">
            </column>
            <column name="REALM_ID" type="VARCHAR(36)">
                <constraints nullable="false" />
            </column>
        </createTable>
         <addPrimaryKey columnNames="ID" constraintName="CONSTR_FEDERATED_USER" tableName="FEDERATED_USER"/>

         <dropDefaultValue tableName="USER_ENTITY" columnName="TOTP" />
         <dropColumn tableName="USER_ENTITY" columnName="TOTP" />

         <addColumn tableName="IDENTITY_PROVIDER">
             <column name="PROVIDER_DISPLAY_NAME" type="VARCHAR(255)"></column>
         </addColumn>

         <addColumn tableName="COMPONENT">
             <column name="SUB_TYPE" type="VARCHAR(255)"></column>
         </addColumn>

         <customChange class="org.keycloak.connections.jpa.updater.liquibase.custom.ExtractRealmKeysFromRealmTable"/>
         <dropColumn tableName="REALM" columnName="CODE_SECRET" />
         <dropColumn tableName="REALM" columnName="PRIVATE_KEY" />
         <dropColumn tableName="REALM" columnName="PUBLIC_KEY" />
         <dropColumn tableName="REALM" columnName="CERTIFICATE" />

         <addColumn tableName="USER_CONSENT">
             <column name="CREATED_DATE" type="BIGINT"/>
             <column name="LAST_UPDATED_DATE" type="BIGINT"/>
         </addColumn>

     </changeSet>

</databaseChangeLog>