Bug 19967 - Search patrons with non-Latin languages is broken if DefaultPatronSearchFields includes date or datetime field
Summary: Search patrons with non-Latin languages is broken if DefaultPatronSearchField...
Status: CLOSED WORKSFORME
Alias: None
Product: Koha
Classification: Unclassified
Component: Patrons (show other bugs)
Version: 16.11
Hardware: All All
: P5 - low major (vote)
Assignee: Bugs List
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2018-01-12 16:21 UTC by Ulrich Kleiber
Modified: 2021-06-14 21:28 UTC (History)
4 users (show)

See Also:
Change sponsored?: ---
Patch complexity: ---
Documentation contact:
Documentation submission:
Text to go in the release notes:
Version(s) released in:


Attachments
Bug 19967: Search patrons with non-Latin languages is brocken. (1.88 KB, patch)
2018-01-15 15:30 UTC, Ulrich Kleiber
Details | Diff | Splinter Review

Note You need to log in before you can comment on or make changes to this bug.
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