Wrong order in the list of nodes because of accents in the MySQL or MariaDB database

Symptoms

By default, the order of nodes by name takes the accent into consideration; see the sample below:

Default order:

• MADRID
• MIRASIERRA
• MÁLAGA (because of the accent)

Expected order:

• MADRID
• MÁLAGA (although the accent keeps the order)
• MIRASIERRA

Cause

Because by default OpenKM keeps data in "utf8_bin" collation.

Resolution

Change the collation of the column NBS_NAME in the table OKM_NODE_BASE to utf8mb4_general_ci. With the utf8mb4_general_ci collation, the SQL engine will order in the expected manner.

ALTER TABLE OKM_NODE_BASE MODIFY NBS_NAME VARCHAR(256) CHARACTER SET utf8_bin COLLATE utf8mb4_general_ci;

Properties

Keywords