Bug 11906

Summary: Bad display of utf-8 chars in Patron lists (Datatable)
Product: Koha Reporter: Marc Véron <veron>
Component: Staff interfaceAssignee: Bugs List <koha-bugs>
Status: CLOSED FIXED QA Contact: Jonathan Druart <jonathan.druart>
Severity: normal    
Priority: P5 - low CC: fridolin.somers, gmcharlt, jonathan.druart, katrin.fischer, olli-antti.kivilahti, tomascohen
Version: Main   
Hardware: All   
OS: All   
Change sponsored?: --- Patch complexity: Small patch
Documentation contact: Documentation submission:
Text to go in the release notes:
Version(s) released in:
Bug Depends on: 10565    
Bug Blocks: 11944, 13596    
Attachments: Bad display of utf-8 chars
Bug 12001: regression test for using DBIC to store & fetch UTF8 strings
Bug 11906: ensure that Koha::Database uses UTF8 mode when connecting to databases
Bug 11906: regression test for using DBIC to store & fetch UTF8 strings
Bug 11906: ensure that Koha::Database uses UTF8 mode when connecting to databases
Bug 11906: regression test for using DBIC to store & fetch UTF8 strings
Bug 11906: ensure that Koha::Database uses UTF8 mode when connecting to databases
Bug 11906: The EncodeUTF8 TT Plugin is useless

Description Marc Véron 2014-03-06 20:13:54 UTC
Created attachment 25916 [details]
Bad display of utf-8 chars

In patron lists patron names and other texts display ???? if they have umlauts and other utf-8 chars.

Something like Беларуская Skjöldóttur dispays as ?????????? Skj�ld�ttur

See screenshot attached.
Comment 1 Marc Véron 2014-03-06 20:35:20 UTC
I tried the solution from Bug 11290 

In ...patron_lists/list.tt something like:

[% p.borrowernumber.surname |html_entity %]

It works for Skjöldóttur, but not for Беларуская
Comment 2 Tomás Cohen Arazi 2014-03-07 13:43:05 UTC
I'm sorry to say I cannot reproduce the problem right now. I'm pretty sure this is related to some data being defaulted to ISO because of not explicitly marking as UTF8 (so is more related to latin+diacritics than non-latin) and hence breaking places where there is actual UTF-8 data.
Comment 3 Owen Leonard 2014-03-10 13:14:15 UTC
I had a similar problem with patron lists and was pointed to this:

http://wiki.koha-community.org/wiki/Koha_on_ubuntu_-_tarball#Tweaking_my.cnf

Making that change fixed it for me. Is that the correct solution, or is that a stopgap for a problem we need to address globally?
Comment 4 Tomás Cohen Arazi 2014-03-10 13:31:03 UTC
(In reply to Owen Leonard from comment #3)
> I had a similar problem with patron lists and was pointed to this:
> 
> http://wiki.koha-community.org/wiki/Koha_on_ubuntu_-_tarball#Tweaking_my.cnf
> 
> Making that change fixed it for me. Is that the correct solution, or is that
> a stopgap for a problem we need to address globally?

If the problem is environment settings-related (having MySQL set correctly is a pre-requisite for running Koha), this bug should be marked as invalid.

I can volunteer to write something to check MySQL configuration sanity so we can (at least) ask users reporting this problems to visit about > system check to see if there's any problem.
Comment 5 Owen Leonard 2014-03-14 13:24:42 UTC
*** Bug 11942 has been marked as a duplicate of this bug. ***
Comment 6 Owen Leonard 2014-03-17 13:21:43 UTC
*** Bug 11949 has been marked as a duplicate of this bug. ***
Comment 7 Marc Véron 2014-03-20 14:45:13 UTC
I followed the advice on 
http://wiki.koha-community.org/wiki/Koha_on_ubuntu_-_tarball#Tweaking_my.cnf
and
http://wiki.koha-community.org/wiki/Encoding_and_Character_Sets_in_Koha
and added the following to: 

/etc/mysql/my.cnf
----------
[mysqld] 
init-connect = 'SET NAMES utf8'
character-set-server=utf8
collation-server=utf8_general_ci
character_set_client=utf8
----------

Then I restarted mysql with: /etc/init.d/mysql restart

As a result, I get the following in mysql: 

mysql> show variables like '%colla%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_general_ci |
| collation_server     | utf8_general_ci |
+----------------------+-----------------+

mysql> show variables like '%char%';
+--------------------------+----------------------------+
| Variable_name            | Value                      |
+--------------------------+----------------------------+
| character_set_client     | utf8                       |
| character_set_connection | utf8                       |
| character_set_database   | utf8                       |
| character_set_filesystem | binary                     |
| character_set_results    | utf8                       |
| character_set_server     | utf8                       |
| character_set_system     | utf8                       |
| character_sets_dir       | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+


...and the charecters now display fine.


@ Tomás Cohen Arazi

Having a MySQL configuration sanity check in about > system check
would be great!
Comment 8 Galen Charlton 2014-03-26 20:01:30 UTC
Actually... those MySQL configuration changes aren't necessary in order to fix the problem.  Something like this suffices:

diff --git a/Koha/Database.pm b/Koha/Database.pm
index 12758bf..ef1e2db 100644
--- a/Koha/Database.pm
+++ b/Koha/Database.pm
@@ -55,7 +55,7 @@ sub _new_schema {
     my $db_passwd = $context->config("pass");
     my $schema    = Koha::Schema->connect(
         "DBI:$db_driver:dbname=$db_name;host=$db_host;port=$db_port",
-        $db_user, $db_passwd );
+        $db_user, $db_passwd, { mysql_enable_utf8 => 1 } );
     return $schema;
 }
 
This wouldn't be the final version of this patch, as we wouldn't want to pass msyql_enable_utf8 to a Pg database, and we would have to make sure that binary data (e.g., images stored in the database) don't get mangled, but I think something like this patch would be more reliable.
Comment 9 Galen Charlton 2014-03-26 20:47:43 UTC Comment hidden (obsolete)
Comment 10 Galen Charlton 2014-03-26 20:47:51 UTC Comment hidden (obsolete)
Comment 11 Chris Cormack 2014-03-26 21:10:47 UTC Comment hidden (obsolete)
Comment 12 Chris Cormack 2014-03-26 21:22:15 UTC Comment hidden (obsolete)
Comment 13 Jonathan Druart 2014-03-27 09:15:57 UTC
Galen,

We had this patch for bug 11944, so I completely agree with it! Actually it is not on bug 11944, it seems it disappears on rebasing/squashing patches :-/

I still have the patch on a local branch and the difference is:
1/ We added
     $schema->storage->on_connect_do('SET NAMES utf8');
in Koha::Database::_new_schema
Which is stupid! From the mysql doc: "if you turn the flag on after connecting, you will need to issue the command SET NAMES utf8 to get the same effect."

2/ We did not propose unit tests, so ++ :)

3/ We removed the Koha::Template::Plugin::EncodeUTF8 uses in circ/renew.pl
This plugin is now useless.
I will submit a followup on QAing, feel free to ask open a new specific report for it.

Marked as Passed QA.
Comment 14 Jonathan Druart 2014-03-27 09:16:52 UTC
Created attachment 26622 [details] [review]
Bug 11906: regression test for using DBIC to store & fetch UTF8 strings

This patch adds a regression test for verifying that a
DBIx::Class schema object initialized by Koha database sets up
the database connection to correct store and retrieve UTF8
values as Perl utf8 strings.

To test:

[1] Apply this patch.
[2] Run prove -v t/db_dependent/Koha_Database.t
[3] The test should fail.
[4] Apply the main patch for this bug, then do step 2 again.
[5] The test should pass.

Signed-off-by: Galen Charlton <gmc@esilibrary.com>
Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz>

Signed-off-by: Jonathan Druart <jonathan.druart@biblibre.com>
Comment 15 Jonathan Druart 2014-03-27 09:16:57 UTC
Created attachment 26623 [details] [review]
Bug 11906: ensure that Koha::Database uses UTF8 mode when connecting to databases

This patch fixes an issue whereby the DBIx::Class schema objects
were not connecting to the underlying database in UTF8 mode.  This
most visibility manifested as patron list pages not displaying
diacritics correctly.

To test:

[1] Create a patron list, and make sure that it contains at least
    one patron whose name or patron category description contains
    a non-ASCII character.
[2] View the list contents; the diacritics should appear mangled.
[3] Apply the patch.
[4] View the patron list again.  This time, the diacritics should
    be displayed correctly.  Note that Apache will also log
    "list.pl: Wide character in print ...", but this is the lesser
    of two evils.
[5] Verify that prove -v t/db_dependent/Koha_Database.t passes.
[6] (extra credit) Verify that  t/db_dependent/Koha_Database.t
    passes when connect to a PostgreSQL database.

Signed-off-by: Galen Charlton <gmc@esilibrary.com>
Signed-off-by: Chris Cormack <chrisc@catalyst.net.nz>

Signed-off-by: Jonathan Druart <jonathan.druart@biblibre.com>
Comment 16 Jonathan Druart 2014-03-27 09:17:02 UTC Comment hidden (obsolete)
Comment 17 Jonathan Druart 2014-03-27 09:48:31 UTC
(In reply to Jonathan Druart from comment #13)
> 3/ We removed the Koha::Template::Plugin::EncodeUTF8 uses in circ/renew.pl
> This plugin is now useless.
> I will submit a followup on QAing, feel free to ask open a new specific
> report for it.

Forget that, it does not work with translated template (the ENCODING flag is not set yet to 'UTF-8' at the template level).
Comment 18 Galen Charlton 2014-03-27 14:45:07 UTC
Great minds, and all that.

Pushed to master.
Comment 19 Fridolin Somers 2014-06-18 15:59:13 UTC
Pushed to 3.14.x, will be in 3.14.08