[TYPO3-english] Re: 6.2.0 Install Tool Update Wizard: MySQL-Error

[Thomas Skierlo]

Hi Xavier,

thank you for your answer.

Quote: Xavier Perseguers (xperseguers) wrote on Wed, 15 January 2014 09:30
----------------------------------------------------
> Hi Thomas,
> 
> > I'm currently trying to get TYPO3 6.2.0 (yesterdays master, fresh
> > install) to work with utf8_unicode_ci collation (instead of
> > utf8_general_ci), but whenever I click on "Update Wizard" in the Install
> > Tool, I'm getting this mySQL Error:
> > 
> > llegal mix of collations (utf8_general_ci,IMPLICIT) and
> > (utf8_unicode_ci,IMPLICIT) for operation '<>'
> > 
> > My my.cnf:
> > 
> > [mysqld]
> > character-set-server=utf8
> > collation-server = utf8_unicode_ci
> > init-connect='SET NAMES utf8'
> > 
> > To make absolutely sure I created an empty database with explicit
> > collation utf8_unicode_ci
> > before I started TYPO3 setup. Right after setup I checked all tables as
> > well as the database. All on "utf8_unicode_ci", looking perfect.
> > 
> > DB Check just loved everythink. Everything "green".
> > 
> > Unless I click on "Update Wizard" in the Install Tool. Doing so it gives
> > me an SQL Error:
> > 
> > exec_SELECTquery
> > ERROR: Illegal mix of collations (utf8_general_ci,IMPLICIT) and
> > (utf8_unicode_ci,IMPLICIT) for operation '<>'
> > lastBuiltQuery : SELECT COUNT(uid) FROM tt_content WHERE media <> '' AND
> > CAST(CAST(media AS DECIMAL) AS CHAR) <> media OR (CType = 'uploads' AND
> > select_key != '')
> 
> According to http://dev.mysql.com/doc/refman/5.0/en/charset-convert.html
> 
> If you use CAST() without specifying CHARACTER SET, the resulting
> character set and collation are defined by the character_set_connection
> and collation_connection system variables. If you use CAST() with
> CHARACTER SET X, the resulting character set and collation are X and the
> default collation of X.
> 

This are the current MySQL system variables:

character set client 	utf8
character set connection: utf8
character set database: utf8
character set filesystem: 	binary
character set results: utf8
character set server: utf8
character set system: utf8
collation connection: utf8_unicode_ci
collation database:	utf8_unicode_ci
collation server: utf8_unicode_ci
init connect: SET NAMES utf8


> So I would suggest to double check if your DBinit param sets the
> character_set to use *for the connection*.
> 

Well, I checked more than twice, and I'm running out of ideas and motivation to carry on with 6.2 (after losing 6 days just with this issue, which is well known on forge since more than a year). Explicitely setting a collation for the query probably would fix the problem, but it would break many non-MySQL engines, like Postgress. 

I assume that you do not have this problem?

Kind regards,

Thomas