Bug 19967

Summary: Search patrons with non-Latin languages is broken if DefaultPatronSearchFields includes date or datetime field
Product: Koha Reporter: Ulrich Kleiber <ulrich.kleiber>
Component: PatronsAssignee: Bugs List <koha-bugs>
Status: CLOSED WORKSFORME QA Contact: Testopia <testopia>
Severity: major    
Priority: P5 - low CC: gmcharlt, jonathan.druart, kyle
Version: 16.11   
Hardware: All   
OS: All   
GIT URL: Change sponsored?: ---
Patch complexity: --- Documentation contact:
Documentation submission: Text to go in the release notes:
Version(s) released in:
Circulation function:
Attachments: Bug 19967: Search patrons with non-Latin languages is brocken.

Description Ulrich Kleiber 2018-01-12 16:21:41 UTC
In this particular case you get the mysql error message: 

Illegal mix of collations for operation 'like'

You can prevent this error by removing dateofbirth from DefaultPatronSearchFields or by the following patch:

diff --git a/C4/Utils/DataTables/Members.pm b/C4/Utils/DataTables/Members.pm
index 2446703..ba32a12 100644
--- a/C4/Utils/DataTables/Members.pm
+++ b/C4/Utils/DataTables/Members.pm
@@ -117,7 +117,12 @@ sub search {
 
         my @where_strs_or;
         for my $searchfield ( split /,/, $searchfields->{$searchfieldstype} ) {
-            push @where_strs_or, "borrowers." . $dbh->quote_identifier($searchfield) . " LIKE ?";
+            if ( $searchfield eq 'dateofbirth' ) {
+                push @where_strs_or, "DATE_FORMAT(borrowers." . $dbh->quote_identifier($searchfield) . ", '%Y-%m-%d') LIKE ?";
+            } else {
+                push @where_strs_or, "borrowers." . $dbh->quote_identifier($searchfield) . " LIKE ?";
+            }
+
             push @where_args, $term;
         }
Comment 1 Katrin Fischer 2018-01-12 17:30:30 UTC
I think we need to do the fix for all date fields, I just tried adding datexpiry and it breaks the search as well. (dateexpiry, dateenrolled, lastseen, updated_on..?)

To test:

- Configure DefaultPatronSearchFields including dateofbirth and surname
- Add a patron to your database with a cyrillig surname
- Try to search for the patron
- > keeps loading forever
Comment 2 Ulrich Kleiber 2018-01-15 15:30:42 UTC
Created attachment 70490 [details] [review]
Bug 19967: Search patrons with non-Latin languages is brocken.

If DefaultPatronSearchFields includes a column with type
date, datetime or timestamp, search patrons with non-Latin
languages hangis on 'Processing...'.
Converting columns with type date, datetime or timestamp to
type string eliminates this bug.
Comment 3 Jonathan Druart 2018-01-15 15:54:06 UTC
Ulrich, you are using a MySQLism, you should use Koha::Objects->columns instead.
Comment 4 Ulrich Kleiber 2018-01-17 13:04:57 UTC
Hi Jonathan, is it possible to get the data type of a column with Koha::Objects->columns?

Katrin mentioned, that you could not reproduce the error under MariaDB. Our MySQL Version is '14.14 Distrib 5.5.58, for debian-linux-gnu (x86_64)'. Perhaps the implicit type conversion has changed in MariaDB.
Comment 5 Jonathan Druart 2020-08-07 13:38:12 UTC
Sorry Ulrich, I forgot to answer you.

I've just retested and it works for me (I tried with lastseen).
Can you confirm this is fixed?
Comment 6 Katrin Fischer 2020-09-01 16:07:22 UTC
We have retested this in our test installationf or 20.05 and can no longer replicate the problem. Closing WORKSFORME!
Comment 7 Ulrich Kleiber 2020-09-02 05:43:16 UTC
We have completely switched to MariaDB