Bug 18686

Summary: TalkingTech_itiva_outbound.pl fails with Illegal mix of collations
Product: Koha Reporter: Larry Baerveldt <larry>
Component: DatabaseAssignee: Galen Charlton <gmcharlt>
Status: CLOSED WORKSFORME QA Contact: Testopia <testopia>
Severity: normal    
Priority: P5 - low CC: kyle, wizzyrea
Version: unspecified   
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:

Description Larry Baerveldt 2017-05-26 22:20:23 UTC
In the big bug fix to clean up UTF8 (bug 11944), the default collation was set to utf8_unicode_ci. However on sites that predate that change, the overduerules table may still use utf8_general_ci. This leads to the following error when running the cronjob TalkingTech_itiva_outbound.pl:

DBD::mysql::st execute failed: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' [for Statement "SELECT borrowers.borrowernumber, borrowers.cardnumber, borrowers.title as patron_title, borrowers.firstname, borrowers.surname,
.... [snip] ....
"] at /usr/share/koha/bin/cronjobs/thirdparty/TalkingTech_itiva_outbound.pl line 230.

The problem is fixed manually by altering table overduerules and changing its collate setting. However, perhaps there should be a fix in updatedatabase.pl to change the collate if the table is still using the old value.
Comment 1 Larry Baerveldt 2017-05-26 22:26:26 UTC
Here is the full error message:

DBD::mysql::st execute failed: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation '=' [for Statement "SELECT borrowers.borrowernumber, borrowers.cardnumber, borrowers.title as patron_title, borrowers.firstname, borrowers.surname,
                borrowers.phone, borrowers.email, borrowers.branchcode, biblio.biblionumber, biblio.title, items.barcode, issues.date_due,
                max(overduerules.branchcode) as rulebranch, TO_DAYS(NOW())-TO_DAYS(date_due) as daysoverdue, delay1, delay2, delay3,
                issues.branchcode as site, branches.branchname as site_name
                FROM borrowers JOIN issues USING (borrowernumber)
                JOIN items USING (itemnumber)
                JOIN biblio USING (biblionumber)
                JOIN branches ON (issues.branchcode = branches.branchcode)
                JOIN overduerules USING (categorycode)
                WHERE ( overduerules.branchcode = borrowers.branchcode or overduerules.branchcode = '')
                AND ( (TO_DAYS(NOW())-TO_DAYS(date_due) ) = delay1
                  OR  (TO_DAYS(NOW())-TO_DAYS(date_due) ) = delay2
                  OR  (TO_DAYS(NOW())-TO_DAYS(date_due) ) = delay3 )
                GROUP BY items.itemnumber
                "] at /usr/share/koha/bin/cronjobs/thirdparty/TalkingTech_itiva_outbound.pl line 230.
Comment 2 Katrin Fischer 2018-08-12 21:39:19 UTC
I believe this should be fixed, Kyle, can you maybe confirm?
Comment 3 Liz Rea 2023-04-24 21:11:23 UTC
No longer seeing these messages, safe to close!