I updated my OJs for the 3.2.1-1 version. After that my search tool isn’t work.
Here my log:
[Wed Jul 01 12:56:38.383951 2020] [proxy_fcgi:error] [pid 8037] [client 201.17.157.228:12590] AH01071: Got error ‘PHP message: PHP Fatal error: Uncaught Exception: DB Error: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=’ Query: SELECT\n\t\t\t\to.submission_id,\n\t\t\t\tMAX(s.context_id) AS journal_id,\n\t\t\t\tMAX(i.date_published) AS i_pub,\n\t\t\t\tMAX(p.date_published) AS s_pub,\n\t\t\t\tCOUNT(*) AS count\n\t\t\tFROM\n\t\t\t\tsubmissions s\n\t\t\t\tJOIN publications p ON (p.publication_id = s.current_publication_id)\n\t\t\t\tJOIN publication_settings ps ON (ps.publication_id = p.publication_id AND ps.setting_name=‘issueId’)\n\t\t\t\tJOIN issues i ON (CAST(i.issue_id AS CHAR) = ps.setting_value)\n\t\t\t\tJOIN submission_search_objects o ON (s.submission_id = o.submission_id)\n\t\t\t\tNATURAL JOIN submission_search_object_keywords o0 NATURAL JOIN submission_search_keyword_list k0\n\t\t\tWHERE\n\t\t\t\ts.status = 3 AND\n\t\t\t\ti.published = 1 AND k0.keyword_text = ? AND i.journal_id = ?\n\t\t\tGROUP BY o.submission_id\n\t\t\tORDER BY count DESC\n\t\t\tLIMIT 500 in /home/revistas/public_html/lib/pkp/classes/db/DAO.inc.php:703\nStack trace:\n#0 /ho…\n’, referer: https://revistas.ufmg.br/index.php/mtr
Can you help me with this? Because I could change my tables collation, but before the last update it was working with utf8 general_ci. This change for utf8 Unicode can be changed in the configuration?
All the tables are in utf8_general_ci. I used this query to discover what type of charset are used in the columns os this tables:
SELECT column_name, character_set_name FROM information_schema.COLUMNS
WHERE table_name in (“submissions”, “publication_settings”, “publications”, “issues”, “submission_search_objects”, “submission_search_object_keywords”, “submission_search_keyword_list”)
Some columns are with utf8 and others with null.
I’ll recover my database before the update to understand if it always had this nulls fields or if the ocurr after the update.
We upgraded OJS from 2.4.8 to 3.2.1.2. All was working fine except the search tool that was broken (HTTP ERROR 500).
We checked the log file and we found PHP Fatal Error related to the type of UTF8 in the database (attached). So we tried to solve it by applying collation utf_general_ci in all tables of the database. (some tables had utf_unicode_ci). Apparently, it solved the problem and it is working fine. The question is, do you think it is a good solution to apply in the production? (we applied it only in a dummy copy space). We are not sure that it can affect the other functions although we now can not see any strange. @asmecher what do you think? How could we check if all functions are working fine?
Thanks in advance. We really appreciate
PHP Fatal error: Uncaught Exception: DB Error: Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation ‘=’ Query: SELECT GREATEST(a.last_modified, i.last_modified) AS last_modified,
a.submission_id AS submission_id,
j.journal_id AS journal_id,
s.section_id AS section_id,
i.issue_id,
NULL AS tombstone_id,
NULL AS set_spec,
NULL AS oai_identifier
FROM
submissions a
JOIN publications p ON (a.current_publication_id = p.publication_id)
JOIN publication_settings psissue ON (psissue.publication_id = p.publication_id AND psissue.setting_name=‘issueId’)
JOIN issues i ON (CAST(i.issue_id AS CHAR) = psissue.setting_value)
JOIN sections s ON (s.section_id = p.section_id)
JOIN journals j ON (j.journal_id = a.context_id)
JOIN journal_settings jsoai ON (jsoai.journal_id = j.journal_id AND jsoai.setting_name=? AND jsoai.setting_value=‘1’)
WHERE i.published = 1 AND j.enabled = 1 AND a.status = ?
AND j.journal_id = ?
AND p.section_ in /home/…/lib/pkp/classes/db/DAO.inc.php on line 703