改进OceanBase的load data local infile时的JDBC驱动抛出异常的错误信息,包含具体行、字段及错误原因

改进OceanBase的load data local infile时的JDBC驱动抛出异常的错误信息,包含具体行、字段及错误原因

需求背景

在开发CSV数据导入功能时,用户通过前端界面上传CSV文件,后端服务使用LOAD DATA INFILE语句将用户上传的CSV数据导入到数据库表中。在此过程中,当遇到各种数据处理错误时(如数据类型不匹配、数据格式异常、字段长度超限、约束违反等),OceanBase的JDBC驱动无法返回详细的错误信息,导致用户无法准确定位问题所在。

MySQL的JDBC驱动在当前场景下可以返回详细的错误信息,例如


Incorrect integer value: 'str6' for column 'XT1' at row 2

而OceanBase 4.3.5当前仅返回如下简略信息:


Incorrect integer value

期望 OceanBase JDBC 驱动在执行用户上传的 CSV 文件导入时,能提供与 MySQL 一致的详细错误提示,包括具体的错误值、涉及的列名、出错的数据行号等信息,以便用户能够快速定位并修复数据问题。


测试表的建表语句为


CREATE TABLE `temp_ads_check_zj` (

  `WLB` varchar(50),

  `MXFL` varchar(50),

  `XT` varchar(50),

  `ZJ` varchar(50),

  `ZJ2` varchar(8000),

  `XT1` bigint(20),

  `ETL_TIME` date

);

OceanBase测试代码如下


String url = "jdbc:oceanbase://xxxxxx:2881/test?useUnicode=true&characterEncoding=utf-8&rewriteBatchedStatements=true&allowMultiQueries=true&allowLoadLocalInfile=true";

String user = "xxxxxx";

String password = "xxxxxx";

String sql = "load data local infile 'x' replace into table temp_ads_check_zj character set utf8mb4 fields terminated by ',' enclosed by '\"' escaped by '\"' lines terminated by '\\n' (@col1, @col2, @col3, @col4, @col5, @col6, @col7) set WLB = nullif(@col1, ''),MXFL = nullif(@col2, ''),XT = nullif(@col3, ''),ZJ = nullif(@col4, ''),ZJ2 = nullif(@col5, ''),XT1 = nullif(@col6, ''),ETL_TIME = nullif(@col7, '')";

try (Connection connection = DriverManager.getConnection(url, user, password);

        Statement statement = connection.createStatement()) {

    OceanBaseStatement oceanBaseStatement = statement.unwrap(OceanBaseStatement.class);

    ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(Files.readAllBytes(Path.of("temp_ads_check_zj.txt")));

    oceanBaseStatement.setLocalInfileInputStream(byteArrayInputStream);

    oceanBaseStatement.executeUpdate(sql);

}

抛出的异常信息为:


java.sql.SQLException: Incorrect integer value

[172.17.0.2:2882] [2025-07-23 09:02:04.065690] [YB42AC110002-00063A947A4CAD6B-0-0]

MySQL数据库测试代码如下


String url = "jdbc:mysql://xxxxxx:3306/test?useUnicode=true&characterEncoding=utf-8&rewriteBatchedStatements=true&allowMultiQueries=true&allowLoadLocalInfile=true";

String user = "xxxxxx";

String password = "xxxxxx";

String sql = "load data local infile 'x' replace into table temp_ads_check_zj character set utf8mb4 fields terminated by ',' enclosed by '\"' escaped by '\"' lines terminated by '\\n' (@col1, @col2, @col3, @col4, @col5, @col6, @col7) set WLB = nullif(@col1, ''),MXFL = nullif(@col2, ''),XT = nullif(@col3, ''),ZJ = nullif(@col4, ''),ZJ2 = nullif(@col5, ''),XT1 = nullif(@col6, ''),ETL_TIME = nullif(@col7, '')";

try (Connection connection = DriverManager.getConnection(url, user, password);

        Statement statement = connection.createStatement()) {

    StatementImpl oceanBaseStatement = statement.unwrap(StatementImpl.class);

    ByteArrayInputStream byteArrayInputStream = new ByteArrayInputStream(Files.readAllBytes(Path.of("temp_ads_check_zj.txt")));

    oceanBaseStatement.setLocalInfileInputStream(byteArrayInputStream);

    oceanBaseStatement.executeUpdate(sql);

}

抛出错误信息为:


Incorrect integer value: 'str6' for column 'XT1' at row 2

测试文件:
temp_ads_check_zj.txt (61 字节)

1)设置trace信息
SET ob_enable_show_trace=‘ON’;

2)执行sql。

3)获取上个命令的trace
select last_trace_id();

4)获取trace对应的节点
select query_sql,svr_ip from gv$ob_sql_audit where trace_id=‘第三步获取的trace信息’;

5)取对应的svr_ip节点 过滤日志
grep “第三步获取的trace信息” observer.log*
grep “第三步获取的trace信息” rootservice.log*

6)提供日志信息即可。