Tuesday, January 21, 2014

Character encoding issue with MySQL

ِAfter a long time, let me share my debugging challenges.

I was troubling with below error for long hours!

 org.apache.tomcat.dbcp.dbcp.SQLNestedException: Cannot create PoolableConnectionFactory (You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '????????????????' at line 1)
   at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createPoolableConnectionFactory(BasicDataSource.java:1549)
   at org.apache.tomcat.dbcp.dbcp.BasicDataSource.createDataSource(BasicDataSource.java:1388)
   at org.apache.tomcat.dbcp.dbcp.BasicDataSource.getConnection(BasicDataSource.java:1044)

What is going wrong?  ...........

One thing I am sure is, it should be character encoding issue as we can see  '????????????????' instead of exact query.

I am wondering that I don't have such short query in my place, moreover this Exception trows immediately try to connect database.

To support my assumption at a glance, I gone through the stack-trace, so root cause was as below, which clearly point that issue is related with character set.

Caused by: java.sql.SQLException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '????????????????' at line 1
   at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2975)
   at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1600)
   at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1695)
   at com.mysql.jdbc.Connection.execSQL(Connection.java:3020)
   at com.mysql.jdbc.Connection.configureClientCharacterSet(Connection.java:2343)
   at com.mysql.jdbc.Connection.initializePropsFromServer(Connection.java:3748)
   at com.mysql.jdbc.Connection.createNewIO(Connection.java:2585)
   at com.mysql.jdbc.Connection.<init>(Connection.java:1485)
   at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:266)


Is it due to character set encoding at Java side or MySQL?

Is it due to wrong mysql jdbc driver version?

I was using mysql-connector-java-3.1.14.jar So I upgrade to correct driver mysql-connector-java-5.1.9.jar. But this couldn't help us.

It is reproducible, so we need to do Reduction & Deduction,

Let us try character encoding at java, Its seem fine as we can see default character encoding is utf-8 and java process even the Arabic string as expected.

So we reduce the problem as only possible area is MySQL.
What next? of course we should experiment various options.

let us find the default character set of MySQL and how we should define various character set in MySQL.

Ohh! it is simple MySQL has configuration file my.cnf, mostly it will locate in /etc for Linux system.

What we need to add OR modify in it?

Just add OR modify below property as below.

character_set_server=utf8
collation_server=utf8_general_ci

We made the necessary changes. 

What next ? off-course restart the MySQL, we did so. Don't forget to do so :-) as it was another learning from my Experience.

Wow That worked well!.

Again learning here is take the big picture of the problem
"Exception was in Java but the fix was in MySQL configuration, If we have only concentrate fix on Java Side then it will be another Killing Bug!"