How to set the table collation to utf_general_ci using doctrine2

Question

Today I got the following error in an application which uses doctrine2

Message: SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (latin1_swedish_ci,IMPLICIT) and (utf8_unicode_ci,COERCIBLE) for operation 'like' 

I checked with phpmyadmin that the collation of the table is latin1_swedish_ci.

Within my application I configured doctrine to use utf8

$em->getEventManager()->addEventSubscriber(new \Doctrine\DBAL\Event\Listeners\MysqlSessionInit('utf8', 'utf8_unicode_ci')); 

My database was generated via install script threw an equal configured entity manager.

How to set my table's collation to utf8_general_ci using doctrine2?

Answer 1

Previously I reported a Bug in Symfony and later moved to Doctrine. @elliot mentioned the problem in create database.

So in this case I guess the bug is still there. So you want to create the database manually with utf-8, then it will do the other work.

But according to Benjamin Eberlei he mentions about postConnect event listener for MySQL which calls 'SET NAMES UTF-8'. The DBAL link he mentioned was broken, else I would have looked into it.

Hope this helps

Answer 2

if you use annotation use code below to set collation, engine and charset:

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

For yml files it's the same.