Setting up the default Database to be UTF8

Setting up the default Database to be UTF8

One mistake even seasoned developers make when starting a Symfony project is forgetting to set up default charset and collation on their database, ending up with latin type collations, which are default for most databases. They might even remember to do it the very first time, but forget that it's all gone after running a relatively common command during development:

Setting UTF8 defaults for MySQL is as simple as adding a few lines to your configuration file (typically my.cnf):

# vim /etc/my.cnf

[mysqld]
# Version 5.5.3 introduced "utf8mb4", which is recommended
collation-server     = utf8mb4_general_ci # Replaces utf8_general_ci
character-set-server = utf8mb4            # Replaces utf8

We recommend against MySQL's utf8 character set, since it does not support 4-byte unicode characters, and strings containing them will be truncated. This is fixed by the newer utf8mb4 character set.

# systemctl restart mariadb

SHOW VARIABLES LIKE 'char%';
SHOW VARIABLES LIKE 'collation%';

Symfony / Doctrine:

# vim src/Mycomp/DemoBundle/Resources/config/doctrine/Product.orm.yml

options:
    collate: utf8mb4_general_ci

# vim app/config/config.yml

doctrine:
    dbal:
        charset:  utf8mb4

Reference:

https://florian.ec/articles/mysql-doctrine-utf8/
http://symfony.com/doc/current/book/doctrine.html#book-doctrine-field-types