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 :(
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.
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 ; 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:
#To convert db tables
#To convert table columns
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.
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
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
Inside the hibernate block, add the following.
Open the file and look for the hibernate block. It will look like this
hibernate{....}
Inside the hibernate block, add the following.
hibernate{
connection.charSet = 'utf8mb4'
connection.characterEncoding='utf8mb4'
connection.useUnicode=true
}
Save the DataSource.groovy file.
