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

Symptons

By default the order of nodes by name take in consideration the accent, see the sample below:

Default order:

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

Expected order:

• MADRID
• MÁLAGA ( althought accent keep the order )
• MIRASIERRA

Cause

Because by default openkm keep 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 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