Quasar Store Logo

How to Fix Collation Issues in MySQL/MariaDB (FiveM)

This guide explains what collation issues are, how to detect them, why they break FiveM resources, and how to fix them cleanly (without hacky query patches). It also explains the warnings that `qs-police-creator` may print and how to disable them if you want.

## What is a collation (and why does it break scripts)?

- A **character set** defines how text is stored (example: `utf8mb4`).

- A **collation** defines how that text is compared/sorted within the character set (example: `utf8mb4_unicode_ci`).Many FiveM resources (and frameworks) compare text across tables, for example:- `users.identifier` (ESX) ⇄ `qs_police_players.identifier`- `players.citizenid` (QBCore) ⇄ `qs_police_players.identifier`- `players.citizenid` (QBCore) ⇄ `qs_police_player_badges.identifier`- `owned_vehicles.owner` (ESX) ⇄ `users.identifier`- `player_vehicles.citizenid` (QBCore) ⇄ `players.citizenid`- `job`, `plate`, `badge`, etc.If the compared columns use **different collations or charsets**, MySQL/MariaDB may throw errors like:- `Illegal mix of collations ...`- `Unknown collation ...`- Queries failing only on some servers (because each server’s database was created/imported differently)## Before you start (important)1. **Make a backup** of your database.2. Make sure you know whether you are using **MariaDB** or **MySQL**.3. Decide the “target” (charset/collation) you want to unify everything to.## Which target should you use (the correct approach)?Rule of thumb: **make your custom tables match your framework** (the “source” columns your server actually uses).In `qs-police-creator`, the detection uses these as the reference:- ESX: `users.identifier` (and `users.job` where relevant)- QBCore: `players.citizenid`In practice, the safest target on most FiveM servers is:- Charset: `utf8mb4`- Collation: `utf8mb4_unicode_ci`Notes:- On **MySQL 8** you might see `utf8mb4_0900_ai_ci`. That is fine on MySQL 8, but **MariaDB does not support it**. For best compatibility across hosts, stick to `utf8mb4_unicode_ci`.## Step 1: Identify what is mismatching### 1A) Check your current database default collation```sqlSELECT SCHEMA_NAME, DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAMEFROM information_schema.SCHEMATAWHERE SCHEMA_NAME = DATABASE();```### 1B) Find all text columns that are not using your target collationReplace `utf8mb4_unicode_ci` if you choose a different target.```sqlSELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME, COLUMN_TYPEFROM information_schema.COLUMNSWHERE TABLE_SCHEMA = DATABASE() AND COLLATION_NAME IS NOT NULL AND COLLATION_NAME <> 'utf8mb4_unicode_ci'ORDER BY TABLE_NAME, COLUMN_NAME;```### 1C) Quickly check the “typical” join keysThis helps you focus on what actually causes resource errors (joins/comparisons).```sqlSELECT TABLE_NAME, COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME, COLUMN_TYPEFROM information_schema.COLUMNSWHERE TABLE_SCHEMA = DATABASE() AND COLUMN_NAME IN ('identifier', 'citizenid', 'owner', 'job', 'plate', 'badge') AND COLLATION_NAME IS NOT NULLORDER BY COLUMN_NAME, TABLE_NAME;```If you see different `COLLATION_NAME` values for columns that are compared to each other (example: `users.identifier` vs `qs_police_players.identifier`), you have found the problem.## Step 2: Fix it (two safe approaches)### Option A) Table-level conversion (fast, broad)This converts **all text columns** in a table to the target charset/collation:```sqlALTER TABLE `table_name` CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;```Pros:- Fast and simple.- Usually fixes everything in one pass.Cons:- It affects all text columns (sometimes you may want specific columns to stay binary/case-sensitive).Recommended use:- Tables that are pure “text data” and do not rely on binary collations.- Your framework tables and typical join-key tables often fall into this category.### Option B) Column-level conversion (recommended, precise)This is the most controlled method: you change only the specific columns that are involved in joins/comparisons.1) First, set your database default (helps for future table/column creation):```sqlALTER DATABASE `your_database` CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;```2) Then, convert only the columns that must match each other. Example patterns:- ESX: - `users.identifier` matches your custom tables’ `identifier` - `owned_vehicles.owner` matches `users.identifier`- QBCore: - `players.citizenid` matches your custom tables’ `identifier` - `player_vehicles.citizenid` (or similar) matches `players.citizenid`- Any script: - Any `job`, `plate`, `badge` columns that are compared across tables should matchYou can generate safe `ALTER TABLE ... MODIFY ...` statements using the following query.This generator focuses on common join keys; adjust the `IN (...)` list as needed:```sqlSELECT CONCAT( 'ALTER TABLE `', TABLE_NAME, '` ', 'MODIFY `', COLUMN_NAME, '` ', COLUMN_TYPE, ' CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ', IF(IS_NULLABLE = 'NO', 'NOT NULL', 'NULL'), ';' ) AS alter_sqlFROM information_schema.COLUMNSWHERE TABLE_SCHEMA = DATABASE() AND COLLATION_NAME IS NOT NULL AND COLUMN_NAME IN ('identifier', 'citizenid', 'owner', 'job', 'plate', 'badge') AND (CHARACTER_SET_NAME <> 'utf8mb4' OR COLLATION_NAME <> 'utf8mb4_unicode_ci')ORDER BY TABLE_NAME, COLUMN_NAME;```Run the generated `ALTER TABLE ...` statements.Important:- This generator intentionally does not recreate every possible attribute (like complex defaults) because databases differ a lot between servers. For most join-key columns, this is fine.- If a column has a required default and the DB complains, re-run the `ALTER` for that column including its default (see the column definition with `SHOW CREATE TABLE table_name;`).## Step 3: Verify the fix### 3A) Confirm your join keys now match```sqlSELECT COLUMN_NAME, COUNT(DISTINCT COLLATION_NAME) AS distinct_collationsFROM information_schema.COLUMNSWHERE TABLE_SCHEMA = DATABASE() AND COLLATION_NAME IS NOT NULL AND COLUMN_NAME IN ('identifier', 'citizenid', 'owner', 'job', 'plate', 'badge')GROUP BY COLUMN_NAMEORDER BY COLUMN_NAME;```For a stable setup, `distinct_collations` should usually be `1` for each join key you actually compare across tables.### 3B) Test the joins that used to failESX example:```sqlSELECT 1FROM users uLEFT JOIN qs_police_players qp ON u.identifier = qp.identifierLIMIT 1;```QBCore example:```sqlSELECT 1FROM players pLEFT JOIN qs_police_players qp ON p.citizenid = qp.identifierLIMIT 1;```If these queries run without `Illegal mix of collations`, you are done.## qs-police-creator Warnings (what they mean)`qs-police-creator` includes a MySQL “guard” that tries to keep the resource **from crashing** when a collation error happens, but it still warns you so you can fix the database properly.You may see two types of warnings:1) **Runtime warning (during a query)**- Printed when a query fails and the error contains `Illegal mix of collations` / `Unknown collation` / `collation`.- The guard prints the warning once and the resource keeps running (some functions may return empty results or `nil` depending on the query type).2) **Precheck warning (on startup)**- Printed at startup if it detects a confirmed mismatch between typical keys (identifiers, plates, jobs, etc.).- The detection tries to confirm using real comparisons to reduce false positives.- The warning includes the **Target** (charset/collation) you should use and suggests `ALTER TABLE ... CONVERT ...` statements for tables that do not match.## Disable warnings (Lua config)In `config/shared/config.lua`:```luaConfig.DisableCollationWarnings = false```- `false`: show warnings (recommended).- `true`: do not print collation warnings (runtime or precheck). The guard will still try to prevent the resource from crashing if a collation error happens.You can also change the docs link printed in the warning (if your build supports it):```luaConfig.CollationFixDocsUrl = "https://..."```## Step 4: Restart your serverAfter applying the SQL changes:1. Restart the server (or at minimum restart the MySQL/MariaDB service + your resources).2. Retry the features that were failing.## Extra notes / common pitfalls- Do not confuse `utf8` with `utf8mb4`. In MySQL/MariaDB, `utf8` often means “3-byte UTF-8” (`utf8mb3`). Many servers mix both historically.- Collation issues usually appear when: - You migrate DBs between hosts - You import SQL dumps without preserving charset/collation - Different resources create tables with different defaults- If you see `Unknown collation 'utf8mb4_0900_ai_ci'` on MariaDB, you imported a dump made for MySQL 8. Convert it to `utf8mb4_unicode_ci` (or another MariaDB-supported collation).