[keycloak-dev] mysql and utfmb4 almost works

[Yaacov Akiba Slama]

Hi,

I installed mysql 5.7.17 with innodb-large-prefix=1 and created a
database using utf8mb4 charset. Then I installed, configured and
launched keycloak-2.5.4

Everything almost "works". The only problem is the 1.9.1 database
change: jpa-changelog-1.9.1.xml

A workaround is to let keycloak fails (assuming initializeEmpty is
true), then change the charset of the table "REALM" to utf8, restart
keycloak then stop it after the database is ready.

Finally change back the charset of the table "REALM" to utf8mb4 and
restart keycloak.

The alternative is to use the patch below. I can also open a PR in github.

What do you think ?

Thanks.

diff --git
a/model/jpa/src/main/resources/META-INF/jpa-changelog-1.9.1-mysql.xml
b/model/jpa/src/main/resources/META-INF/jpa-changelog-1.9.1-mysql.xml
new file mode 100644
index 0000000000..55afffc5a4
--- /dev/null
+++ b/model/jpa/src/main/resources/META-INF/jpa-changelog-1.9.1-mysql.xml
@@ -0,0 +1,30 @@
+<?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
logicalFilePath="META-INF/db2-jpa-changelog-1.9.1.xml"
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">
+            <dbms type="mysql" />
+        </preConditions>
+
+        <!-- Can't increase publicKey and certificate size on mysql
when utf8mb4 is used. Need to find better solution -->
+        <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>
diff --git
a/model/jpa/src/main/resources/META-INF/jpa-changelog-1.9.1.xml
b/model/jpa/src/main/resources/META-INF/jpa-changelog-1.9.1.xml
index c083bc9a2b..d67f97b903 100755
--- a/model/jpa/src/main/resources/META-INF/jpa-changelog-1.9.1.xml
+++ b/model/jpa/src/main/resources/META-INF/jpa-changelog-1.9.1.xml
@@ -20,7 +20,10 @@
     <changeSet author="keycloak" id="1.9.1">
         <preConditions onSqlOutput="TEST" onFail="MARK_RAN">
             <not>
+                <or>
                 <dbms type="db2" />
+                <dbms type="mysql" />
+                </or>
             </not>
         </preConditions>