MySQL: from utf8 to utf8mb4

After my last upgrade from MySQL 5.1 to MySQL 5.5 to use utf8mb4 instead of utf8, I noticed that some accented characters were being stored as question mark.

To fix this problem, I had to update the character set of the database, tables and columns and make sure that the my.cnf contains the good charset.

Server Character Set and Collation

In /etc/mysql/my.cnf file make sure you have the following:

[mysqld]
# ...
character-set-server = utf8mb4
collation-server     = utf8mb4_unicode_ci
# ...

Display the current character set

To see the current character set, issue the following command:

For Schemas:

SELECT default_character_set_name FROM information_schema.SCHEMATA S
WHERE schema_name = "schemaname";

For Tables:

SELECT CCSA.character_set_name FROM information_schema.`TABLES` T,
       information_schema.`COLLATION_CHARACTER_SET_APPLICABILITY` CCSA
WHERE CCSA.collation_name = T.table_collation
  AND T.table_schema = "schemaname"
  AND T.table_name = "tablename";

For Columns:

SELECT character_set_name FROM information_schema.`COLUMNS` C
WHERE table_schema = "schemaname"
  AND table_name = "tablename"
  AND column_name = "columnname";

Alter database character set:

Use the following command:

alter database schemaname CHARACTER SET = utf8mb4;

Alter table and column character set

ALTER TABLE tablename DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, MODIFY columname LONGTEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci ;

ALTER TABLE tablename CHARACTER SET utf8mb4 collate utf8mb4_unicode_ci;
ALTER TABLE tablename MODIFY columnname longtext CHARACTER SET utf8mb4 collate utf8mb4_unicode_ci;

Finally, the output of SHOW VARIABLES LIKE 'character_set%'; should at least look like to the following:

mysql> SHOW VARIABLES LIKE 'character_set%';
+--------------------------+--------------------------------------------------------------------+
| Variable_name            | Value                                                              |
+--------------------------+--------------------------------------------------------------------+
| character_set_client     | utf8                                                               |
| character_set_connection | utf8                                                               |
| character_set_database   | utf8mb4                                                            |
| character_set_filesystem | binary                                                             |
| character_set_results    | utf8                                                               |
| character_set_server     | utf8mb4                                                            |
| character_set_system     | utf8                                                               |
| character_sets_dir       | /usr/local/appservers/mysql-5.5.21-linux2.6-x86_64/share/charsets/ |
+--------------------------+--------------------------------------------------------------------+
8 rows in set (0.00 sec)

If you are using MySQL with a Java application, you must upgrade your mysql-connector-java to the latest version, at the moment of writing, it is 5.1.18 or at least to the 5.1.14

Here is the maven dependency:

 mysql
 mysql-connector-java
 5.1.18

Resources: http://dev.mysql.com/doc/refman/5.5/en/charset-unicode-upgrading.html