Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) Exception while upgrading from 8.6.1 to 8.7.0
While upgrading TYPO3 8.6.1 to 8.7.0 using the upgrade wizard I just went into the following exception after the "Create tables and fields" action.
Oops, an error occurred! An exception occurred while executing 'SELECT COUNT(*) FROM `sys_refindex` WHERE `hash` <> LOWER(MD5(CONCAT_WS('///', CAST(`tablename` AS CHAR), CAST(`recuid` AS CHAR), CAST(`field` AS CHAR), CAST(`flexpointer` AS CHAR), CAST(`softref_key` AS CHAR), CAST(`softref_id` AS CHAR), CAST(`deleted` AS CHAR), CAST(`workspace` AS CHAR), CAST(`ref_table` AS CHAR), CAST(`ref_uid` AS CHAR), CAST(`ref_string` AS CHAR), '2')))': Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) for operation '<>'
The install Tool keeps crashing and throwing this exeption until I did the following fix to the Database:
mysql> SET collation_connection = 'utf8_general_ci'; mysql> USE your_typo3_database mysql> ALTER DATABASE your_typo3_database CHARACTER SET utf8 COLLATE utf8_general_ci; mysql> ALTER TABLE sys_refindex CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci;
Originally the iInstance was installed on TYPO3 7.6 LTS and upgraded to 8.6.1 a couple of weeks ago without any problems. I did a rollback of the machine and tried the upgrade again and again doing all the Compare Database stuff etc. in different order before the upgrade to 8.7 always ending up in a crashed install Tool. However this might be a general problem under some circumstances. Having done the database changes there where no further problems upgrading to 8.7
PHP 7.0.17 fpm
#2 Updated by Morton Jonuschat about 2 years ago
- Status changed from New to Rejected
TYPO3 requires the database to be in UTF8 collation as early as TYPO3 6.2. While TYPO3 mostly works with an unsupported setup using a mix of collations or on non UTF8 tables errors like the one mentioned can occur. There is little what the Core can do to remediate this.
The fix you applied, converting the tables to a supported encoding, is the only reliable way to go.
For more information you can also check out the UTF8 Wiki which was set up for TYPO3 6.2: https://wiki.typo3.org/UTF-8_support
#3 Updated by Marc von Schalscha about 2 years ago
I know that requirement but do not have any influence on how the DBA´s are setting up their database servers. But that is not the interesting point. As I mentioned, this instance was a newly installed TYPO3 7.6. so why does sys_refindex has the wrong default character set and collation? Tables seems surprisedly be created without setting DEFAULT CHARACTER SET and COLLATE to UTF-8. I checked the database and figured out, that most of the TYPO3 tables still have latin1_swedish_ci. All other non TYPO3 tables are using utf8_general_ci. If I create a new table setting DEFAULT CHARACTER SET and COLLATE to UTF-8 it has utf8.
So i guess that TYPO3 7.6 LTS has not set UTF-8 by default while creating the tables and therefore there could be a lot of TYPO3 installations having exactly this problem out there (just think of shared hoster environments). However you are absolutely right, there is little the install tool can do about this. But maybe there should be a check of table collation, warning the user before using the upgrade wizard or at least the exception being caught to prevent a complete broken install tool. Ideally with a hint how to solve the problem. ;-)