Fix MariaDB collation

Relution expects the character set utf8mb4 with the collation utf8mb4_general_ci for all tables and text columns in a MariaDB database. If individual tables, columns or the default collation of the database deviate from this, individual operations abort with database errors. Unlike the older character set utf8 (utf8mb3), utf8mb4 also stores four-byte characters, e.g. emojis.

This guide only applies to installations with MariaDB. The scripts cannot be used for PostgreSQL databases. The supported databases are listed under System requirements →.

Symptoms

A deviating collation is often first noticed with Automated Device Enrollment (ADE, DEP in the portal): the device synchronization fails for all organizations, while all other operations continue normally. With every synchronization, a message like the following appears in the Relution log:

Internal Exception: java.sql.SQLException: Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='
Error Code: 1267
Call: DELETE FROM `mdm_auto_enrllmnt_dflt_plcy` USING `mdm_auto_enrllmnt_dflt_plcy`, `TL_mdm_auto_enrllmnt` WHERE `mdm_auto_enrllmnt_dflt_plcy`.`auto_enrollment_uuid` = `TL_mdm_auto_enrllmnt`.`uuid`

The collations shown may differ depending on the installation, e.g. utf8mb4_uca1400_ai_ci instead of utf8mb4_unicode_ci. The same error can also occur with other bulk changes, e.g. when deleting a user. The location of the log files is described under Relution Logs →.

Cause

Relution creates its tables without an explicit collation. When created, they take on the default collation of the database. The Relution database connection uses utf8mb4_general_ci.

For bulk changes, such as deleting several device enrollments during the ADE synchronization, Relution creates temporary helper tables with the prefix TL_, which persist for the duration of the respective database connection. These tables also take on the default collation of the database. If it deviates from the collation of the actual tables, MariaDB cannot compare the columns with each other and aborts the query. The stored data is not affected.

Deviations typically arise in the following cases:

The database was created on a MariaDB server without collation-server=utf8mb4_general_ci. Without explicit configuration, newer MariaDB versions use a different default collation, and tables from later Relution updates then also receive this collation.
The tables were converted to utf8mb4_general_ci afterwards, but the default collation of the database itself was not.
The database was transferred to a new server or a new container via dump. The dump restores the tables with their original collation, but the newly created database itself receives the default of the new server.
The database originates from an older installation with the character set utf8 and was never converted.

Scripts

Checking and correcting take place in two steps. The script generate_mariadb_utf8mb4_conversion.sql does not change anything in the database itself. It checks the database, tables and columns and uses the result to generate the script convert_utf8mb4.sql, which only contains statements for objects that actually deviate:

The default character set and default collation of the database are aligned if they deviate.
Affected tables are converted to utf8mb4 with utf8mb4_general_ci and, if necessary, to InnoDB with the row format DYNAMIC.
Foreign keys that originate from an affected table or reference it are removed before the conversion and recreated unchanged afterwards.

Tables that are already correct remain unchanged.

The scripts are designed for MariaDB 10.11 LTS and newer. The generator only includes the statement SET SESSION character_set_collations if the MariaDB version supports it (11.2 and later). The generator and the generated script are therefore run on the same MariaDB server.

If this guide is viewed as a PDF file, quotation marks may have been replaced with their typographic (curly) versions. This makes the syntax of the script incorrect. The download links or the HTML version of this document are therefore used for the scripts.

Generator script

Download generate_mariadb_utf8mb4_conversion.sql →

generate_mariadb_utf8mb4_conversion.sql

SET @db = 'relution';

-- Base tables whose default charset/collation or any character column deviates from utf8mb4/utf8mb4_general_ci
WITH conv AS (
  SELECT t.TABLE_NAME
  FROM information_schema.TABLES t
  WHERE t.TABLE_SCHEMA = @db AND t.TABLE_TYPE = 'BASE TABLE'
    AND (t.TABLE_COLLATION <> 'utf8mb4_general_ci'
         OR EXISTS (SELECT 1
                    FROM information_schema.COLUMNS c
                    WHERE c.TABLE_SCHEMA = t.TABLE_SCHEMA
                      AND c.TABLE_NAME = t.TABLE_NAME
                      AND c.CHARACTER_SET_NAME IS NOT NULL
                      AND (c.CHARACTER_SET_NAME <> 'utf8mb4'
                           OR c.COLLATION_NAME <> 'utf8mb4_general_ci')))
),
-- Foreign keys where the referencing or the referenced table gets converted
fk AS (
  SELECT rc.*
  FROM information_schema.REFERENTIAL_CONSTRAINTS rc
  WHERE rc.CONSTRAINT_SCHEMA = @db
    AND (rc.TABLE_NAME IN (SELECT TABLE_NAME FROM conv)
         OR (rc.UNIQUE_CONSTRAINT_SCHEMA = @db
             AND rc.REFERENCED_TABLE_NAME IN (SELECT TABLE_NAME FROM conv)))
)
SELECT _sql FROM (
  SELECT 1 AS step, '' AS tbl, '' AS obj, "SET SESSION innodb_strict_mode = OFF;" AS _sql
  UNION ALL
  -- Only on MariaDB 11.2+; older versions (e.g. 10.11 LTS) do not know this variable
  SELECT 2, '', '', "SET SESSION character_set_collations = 'utf8mb4=utf8mb4_general_ci';"
  FROM information_schema.SESSION_VARIABLES
  WHERE VARIABLE_NAME = 'CHARACTER_SET_COLLATIONS'
  UNION ALL
  SELECT 3, '', '', "SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci';"
  UNION ALL
  SELECT 4, '', '', "SET foreign_key_checks = 0;"
  UNION ALL
  SELECT 5, '', '', CONCAT("ALTER DATABASE `", s.SCHEMA_NAME, "` CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci;")
  FROM information_schema.SCHEMATA s
  WHERE s.SCHEMA_NAME = @db
    AND (s.DEFAULT_CHARACTER_SET_NAME <> 'utf8mb4' OR s.DEFAULT_COLLATION_NAME <> 'utf8mb4_general_ci')

  UNION ALL
  SELECT 6, fk.TABLE_NAME, fk.CONSTRAINT_NAME,
         CONCAT("ALTER TABLE `", fk.CONSTRAINT_SCHEMA, "`.`", fk.TABLE_NAME,
                "` DROP FOREIGN KEY `", fk.CONSTRAINT_NAME, "`;")
  FROM fk

  UNION ALL
  SELECT 7, t.TABLE_NAME, '',
         CONCAT("ALTER TABLE `", t.TABLE_SCHEMA, "`.`", t.TABLE_NAME,
                "` ENGINE=InnoDB ROW_FORMAT=DYNAMIC;")
  FROM information_schema.TABLES t
  JOIN conv ON conv.TABLE_NAME = t.TABLE_NAME
  WHERE t.TABLE_SCHEMA = @db
    AND (t.ENGINE <> 'InnoDB' OR t.ROW_FORMAT <> 'Dynamic')

  UNION ALL
  SELECT 8, conv.TABLE_NAME, '',
         CONCAT("ALTER TABLE `", @db, "`.`", conv.TABLE_NAME,
                "` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;")
  FROM conv

  UNION ALL
  SELECT 9, fk.TABLE_NAME, fk.CONSTRAINT_NAME,
         CONCAT("ALTER TABLE `", fk.CONSTRAINT_SCHEMA, "`.`", fk.TABLE_NAME,
                "` ADD CONSTRAINT `", fk.CONSTRAINT_NAME, "` FOREIGN KEY (",
                GROUP_CONCAT(CONCAT("`", k.COLUMN_NAME, "`") ORDER BY k.ORDINAL_POSITION SEPARATOR ", "),
                ") REFERENCES `", k.REFERENCED_TABLE_SCHEMA, "`.`", fk.REFERENCED_TABLE_NAME, "` (",
                GROUP_CONCAT(CONCAT("`", k.REFERENCED_COLUMN_NAME, "`") ORDER BY k.ORDINAL_POSITION SEPARATOR ", "),
                ") ON DELETE ", fk.DELETE_RULE, " ON UPDATE ", fk.UPDATE_RULE, ";")
  FROM fk
  JOIN information_schema.KEY_COLUMN_USAGE k
    ON k.CONSTRAINT_SCHEMA = fk.CONSTRAINT_SCHEMA
   AND k.TABLE_NAME = fk.TABLE_NAME
   AND k.CONSTRAINT_NAME = fk.CONSTRAINT_NAME
   AND k.REFERENCED_TABLE_NAME IS NOT NULL
  GROUP BY fk.CONSTRAINT_SCHEMA, fk.TABLE_NAME, fk.CONSTRAINT_NAME,
           k.REFERENCED_TABLE_SCHEMA, fk.REFERENCED_TABLE_NAME,
           fk.DELETE_RULE, fk.UPDATE_RULE

  UNION ALL
  SELECT 10, '', '', "SET foreign_key_checks = 1;"
) AS script
ORDER BY step, tbl, obj;

Sample output without findings

If the database contains no deviations, the generated script convert_utf8mb4.sql consists solely of SET statements as in this example. In this case, there is nothing to correct. On MariaDB versions before 11.2, the line with character_set_collations is missing.

SET SESSION innodb_strict_mode = OFF;
SET SESSION character_set_collations = 'utf8mb4=utf8mb4_general_ci';
SET NAMES 'utf8mb4' COLLATE 'utf8mb4_general_ci';
SET foreign_key_checks = 0;
SET foreign_key_checks = 1;

Procedure

The changes made by the script cannot be undone by a rollback. A full backup is created before the database is modified.

Required steps:

The Relution service is stopped.
A backup of the database is created, see also Disaster Recovery →.
The generator script is run on the Relution database, and the output is written to the file convert_utf8mb4.sql.
The file convert_utf8mb4.sql is checked. If, like the sample output, it only contains SET statements, there is nothing to correct and step 5 is skipped.
The file convert_utf8mb4.sql is run on the Relution database.
The Relution service is started.

The restart is mandatory. The temporary helper tables each persist for the duration of a database connection; otherwise, connections that are already open would continue to use the helper table created with the wrong collation. On large databases, step 5 can take some time, as MariaDB rewrites every affected table in the process.

In the following commands, the placeholders in angle brackets are replaced with the values from application.yml or the Compose file (compose.yaml, on older installations docker-compose.yml). The commands and the generator script assume the database name relution. If a different database name was chosen during installation, it is replaced in line 1 of the generator script (SET @db = 'relution';) and in the commands. The name is located at the end of the database URL in application.yml, e.g. relution in jdbc:mariadb://localhost/relution?useServerPrepStmts=true.

The options -N, -B and -r ensure that the output is written without column headers, table borders and escaping and can therefore be run directly as a script.

Docker

The commands are run in the directory /opt/relution/, in which the generator script is also stored.

docker compose stop relution

docker exec <containername> mariadb-dump -u <username> -p<password> --single-transaction relution > relution_backup.sql

docker exec -i <containername> mariadb -u <username> -p<password> -N -B -r relution < generate_mariadb_utf8mb4_conversion.sql > convert_utf8mb4.sql

cat convert_utf8mb4.sql

docker exec -i <containername> mariadb -u <username> -p<password> relution < convert_utf8mb4.sql

docker compose start relution

Here, the password is specified directly after -p, as the scripts are passed via standard input and an interactive password prompt is not possible.

To avoid the password being stored in plain text in the shell history, the command can be prefixed with a space, or the history can be cleaned up afterwards.

Native

On Linux:

sudo systemctl stop relution

mariadb-dump -u <username> -p --single-transaction relution > relution_backup.sql

mariadb -u <username> -p -N -B -r relution < generate_mariadb_utf8mb4_conversion.sql > convert_utf8mb4.sql

cat convert_utf8mb4.sql

mariadb -u <username> -p relution < convert_utf8mb4.sql

sudo systemctl start relution

On Windows, the same mariadb calls are run in the command prompt, e.g. via the shortcut Command Prompt (MariaDB 10.11 (x64)). In PowerShell, redirection with < does not work. There, the content of the generated file is displayed with type convert_utf8mb4.sql.

Verify the result

After the conversion, the generator script is run again as in step 3. If the new output only contains the SET statements of the sample output, the database and tables are fully set to utf8mb4 with utf8mb4_general_ci. The generator script makes no changes and can be run again at any time.

Prevention

So that newly created databases and tables receive the correct collation right away, the MariaDB server is operated with the following settings in the [mysqld] section:

[mysqld]
collation-server=utf8mb4_general_ci
character-set-server=utf8mb4
character-set-collations=utf8mb4=utf8mb4_general_ci

From MariaDB 11.5, the line character-set-collations is required. Without it, MariaDB uses the collation utf8mb4_uca1400_ai_ci for connections, even if the database and tables are set to utf8mb4_general_ci. The option is available from MariaDB 11.2. Older versions such as MariaDB 10.11 LTS do not know it and do not start with this line; there, it is omitted.

The current settings are checked in the MariaDB console. The Relution database is selected first:

USE relution;
SHOW VARIABLES LIKE '%collation%';

The values for collation_server and collation_database are utf8mb4_general_ci. Without USE, no database is selected (recognizable by the prompt MariaDB [(none)]), and collation_database only shows the server default instead of the setting of the Relution database. If collation_connection is set to utf8mb4_uca1400_ai_ci on MariaDB 11.5 or later, the line character-set-collations is missing from the configuration. The complete configuration is described in the article Deprecated: Linux (Alma) →.

Customer Hub

Fix MariaDB collation

Symptoms #

Cause #

Scripts #

Generator script #

Sample output without findings #

Procedure #

Docker #

Native #

Verify the result #

Prevention #