I need to work with a database encoding in utf8mb4, so, can I config OJS to work with encoding in utf8mb4?
Hi @murilodbva,
OJS’s front-end uses UTF8, so only UTF8 characters will be stored in the database. UTF8MB4 is a superset of UTF8, so I don’t think OJS will see any difference. If your database connection in config.inc.php is configured as UTF8, and if you add content to the database that is UTF8MB4 but not UTF8, I believe the database will attempt to transcode down to UTF8.
Long story short: I haven’t tried this, but you should be OK.
Regards,
Alec Smecher
Public Knowledge Project Team
1 Like
(I hope it’s okay to answer to this slightly older thread)
@asmecher: “Long story short: I haven’t tried this, but you should be OK.”
Unfortunately, at least for me this does not seem to be the case. I set up some journals with utf8mb4 as the default encoding because to the best of my knowledge this seems to be best practice these days (mysql’s utf8 is actually not really utf8). When i upgrade from OJS 3.1 to 3.2, however, i get this:
"[version: 3.2.0.3]
[code: Installer Installer::checkPhpVersion]
[code: Installer Installer::migrateSubmissionCoverImages]
[data: dbscripts/xml/upgrade/3.2.0_preupdate_email_templates.xml]
[data: dbscripts/xml/upgrade/3.2.0_preupdate_versioning_articleGalleySettings.xml (skipped)]
[data: dbscripts/xml/upgrade/3.2.0_preupdate_versioning.xml]
[data: dbscripts/xml/upgrade/3.2.0_preupdate_last_activity.xml]
[schema: lib/pkp/xml/schema/common.xml]
ERROR: Upgrade failed: DB: Specified key was too long; max key length is 767 bytes
"
There seem to be solutions (refer eg. mysql - #1071 - Specified key was too long; max key length is 767 bytes - Stack Overflow) but these would, if i understand it correctly, required changes to the OJS db schema.
Thus, at the moment i am stuck with a couple of journals that can not be updated to OJS 3.2 because the database breaks. So, i wanted to ask if, by any chance, someone has a hint for me on how to solve this situation.
Many thanks!
1 Like
Hi @thf,
The other solution is to upgrade to MySQL 5.7 or newer, where the limitation has been increased to from 767 bytes to 3072 bytes according to the link above. I suspect one of the motivations behind this is the increased use of UTF8MB4.
Regards,
Alec Smecher
Public Knowledge Project Team
Hello @asmecher,
thanks for your response! Updating MySQL/mariadb would probably be the best solution but unfortunately this is not feasible for the foreseeable future since i have to work with the default mariadb of the LTS OS we use. So at the moment it seems like i have to convert the databases one by one to utf8.
Hi @thf,
I do suspect avoiding UTF8MB4 with older MySQL/MariaDB is probably the best solution in the meantime. You might be able to use mysqldump and iconv to convert a whole DB without having to treat each table separately, if that’s what you’re currently doing.
Regards,
Alec Smecher
Public Knowledge Project Team
Hello,
thank you for your suggestion. For now, all i did was setting up the database anew with utf8 as charset/collation and then reimport a dump without any modifications. This actually worked (or at least it appeared so) but i only tested this for an OJS instance with barely any content. But i have also some OJS installations with a lot more content and for those it is probably better to do the conversion you suggested just to be sure (esp. since encoding errors easily can go unnoticed).
Thanks!
1 Like
Hi Alec,
Several shared-hosting providers are still on MySQL 5.6, including Hostgator and GoDaddy. As a consequence, I am having a problem restoring the OJS database (error #1071 - Specified key was too long). Any pointers?