Oracle-MySQL透明网关配置中关于Driver的坑

作者介绍

张忆蔚，广东联通数字与智能化创新中心DBA。

当数据存储在不同种类的数据库中，我们便会面临异类数据访问的问题。Oracle数据库网关提供了在Oracle环境中透明地访问异构数据库的能力，这个特性可以减少应用程序定制化开发工作提升应用程序的可移植性，也可以用于异构数据库间的数据访问及数据迁移。

本文将介绍Oracle-MySQL透明网关的配置方法及odbc.ini中Driver选择带来的影响。

一、 Oracle数据库ODBC网关概述

透明网关技术由异构服务和Oracle数据库ODBC网关两部分组成，二者共同支持从Oracle环境对非Oracle系统的透明访问。异构服务是数据库的集成组件，提供了连接到非Oracle系统的通用技术。Oracle ODBC网关包括SQL映射、数据类型转换等能力，通过动态查询功能访问与ODBC标准兼容的数据源。

Oracle Gateway for ODBC架构（Oracle与异构数据库部署在不同主机上）：

在以上架构中：

1. 客户端通过Oracle Net连接到Oracle数据库；
2. 异构服务（Heterogeneous Services）组件通过Oracle Net连接到Oracle ODBC 网关；
3. 网关与ODBC driver manager和ODBC driver通信；
4. 当用户会话第一次使用dblink连接到非Oracle系统时会产生一个专用的代理进程，每个用户会话接收自己的代理进程。当用户会话终止代理进程也终止。

二、Oracle-MySQL透明网关配置方法

1、环境及版本

本文主要介绍Oracle-MySQL透明网管的配置，涉及的服务器环境及操作系统数据库版本如下：

• 服务器1（OS RHEL7.3）：Oracle 11.2.0.4
• 服务器2（OS RHEL7.7）：MySQL 8.0

2、配置步骤

Oracle-MySQL透明网关的工作方式可简述为：

Oracle数据库 --> DBLINK --> TNS别名 --> Listener监听 --> ODBC --> MySQL connector --> MySQL监听 --> MySQL数据库。

Gateway配置主要包括以下步骤（在Oracle服务器上进行）：

1）验证Oracle透明网关已安装

Oracle 11.2.0.4默认安装了odbc透明网关dg4odbc，验证：

2）安装Driver Manager

Driver Manager负责管理应用程序和驱动程序间的通信，包括：解析DSN (数据源名称,ODBC的数据源名称在ODBC.INI文件中配置)，加载和卸载驱动程序，处理ODBC调用，将其传递给驱动程序。

root用户安装Driver Manager ,本案例使用unixODBC：

[root@MySQL-Test-1 ]# yum install unixODBC*

[root@MySQL-Test-1 ]#rpm -qa |grep unixODBC

unixODBC-devel-2.3.1-11.el7.x86_64

unixODBC-2.3.1-11.el7.x86_64

[root@MySQL-Test-1 ~]#

核查安装情况：

3）安装mysql odbc驱动

Connector/ODBC(MyODBC驱动程序)实现ODBC API所提供的功能，它负责处理ODBC函数调用，将SQL请求提交给MySQL服务器，并将结果返回给应用程序。

mysql-connector-odbc rpm包：mysql-connector-odbc-8.0.19-1.el7.x86_64.rpm

Rpm包下载地址：https://dev.mysql.com/downloads/connector/odbc/

[root@MySQL-Test-1 ]# rpm -ivh mysql-connector-odbc-8.0.19-1.el7.x86_64.rpm

4）odbc.ini配置

ODBC.INI是ODBC配置文件，记录了连接到服务器所需的驱动信息和数据库信息。Driver Manager将使用它来确定加载哪个驱动程序（使用数据源名DSN）。驱动程序将根据指定的DSN来读取连接参数。

在/etc/odbc.ini文件中定义DSN（数据源名称，如mysql_ iom86），配置数据源信息（驱动、IP、端口、用户名、口令、库名等）：

[oracle@MySQL-Test-1 etc]$ more odbc.ini

[mysql_iom86]

Description = ODBC for MySQL

Driver = /usr/lib64/libmyodbc8w.so

Server = xx.xx.xx.86

Port = 3306

User = username

Password = password

Database = test

CHARSET = utf8

[root@MySQL-Test-1 etc]#

5）测试mysql odbc是否配置成功

用isql测试mysql odbc是否配置成功，isql是unixODBC带的一个ODBC客户端访问工具，可使用isql +数据源名来访问目标数据库。

6）hs透明网关配置

在"ORACLE_HOME\hs\admin"目录下，默认存在名为"initdg4odbc.ora"的文件，每个使用DG4ODBC的实例，都必须对应一个"init*.ora"文件，文件命名规则：init+<网关sid>+.ora，文件主要参数：

• HS_FDS_CONNECT_INFO：ODBC数据源管理中的DSN名称
• HS_FDS_TRACE_LEVEL：追踪级别参数，出于性能影响一般不配置或者配置为"OFF"，若遇到网关问题需要跟踪日志，则配置为"Debug"

[oracle@MySQL-Test-1 admin]$ more initdg4mysql86.ora

##HS Configuration

HS_FDS_CONNECT_INFO = mysql_iom86

HS_FDS_TRACE_LEVEL = OFF

HS_FDS_SHAREABLE_NAME = /usr/lib64/libodbc.so

HS_LANGUAGE=AMERICAN_AMERICA.zhs16gbk

HS_NLS_NCHAR = UCS2

HS_FDS_FETCH_ROWS=1000

HS_RPC_FETCH_REBLOCKING=OFF

##ODBC Configuration

set ODBCINI=/etc/odbc.ini

[oracle@MySQL-Test-1 admin]$

7）网关监听配置

在listener.ora中增加如下配置，sid_name要与上面hs网关配置的网关sid一致（dg4mysql86）：

SID_LIST_LISTENER =

(SID_LIST =

(SID_DESC=

(SID_NAME=dg4mysql86)

(ORACLE_HOME=/oracle/app/product/11.2.0/dbhome_1)

(PROGRAM=dg4odbc)

)

)

8）tns配置

• ADDRESS：填写透明网关的IP地址和端口
• SID：指定连接网关的SID
• HS：指定连接的是非ORACLE数据库

在tnsnames.ora中配置：

Mysql86 =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost )(PORT = 1521))

)

(CONNECT_DATA =

(SID = dg4mysql86)

)

(HS = OK)

)

9）dblink创建以及透明网关测试

SQL> create DATABASE LINK link_mysql86 connect to "username" identified by "password" using 'mysql86';

测试在Oracle端通过透明网关访问MySQL库数据并建表（8千万+记录，2.5G数据量，约4分钟，等待事件多为：“HS message to agent”）：

三、 Where条件子句未传到MySQL端的问题

透明网关的配置本身比较简单，但是在使用时也容易遇到一些意想不到问题，比如“SQL语句中的where条件未传到MySQL端导致全表扫描”的问题。

1、问题描述

在使用透明网关过程中发现当通过透明网关访问MySQL数据库中某张千万级大表时，SQL语句中虽然包含了有索引字段的where条件，执行时却不能较快返回结果（而这种情况并不是在所有SQL中都会发生），如：

select SERIAL_NUMBER from "to_b_olcomwork"@link_mysql86 where SERIAL_NUMBER='18602031223';

语句在MySQL中执行按索引扫描，很快返回结果：

但通过透明网关执行较长时间后因网络中断报错：

SQL> select SERIAL_NUMBER from "to_b_olcomwork"@link_mysql86 where SERIAL_NUMBER='18602031223';

ERROR at line 1:

ORA-03113: end-of-file on communication channel

Process ID: 20130

Session ID: 956 Serial number: 12917

Elapsed: 00:07:54.49

SQL>

核查MySQL中发现传入的语句没有where条件，做全表扫描：

MySQL [iom]> show full processlist;

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

| Id | User | Host | db | Command | Time | State | Info |

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

| 191413 | qyr_iom0020 | unicom86:40251 | iom | Query | 0 | starting | show full processlist |

| 191472 | qyr_iom0020 | 132.121.26.26:42444 | iom | Sleep | 59 | | NULL |

| 191473 | qyr_iom0020 | 132.98.26.17:40979 | iom | Query | 24 | executing | SELECT `SERIAL_NUMBER` FROM `to_b_olcomwork` |

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

3 rows in set (0.00 sec)

MySQL [iom]>

在Oracle端查看执行计划也可见是将所有数据取到本地后再进行条件过滤：

2、问题分析及解决

诚然我们可以通过DBMS_HS_PASSTHROUGH包将原始的完整SQL直接传递到MySQL端执行，但这并未从根本上解决问题。

笔者针对这个问题在MOS上开了SR，在与后台支持工程师一同进行了数轮测试及trace跟踪（比如调整HS_FDS_FETCH_ROWS，HS_RPC_FETCH_REBLOCKING的设置、针对不同版本的MySQL库及不同的表测试等）后，最终定位这个问题与我们在odbc.ini中设置的Driver有关：当设置Driver = /usr/lib64/libmyodbc8a.so （而不是/usr/lib64/libmyodbc8w.so）时，之前的问题SQL可传送where条件到MySQL端。

进一步研究，MySQL 有两个ODBC driver：libmyodbc8a.so 是ANSI ODBC driver，libmyodbc8w.so 是UNICODE ODBC driver，二者描述解析数据的方式是有不同的：

• 使用SQL_C_CHAR检索SQL_CHAR/SQL_VARCHAR的数据，这意味着数据将位于non-Unicode连接字符集中，并映射为Oracle的CHAR/ VARCHAR2类型；

• 使用SQL_C_WCHAR检索SQL_WCHAR/SQL_WVARCHAR的数据，这意味着数据将位于Unicode连接字符集中，并映射为Oracle的NHCHAR/ NVARCHAR2类型。

所以当使用"libmyodbc8w.so" UNICODE driver运行问题SQL时，将使用SQL_C_WCHAR检索列并映射为Oracle的NHCHAR/ NVARCHAR2类型，而基于N*CHAR列的where子句会被从SELECT语句中drop掉再发送到外部数据库；而当使用" libmyodbc8a.so " ANSI driver时，基于CHAR/VARCHAR列的where子句则不会被中drop掉。

由于SERIAL_NUMBER字段是varchar类型，使用"libmyodbc8w.so"UNICODE driver时，where子句被drop掉了，但改为" libmyodbc8a.so " ANSI driver时，where子句则被传到了远端MySQL库中，如下：

这一点我们在网关的trace文件中也可以看到，如：

使用"libmyodbc8w.so" UNICODE driver：

=> dg4mysql86w_agt_630.trc

--------------------------------------

Entered hgopcda at 2020/09/30-17:54:30

Column:9(SERIAL_NUMBER): dtype:-9 (WVARCHAR), prc/scl:50/0, nullbl:1, octet:450, sign:1, radix:10

Exiting hgopcda, rc=0 at 2020/09/30-17:54:30

:

Entered hgopars, cursor id 1 at 2020/09/30-17:54:30

type:0

SQL text from hgopars, id=1, len=44 ...

00: 53454C45 43542060 53455249 414C5F4E [SELECT `SERIAL_N]

10: 554D4245 52602046 524F4D20 60746F5F [UMBER` FROM `to_]

20: 625F6F6C 636F6D77 6F726B60 [b_olcomwork`]

使用" libmyodbc8a.so " ANSI driver：

=> dg4mysql86a_agt_32490.trc

----------------------------------------------------

Entered hgopcda at 2020/09/30-17:53:06

Column:9(SERIAL_NUMBER): dtype:12 (VARCHAR), prc/scl:50/0, nullbl:1, octet:450, sign:1, radix:10

Exiting hgopcda, rc=0 at 2020/09/30-17:53:06

:

Entered hgopars, cursor id 1 at 2020/09/30-17:53:06

type:0

SQL text from hgopars, id=1, len=89 ...

00: 53454C45 43542041 312E6053 45524941 [SELECT A1.`SERIA]

10: 4C5F4E55 4D424552 60204652 4F4D2060 [L_NUMBER` FROM `]

20: 746F5F62 5F6F6C63 6F6D776F 726B6020 [to_b_olcomwork` ]

30: 41312057 48455245 2041312E 60534552 [A1 WHERE A1.`SER]

40: 49414C5F 4E554D42 4552603D 27313836 [IAL_NUMBER`='186]

50: 30323033 31323233 27 [02031223']

因此，在这个case中，我们使用ANSI ODBC Driver "libmyodbc8a.so"可以解决where条件子句问题。

四、小结

Oracle数据库网关提供了在Oracle环境中透明地访问异构数据库的能力，可以用于异构数据库间的数据访问及数据迁移。本文介绍了Oracle-MySQL透明网关的配置方法，也记录了在使用透明网关过程中遇到的“Where条件子句未传到MySQL端”问题以及解决过程，讲述了ODBC Driver选择带来的影响，希望在此分享给有需要的同学。

dbaplus社群（ID：dbaplus），关注了解更多原创精选内容