Resolving Database Collation & UTF-8 Encoding Issues in OJS

Academic research is inherently international. Abstracts, author names, and citation references frequently include German umlauts, French accents, Cyrillic typography, Arabic scripts, and complex mathematical formulas.

In older versions of OJS (such as 2.4.x and early 3.x), default installations often established MySQL databases with latin1_swedish_ci or incomplete 3-byte utf8 (utf8mb3). When modern Unicode characters were stored inside latin1 columns, MySQL either silently truncated the strings or stored them as double-encoded mojibake (e.g., displaying "é" instead of "é").

To inspect the current status of your database, run the following diagnostic SQL queries in MariaDB/MySQL:

FROM information_schema.tables

WHERE table_schema = DATABASE()

GROUP BY table_collation, table_name;

SELECT table_name, column_name, character_set_name, collation_name

FROM information_schema.columns

WHERE table_schema = DATABASE()

AND data_type IN ('varchar', 'text', 'longtext', 'mediumtext')

AND collation_name != 'utf8mb4_unicode_ci';

Converting hundreds of tables manually is error-prone. You can dynamically generate conversion statements directly from the MySQL information_schema:

SELECT CONCAT('ALTER TABLE `', table_name, '` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;')

FROM information_schema.tables

WHERE table_schema = DATABASE()

AND table_type = 'BASE TABLE';

Export these statements, review them for any custom views, and execute them within a single administrative transaction.

Once the database engine and tables are unified under utf8mb4, OJS must be instructed to communicate with MariaDB using the matching charset.

[i18n]

client_charset = "utf-8"

connection_charset = "utf8mb4"

database_charset = "utf8mb4"

In OJS 3.4 and 3.5, the database driver uses modern PDO/MySQLi parameters:

[database]

collation = "utf8mb4_unicode_ci"

On older MySQL 5.7 instances, converting a VARCHAR(255) column to utf8mb4 can exceed the 767-byte InnoDB prefix limit (255 chars * 4 bytes = 1020 bytes). Ensure innodb_large_prefix is enabled or upgrade the database to MariaDB 10.6+ / MySQL 8.0 where the limit is natively 3072 bytes.