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

[Steffen Wickham]

Am 28.03.13 09:12, schrieb 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
>

Hello Robert,

thank you for your excellent answer! :)
I'd had a deeper look at my mysql and database settings and changed
default collation to utf8_unicode_ci for both now.

Here are my settings for the "mysqld" section of /etc/my.cnf regarding
default collation and character set (simply add them to your config and
restart mysql server):
init-connect='SET NAMES utf8'
init-connect='SET collation_connection = utf8_unicode_ci'
character-set-server=utf8
collation-server = utf8_unicode_ci


Now I can continue working on my app. Thank you! :)

Greetings
Steffen