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; }
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
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.
Ulrich, you are using a MySQLism, you should use Koha::Objects->columns instead.
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.
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?
We have retested this in our test installationf or 20.05 and can no longer replicate the problem. Closing WORKSFORME!
We have completely switched to MariaDB