Bug #82690
closed
Missing migrations for database tables
0%
Description
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.
Updated by Susanne Moog about 4 years ago
- Status changed from New to Closed
You can set the default charset/collation via local configuration. If you were using _general_ci before, you could either set that as default or convert your tables to the new default.
Same is true for the change in later versions to utf8mb4 - see https://docs.typo3.org/c/typo3/cms-core/master/en-us/Changelog/9.5/Feature-80398-Utf8mb4OnMysqlByDefaultForNewInstances.html
If upgrading instances, admins may change LocalConfiguration.php to use this feature. The core does not provide mechanisms to update the collation of existing tables from utf8_unicode_ci to utf8mb4_unicode_ci for existing instances, though. Admins need to manage that on their own if needed, the reports module shows an information if the table schema uses mixed collations. This should be fixed after manually configuring utf8mb4 to avoid SQL errors when joining tables having different collations.