1

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?

Jakob Alexander Eichler
  • 2,988
  • 3
  • 33
  • 49
  • Depending on your application's requirements, consider reading [this post](http://stackoverflow.com/questions/766809/whats-the-difference-between-utf8-general-ci-and-utf8-unicode-ci). – BentCoder Oct 26 '14 at 11:05

2 Answers2

1

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.

Igor Vizma
  • 370
  • 5
  • 10
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

Hari K T
  • 4,174
  • 3
  • 32
  • 51
  • The link to the postConnect listener seems to be iinvalid. – Jakob Alexander Eichler Nov 08 '11 at 23:31
  • I have linked to the post thread in groups which @beberlei points to the DBAL. As I mentioned earlier "The DBAL link he mentioned was broken". But may be you ask in doctrine users group to him to get an answer :(. – Hari K T Nov 09 '11 at 05:02
  • IS it possible to get a list of all table names from the doctrine api? Than you could correct this with native queries in your install script. – Jakob Alexander Eichler Nov 10 '11 at 14:25
  • @tokam , the only thing you want to do is create db in utf-8 . Doctrine will do the rest creating the collation as utf-8 general ci. – Hari K T Nov 11 '11 at 04:16