Grails and Hibernate: Saving emoticons ( unicode ) to MYSQL.

We built a Grails application that uses a mail plugin, to send and retrieve emails from mail servers. The plugin employed a scheduled job to retrieve and store the emails on a MySql database table.

Initially everything worked as specified until recently when I noticed that some mails were not saved to MYSQL. The error message from MYSQL was 'incorrect string value' bla bla bla....

On close examination, I noticed that the troublesome emails contained emoticons (unicode characters). The unicode characters were responsible for my troubles :(

The issue

I dug through the MySQL documentation and learnt that my MySQL instance was having a hard time accepting those unicode characters because the database encoding was set to "utf-8".

Solution

To solve the problem required a change of the mysql default encoding from "utf-8" to "utf8mb4".

Note:We used a MySQL version of 5.6.13 but this solution will also apply to MySQL version 5.5 and higher.

If you are starting out with a fresh MySQL schema, all you need to do is specify the encoding in your schema creation script.


CREATE SCHEMA `your_db` DEFAULT CHARACTER SET utf8mb4 ; 



Then jump to Step 4.

For everyone trying to convert an existing db schema or table encoding from "utf-8" to "utf8mb4" please follow the steps below.

Step 1. Backup your database. 

The importance of creating backups cannot be overemphasized. Please create a database backup before embarking on this task.
  

To learn how to backup your database, please see MySQL Backup methods.

Step 2 : Upgrade your version of MySQL ( optional )

At this point, I assume you have performed a backup of your database. If your MySQL db version is below 5.5.x please upgrade your version to the latest available database ( newer is supposed to be better right? ) .

I am currently using MySQL version 5.6.13

Step 3 : Write schema charset conversion scripts

Run the following scripts for MySQL. If you do not know how to run MySQL scripts please contact your database administrator for help :) 

# To convert db:
ALTER DATABASE database_name CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;

#To convert db tables
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

#To convert table columns
ALTER TABLE table_name CHANGE column_name column_name VARCHAR(191) CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;


Please do not copy and paste the code as is. You need to insert the appropriate properties/attributes which depend on your database name, table name, column type, etc.

In my case, I converted the database, and converted the important tables ;)

Step 4 Modify client and server connection char set

Locate your configuration file for MySQL; On a Windows machine, I found one at

C:/programData/MySQL/MySQL Server 5.6/my.ini

On a Linux/Unix machine, the location of the configuration file may depend on your MySQL installation directory and some other factors.

For simplicity I found my configuration file at //PathToMySQL/my.cnf

Open the file, and make sure property "character-set-server" is set to utf8mb4

I pasted a snippet from my config file below 

# The default character set that will be used when a new schema or table is

# created and no character set is defined

character-set-server = utf8mb4

Step 5 : Configure grails/hibernate

If you use Hibernate, then you need to update your configuration file. In my case, I use Grails, my configuration file is located at //PathToGrailsApplication/grails-app/conf/DataSource.groovy.


Open the file and look for the hibernate block. It will look like this

hibernate{....}


If you do not already have it then feel free to add it.

Inside the hibernate block, add the following.

hibernate{

    connection.charSet = 'utf8mb4'
    connection.characterEncoding='utf8mb4'
    connection.useUnicode=true
}




Save the DataSource.groovy file.

Done