改进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 字节)