Database Collation - Can they be mixed?

[Jeff_Mash]

For years, our database was latin1_swedish_ci collation.

Sometime last year, in order to curb some of the linkpoint_api errors we were getting with invalid XML characters due to foreign addresses, we changed many tables to be utf8_unicode_ci collation.

Now that we have upgraded to 1.5.1, I notice that our database is still a mixture of both these collations. Some tables are UTF8, others are Latin1.

We are experiencing major MySQL hangups that are slowing down and crippling our server recently, and I'm trying to narrow down anything that could be causing it.

MY QUESTION: was is the RECOMMENDED collation for all database tables in ZenCart? Should I go ahead and make sure ALL TABLES are utf8_unicode_ci, or should I revert everything back to latin1_swedish_ci?

For the record, I noticed that in the zencart Config files, the following variable is set: define('DB_CHARSET', 'utf8');

Looking forward to a response!

- Jeff

[DrByte]

Some fields (text/binary/varchar) have a collation, some don't. If the fields are never used in SQL joins with other fields using a different collation, then there's no *technical* requirement for them to all be the same. Knowing what joins are used requires detailed query analysis of all database activity, and of course that's not likely something you have intimate knowledge of. I mention it because you asked whether things need to match: answer is yes and no.

If you are having database problems related to collation, then you'll be getting SQL errors, which should be visible in PHP logs including those redirected by Zen Cart to the /logs/ folder.

Merely changing the collation at the "database" level or the "table" level only establishes what collation will be used when additional tables/fields are created, and does not directly change existing fields or existing tables.
That is to say: collation only really matters at the "field" level.
... which then begs the warning:
Converting fields from one collation to another can be risky since any multibyte characters already in those fields will be damaged when ALTERing the field structure unless you take special specific precautions to preserve things correctly.

Of course, as you say, there's a lot to be said for consistency and of course it's cleaner and simpler if everything's using the same collation. And of course having it consistent rules out that piece as a contributing factor to any other problems.