Illegal mix of collations

On installations using MariaDB, individual operations fail with database error 1267. This 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 to work 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 characteristic sign is a table with the prefix TL_ in the query. The location of the log files is described under Relution Logs →.

Cause

Relution expects the collation utf8mb4_general_ci for all text columns. The database tables are created accordingly, and the Relution database connection uses this collation as well.

For bulk operations, such as deleting multiple device enrollments during the ADE synchronization, Relution briefly creates temporary helper tables with the prefix TL_. No collation is specified for these tables, so they inherit the default collation of the database. If this default collation differs from utf8mb4_general_ci, MariaDB cannot compare the columns of the helper table with the columns of the actual tables and aborts the query. The stored data is not affected.

A differing default collation typically occurs in the following cases:

The database was created on a MariaDB server whose collation-server is not set to utf8mb4_general_ci. Newer MariaDB versions use a different default collation unless explicitly configured.
The database was transferred to a new server or container via a dump. The dump restores the tables with their original collation, but the newly created database itself receives the default of the new server.

Solution

The default collation of the database is aligned with the collation of the Relution tables. This only changes the default for newly created tables; existing tables and data remain unchanged.

A complete backup of the database is recommended before modifying the database.

Required steps:

The Relution service is stopped.
A backup of the database is created.
The script below is saved as repair_temp_table_collation_mariadb.sql and executed against the Relution database.
The output of the script is checked (see below).
The Relution service is started.

The restart is mandatory. The temporary helper tables exist for the lifetime of a database connection. Otherwise, connections that are already open continue to use the helper table created with the wrong collation.

Script

The script determines the collation of the Relution tables, lists deviating columns and sets the default collation of the database. It can safely be executed multiple times.

repair_temp_table_collation_mariadb.sql

-- Fixes "Illegal mix of collations" (error 1267) on temporary TL_* tables on MariaDB.
-- Only sets the default collation of the database, existing tables and data remain unchanged.
-- Relution must be restarted afterwards.

-- 1) Diagnostics
SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = DATABASE();

SELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME IN ('mdm_auto_enrllmnt', 'mdm_auto_enrllmnt_dflt_plcy')
  AND COLLATION_NAME IS NOT NULL
ORDER BY TABLE_NAME, COLUMN_NAME;

SELECT CHARACTER_SET_NAME, COLLATION_NAME
INTO @ref_charset, @ref_collation
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND TABLE_NAME = 'mdm_auto_enrllmnt'
  AND COLUMN_NAME = 'uuid';

-- 2) Columns deviating from the reference collation
SELECT @ref_collation AS reference_collation;

SELECT COLLATION_NAME, COUNT(*) AS column_count, COUNT(DISTINCT TABLE_NAME) AS table_count
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND COLLATION_NAME IS NOT NULL
GROUP BY COLLATION_NAME
ORDER BY column_count DESC;

SELECT TABLE_NAME, COLUMN_NAME, COLUMN_TYPE, COLLATION_NAME
FROM information_schema.COLUMNS
WHERE TABLE_SCHEMA = DATABASE()
  AND COLLATION_NAME IS NOT NULL
  AND COLLATION_NAME <> @ref_collation
ORDER BY TABLE_NAME, COLUMN_NAME;

-- 3) Align the default collation of the database
SET @ddl := IF(
    @ref_collation IS NULL,
    'SELECT ''ERROR: mdm_auto_enrllmnt.uuid not found - wrong schema?'' AS notice',
    CONCAT('ALTER DATABASE `', DATABASE(), '` CHARACTER SET ', @ref_charset, ' COLLATE ', @ref_collation)
);
PREPARE stmt FROM @ddl;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM information_schema.SCHEMATA
WHERE SCHEMA_NAME = DATABASE();

In the following commands, the placeholders in angle brackets are replaced with the values from the application.yml or docker-compose.yml.

Docker

The commands are executed in the /opt/relution/ directory, where the script is also stored.

docker compose stop relution

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

docker exec -i <containername> mariadb -t -u <username> -p<password> <database> < repair_temp_table_collation_mariadb.sql

docker compose start relution

The password is specified directly after -p here, because the script is passed via standard input and an interactive password prompt is not possible in that case.

To prevent the password from 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 <database> > relution_backup.sql

mariadb -t -u <username> -p <database> < repair_temp_table_collation_mariadb.sql

sudo systemctl start relution

Alternatively, the script can also be executed against the Relution database using a management tool such as HeidiSQL or DBeaver.

Check the output

The last output of the script shows the new default collation of the database. It then matches the collation of the Relution tables, usually utf8mb4_general_ci.

If the message ERROR: mdm_auto_enrllmnt.uuid not found - wrong schema? is displayed instead, the script was not executed against the Relution database. In this case, nothing was changed.

Section 2 of the script lists all text columns whose collation deviates from the reference. Normally, this list is empty. If it contains entries, tables with the wrong collation have been created since the database was set up, for example by database updates as part of Relution updates. These tables can trigger the same error in other operations. They are fully converted to utf8mb4_general_ci following the guide under DB Conversion to UTF8MB4 →.

Prevention

To ensure that newly created databases 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

The current settings are checked with:

SHOW VARIABLES LIKE '%collation%';

The values for collation_server and collation_database are utf8mb4_general_ci. The complete configuration is described in the installation guide for Linux →.

Customer Hub

Illegal mix of collations

Cause #

Solution #

Script #

Docker #

Native #

Check the output #

Prevention #