Thursday, September 17, 2015

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".


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.


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


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

Inside the hibernate block, add the following.


    connection.charSet = 'utf8mb4'

Save the DataSource.groovy file.


Wednesday, August 12, 2015

How to upgrade Grails 2 applications and plugins to Grails 3

How to upgrade Grails 2 applications and plugins to work with Grails 3

 The Grails framework is a web framework for developing dynamic websites. It has been recently upgraded to version 3.0 and the upgrade has introduced breaking changes. According to the grails documentation, Grails 3.0 is a complete ground up rewrite of Grails and introduces new concepts and components for many parts of the framework.

The 'rewrite' part means that a lot has changed, including class packages.

This means that your plugin will break if it imports any of the Grails API packages that have been moved.

Although the Grails documentation provides a guide for upgrading applications and plugins, it does not provide information about all the Grails API package changes.

To save software development time, please find some of the package changes below.

Grails 2 Grails 3
org.codehaus.groovy.grails.web.servlet.mvc.GrailsParameterMap grails.web.servlet.mvc.GrailsParameterMap
org.codehaus.groovy.grails.web.servlet.GrailsApplicationAttributes org.grails.web.util.GrailsApplicationAttributes
org.codehaus.groovy.grails.web.servlet.mvc.GrailsWebRequest org.grails.web.servlet.mvc.GrailsWebRequest
org.codehaus.groovy.grails.web.mapping.UrlMappingInfo grails.web.mapping.UrlMappingInfo
org.codehaus.groovy.grails.plugins.web.api.ResponseMimeTypesApi org.grails.web.mime.HttpServletResponseExtension
org.codehaus.groovy.grails.commons.GrailsApplication grails.core.GrailsApplication
org.codehaus.groovy.grails.web.mapping.DefaultUrlMappingsHolder org.grails.web.mapping.DefaultUrlMappingsHolder
org.codehaus.groovy.grails.commons.DefaultGrailsControllerClass org.grails.core.DefaultGrailsControllerClass
org.codehaus.groovy.grails.web.mapping.DefaultUrlMappingEvaluator org.grails.web.mapping.DefaultUrlMappingEvaluator
org.codehaus.groovy.grails.commons.DefaultGrailsApplication grails.core.DefaultGrailsApplication
org.codehaus.groovy.grails.commons.ClassPropertyFetcher org.grails.core.util.ClassPropertyFetcher
org.codehaus.groovy.grails.web.util.WebUtils org.grails.web.util.WebUtils
org.codehaus.groovy.grails.commons.ControllerArtefactHandler org.grails.core.artefact.ControllerArtefactHandler

Essentially, you need to change your package imports from Grails 2 plugins to the corresponding packages in Grails 3.

For more information about how to upgrade your grails application please see Upgrading your Grails application

Tuesday, August 4, 2015

Parsing and binding date formats from params in Grails.

"Grails is a powerful web framework for the Java platform aimed at multiplying developers' productivity" - grails.

We recently decided to rewrite some parts of our software ( written in grails )as web applications. 

The reason we made that decision is something I hope to write about soon. The task required us to send JSON strings (POST request) that contain datetime information along with other bits for data to the server. 

To bind a date string from the 'param' seamlessly ( without writing extra code to extract the date in your controller) you need to add the following to your grails config file ( config.groovy or application.yml ).

grails.databinding.dateFormats = [ "yyyy-MM-dd'T'HH:mm:ss.SSSXXX", "yyyy-MM-dd'T'HH:mm:ss.SSSZ" ,'yyyy-MM-dd HH:mm:ss.S','MMddyyyy' ]

With the above configuration in place, there was no need to extract the date and bind it manually to the domain class or command object.