Database collations mismatch - TYPO3 Upgrade from 7.x to 10.x-CodePudding

I'm really stuck on some problems that occurred during my TYPO3 upgrade from 7 to 10.

The following error shows in the log and due to this, I can't do anything on the backend.

Core: Exception handler (WEB): Uncaught TYPO3 Exception: An exception occurred while executing 'DELETE tags2, cache1 FROM cache_hash_tags AS tags1 JOIN cache_hash_tags AS tags2 ON tags1.identifier = tags2.identifier JOIN cache_hash AS cache1 ON tags1.identifier = cache1.identifier WHERE tags1.tag IN ('pageId_1','sys_template','sys_template_1')': Illegal mix of collations (utf8_unicode_ci,IMPLICIT) and (utf8_general_ci,IMPLICIT) for operation '=' | Doctrine\DBAL\Exception\DriverException thrown in file /html/typo3/typo3_src-10.4.21/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php in line 110.

And I found this in DB there are different collations used and I'm thinking this would be the cause of the error.

dbimage

My question is, can we change the collations and charset of the entire database, tables, and table fields?

CodePudding user response：

This is not an issue of TYPO3. It is caused by the changed default settings of your database.

As you'll see ext_tables.sql does not contain any information about which collation should be used. It's a choice of you/your sysadmin.

Back to your question. "can we change the collations and charset of the entire database, tables, and table fields?": it depends ;-)

Changing the collation of existing tables can be possible but might cause problems with the stored data.

For tables storing temporary data (like the cache tables) truncating the tables and changing the collation should be harmless. (Or maybe faster: delete them and do a "Database compare")
For tables storing content, you should check if changing the collation is compatible.

CodePudding user response：

You talk about a collation mismatch between utf8_general_ci and utf8_unicode_ci. Both basically only differ in the default sorting of a query result. The data stored in the tables is identical.

Your screenshot lets me assume that you have access to phpMyAdmin. To change the collation of a table with phpMyAdmin, select the table and open the "Operations" tab. You will find a select field to choose the desired collation. Change it and save.