Symfony: Doctrine build-all and table collation

When working on Symfony project it’s common to run symfony doctrine:build-all (update – in V1.4 symfony doctrine:build –all) constantly. But after the command is run, the collation / encoding is changed to something that wasn’t used when the database was created.

For instances, the database was created with utf8_general_ci. When the task is run, it’s changed to latin1_swedish_ci.

Unlike propel task, when doctrine task is run, database is dropped and re-created. hence system default encoding is used. It seems that you can’t set default collation for database in symfony / doctrine.

I run into this problem when testing out symfony V1.2 and now it’s still happening in V1.3 beta (update – still the same in V1.4).

The problem was reported. This can be fixed by hacking the file path/to/symfony/lib/plugins/sfDoctrinePlugin/lib/database/sfDoctrineDatabase.class.php. go to line 84 and replace:

if (is_string($value)) { 
  if (defined('Doctrine::' . strtoupper($stringName) . '_' . strtoupper($value))) { 
    $value = constant('Doctrine::'.strtoupper($stringName) . '_' . strtoupper($value)); 
  } else { 
    $value = strtoupper($value); 
  } 
}

Add attributes to path/to/project/config/databases.yml:

all:
  doctrine:
    class: sfDoctrineDatabase
    param:
      dsn: 'mysql:host=localhost;dbname=mydbname'
      username: user
      password: password
      attributes:
        default_table_collate: utf8_general_ci
        default_table_charset: utf8

Run ‘symfony doctrine:build-all‘ again. The tables should be created with the correct encoding.

Until this is fixed in the core code, you have to hack this code everytime symfony is upgraded.

Without hacking the core code

another way is to add options to all tables defined in config/doctrine/schema.yml:

MyTable:
  options: { collate: utf8_unicode_ci, charset: utf8 }
  columns:
    ....

The database will be re-created to system default encoding, but the table will use the specified collation.

The cons: tables created by plugins still use system default. Individual schema.yml has to be hacked, and you have to remember to do this everytime the plugin is upgraded.

Update: a even better solution

IMO, this is a better solution.

4 thoughts on “Symfony: Doctrine build-all and table collation

  1. Alex Reply

    Thank you for your comment! It helped me to save a lot of my time. And I’d like to ask you correct the structure of the databases.yml – it should be as follows:

    all:
    ..doctrine:
    ….class: sfDoctrineDatabase
    ….param:
    ……dsn: ‘mysql:host=localhost;dbname=mydbname’
    ……username: user
    ……password: password
    ……attributes:
    ……..default_table_collate: utf8_general_ci
    ……..default_table_charset: utf8

  2. Ben Reply

    After reading your three different ways to set collation on symfony database (editing core, specifying collation on each table, and editing configuration file), and actually trying all of them, I found out you can set collation by….

    1. set
    options:
    charset: utf8
    collate: utf8_unicode_ci

    2. add
    attributes:
    default_table_charset: utf8
    default_table_collate: utf8_unicode_ci
    to ‘all’ environment in your database.yml

    also make sure your default char set/collation is set correctly on your database.

    It’s possible my methods works because Symfony fixed it.
    I’m using Symfony 1.4.6

    I’m writing this because this post is one of highest ranked page on Google when I search “symfony collate.”

Leave a Reply

Your email address will not be published. Required fields are marked *