Issue with encoding from mysql to MariaDB , from ojs 3.2 to 3.3.0.14

Software Support
, , ,
1

Hello, I share with you a problem I had during a migration from ojs 3.2 to ojs 3.3.0.14 and especially from mysql to MariaDB:
Everything went well until I redo a php tools/rebuildSearchIndex.php, an error appeared if an article included an Asian character or other special character.

  • Check that the MariaDB instance is configured in utf8mb4 (by default the character set utf8 is mapped to utf8mb3, which is not the case with recent versions of mysql):
[mysqld]
character-set-client=utf8mb4
character-set-server=utf8mb4
[mysql]
default-character-set=utf8mb4
[client]
default-character-set=utf8mb4

  • Create the database:
    CREATE DATABASE ojs CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
    (Check the encoding with the sql command:
    SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';)

  • If you need to import a sql dump, do the dump like this:
    mysqldump -h localhost -u ojs -pz4q7K9HhKPRfUvshHMW6 -c -e --default-character-set=utf8mb4 --single-transaction --skip-set-charset --add-drop-database -B ojs > dump.sql
    Then fix it under vim with the following commands:

%s/utf8mb3/utf8mb4/
%s/utf8mb3_general_ci/utf8mb4_general_ci/
%s/utf8mb3_bin/utf8mb4_bin/

  • Then do your import into the new database.
    You can check the collation like this:
    select TABLE_NAME,COLUMN_NAME,COLLATION_NAME from information_schema.columns where table_schema = 'ojs' order by table_name,ordinal_position;

  • On the php side, correct the config.inc.php to have these lines:

...
[database]
; Database snack
collation = utf8mb4_general_ci
...
[i18n]
...
connection_charset = utf8mb4

…and voila :wink: