We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.

Setting database collation

How can I se my collation to utf8mb4_general_ci? I am getting this error:

Illegal mix of collations (utf8_general_ci,IMPLICIT) and (utf8mb4_general_ci,COERCIBLE)

If add this to database connection, I no loger get error, but trying to save emojis to database will save only ??????

  "options" => array(
        PDO::MYSQL_ATTR_INIT_COMMAND => 'SET NAMES utf8mb4'
    )

I am not quite sure where the problem is.

edited Mar '16

Just some thoughts:

1) You should set your charset when connecting to your db. With phalcon like so:

    // Database connection     
    $di['db'] = new \Phalcon\Db\Adapter\Pdo\Mysql(array(
        'host' => $config->database->host,
        'username' => $config->database->username,
        'password' => $config->database->password,
        'dbname' => $config->database->name,
        'charset' => 'utf8',
    )); 

2) Check if your database has the same collation, perhaps you changed it in your code but forgot the db? Or this specific column has wrong collation by mistake?

3) Check if you have models metadata cache running and you forgot to clear it after changing database?

Hello, Nikolay.

Would it be possible to set the charset as utf8mb4 instead?

I mean, if we have to store a four-byte-code in order to save an emoji, for example (which is my case), shouldn't that work? Because I've tried that, but that's no working.

Just some thoughts:

1) You should set your charset when connecting to your db. With phalcon like so:

   // Database connection     
   $di['db'] = new \Phalcon\Db\Adapter\Pdo\Mysql(array(
       'host' => $config->database->host,
       'username' => $config->database->username,
       'password' => $config->database->password,
       'dbname' => $config->database->name,
       'charset' => 'utf8',
   )); 

2) Check if your database has the same collation, perhaps you changed it in your code but forgot the db? Or this specific column has wrong collation by mistake?

3) Check if you have models metadata cache running and you forgot to clear it after changing database?

Yeah, should work!

Have you checked if your database or tables have the same collation? When in a hurry I often forget to swap latin encoding to utf and go back after to fix it :)

Yep, the table collation was still set as utf8.

It's working pretty good now.

Thank you so much, Nikolay.