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

## 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).