[keycloak-dev] mysql and utfmb4 almost works

Tue Feb 28 10:41:24 EST 2017

I am attaching the patch because it was mangled by the mail client.

On 28/02/17 17:35, Yaacov Akiba Slama wrote:
> 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"?>
> +
> +
> +<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>
> +
> +        
> +        <modifyDataType tableName="REALM" columnName="PRIVATE_KEY"
> 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>
>  
>
> _______________________________________________
> keycloak-dev mailing list
> keycloak-dev at lists.jboss.org
> https://lists.jboss.org/mailman/listinfo/keycloak-dev


-------------- next part --------------

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>
 
