MySQL to Oracle Characterset Problem -- Solved

In our environment, one of the Oracle production DB fetches MySQL DB data for reporting purposes. Recently application team reported that they are getting data WITH SPACE in between. 

DOC	REF	REFNO	REMARKS
R C	R A	1 0 1 2 0	R E C E I P T
R C	R A	1 0 1 2 0	D E P O S I T

Solution:

1. Check Character set of MySQL database.

mysql> show variables like "character_set_database";

+------------------------+--------+

| Variable_name          | Value  |

+------------------------+--------+

| character_set_database | latin1 |

+------------------------+--------+

1 row in set (0.00 sec)

2. Insert below line in odbc.ini file

set LD_LIBRARY_PATH=/usr/lib64/lib

Description     = MySQL Production database

Trace       = ON

TraceFile   = /tmp/stderr

SERVER      = 10.xx.XX.XX

USER        = root

PASSWORD    = XXXX

PORT        = 3306

DATABASE    = MYSQLPROD

Driver=/home/oracle/oracleTOmysql/lib/libmyodbc8w.so

CHARSET = latin1

3. Fetch the data again from oracle DB. Now getting proper data from MySQL.

DOC	REF	REFNO	REMARKS
RC	RA	10120	RECEIPT
RC	RA	10120	DEPOSIT

Hope this will help 😊