How to handle SQL state [HY000] , error code [1366] in MySQL ?

Posted: August 26, 2012 in Database
Tags: ,

This is one of the common problems that might arise if you are using MySQL Database for your project.

Often our project encoding is UTF-8 but many are not aware of the fact that MySQL supports utf8-support_ci format for the database columns. In such cases, while we are trying to persist the application data into the MySQL database, we will encounter the following error. 

 SQL state [HY000]; error code [1366]

This is due to Unicode Surrogates. (You can read more about them here http://en.wikipedia.org/wiki/Mapping_of_Unicode_characters#Surrogates ) Surrogate characters are not real characters. They are reserved for UTF-16 encoding.

So the fix for this problem is to trim these character using the method  

boolean java.lang.Character.isHighSurrogate(char ch)   and   boolean java.lang.Character.isLowSurrogate(char ch)

Example :

public static String trimUnicodeSurrogateCharacters(String message) {
    StringBuilder sb = new StringBuilder();
   for (int i = 0; i < message.length(); i++) {
         char ch = text.charAt(i);
         if (!Character.isHighSurrogate(ch) && !Character.isLowSurrogate(ch)) {
             sb.append(ch);
      }
  }
 return sb.toString();
}

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s