Oracle 到 OB 兼容性测试：一段神奇的代码漂流记之第二篇

原创尚雷 All China Database Union 2023-12-14

681

一、前言

近期一直在配合Oracle到OceanBase的兼容性业务适配测试，使用的是企业版OceanBase，版本号是 4.2.1.1，目前测试的是众多业务系统重最核心的业务系统，因为该系统涉及模块较多，本次作为功能性调研验证测试，选取了该系统最核心的业务模块进行测试。
通过测试，确实证明了OceanBase企业版对Oracle数据库的兼容性还挺好，绝大多数业务场景在OceanBase也能正常运行。
我们甚至和OB的人开玩笑，如果OB能很好的完成这套系统的迁移测试，以后你们都可以在外面吹一下，其它Oracle数据库迁移都不在话下。

很多人喜欢拿国产数据库去和Oracle对比，但毕竟Oracle是一款已存在几十年，并经过千万个业务的验证的经典数据库，听一位业内大佬讲，他们在二三十年前接触Oracle数据库的时候，那时的Oracle也是问题不断，经过几十年的修补磨合，才让Oracle在业内这么优秀。

所以我们应该抱着宽容的心态去接纳当前的国产数据库，让其好好的成长。

目前国内国产数据库风头正高，我不喜欢有些人一棒子打死Oracle，极力吹捧某国产数据库，我也更不喜欢去大肆吹捧Oracle，极力去贬低国产数据库。我始终认为，不同场景、不同业务、不同预算等就应该选择最适合自己的数据库，适合自己的就是最好的。

话题说的有些远了，我们还是回归正题。我们本次的测试，OceanBase可以覆盖了绝大多数的场景，但有些场景OceanBase还是难以满足，需要进行适配性的改造。

接下来，我将描述本次测试中的一个兼容性问题。

复制

文笔浅陋,才疏学浅，还望有经验的大牛能多多指正。也希望大家在评论区发表自己见解。

二、问题描述

这套Oracle核心数据库，体量比较大，有20多T，已存在了20多年，目前的测试是基于从Oracle测试环境全量数据同步过来的，两边的数据是一致的。
当应用连接到OceanBase数据库进行测试时，其中一个场景研发测试报错，研发那边提供了报错日志，日志内容如下：
org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:63)
at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:37)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)
at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:99)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:67)
at org.hibernate.loader.Loader.getResultSet(Loader.java:2322)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2075)
at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:2037)
at org.hibernate.loader.Loader.doQuery(Loader.java:956)
at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:357)
at org.hibernate.loader.Loader.doList(Loader.java:2868)
at org.hibernate.loader.Loader.doList(Loader.java:2850)
at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2682)
at org.hibernate.loader.Loader.list(Loader.java:2677)
at org.hibernate.loader.custom.CustomLoader.list(CustomLoader.java:338)
at org.hibernate.internal.SessionImpl.listCustomQuery(SessionImpl.java:2186)
at org.hibernate.internal.AbstractSharedSessionContract.list(AbstractSharedSessionContract.java:1204)
at org.hibernate.query.internal.NativeQueryImpl.doList(NativeQueryImpl.java:177)
at org.hibernate.query.internal.AbstractProducedQuery.list(AbstractProducedQuery.java:1617)
at org.hibernate.query.internal.AbstractProducedQuery.uniqueResult(AbstractProducedQuery.java:1659)
at com.focustech.edt.chk.oss.dao.xxxxInfoLogDao.getRemarkCountByProdId(xxxxInfoLogDao.java:299)
at com.focustech.edt.chk.oss.dao.xxxxInfoLogDao$$FastClassBySpringCGLIB$$4574643d.invoke(<generated>)
at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:793)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:763)
at org.springframework.dao.support.PersistenceExceptionTranslationInterceptor.invoke(PersistenceExceptionTranslationInterceptor.java:137)
... 127 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: (conn=829532) ORA-01722: invalid number
at com.oceanbase.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:110)
at com.oceanbase.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:202)
at com.oceanbase.jdbc.OceanBaseStatement.executeExceptionEpilogue(OceanBaseStatement.java:312)
at com.oceanbase.jdbc.JDBC4ServerPreparedStatement.prepareExecuteInternal(JDBC4ServerPreparedStatement.java:875)
at com.oceanbase.jdbc.JDBC4ServerPreparedStatement.execute(JDBC4ServerPreparedStatement.java:778)
at com.oceanbase.jdbc.JDBC4ServerPreparedStatement.executeQuery(JDBC4ServerPreparedStatement.java:742)
at org.hibernate.engine.jdbc.internal.ResultSetReturnImpl.extract(ResultSetReturnImpl.java:57)
... 149 common frames omitted
Caused by: com.oceanbase.jdbc.internal.util.exceptions.OceanBaseSqlException: ORA-01722: invalid number
at com.oceanbase.jdbc.internal.util.exceptions.OceanBaseSqlException.of(OceanBaseSqlException.java:79)
at com.oceanbase.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:199)
at com.oceanbase.jdbc.internal.protocol.AbstractQueryProtocol.exceptionWithQuery(AbstractQueryProtocol.java:180)
at com.oceanbase.jdbc.internal.protocol.AbstractQueryProtocol.executePreparedQuery(AbstractQueryProtocol.java:596)
at com.oceanbase.jdbc.JDBC4ServerPreparedStatement.prepareExecuteInternal(JDBC4ServerPreparedStatement.java:855)
... 152 common frames omitted
Caused by: java.sql.SQLException: ORA-01722: invalid number
at com.oceanbase.jdbc.internal.protocol.AbstractQueryProtocol.readErrorPacket(AbstractQueryProtocol.java:2191)
at com.oceanbase.jdbc.internal.com.send.ComStmtPrepareExecute.read(ComStmtPrepareExecute.java:242)
at com.oceanbase.jdbc.internal.protocol.AbstractQueryProtocol.executePreparedQuery(AbstractQueryProtocol.java:592)
... 153 common frames omitted

基于该应用场景，研发也提供了对应的报错SQL代码，相应代码如下：
String sb = " SELECT COUNT(1) " +
        " FROM xxxxx.OS_INFO_LOG INFO" +
        " WHERE INFO_TYPE = '2' " +
        " AND ((INFO_CATEGORY = 2 and LAN_CODE = ? and " +
        " INFO_RELAT_ID = ?) OR " +
        " (INFO_CATEGORY = 1 and LAN_CODE = ? and " +
        " INFO_RELAT_ID = ? and " +
        " (POSITION like '%2%' or POSITION like '%4%' or " +
        " POSITION like '%5%'))) " +
        " AND INFO_WEIGHT > 0  " +
        " AND (NOT EXISTS (SELECT 1 FROM xxxxxx.EDT_RMK_INFO_EXT E WHERE INFO.REC_ID = E.INFO_ID) OR EXISTS (SELECT 1 FROM xxxxxx.EDT_RMK_INFO_EXT E  WHERE INFO.REC_ID = E.INFO_ID AND ((E.BEGIN_TIME < 1 + TRUNC(SYSDATE) AND " +
        " E.END_TIME >= TRUNC(SYSDATE)) OR (E.BEGIN_TIME IS NULL AND E.END_TIME IS NULL))))  ";
java.sql.PreparedStatement st2 = connection.prepareStatement(sb);
st2.setString(1, "0");
st2.setLong(2, 3415);
st2.setString(3, "0");
st2.setLong(4, 3415);
ResultSet rs2 = st2.executeQuery();
将该SQL在OceanBase 的ODC下执行也同样报：ErrorCode = 1722, SQLState = 42000, Details = ORA-01722: invalid number
复制

三、问题排查

报ORA-01722: invalid number，显示该报错是一个“无效数字”的报错，初看应该是数据库里有和表字段不匹配的类型。
根据研发提供的SQL代码，将对应的变量信息进行替换格式化后如下：
SELECT
  COUNT(1)
FROM
  xxxxx.OS_INFO_LOG。  INFO
WHERE
  INFO_TYPE = '2'
  AND (
    (
      INFO_CATEGORY = 2
      and LAN_CODE = '0'
      and INFO_RELAT_ID = 3415
    )
    OR (
      INFO_CATEGORY = 2
      and LAN_CODE = '0'
      and INFO_RELAT_ID = 3415
      and (
        POSITION like '%2%'
        or POSITION like '%4%'
        or POSITION like '%5%'
      )
    )
  )
  AND INFO_WEIGHT > 0
  AND (
    NOT EXISTS (
      SELECT
        1
      FROM
        xxxxxx.EDT_RMK_INFO_EXT E
      WHERE
        INFO.REC_ID = E.INFO_ID
    )
    OR EXISTS (
      SELECT
        1
      FROM
        xxxxxx.EDT_RMK_INFO_EXT E
      WHERE
        INFO.REC_ID = E.INFO_ID
        AND (
          (
            E.BEGIN_TIME < 1 + TRUNC(SYSDATE)
            AND E.END_TIME >= TRUNC(SYSDATE)
          )
          OR (
            E.BEGIN_TIME IS NULL
            AND E.END_TIME IS NULL
          )
        )
    )
  );
将以上SQL分别在Oracle和OceanBase去执行，发现在Oracle执行正常，OB执行报Caused by: java.sql.SQLException: ORA-01722: invalid number
OB上的数据是从Oracle全量同步过来的，该SQL在Oracle上单独执行是没有报错的，难道是在迁移过程中有数据发生了转换。于是分别查询了表字段相应的内容。

两个表的字段类型如下：
desc OS_INFO_LOG ;
+-------------------+---------------------+------+-----+---------+-------+
| FIELD             | TYPE                | NULL | KEY | DEFAULT | EXTRA |
+-------------------+---------------------+------+-----+---------+-------+
| REC_ID            | NUMBER(10)          | NO   | PRI | NULL    | NULL  |
| LAN_CODE          | VARCHAR2(1 CHAR)    | NO   | MUL | NULL    | NULL  |
| INFO_SUBJECT      | VARCHAR2(200 CHAR)  | YES  | MUL | NULL    | NULL  |
| INFO_KEYWORD      | VARCHAR2(200 CHAR)  | YES  | MUL | NULL    | NULL  |
| INFO_CATEGORY     | VARCHAR2(1 CHAR)    | NO   | MUL | NULL    | NULL  |
| INFO_TYPE         | VARCHAR2(1 CHAR)    | NO   | MUL | NULL    | NULL  |
| INFO_REL_ID       | NUMBER(10)          | NO   | MUL | NULL    | NULL  |
。。。。。。省略部分内容
| EXIST_ACCESORY    | VARCHAR2(8 CHAR)    | YES  | NULL | '0'     | NULL  |
| FILE_NAME         | VARCHAR2(50 CHAR)   | YES  | NULL | NULL    | NULL  |
| POSITION          | VARCHAR2(50 CHAR)   | YES  | NULL | NULL    | NULL  |
+-------------------+---------------------+------+-----+---------+-------+

desc EDT_RMK_INFO_EXTE;
+---------------+--------------------+------+-----+---------+-------+
| FIELD         | TYPE               | NULL | KEY | DEFAULT | EXTRA |
+---------------+--------------------+------+-----+---------+-------+
| INFO_ID       | NUMBER(10)         | NO   | PRI | NULL    | NULL  |
| TYPE          | VARCHAR2(1 CHAR)   | YES  | MUL | NULL    | NULL  |
| NAME          | VARCHAR2(500 CHAR) | YES  | NULL | NULL    | NULL  |
| CAT_CODES     | VARCHAR2(500 CHAR) | YES  | NULL | NULL    | NULL  |
| BEGIN_TIME    | DATE               | YES  | MUL | NULL    | NULL  |
| END_TIME      | DATE               | YES  | MUL | NULL    | NULL  |
。。。。。。省略部分内容
+---------------+--------------------+------+-----+---------+-------+

Oracle和OB两边类型都一致。

从研发提供的SQL代码和表结构对比，发现INFO_CATEGORY是一个VARCHAR2类型，但应用传值是一个数字类型，我尝试将INFO_CATEGORY值调整为 '2',改写后的SQL代码如下：
SELECT
  COUNT(1)
FROM
  xxxxx.OS_INFO_LOG INFO
WHERE
  INFO_TYPE = '2'
  AND (
    (
      INFO_CATEGORY = '2'
      and LAN_CODE = '0'
      and INFO_RELAT_ID = 3415
    )
    OR (
      INFO_CATEGORY = '2'
      and LAN_CODE = '0'
      and INFO_RELAT_ID = 3415
      and (
        POSITION like '%2%'
        or POSITION like '%4%'
        or POSITION like '%5%'
      )
    )
  )
  AND INFO_WEIGHT > 0
  AND (
    NOT EXISTS (
      SELECT
        1
      FROM
        xxxxxx.EDT_RMK_INFO_EXT E
      WHERE
        INFO.REC_ID = E.INFO_ID
    )
    OR EXISTS (
      SELECT
        1
      FROM
        xxxxxx.EDT_RMK_INFO_EXT E
      WHERE
        INFO.REC_ID = E.INFO_ID
        AND (
          (
            E.BEGIN_TIME < 1 + TRUNC(SYSDATE)
            AND E.END_TIME >= TRUNC(SYSDATE)
          )
          OR (
            E.BEGIN_TIME IS NULL
            AND E.END_TIME IS NULL
          )
        )
    )
  );
将该SQL在OB和Oracle上分别执行，这次都没有报错，得到相同结果, ODC 执行结果如下：
复制

为什么未修改之前的SQL可以在Oracle上正常执行，跟OB厂商的人沟通，OB也可以做到像Oracle那样类型的转换。
于是我分别在OB和Oracle下查询了INFO_CATEGORY这个distinct值，得到的结果如下：
SQL> select distinct INFO_CATEGORY from xxxxx.OS_INFO_LOG;

INFO
----
0

2
5
6
7
1
3
4

9 rows selected.

显示INFO_CATEGORY字段有空的值，但到底这个值是不是空呢，于是又用如下方法查询了不同类型的值的长度，得到的结果如下所示：
SQL> select length(category),category  from (select distinct INFO_CATEGORY category from xxxxx.OS_INFO_LOG);   

LENGTH(CATEGORY) CATE
---------------- ----
               1 0
               1
               1 2
               1 5
               1 6
               1 7
               1 1
               1 3
               1 4
复制

显示看似为空的值其实是一个空格，但其实不是空。
我于是又将原SQL上不同字段的值使用 ''和不使用 ''分别在Oracle和OceanBase上进行了测试，结果也都不一样。
复制

当直接在Oracle上去查询这个字段的时候，其实也是会报类型不同的错误，以下是查询结果：
SQL> select to_number(info_category) from xxxx.OS_INFO_LOG;
select to_number(info_category) from xxxxx.OS_INFO_LOG
       *
ERROR at line 1:
ORA-01722: invalid number
复制

四、问题处理

将该问题反馈给了OceanBase工程师，OceanBase工程师的反馈是因为两者的驱动不同，因SQL不规范，导致产生隐式转换，同时表存在无法转换的数据导致报错，对于该问题，如果OceanBase适配，就需要进行大规模的优化驱动修改，这个是一个很大的工作量，对于此类问题，可以使用绑定执行计划来规避。
但这个业务系统可能存在很多这种类型的SQL，很多业务SQL是通过业务拼接生成的，所以通过绑定变量去执行，显然又不太可能，所以对于此类问题，OceanBase暂时确实也没有更好的解决办法。

OceanBase对于这种多表关联隐式转换方面还是有待改进的。

所以，回到开头，就像我前一篇文章Oracle 到 OB兼容性测试：一段神奇的代码漂流记所描述的那样，有时不得不佩服Oracle的强大。

但我也相信随着时间的推移，国产数据库也会变得越来越优秀。