First, this is incorrect. If your database collation is latin1_swedish_ci, then your charset needs to match, i.e. latin1, which means you need to be presenting data in a latin1 compatible charset in the website (front-end and back-end). What is happening is, since you specify utf8 in the connection_charset value, the database connections are initialized using SET CHARACTER SET utf8. This forces the MySQL client API to convert result sets and string values in the queries to utf8 when coming out of the latin1 database, and back again when saving data into the database. This can be lossful, depending on the characters being used. This works because SET CHARACTER SET uses the actual database collation (latin1_swedish_ci) to set the client connection charset to latin1...
A SET CHARACTER SET x statement is equivalent to these three statements:
SET character_set_client = x;
SET character_set_results = x;
SET collation_connection = @@collation_database;
Setting collation_connection also sets character_set_connection to the character set associated with the collation (equivalent to executing SET character_set_connection = @@character_set_database). It is not necessary to set character_set_connection explicitly.
So regardless of what you pass, the collation_connection is always going to match the actual database. This is a good thing.
But this is where the database connection method can change this behavior; by using SET NAMES instead of SET CHARACTER SET, you can force the character_set_connection to utf8...
A SET NAMES ’x’ statement is equivalent to these three statements:
SET character_set_client = x;
SET character_set_results = x;
SET character_set_connection = x;
Setting character_set_connection to x also sets collation_connection to the default collation for x. It is not necessary to set that collation explicitly. To specify a particular collation for the character sets, use the optional COLLATE clause:
SET NAMES ’charset_name’ COLLATE ’collation_name’
Here you can control the character_set_connection explicitly; now the MySQL client will treat the database connection in the forced charset definition. IMHO, this is not at all desirable, and gets extremely confusing.
My rule of thumb, always use UTF-8 charset on the web site and always use utf8/utf8_unicode_ci when creating a MODx database (the install will do this automatically if you have privileges to create databases). It avoids all the confusion over what is being converted where, which has lead many people down the wrong path trying to solve these issues.
BTW, you cannot change the collation on existing tables without corrupting your data. That’s why it is important to always get this right from the beginning, or you will end up recreating all of your content, or at least fixing a lot of it either manually or via a bunch of complex and scary steps in MySQL.
).
MODX Staff
MODX Staff