symphonycms/symphony-2 — Issue #997: Collation confusion in the Symphony DB
Author: michael-e
Created: 2012-01-30 10:48:11+00:00
State: open

Beginning with the Members extension, the Working Group started to explicitely set a character set (UTF-8) **and** a collation for newly created tables in the database by using:

DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci

Some extensions have followed this example, but the core fields (packaged with Symphony) have not. The latter don't explicitely set a collation for tables, so MySQL will use the default collation (which is `utf8_general_ci` for the `utf8` character set). So now we find different collations on tables in the database.

This is, for example, a problem for the Email Newsletter Manager, which uses a temporary table for 'sent' email addresses. In order to find the next batch of addresses to send to, it must `JOIN` this table with the table containing the recipient addresses. Here comes the problem: A `JOIN` only works on tables with the same collation, otherwise MySQL will throw an error.

In the ENM forum thread I described [how to "quick fix" this and get the ENM running](http://symphony-cms.com/discuss/thread/82064/4/#position-68).

Nevertheless we should do two things in the near future:

- **Clean up the "collation confusion" in Symphony** (core fields, fields provided by extensions) and publish some "best practice" to tell people how DB tables should be created.
- **For the ENM, maybe find a way** to get the collation of the current email address field table, then use the same collation for the temporary table.