Uncaught Exception: DB Error: Illegal mix of collations - Problem with HTML Viewer


I’m having some troubles with HTML viewer. Here is the log:

[Fri Oct 09 19:03:52.295405 2020] [php7:error] [pid 29284] [client] PHP Fatal error:  Uncaught Exception: DB Error: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' Query: SELECT\ti.*\n\t\t\tFROM\tissues i,\n\t\t\t\tsubmissions a\n\t\t\tLEFT JOIN publications p ON (p.publication_id = a.current_publication_id)\n\t\t\tLEFT JOIN publication_settings ps ON (ps.publication_id = p.publication_id)\n\t\t\tWHERE\tps.setting_name = ? AND ps.setting_value = CAST(i.issue_id as CHAR) AND\n\t\t\t\ta.submission_id = ? AND\n\t\t\t\ta.context_id = i.journal_id in /var/www/html/ojs/lib/pkp/classes/db/DAO.inc.php:703\nStack trace:\n#0 /var/www/html/ojs/lib/pkp/classes/db/DAO.inc.php(103): DAO->handleError(Object(ADODB_mysqli), 'SELECT\\ti.*\\n\\t\\t\\tF...')\n#1 /var/www/html/ojs/classes/issue/IssueDAO.inc.php(569): DAO->retrieve('SELECT\\ti.*\\n\\t\\t\\tF...', Array)\n#2 /var/www/html/ojs/plugins/generic/htmlArticleGalley/HtmlArticleGalleyPlugin.inc.php(180): IssueDAO->getBySubmissionId(75)\n#3 /var/www/html/ojs/plugins/generic/htmlArticleGalley/HtmlArticleGalleyPlugin.inc.php(101): HtmlArticleGalleyPlugin- in /var/www/html/ojs/lib/pkp/classes/db/DAO.inc.php on line 703, referer: http://.../index.php/revista/article/view/75/45

If I use the same value on config.inc.php, HTML viewer loads correctly, but every special character in the site crashes.

Although, if I use differents value, eg utf8 / utf-8, all website has no character problem but HTML viewer returns log error.

Eg. 1 - website character ok / html viewer dont load:

client_charset = utf-8
connection_charset = utf8

Eg. 2 - website character crashes / html viewer loads ok:

client_charset = utf-8
connection_charset = utf-8

PHP: 7.2

Any idea how can I solve it?

Hi @geniusdesign,

Search this forum for the message Illegal mix of collations and you’ll find a couple of threads that walk you through solving the issue. Don’t “correct” connection_charset to utf8 – it needs to be utf-8 and the inconsistency is due to the requirements of 3rd-party libraries that those settings configure.

Alec Smecher
Public Knowledge Project Team

Hi @asmecher,

I’ve read this issue Database error: Illegal mix of collations · Issue #6045 · pkp/pkp-lib · GitHub, applied this solution pkp/pkp-lib#6045 Add configuration parameter for DB collation · pkp/pkp-lib@c6f1b4c · GitHub (despite this file didn’t exist, I’ve created it), edited config.inc.php with collation but didn’t solve my problem.

My OJS version is 3.2.1-1, so collation information already existed in config.inc.php file. I1ve tried many things, but no success.

If I change connection_charset to utf8 administrator didn’t loads: Malformed UTF-8 characters, possibly incorrectly encoded, referer: I don’t know which 3rd-party libraries could cause this problem. I don’t know what to do.