vBulletin character set & collation, double check phpmyadmin / MySQL for charset settings

MySQL server and database character set and collations are confusing, I don’t understand it all as I mainly deal with MySQL default latin1 character set and latin1_swedish_ci collation for English based forums. But for non-English based vBulletin forums it can be a pain to get right especially if you rely on and trust your web host has configured MySQL server settings correctly. You can find a full list of MySQL character set and collations here.

I’m posting this note, as recently came across an issue a few non-English based vB clients have experienced, that is their MySQL server settings weren’t correctly configured and they relied on info reported by phpmyadmin’s front page – assuming their MySQL server was configured to default to UTF8 and was wondering why their non-English characters weren’t displayed correctly.

[box type=”shadow”]
Note: For MySQL 5.5 server users or recent upgraders be careful. With MySQL 5.0.x/5.1 default character set is latin1 but for some servers it may have changed to default to UTF8 with MySQL 5.5.x server versions. So if you made a mysqldump backup of your database on MySQL 5.0.x/5.1.x and imported to new MySQL 5.5 server, your database, database table names may have be converted to UTF8 instead of latin1. Definitely worth double checking or doing test MySQL database migration if you are moving to a MySQL 5.5.x based server.
[/box]

To the untrained eye, the below front phpmyadmin page reports MySQL chartset = UTF8. But that isn’t entirely correct, that line of info refers to phpmyadmin’s session connection to MySQL server as being UFT8 and not that your MySQL server is configured to default to UFT8.

So how do you correctly determine what your MySQL server default character set and collation are ? There’s several ways.

1).

Check phpmyadmin variables tab for ‘global value’ for the following MySQL variables:

This is what you usually see for MySQL server defaults which are latin1 character set with latin1_swedish_ci collation. Note, character_set_system is UTF8 by default on latin1 default system and that is normal as MySQL system database and tables themselves at /var/lib/mysql/mysql/* are UTF8 by default. But every other database created defaults to latin1 if database and table creation queries don’t specify charset and collation values.

| character_set_client                      | latin1                                                                                    |
| character_set_connection                  | latin1                                                                                    |
| character_set_database                    | latin1                                                                                    |
| character_set_filesystem                  | binary                                                                                    |
| character_set_results                     | latin1                                                                                    |
| character_set_server                      | latin1                                                                                    |
| character_set_system                      | utf8                                                                                      |
| character_sets_dir                        | /usr/share/mysql/charsets/                                                                |
| collation_connection                      | latin1_swedish_ci                                                                         |
| collation_database                        | latin1_swedish_ci                                                                         |
| collation_server                          | latin1_swedish_ci

Checking phpmyadmin variables tab, you will see it reports both the phpmyadmin session value being UTF8 as well as the global value being latin1 and latin1_swedish_ci. Which means, any databases or tables created without specifically specifying the charset and collation will default to MySQL server defaults, which is usually latin1 and latin1_swedish_ci respectively.