TYPO3 Core

The subject might be misleading but I had the following problem:

I got SQL Exceptions thrown in the frontend like "Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8_unicode_ci,IMPLICIT) for operation 'like'" caused by a database query which joined two tables and compared their fields.
Obviously this was caused because the tables had different collations.

The Database Server has `utf8_general_ci` set as default collation.

This made me wonder because some tables had `utf8_unicode_ci` while others had `utf8_general_ci`.
However if I create a new table in `ext_tables.sql` and run a migration through the install tool, this creates the table in `utf8_unicode_ci`.

I figured out that Doctrine, especially the \Doctrine\DBAL\Platforms\MySqlPlatform adapter class, sets the collate to `utf8_unicode_ci` if no other was configured somehow.

Then I realised, that I installed the typo3 installation a few months ago in TYPO3 7.6. Somewhen in TYPO3 8.x the database backend was changed to Doctrine.
So I concluded that these collations mismatched was caused by a changed environment.

I checked a fresh typo3 installation in all its tables are in `utf8_unicode_ci`.

Upgrading to new versions of typo3 (from TYPO3 prior to Doctrine to TYPO3 8.5(?)) causes this mismatch.

So far I didn't find any migration scripts which handles the old tables.