<div dir="ltr">Took a look at this yesterday and could confirm the problem. Seems to be isolated to MySQL as it works fine on H2 and PostgreSQL at least.<div><br></div><div>Can you create a JIRA for it please?<br><div><br></div><div><br></div></div></div><div class="gmail_extra"><br><div class="gmail_quote">On 16 August 2016 at 15:38, Igor Zuk <span dir="ltr"><<a href="mailto:igor.zuk@qualitytaskforce.com" target="_blank">igor.zuk@qualitytaskforce.com</a>></span> wrote:<br><blockquote class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
<div lang="PL" link="blue" vlink="purple">
<div>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Hi Stian<u></u><u></u></span></p>
<p class="MsoNormal"><span style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">I hope you had a good time during holiday. Your autoresponse asked me to resend you the email when you’re back, so here
it is.<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<div>
<div style="border:none;border-top:solid #e1e1e1 1.0pt;padding:3.0pt 0cm 0cm 0cm">
<p class="MsoNormal"><b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span style="font-size:11.0pt;font-family:"Calibri",sans-serif"> Igor Zuk
<br>
<b>Sent:</b> Wednesday, August 03, 2016 4:37 PM<br>
<b>To:</b> '<a href="mailto:stian@redhat.com" target="_blank">stian@redhat.com</a>' <<a href="mailto:stian@redhat.com" target="_blank">stian@redhat.com</a>><br>
<b>Cc:</b> <a href="mailto:keycloak-user@lists.jboss.org" target="_blank">keycloak-user@lists.jboss.org</a><br>
<b>Subject:</b> RE: [keycloak-user] Keycloak user data encoding<u></u><u></u></span></p>
</div>
</div><span class="">
<p class="MsoNormal"><u></u> <u></u></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Sorry for a delayed response, I had to temporarily suspend the investigation.</span><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif"><u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Yes, I can reproduce the issue anytime using Docker.<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">I set up as default as possible environment with latest MySQL:<u></u><u></u></span></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">docker run --name mysql -e MYSQL_ROOT_PASSWORD=root -e MYSQL_DATABASE=keycloak -e MYSQL_USER=keycloak -e MYSQL_PASSWORD=keycloak
-P -d mysql:latest<u></u><u></u></span></b></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">and Keycloak in the same version as where I found the issue, 1.9.2.Final:<u></u><u></u></span></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">docker run --name keycloak -e MYSQL_DATABASE=keycloak -e MYSQL_USER=keycloak -e MYSQL_PASSWORD=keycloak -e MYSQL_PORT_3306_TCP_ADDR=192.<wbr>168.99.100
-e MYSQL_PORT_3306_TCP_PORT=32779 -P -d jboss/keycloak-mysql:1.9.2.<wbr>Final<u></u><u></u></span></b></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">The results were identical, special letters in names were replaced with question marks. It turned out, that Keycloak created
all its tables with the DB's default encoding, latin1 (ISO-8859-1). I've checked it with a query:<u></u><u></u></span></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">SELECT character_set_name FROM information_schema.`COLUMNS` WHERE table_name = "USER_ENTITY" AND column_name = "FIRST_NAME";<u></u><u></u></span></b></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Once again I've manually changed the encoding of a single column:<u></u><u></u></span></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">ALTER TABLE `USER_ENTITY` MODIFY `FIRST_NAME` VARCHAR(255) CHARACTER SET utf8;<u></u><u></u></span></b></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">It worked, but Keycloak was still putting there names with question marks, so the issue was fully reproduced.<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Because it seems, that Keycloak uses the DB's default encoding, I tried changing it in MySQL. I've removed its container
entirely and started it with two parameters appended to Docker run command:<u></u><u></u></span></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">--character-set-server=utf8mb4 --collation-server=utf8mb4_<wbr>unicode_ci<u></u><u></u></span></b></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">It seems, that it worked, but then Keycloak refused to start at all throwing exception with error:<u></u><u></u></span></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">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)]<u></u><u></u></span></b></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">It looks that Keycloak is not able to work with UTF-8 in databases at all! The full startup log is here:
<a href="http://pastebin.com/VMTARqgF" target="_blank">http://pastebin.com/VMTARqgF</a><u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
</span><p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Because 1.9.2.Final is quite dated, I've checked the latest available MySQL-preconfigured version, 2.0.0.Final. I've repeated
all the steps and the results were identical.<u></u><u></u></span></p><span class="">
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">The example name I'm working with is Mściwy Żółw. Only the letter 'ó' is working, that's because it's encodable in ISO-8859-1.<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"><u></u> <u></u></span></p>
</span><p class="MsoNormal"><b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif"> Stian Thorgersen [<a href="mailto:sthorger@redhat.com" target="_blank">mailto:sthorger@redhat.com</a>]
<br><span class="">
<b>Sent:</b> Friday, July 15, 2016 6:48 AM<br>
<b>To:</b> Igor Zuk <<a href="mailto:igor.zuk@qualitytaskforce.com" target="_blank">igor.zuk@qualitytaskforce.com</a><wbr>><br>
</span><b>Cc:</b> <a href="mailto:keycloak-user@lists.jboss.org" target="_blank">keycloak-user@lists.jboss.org</a></span></p><div><div class="h5"><br>
<b>Subject:</b> Re: [keycloak-user] Keycloak user data encoding<u></u><u></u></div></div><p></p><div><div class="h5">
<p class="MsoNormal"><span lang="EN-US"><u></u> <u></u></span></p>
<div>
<p class="MsoNormal"><span lang="EN-US">It's strange that no one else has reported this. We had several people report the issue with umlats, but no one else seems to have the issue with the database encoding. Maybe there's something different with your database
config? Could you try with a default MySQL database installation and see if you can reproduce the issue? Also, can you give me a sample name that shows the problems.<u></u><u></u></span></p>
<div>
<p class="MsoNormal"><span lang="EN-US"><u></u> <u></u></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">I added a test for umlats to registration and account management, see </span><a href="https://github.com/keycloak/keycloak/pull/3036" target="_blank"><span lang="EN-US">https://github.com/<wbr>keycloak/keycloak/pull/3036</span></a><span lang="EN-US">.
Once it's in I'll schedule a run with CI, which tests with a range of different databases.<u></u><u></u></span></p>
</div>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"><u></u> <u></u></span></p>
<div>
<p class="MsoNormal"><span lang="EN-US">On 12 July 2016 at 16:13, Igor Zuk <</span><a href="mailto:igor.zuk@qualitytaskforce.com" target="_blank"><span lang="EN-US">igor.zuk@qualitytaskforce.com</span></a><span lang="EN-US"><wbr>> wrote:<u></u><u></u></span></p>
<blockquote style="border:none;border-left:solid #cccccc 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0cm;margin-bottom:5.0pt">
<div>
<div>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">Thank you for a quick response.</span><span lang="EN-US"><u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><span lang="EN-US"><u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">I’m using 1.9.2.Final and the problem is a bit different, it’s not limited to registration
screen.</span><span lang="EN-US"><u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><span lang="EN-US"><u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d">I’m saying, that ISO-8859-1 is the default encoding, because all the text columns in USER_ENTITY
table had encoding latin1. The table was created completely by Keycloak as the database was empty in the beginning. I manually switched encoding of FIRST_NAME to UTF-8 and modified it so it contained special letters. I started the user editor in Keycloak admin
console and this name was displayed correctly. I added a single character to it, saved, and then the name got messed up with question marks instead of all special characters.</span><span lang="EN-US"><u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif;color:#1f497d"> </span><span lang="EN-US"><u></u><u></u></span></p>
<p class="MsoNormal"><b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">From:</span></b><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif"> Stian
Thorgersen [mailto:</span><a href="mailto:sthorger@redhat.com" target="_blank"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">sthorger@redhat.com</span></a><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">]
<br>
<b>Sent:</b> Tuesday, July 12, 2016 3:43 PM<br>
<b>To:</b> Stian Thorgersen <</span><a href="mailto:stian@redhat.com" target="_blank"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">stian@redhat.com</span></a><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">><br>
<b>Cc:</b> Igor Zuk <</span><a href="mailto:igor.zuk@qualitytaskforce.com" target="_blank"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">igor.zuk@qualitytaskforce.com</span></a><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif"><wbr>>;
</span><a href="mailto:keycloak-user@lists.jboss.org" target="_blank"><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif">keycloak-user@lists.jboss.org</span></a><span lang="EN-US" style="font-size:11.0pt;font-family:"Calibri",sans-serif"><br>
<b>Subject:</b> Re: [keycloak-user] Keycloak user data encoding</span><span lang="EN-US"><u></u><u></u></span></p>
<div>
<div>
<p class="MsoNormal"><span lang="EN-US"> <u></u><u></u></span></p>
<div>
<p class="MsoNormal"><span lang="EN-US">By the way this was fixed in 1.6.0.Final, see </span><a href="https://issues.jboss.org/browse/KEYCLOAK-1830?jql=project%20%3D%20KEYCLOAK%20AND%20text%20~%20%22encoding%22" target="_blank"><span lang="EN-US">https://issues.jboss.org/<wbr>browse/KEYCLOAK-1830?jql=<wbr>project%20%3D%20KEYCLOAK%<wbr>20AND%20text%20~%20%<wbr>22encoding%22</span></a><span lang="EN-US"><u></u><u></u></span></p>
<div>
<p class="MsoNormal"><span lang="EN-US"> <u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">Are you using an old version?<u></u><u></u></span></p>
</div>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"> <u></u><u></u></span></p>
<div>
<p class="MsoNormal"><span lang="EN-US">On 12 July 2016 at 15:37, Stian Thorgersen <</span><a href="mailto:sthorger@redhat.com" target="_blank"><span lang="EN-US">sthorger@redhat.com</span></a><span lang="EN-US">>
wrote:<u></u><u></u></span></p>
<blockquote style="border:none;border-left:solid #cccccc 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0cm;margin-bottom:5.0pt">
<div>
<p class="MsoNormal"><span lang="EN-US">Why are you saying the default encoding is ISO-8859-1? All forms are encoded as UTF-8 and all strings passed to the database should be UTF-8 encoded as well.<u></u><u></u></span></p>
<div>
<p class="MsoNormal"><span lang="EN-US"> <u></u><u></u></span></p>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US">The only thing that is ISO-8859-1 is the message properties, but those are converted to UTF-8 when added to HTML pages.<u></u><u></u></span></p>
</div>
</div>
<div>
<p class="MsoNormal"><span lang="EN-US"> <u></u><u></u></span></p>
<div>
<div>
<div>
<p class="MsoNormal"><span lang="EN-US">On 12 July 2016 at 14:58, Igor Zuk <</span><a href="mailto:igor.zuk@qualitytaskforce.com" target="_blank"><span lang="EN-US">igor.zuk@qualitytaskforce.com</span></a><span lang="EN-US"><wbr>>
wrote:<u></u><u></u></span></p>
</div>
</div>
<blockquote style="border:none;border-left:solid #cccccc 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-top:5.0pt;margin-right:0cm;margin-bottom:5.0pt">
<div>
<div>
<div>
<div>
<p class="MsoNormal"><span lang="EN-US">Hi<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US"> <u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">I have an encoding problem. By default users' data fields (e.g. first name and last name) are encoded using ISO-8859-1. People from many countries can't properly
create accounts as their personal data is silently messed up. How can I fix it?<u></u><u></u></span></p>
<p><span lang="EN-US" style="font-family:Symbol">·</span><span lang="EN-US" style="font-size:7.0pt">
</span><span lang="EN-US">The MySQL DB receives already damaged names. By default all columns are ISO-8859-1-encoded, but manually converting them to UTF-8 doesn't help.<u></u><u></u></span></p>
<p><span lang="EN-US" style="font-family:Symbol">·</span><span lang="EN-US" style="font-size:7.0pt">
</span><span lang="EN-US">Manual account modification from admin console has same effect.<u></u><u></u></span></p>
<p><span lang="EN-US" style="font-family:Symbol">·</span><span lang="EN-US" style="font-size:7.0pt">
</span><span lang="EN-US">Change of default server (Wildfly) encoding to UTF-8 doesn't do anything.<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US"> <u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">Best regards<u></u><u></u></span></p>
<p class="MsoNormal"><span lang="EN-US">Igor Żuk<u></u><u></u></span></p>
</div>
</div>
<p class="MsoNormal"><span lang="EN-US"> <u></u><u></u></span></p>
</div>
</div>
<p class="MsoNormal"><span lang="EN-US">______________________________<wbr>_________________<br>
keycloak-user mailing list<br>
</span><a href="mailto:keycloak-user@lists.jboss.org" target="_blank"><span lang="EN-US">keycloak-user@lists.jboss.org</span></a><span lang="EN-US"><br>
</span><a href="https://lists.jboss.org/mailman/listinfo/keycloak-user" target="_blank"><span lang="EN-US">https://lists.jboss.org/<wbr>mailman/listinfo/keycloak-user</span></a><span lang="EN-US"><u></u><u></u></span></p>
</blockquote>
</div>
<p class="MsoNormal"><span lang="EN-US"> <u></u><u></u></span></p>
</div>
</blockquote>
</div>
<p class="MsoNormal"><span lang="EN-US"> <u></u><u></u></span></p>
</div>
</div>
</div>
</div>
</div>
</blockquote>
</div>
<p class="MsoNormal"><span lang="EN-US"><u></u> <u></u></span></p>
</div>
</div></div></div>
</div>
</blockquote></div><br></div>