Illegal mix of collations when assigning article to issue (OJS 3.2.1-2)

Software Support
1

OJS 3.2.1-2
PHP 7.3.25
DB pilot mysqli
DB version 10.0.35-MariaDB-wsrep

Greetings,

One of our journals report being unable to assign a publication to an issue.

  1. When assigning an article to an issue, we get this message :
    The publication’s issue details have been updated.

  2. When scheduling the article for publication, we get this message :
    All publication requirements have been met. This will be published when Vol. 32 No. 2 (2019) is published. Are you sure you want to schedule this for publication?

  3. However, when clicking to confirm, we get this message:
    An unexpected error occurred. You may have been logged out. Please reload the page and try again.

Everytime the button Schedule for Publication is clicked, the two following errors are logged :

[Tue Dec 15 10:57:00.116185 2020] [php7:notice] [pid 26543] [client 132.208.244.19:45232] Slim Application Error:\nType: Exception\nMessage: DB Error: Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation ‘=’ Query: SELECT\ti.*\n\t\ (+ around 50 more lines)

“PUT /rqdi/api/v1/submissions/706/publications/656/publish HTTP/1.1” 500 445

After reading several similar cases on the forum, I decided to upgrade from 3.2.1 to 3.2.1-2 in order to integrate #6045, and I made sure to set config.inc.php to utf8_unicode_ci to match our database parameters (see below).

However, I might be misinterpreting those parameters, since the upgrade hasn’t fixed our problem. Is there something I’m missing?

collation_connection | utf8_general_ci
collation_database | utf8_unicode_ci
collation_server | latin1_swedish_ci |
character_set_client | utf8
character_set_connection | utf8
character_set_database | utf8
character_set_filesystem | binary
character_set_results | utf8
character_set_server | latin1
character_set_system | utf8
character_sets_dir | /usr/share/mysql/charsets/

Update

I discussed the matter with our dbadmin, and he noticed the tables were in utf8_general_ci while the db itself was in utf8_unicode_ci. He changed the db to utf8_general_ci so both parameters would match, and I updated config.inc.php – but I we still have the same bug.

2

Problem fixed. The database had the right collation, but not some tables.

When a database is created, a defaut collation can be specified a every level: database, tables or columns. If no value is given, each entity inherits the value of its parent. It appears that some of our tables (and only some, which is weird) had a default collation value, so changing the database default collation was having no effect of those tables.

I changed the default collation of those tables to match the database, et voilà.