Errors on install/after upgrade of OJS instance with Ukrainian locale

[Vitaliy-1]

During the install of a fresh OJS 3.2 instance or after the upgrade to 3.2 I get problems related to database entries with Cyrillic script

To reproduce the problem during install:

1. Download OJS 3.2.0 release: https://pkp.sfu.ca/ojs/ojs_download/
2. Go to the installation form and enter data, check Ukrainian as a primary language; encoding utf-8
3. Run install.
   I receive:

DB Error: Incorrect string value: '\xD0\x9D\xD0\xBE\xD0\xB2...' for column 'subject' at row 1 Query: INSERT INTO email_templates_default_data (email_key, locale, subject, body, description) VALUES ('NOTIFICATION', 'uk_UA', 'Нове повідомлення від сайту {$siteTitle}', 'Ви маєте нове повідомлення від сайту {$siteTitle}:<br />\n<br />\n{$notificationContents}<br />\n<br />\nПосилання: {$url}<br />\n<br />\n{$principalContactSignature}', 'Цей лист надсилається зареєстрованому користувачу, який обрав опцію отримання повідомлень цього типу електронною поштою.')

Another problem that I think is related appears after the upgrade of test OJS instance that uses Ukrainian locale. Upgrade process runs without problems but when I try access submissions in the dashboard I recieve:

[02-Mar-2020 12:58:31 UTC] Slim Application Error:
Type: RuntimeException
Code: 5
Message: Malformed UTF-8 characters, possibly incorrectly encoded
File: /var/www/ojs-3.2.0/lib/pkp/lib/vendor/slim/slim/Slim/Http/Response.php
Line: 356
Trace: #0 /var/www/ojs-3.2.0/lib/pkp/api/v1/_submissions/PKPBackendSubmissionsHandler.inc.php(166): Slim\Http\Response->withJson(Array)
#1 [internal function]: PKPBackendSubmissionsHandler->getMany(Object(Slim\Http\Request), Object(APIResponse), Array)
#2 /var/www/ojs-3.2.0/lib/pkp/lib/vendor/slim/slim/Slim/Handlers/Strategies/RequestResponse.php(40): call_user_func(Array, Object(Slim\Http\Request), Object(APIResponse), Array)
#3 /var/www/ojs-3.2.0/lib/pkp/lib/vendor/slim/slim/Slim/Route.php(281): Slim\Handlers\Strategies\RequestResponse->__invoke(Array, Object(Slim\Http\Request), Object(APIResponse), Array)
#4 /var/www/ojs-3.2.0/lib/pkp/lib/vendor/slim/slim/Slim/MiddlewareAwareTrait.php(117): Slim\Route->__invoke(Object(Slim\Http\Request), Object(APIResponse))
#5 /var/www/ojs-3.2.0/lib/pkp/lib/vendor/slim/slim/Slim/Route.php(268): Slim\Route->callMiddlewareStack(Object(Slim\Http\Request), Object(APIResponse))
#6 /var/www/ojs-3.2.0/lib/pkp/lib/vendor/slim/slim/Slim/App.php(503): Slim\Route->run(Object(Slim\Http\Request), Object(APIResponse))
#7 /var/www/ojs-3.2.0/lib/pkp/classes/security/authorization/internal/ApiAuthorizationMiddleware.inc.php(76): Slim\App->__invoke(Object(Slim\Http\Request), Object(APIResponse))
#8 [internal function]: ApiAuthorizationMiddleware->__invoke(Object(Slim\Http\Request), Object(APIResponse), Object(Slim\App))

Checking PKPBackendSubmissionsHandler::getMany I noticed that in $data all Ukrainian fields have improper encoding, e.g.:

[fullTitle] => Array
                                        (
                                            [en_US] => Interrelation of neuroticism with PTSD in combatants
                                            [uk_UA] => ????????�???? ?????????? ? ????????????????? ?????????? ????????? ? ????????? ??????? ???
                                        )

Although they seem to be properly encoded in the database.
executing SELECT @@character_set_database, @@collation_database gives utf8mb4 | utf8mb4_general_ci

mysql Ver 14.14 Distrib 5.7.29, for Linux (x86_64)
mysqld Ver 5.7.29-0ubuntu0.18.04.1 for Linux on x86_64

[NateWr]

Can you provide us with some sample Cyrillic to use?

[Vitaliy-1]

E.g., the title that fails to be recognized like utf-8 looks:

Взаємозв’язок нейротизму з посттравматичними стресовими розладами в учасників бойових дій

I was able to install OJS with Ukrainian locale through command line and the same title is encoded in the database as:

Взаємозв’�зок нейротизму з по�ттравматичними �тре�овими розладами в уча�ників бойових дій

(I've run submission with the same title though the workflow)

[NateWr]

I was unable to reproduce this. However, I wasn't working from the package download. Here's what I did:

1. With a latest master checkout of OJS, I created a new database and performed an installation, during which I installed Ukranian language.
2. After installation I made a submission with the title and abstract, copied from your comment.
3. Then I went to the submissions list and the submission appeared correctly in My Queue and All Active.

Since the All Active list is lazy loaded, that would have loaded from the API. I suspect there's a database issue on your end, but I'm not good enough with character sets to know what that might be.

I'm out of time this afternoon, but if you want, I can try out an install direct from the downloadable package tomorrow.

[NateWr]

Oh, one other thing: I didn't use Ukraine as primary language. I used it as an additional language. I'll try one real quick with Ukraine as primary language.

[Vitaliy-1]

I've encountered the problem only with the package download. The same test instance was upgraded normally 2 days ago to master branch

[Vitaliy-1]

let me check master

[NateWr]

(Same when using Ukraine as primary language.)

[asmecher]

@Vitaliy-1, could this be a side-effect of the utf8mb4 tweaks you're working with? I remember we discussed this a month or so ago, but can't find the link.

[NateWr]

I just tested installing from the package and the characters came out ok for me.

[Vitaliy-1]

@asmecher, have no idea.
Thanks Nate, thus the problem is on my side. The issue can be closed, I'll open it if find something useful.

[Vitaliy-1]

It seems that I found the problem. Connection character set isn't updated for me in the config file. When I performing install it's set to unicode (UTF-8) in the form but after installation in config I see that connection_charset is set to off, thus during installation I suppose it fallbacks to the default, which in mysql isn't utf8. If set to utf8 manually - it works as expected. I'm wondering why. @NateWr, it doesn't affect your test installs, right?

[Vitaliy-1]

Ahh, I see, databaseCharset was removed in OJS 3.2, thus, when trying to install OJS by default latin1_swedish_ci by MySQL is used, this causes error for non-latin scripts. If I'm being right, might this be inconvenient for other users as well?

[asmecher]

If I recall correctly, @Vitaliy-1, the database_charset option was a non-standard extension we added to ADODB more than 10 years ago; when we moved to a newer version of ADODB (and removed our modifications) it seemed like the defaults had all become UTF8-safe (where they used to be Latin1). I wonder if your system is unusual?

[asmecher]

This has been reported elsewhere as fixed by creating the database with DEFAULT CHARACTER SET utf8: https://forum.pkp.sfu.ca/t/multiple-languages-in-ojs3-2/59245

[tsvetlin]

Same issue with Hungarian. Creating database beforehand is a workaround.