Extra space (blanks padded) with data

Most of us experience blank padded with data, when we deal with database, experienced developer will look the data type is varchar or not and it's size defined as per the requirement and fix the issue quickly.

Let me share another experience with extra padded space that may occur even when we carefully select the data type.

We deployed tested code in production, but it got failing since there is extra space in the value when we retrieve from the oracle database table. Same code work fine in SIT environment,

So debugging was found to be easy as it is environmental issue.

We verify the column type in both environment, surprisingly both are char(2).

Then what will be the issue?
why it works fine in SIT not in production, Although we define data type char of size 2, we are getting data as "01    " instead of  "01"

When we dig into deep we realize that even though both are char(2) there is difference in both environment, 

In production it was CHAR(2 CHAR) but in SIT it is CHAR(2 BYTE)

root cause is When neither BYTE nor CHAR is specified, the setting of NLS_LENGTH_SEMANTICS at the time of column creation determines which is used.

so we alter the table column data type and it got working fine :-)

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