<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><meta http-equiv=Content-Type content="text/html; charset=utf-8"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0cm;
margin-bottom:.0001pt;
font-size:12.0pt;
font-family:"Times New Roman",serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.gmailmsg
{mso-style-name:gmail_msg;}
span.EmailStyle18
{mso-style-type:personal-reply;
font-family:"Calibri",sans-serif;
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;
mso-fareast-language:EN-US;}
@page WordSection1
{size:612.0pt 792.0pt;
margin:72.0pt 72.0pt 72.0pt 72.0pt;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-AU link=blue vlink=purple><div class=WordSection1><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>Thanks for the info, Jonathan. That’s really helpful!<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'>I think I’ll raise a new bug report since none of those address the database encoding specifically.<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>David Cook<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>Systems Librarian<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>Prosentient Systems<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>72/330 Wattle St<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>Ultimo, NSW 2007<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>Australia<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'><o:p> </o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>Office: 02 9212 0899<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif'>Direct: 02 8005 0595<o:p></o:p></span></p><p class=MsoNormal><span style='font-size:11.0pt;font-family:"Calibri",sans-serif;mso-fareast-language:EN-US'><o:p> </o:p></span></p><div style='border:none;border-left:solid blue 1.5pt;padding:0cm 0cm 0cm 4.0pt'><div><div style='border:none;border-top:solid #E1E1E1 1.0pt;padding:3.0pt 0cm 0cm 0cm'><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'> koha-devel-bounces@lists.koha-community.org [mailto:koha-devel-bounces@lists.koha-community.org] <b>On Behalf Of </b>Jonathan Druart<br><b>Sent:</b> Saturday, 25 March 2017 1:39 AM<br><b>To:</b> koha-devel@lists.koha-community.org<br><b>Subject:</b> Re: [Koha-devel] MySQL silently truncating strings due to 3 byte limit in 'utf8' charset<o:p></o:p></span></p></div></div><p class=MsoNormal><o:p> </o:p></p><div><p class=MsoNormal>Yes it's a known issue, see bug 15794.<o:p></o:p></p><div><p class=MsoNormal>We already discuss this a bit on bug 11944 as well.<o:p></o:p></p><div><p class=MsoNormal>I know PTFS-E guys are using utf8mb4 already.<o:p></o:p></p></div><div><p class=MsoNormal>It should not be hard to make this move, at least for new installs.<o:p></o:p></p></div><div><p class=MsoNormal><o:p> </o:p></p></div><div><p class=MsoNormal>Cheers,<o:p></o:p></p></div><div><p class=MsoNormal style='margin-bottom:12.0pt'>Jonathan<o:p></o:p></p><div><div><p class=MsoNormal>On Fri, 24 Mar 2017 at 03:08 David Cook <<a href="mailto:dcook@prosentient.com.au">dcook@prosentient.com.au</a>> wrote:<o:p></o:p></p></div><blockquote style='border:none;border-left:solid #CCCCCC 1.0pt;padding:0cm 0cm 0cm 6.0pt;margin-left:4.8pt;margin-right:0cm'><div><div><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Hi all,<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I ran into a problem this afternoon where MySQL was silently truncating my UTF8 encoded string in biblio_metadata.metadata and biblio.notes. Admittedly, the string had some funny looking data*, but it was all valid UTF8. I had zero problems with XML::LibXML or MARC::Record. However, whenever I would use DBI or the mysql command line client, I’d get a truncated string**. This meant a completely invalid XML record which is irretrievable from the database using Koha’s XML handling methods. <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>However, I eventually found <a href="https://bugs.mysql.com/bug.php?id=53005" target="_blank">https://bugs.mysql.com/bug.php?id=53005</a> where they state that the MySQL ‘utf8’ charset has a 3 byte limit (<a href="https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-utf8.html" target="_blank">https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-utf8.html</a>). That tends to be fine in most cases, but <span class=gmailmsg><span style='font-family:"Cambria Math",serif'>𝔤 </span></span>is 4 bytes (f0 9d 94 a4 <a href="http://dev.networkerror.org/utf8/?start=120100&end=120200&cols=4&show_uni_int=on&show_uni_hex=on&show_html_ent=on&show_raw_hex=on&show_raw_bin=on" target="_blank">http://dev.networkerror.org/utf8/?start=120100&end=120200&cols=4&show_uni_int=on&show_uni_hex=on&show_html_ent=on&show_raw_hex=on&show_raw_bin=on</a>). It was on this character that the truncation happened. Apparently, there is a 4 byte UTF-8 charset called utf8mb4: <a href="https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-utf8mb4.html" target="_blank">https://dev.mysql.com/doc/refman/5.7/en/charset-unicode-utf8mb4.html</a>. If you “SET NAMES = ‘utf8mb4’” for your client and set the charset for your database column, that will allow you to correctly insert the data. <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>So the truncation doesn’t generate an error but it does generate a warning, which is much more obvious in the mysql CLI client than when using DBI:<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>MariaDB [devkohadcook]> update biblio set abstract = 'We introduce a new perspective and a generalization of spectral networks for 4d N=2" style="position: relative;" tabindex="0" id="MathJax-Element-1-Frame" class="MathJax"><span class=gmailmsg><span lang=EN-US></span></span>=2 theories of class S" style="position: relative;" tabindex="0" id="MathJax-Element-2-Frame" class="MathJax"><span class=gmailmsg><span lang=EN-US></span></span> associated to Lie algebras g=An" style="position: relative;" tabindex="0" id="MathJax-Element-3-Frame" class="MathJax"><span class=gmailmsg><span style='font-family:"Cambria Math",serif'>𝔤</span></span>=An, Dn" style="position: relative;" tabindex="0" id="MathJax-Element-4-Frame" class="MathJax">Dn, E6" style="position: relative;" tabindex="0" id="MathJax-Element-5-Frame" class="MathJax">E6, and E7" style="position: relative;" tabindex="0" id="MathJax-Element-6-Frame" class="MathJax">E7. Spectral networks directly compute the BPS spectra of 2d theories on surface defects coupled to the 4d theories. A Lie algebraic interpretation of these spectra emerges naturally from our construction, leading to a new description of 2d-4d wall-crossing phenomena. Our construction also provides an efficient framework for the study of BPS spectra of the 4d theories. In addition, we consider novel types of surface defects associated with minuscule representations of g" style="position: relative;" tabindex="0" id="MathJax-Element-7-Frame" class="MathJax"><span class=gmailmsg><span style='font-family:"Cambria Math",serif'>𝔤</span></span>.' where biblionumber = 50314;<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Query OK, 0 rows affected, 1 warning (0.00 sec)<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Rows matched: 1 Changed: 0 Warnings: 1<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>Because it doesn’t raise an exception, I’d say it’s silent. You have to check for it. Using mysql, you can send “SHOW WARNINGS” after the insert query and you can see something like:<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>+---------+------+--------------------------------------------------------------------------------+<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>| Level | Code | Message |<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>+---------+------+--------------------------------------------------------------------------------+<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>| Warning | 1366 | Incorrect string value: '\xF0\x9D\x94\xA4=A...' for column 'abstract' at row 1 |<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>+---------+------+--------------------------------------------------------------------------------+<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>1 row in set (0.01 sec)<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>With DBI, it’s harder: <a href="http://stackoverflow.com/questions/7946655/dbi-perl-logging-mysql-warnings" target="_blank">http://stackoverflow.com/questions/7946655/dbi-perl-logging-mysql-warnings</a>. Apparently you can use $dbh->{mysql_warning_count} to check for warnings, and then send “SHOW WARNINGS” via the same database handle and that should give you your warning result. Or you can run MySQL in “traditional” mode which treats warnings as errors. <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>So this seems like an edgecase… it’s some wonky data (albeit valid UTF8 data) in an otherwise English record. <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>However, according to MySQL (I haven’t verified for myself), Chinese, Japanese, and Korean sometimes use 4 bytes for characters. So people using these languages with Koha could be getting truncated data without realising that’s what is happening or why.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I’ve tried out utf8mb4 and it works great. I think the downside is that it uses more space for certain types of columns because it needs to allocate more space for that 4<sup>th</sup> byte. I don’t know whether we should consider switching from utf8 to utf8mb4, so that we’re inclusive of CJK and comply with RFC3629 which states that there’s a 4 byte maximum.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I think it’s worth discussion though. This wouldn’t just affect libraries in Asia. It would affect any library which stores records with CJK characters in a MARC 880 “Alternate Graphic Representation” field. <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>I’m at the end of my Friday work day, so I’m going to leave it there, but food for thought!<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>*Aforementioned strange data:<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>We introduce a new perspective and a generalization of spectral networks for 4d N=2" style="position: relative;" tabindex="0" id="MathJax-Element-1-Frame" class="MathJax"><span class=gmailmsg><span lang=EN-US></span></span>=2 theories of class S" style="position: relative;" tabindex="0" id="MathJax-Element-2-Frame" class="MathJax"><span class=gmailmsg><span lang=EN-US></span></span> associated to Lie algebras g=An" style="position: relative;" tabindex="0" id="MathJax-Element-3-Frame" class="MathJax"><span class=gmailmsg><span style='font-family:"Cambria Math",serif'>𝔤</span></span>=An, Dn" style="position: relative;" tabindex="0" id="MathJax-Element-4-Frame" class="MathJax">Dn, E6" style="position: relative;" tabindex="0" id="MathJax-Element-5-Frame" class="MathJax">E6, and E7" style="position: relative;" tabindex="0" id="MathJax-Element-6-Frame" class="MathJax">E7. Spectral networks directly compute the BPS spectra of 2d theories on surface defects coupled to the 4d theories. A Lie algebraic interpretation of these spectra emerges naturally from our construction, leading to a new description of 2d-4d wall-crossing phenomena. Our construction also provides an efficient framework for the study of BPS spectra of the 4d theories. In addition, we consider novel types of surface defects associated with minuscule representations of g" style="position: relative;" tabindex="0" id="MathJax-Element-7-Frame" class="MathJax"><span class=gmailmsg><span style='font-family:"Cambria Math",serif'>𝔤</span></span>.<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>** Truncated string:<o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'>We introduce a new perspective and a generalization of spectral networks for 4d N=2" style="position: relative;" tabindex="0" id="MathJax-Element-1-Frame" class="MathJax"><span class=gmailmsg><span lang=EN-US></span></span>=2 theories of class S" style="position: relative;" tabindex="0" id="MathJax-Element-2-Frame" class="MathJax"><span class=gmailmsg><span lang=EN-US></span></span> associated to Lie algebras g=An" style="position: relative;" tabindex="0" id="MathJax-Element-3-Frame" class="MathJax"><span class=gmailmsg><span style='font-family:"Cambria Math",serif'>𝔤</span></span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span class=gmailmsg>David Cook</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span class=gmailmsg>Systems Librarian</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span class=gmailmsg>Prosentient Systems</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span class=gmailmsg>72/330 Wattle St</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span class=gmailmsg>Ultimo, NSW 2007</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span class=gmailmsg>Australia</span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span class=gmailmsg> </span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span class=gmailmsg>Office: <a href="tel:02%2092%2012%2008%2099" target="_blank">02 9212 0899</a></span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'><span class=gmailmsg>Direct: <a href="tel:02%2080%2005%2005%2095" target="_blank">02 8005 0595</a></span><o:p></o:p></p><p class=MsoNormal style='mso-margin-top-alt:auto;mso-margin-bottom-alt:auto'> <o:p></o:p></p></div></div><p class=MsoNormal>_______________________________________________<br>Koha-devel mailing list<br><a href="mailto:Koha-devel@lists.koha-community.org" target="_blank">Koha-devel@lists.koha-community.org</a><br><a href="http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel" target="_blank">http://lists.koha-community.org/cgi-bin/mailman/listinfo/koha-devel</a><br>website : <a href="http://www.koha-community.org/" target="_blank">http://www.koha-community.org/</a><br>git : <a href="http://git.koha-community.org/" target="_blank">http://git.koha-community.org/</a><br>bugs : <a href="http://bugs.koha-community.org/" target="_blank">http://bugs.koha-community.org/</a><o:p></o:p></p></blockquote></div></div></div></div></div></div></body></html>