[Bug 19967] New: Search patrons with non-Latin languages is brocken if DefaultPatronSearchFields includes dateofbirth
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19967 Bug ID: 19967 Summary: Search patrons with non-Latin languages is brocken if DefaultPatronSearchFields includes dateofbirth Change sponsored?: --- Product: Koha Version: 16.11 Hardware: All OS: All Status: NEW Severity: normal Priority: P5 - low Component: Patrons Assignee: koha-bugs@lists.koha-community.org Reporter: ulrich.kleiber@bsz-bw.de QA Contact: testopia@bugs.koha-community.org CC: gmcharlt@gmail.com, kyle.m.hall@gmail.com 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; } -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19967 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |katrin.fischer@bsz-bw.de Summary|Search patrons with |Search patrons with |non-Latin languages is |non-Latin languages is |brocken if |broken if |DefaultPatronSearchFields |DefaultPatronSearchFields |includes dateofbirth |includes dateofbirth -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19967 --- Comment #1 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- 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 -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19967 --- Comment #2 from Ulrich Kleiber <ulrich.kleiber@bsz-bw.de> --- Created attachment 70490 --> https://bugs.koha-community.org/bugzilla3/attachment.cgi?id=70490&action=edit 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. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19967 Jonathan Druart <jonathan.druart@bugs.koha-community.org> changed: What |Removed |Added ---------------------------------------------------------------------------- CC| |jonathan.druart@bugs.koha-c | |ommunity.org --- Comment #3 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- Ulrich, you are using a MySQLism, you should use Koha::Objects->columns instead. -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19967 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Summary|Search patrons with |Search patrons with |non-Latin languages is |non-Latin languages is |broken if |broken if |DefaultPatronSearchFields |DefaultPatronSearchFields |includes dateofbirth |includes date or datetime | |field -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19967 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Severity|normal |major -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19967 --- Comment #4 from Ulrich Kleiber <ulrich.kleiber@bsz-bw.de> --- 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. -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19967 --- Comment #5 from Jonathan Druart <jonathan.druart@bugs.koha-community.org> --- 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? -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19967 Katrin Fischer <katrin.fischer@bsz-bw.de> changed: What |Removed |Added ---------------------------------------------------------------------------- Resolution|--- |WORKSFORME Status|NEW |RESOLVED --- Comment #6 from Katrin Fischer <katrin.fischer@bsz-bw.de> --- We have retested this in our test installationf or 20.05 and can no longer replicate the problem. Closing WORKSFORME! -- You are receiving this mail because: You are the assignee for the bug. You are watching all bug changes.
https://bugs.koha-community.org/bugzilla3/show_bug.cgi?id=19967 --- Comment #7 from Ulrich Kleiber <ulrich.kleiber@bsz-bw.de> --- We have completely switched to MariaDB -- You are receiving this mail because: You are watching all bug changes. You are the assignee for the bug.
participants (1)
-
bugzilla-daemon＠bugs.koha-community.org