Bug 18686 - TalkingTech_itiva_outbound.pl fails with Illegal mix of collations
Summary: TalkingTech_itiva_outbound.pl fails with Illegal mix of collations
Status: CLOSED WORKSFORME
Alias: None
Product: Koha
Classification: Unclassified
Component: Database (show other bugs)
Version: unspecified
Hardware: All All
: P5 - low normal (vote)
Assignee: Galen Charlton
QA Contact: Testopia
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2017-05-26 22:20 UTC by Larry Baerveldt
Modified: 2023-12-28 20:42 UTC (History)
3 users (show)

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


Attachments

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