Fix illegal or mismatched collation errors¶

InteLIS shows an Illegal mix of collations error, usually after a database was restored from another MySQL or MariaDB server.

Open a terminal on the InteLIS machine and take a backup. The repair rewrites every table that uses a different collation.
```
intelis backup
```
Run the collation repair:
```
intelis db:collation
```
It converts the InteLIS database and the interfacing database, when interfacing is switched on. It runs as the web server account and may ask for the administrator password first.
If intelis is not recognised

The install is older. Run the repair directly:
```
cd /var/www/intelis && sudo -u www-data php vendor/bin/db-tools collation --all
```
On older installs, use /var/www/vlsm in place of /var/www/intelis.
Check that nothing is left to convert:
```
intelis db:collation -- --dry-run
```
Each database reports 0 need conversion.

Keep the -- before --dry-run

Without it, the --dry-run option is dropped and the command converts the tables instead of only reporting.

intelis update runs the same repair during every update.

Convert step by step with db-tools

To review the changes before applying them, run db-tools directly from the InteLIS folder:

cd /var/www/intelis

List what would change, without changing anything:

sudo -u www-data php vendor/bin/db-tools collation --all --dry-run

Apply the conversion:

sudo -u www-data php vendor/bin/db-tools collation --all

--all covers both databases. Without it, only the InteLIS database is converted.

What this fixes

The error appears when a query compares text columns stored with different collations. The repair picks the collation from the server version and converts every table and column to it. MySQL 8 gets utf8mb4_0900_ai_ci. MariaDB and MySQL 5.7 get utf8mb4_unicode_ci.

Do not set tables to utf8mb4_general_ci by hand, for example in phpMyAdmin. Tables that later updates create or change get the server's default collation, so the error comes back after the next update.