CODEWITHSUNDEEP
symfonydoctrine-orm
Amit
Amit

Reputation: 3754

Doctrine2: How to set all tables to collate with UTF8

I am using Doctrine with Symfony2. My config.yml file looks something like this:-

Doctrine Configuration

doctrine:
    dbal:
        driver: %database_driver%
        host: %database_host%
        port: %database_port%
        dbname: %database_name%
        user: %database_user%
        password: %database_password%
        charset: UTF8

Unfortunately my tables are not collating to the UTF8_general_ci or UTF8_unicode_ci I tried 

collate: utf8_unicode_ci

But Doctrine2 didn't recognize the option.

How can I achieve the same?

Upvotes: 31

Views: 55117

Answers (14)

Daniel Alves Barbosa
Daniel Alves Barbosa

Reputation: 1

@ORM\Table(name="user", options={"collate"="utf8_unicode_ci", "charset"="utf8 ", "engine"="InnoDB"})

Upvotes: 0

Nanocom
Nanocom

Reputation: 3726

The charset: UTF8 option is just useful to ask Doctrine to execute SET NAMES UTF-8 on each page. I don't have any specific configuration for Doctrine, and my tables are by default in utf8_general_ci InnoDB. Read this part of the documentation: https://www.doctrine-project.org/projects/doctrine-orm/en/latest/reference/faq.html#how-do-i-set-the-charset-and-collation-for-mysql-tables, it answers your question:

You can’t set these values inside the annotations, yml or xml mapping files. To make a database work with the default charset and collation you should configure MySQL to use it as default charset, or create the database with charset and collation details. This way they get inherited to all newly created database tables and columns.

Upvotes: 16

Massimiliano Arione
Massimiliano Arione

Reputation: 2466

Since DoctrineBundle 2.0, utf8mb4 is the default value for MySQL. So, you don't need to configure anything anymore.

See https://github.com/doctrine/DoctrineBundle/blob/master/UPGRADE-2.0.md

Upvotes: 0

Dawid Job
Dawid Job

Reputation: 1

I had have the same problem, and after reading this documentation from doctrine project page I decided to abandon solution with yml file.

Upvotes: 0

Igor Vizma
Igor Vizma

Reputation: 370

Use code below to set collation, engine and charset (annotation example): 

/**
* @ORM\Table(
*     name="temporary", 
*     options={"collate":"utf8_general_ci", "charset":"utf8", "engine":"MyISAM"}
* )  
* @ORM\Entity
*/

Source: http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/annotations-reference.html#annref-column

Upvotes: 8

skuteq
skuteq

Reputation: 41

I've got this problems with fetching data which includes polish characters from database. It looks like:

effects of displaying data from db

my config.yml is like:

doctrine:
dbal:
    driver:   pdo_mysql
    host:     "%database_host%"
    port:     "%database_port%"
    dbname:   "%database_name%"
    user:     "%database_user%"
    password: "%database_password%"
    charset:  UTF8
    options:
         1002:  "SET NAMES 'UTF8'"

and I've been searching for an answer for couple of hours - Im so tired of this. "1002: SET NAMES 'utf8'" Doesnt work for me. But when I tryed to access my db from simple php script (out of symfony) the effect was the same but when I added line:

mysql_query("SET NAMES 'utf8'");

it worked properly. So it seems to be little strange. All the tables in my db have 'utf8_unicode_ci' set.

Upvotes: 4

DevWL
DevWL

Reputation: 18840

UPDATE:

See Symfony3.1 book for reference (click here):

Also notice the use of utf8mb4 instead of plain utf8. ("Symfony recommends utf8mb4 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.")

Setting UTF8 defaults for MySQL is as simple as adding a few lines to your configuration file (typically 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

You can also change the defaults for Doctrine so that the generated SQL uses the correct character set.

# app/config/config.yml
doctrine:
    dbal:
        charset: utf8mb4
        default_table_options:
            charset: utf8mb4
            collate: utf8mb4_unicode_ci

Upvotes: 16

esserj
esserj

Reputation: 176

if you are looking for a way to have migrations be created correctly you should setup the connection

you can set the default_table_options connection option to achieve this: in symfony this is done through:

doctrine:
    dbal:
        default_table_options:
            charset: utf8
            collate: utf8_general_ci

for those looking to do it in plain doctrine this translates to doctrine connection option defaultDatabaseOptions, and is passed to the entity manager together with your database credentials etc:

[
    ...
    'driver' => ...
    'user' => ...
    ...
    'defaultDatabaseOptions' => [
         'charset' => 'utf8',
         'collate' => 'utf8_general_ci'
    ]
]

Upvotes: 0

Sithu
Sithu

Reputation: 4862

I have successfully used options: collate in YML configuration of Symfony 2.7.

MyBundle\Entity\Company:
    type: entity
    repositoryClass: MyBundle\Repository\CompanyRepository
    table: company
    options:
        collate: utf8_general_ci

Upvotes: 0

v0rin
v0rin

Reputation: 500

I suggest you try add this setting to your Doctrine configuration:

    options:
        1002: "SET NAMES 'UTF8' COLLATE 'utf8_unicode_ci'"

So it looks like this:

    doctrine:
        dbal:
            driver: %database_driver%
            host: %database_host%
            port: %database_port%
            dbname: %database_name%
            user: %database_user%
            password: %database_password%
            charset: UTF8
            options:
                1002: "SET NAMES 'UTF8' COLLATE 'utf8_unicode_ci'"

As a reference Doctrine Configuration: http://symfony.com/doc/2.0/reference/configuration/doctrine.html#reference-dbal-configuration

And in case you are using MySql: http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html

BTW. I had problems with displaying polish characters and adding only set names without collation (as below) helped.

    options:
        1002: "SET NAMES 'UTF8'

Upvotes: 12

rickard2
rickard2

Reputation: 291

The behavior of collate has changed in doctrine: http://www.doctrine-project.org/jira/browse/DDC-2139

The collation is now set to utf8_unicode_ci if you don't specify anything at the table level. The way to go right now is to add it to options in your table declaration: 

/**
 * @ORM\Table(options={"collate"="utf8_swedish_ci"})
 * @ORM\Entity
 */

This will generate the correct collation for the table: 

$ php app/console doctrine:schema:update --dump-sql --env=test | grep swedish
...  DEFAULT CHARACTER SET utf8 COLLATE utf8_swedish_ci ENGINE = InnoDB;

I've filed an issue for the documentation to be updated to reflect this behaviour.

Upvotes: 28

Marco Chen
Marco Chen

Reputation: 29

I met the same problems. By search the code, I find the code in vendor/doctrine/dbal/lib/Doctrine/DBAL/Platforms/MySqlPlatform.php

if ( ! isset($options['collate'])) {
  $options['collate'] = 'utf8_unicode_ci';
}

So I changed it to 'utf8_general_ci', and it worked. All of tables' collation changed to utf8_general_ci after rebuild, but I still have one question: when I changed the annotations, I got the following error message:

[Creation Error] The annotation @ORM\Table declared on class Gvsun\UserBundle\Entity\User does not have a property named "collation". Available properties: name, schema, indexes, uniqueConstraints, options

I search the documentation of Doctrine, but I didn't find the 'option' property, can someone tell me how to use options property, I think it might be able to change collate in annotations settings?

Upvotes: 2

Amit
Amit

Reputation: 3754

You can refer to the documentation here http://dev.mysql.com/doc/refman/5.0/en/charset-applications.html if you are using ORMs like Doctrine.

Particularly: adding/editing the [mysqld] block in your my.cnf (usually found in /etc/my.cnf or xampp/mysql/my.cnf)

[mysqld]
character-set-server=utf8
collation-server=utf8_general_ci

Upvotes: 4

smoreno
smoreno

Reputation: 3530

I create manually my data base with with UTF8 collate (ex. with phpmyadmin). If I do this, all tables create with command doctrine:schema:create will have collate utf8.

Upvotes: 0

Related Questions