Project

General

Profile

Actions

Bug #80733

closed

Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE) Exception while upgrading from 8.6.1 to 8.7.0

Added by Marc von Schalscha about 7 years ago. Updated about 7 years ago.

Status:
Rejected
Priority:
Should have
Assignee:
-
Category:
Install Tool
Target version:
-
Start date:
2017-04-06
Due date:
% Done:

0%

Estimated time:
TYPO3 Version:
8
PHP Version:
7.0
Tags:
Complexity:
Is Regression:
No
Sprint Focus:

Description

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

System:
Debian 8
MySQL 5.5.52
PHP 7.0.17 fpm


Related issues 2 (0 open2 closed)

Related to TYPO3 Core - Bug #81496: Invalid Charset UTF8 at Hoster StratoClosed2017-06-07

Actions
Related to TYPO3 Core - Bug #87034: Fresh Install Wrong DB Connection Collation Closed2018-11-29

Actions
Actions #1

Updated by Thomas Hohn about 7 years ago

  • Category set to Install Tool
Actions #2

Updated by Morton Jonuschat about 7 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

Actions #3

Updated by Marc von Schalscha about 7 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. ;-)

Actions #4

Updated by Markus Klein almost 7 years ago

  • Related to Bug #81496: Invalid Charset UTF8 at Hoster Strato added
Actions #5

Updated by Susanne Moog about 5 years ago

  • Related to Bug #87034: Fresh Install Wrong DB Connection Collation added
Actions

Also available in: Atom PDF