[FLOW3-general] What is the correct database collation setting?

[Robert Lemke]

Hi Steffen,

On 27.03.2013, at 22:54, Steffen Wickham <steffen at gaming-inc.de> wrote:

> I'm working hard on my Flow application for a long time. Now I want to
> move the whole system from my pretty old Debian system to my shiny new
> CentOS based virtual machine (based on nginx webserver, PHP 5.4 as FCGI
> and MySQL 5.5). I already copied all files to the new server, got Flow
> base distribution running but I found some strange database settings in
> my MySQL database before I export them.
> 
> Long time ago, I had decided to setup everything from scratch while
> upgrading my app to Flow 2.0. I chose to set up database with collation
> "utf8_unicode_ci" what might be a mistake I think. Now I found some
> tables which collation is set to "utf8_general_ci" and had been created
> by doctrine migration during development process. Before I move my
> database to the new server I think I have to fix that issue. Is there
> any recommendation for the correct collation? Is it a problem to keep
> one of the collation? Any hint to convert all columns to another
> collation without losing special characters (especially umlauts)?
> 
> I really hope you can help me!

To put it short: utf8_unicode_ci is what you want, utf8_general_ci is the ASCII
stone age collation which makes things dirty. (Karsten Dambekalns just found
a nice article about it yesterday, poke him if you're interested ;-)

As far as I know, the migrations which are provided with the Flow packages don't
contain any hardcoded collation, so you will end up with what you have set as
a default.

I recommend to either do that (not mention any collation) or use utf8_unicode_ci.

At any rate, make sure to run your servers with the right collation in the "collation-server"
setting of your my.conf.

Hope that helps,

Robert

-- 
Robert Lemke
Lead Developer TYPO3 Neos and TYPO3 Flow
Co-Founder TYPO3 Association

Blog: robertlemke.com
Get involved: typo3.org – flow.typo3.org – neos.typo3.org