search

apache / incubator-seata

:fire: Seata is an easy-to-use, high-performance, open source distributed transaction solution.
https://seata.apache.org/
Apache License 2.0
25.26k stars 8.77k forks source link

seata1.4.2版本兼容 mysql8.0.22 数据库列大小写问题 #4764

Closed azengqiang closed 2 years ago

azengqiang commented 2 years ago

Ⅰ. Issue Description

执行 update 语句，seata代理数据源构造 beforeImage 时，buildTableRecords 方法报了一个 NPE 异常

Ⅱ. Describe what happened

经覆盖源码打印日志发现是跟数据库连接池中的API从ResultSet中获取列名有关系，8.0.22版本以前的MySQL获取到的列名以数据库列大小写为准，而8.0.22版本及以后数据库却以 update 语句中的字段名大小写为准。异常堆栈如下：

Caused by: org.springframework.jdbc.UncategorizedSQLException: 
### Error updating database.  Cause: java.sql.SQLException: io.choerodon.core.exception.CommonException: BuildRecords cannot get column meta: DEFECT_TREATMENT_ID
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: UPDATE test_seata SET DEFECT_TREATMENT_ID = ?, INSPECTION_PLATFORM_NUM = ?, LAST_UPDATED_BY = ?, LAST_UPDATE_DATE = ?, OBJECT_VERSION_NUMBER = OBJECT_VERSION_NUMBER + 1 WHERE INSPECTION_PLATFORM_ID = ? AND TENANT_ID = ? AND OBJECT_VERSION_NUMBER = ?
### Cause: java.sql.SQLException: io.choerodon.core.exception.CommonException: BuildRecords cannot get column meta: DEFECT_TREATMENT_ID
; uncategorized SQLException; SQL state [null]; error code [0]; io.choerodon.core.exception.CommonException: BuildRecords cannot get column meta: DEFECT_TREATMENT_ID; nested exception is java.sql.SQLException: io.choerodon.core.exception.CommonException: BuildRecords cannot get column meta: DEFECT_TREATMENT_ID
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92) ~[mybatis-spring-2.0.6.jar!/:2.0.6]
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:441) ~[mybatis-spring-2.0.6.jar!/:2.0.6]
    at com.sun.proxy.$Proxy206.update(Unknown Source) ~[na:na]
    at org.mybatis.spring.SqlSessionTemplate.update(SqlSessionTemplate.java:288) ~[mybatis-spring-2.0.6.jar!/:2.0.6]
    at org.hzero.boot.modeler.executor.action.BatchSaveAction.processSingleTableSave(BatchSaveAction.java:215) ~[hzero-boot-modeler-driver-2.2.5.RELEASE.jar!/:2.2.5.RELEASE]
    at org.hzero.boot.modeler.executor.action.BatchSaveAction.batchSaveRecursion(BatchSaveAction.java:89) ~[hzero-boot-modeler-driver-2.2.5.RELEASE.jar!/:2.2.5.RELEASE]
    at org.hzero.boot.modeler.executor.action.BatchSaveAction.doAction(BatchSaveAction.java:64) ~[hzero-boot-modeler-driver-2.2.5.RELEASE.jar!/:2.2.5.RELEASE]
    ... 145 common frames omitted
Caused by: java.sql.SQLException: io.choerodon.core.exception.CommonException: BuildRecords cannot get column meta: DEFECT_TREATMENT_ID
    at io.seata.rm.datasource.exec.ExecuteTemplate.execute(ExecuteTemplate.java:115) ~[seata-all-1.4.2.jar!/:1.4.2]
    at io.seata.rm.datasource.exec.ExecuteTemplate.execute(ExecuteTemplate.java:50) ~[seata-all-1.4.2.jar!/:1.4.2]
    at io.seata.rm.datasource.PreparedStatementProxy.execute(PreparedStatementProxy.java:55) ~[seata-all-1.4.2.jar!/:1.4.2]
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.update(PreparedStatementHandler.java:47) ~[mybatis-3.5.6.jar!/:3.5.6]
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.update(RoutingStatementHandler.java:74) ~[mybatis-3.5.6.jar!/:3.5.6]
    at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:50) ~[mybatis-3.5.6.jar!/:3.5.6]
    at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[mybatis-3.5.6.jar!/:3.5.6]
    at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) ~[mybatis-3.5.6.jar!/:3.5.6]
    at sun.reflect.GeneratedMethodAccessor514.invoke(Unknown Source) ~[na:na]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_332]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_332]
    at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49) ~[mybatis-3.5.6.jar!/:1.8.1.RELEASE]
    at org.hzero.mybatis.parser.SqlParserInterceptor.intercept(SqlParserInterceptor.java:84) ~[hzero-boot-cusz-1.0.0-SNAPSHOT.jar!/:1.8.1.RELEASE]
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.5.6.jar!/:1.8.1.RELEASE]
    at com.sun.proxy.$Proxy344.update(Unknown Source) ~[na:na]
    at sun.reflect.GeneratedMethodAccessor514.invoke(Unknown Source) ~[na:na]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_332]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_332]
    at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49) ~[mybatis-3.5.6.jar!/:1.8.1.RELEASE]
    at org.hzero.boot.customize.interceptor.ResetBoundSqlInterceptor.intercept(ResetBoundSqlInterceptor.java:47) ~[hzero-boot-cusz-1.0.0-SNAPSHOT.jar!/:1.0.0-SNAPSHOT]
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.5.6.jar!/:1.8.1.RELEASE]
    at com.sun.proxy.$Proxy344.update(Unknown Source) ~[na:na]
    at sun.reflect.GeneratedMethodAccessor514.invoke(Unknown Source) ~[na:na]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_332]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_332]
    at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49) ~[mybatis-3.5.6.jar!/:1.8.1.RELEASE]
    at io.choerodon.mybatis.language.MultiLanguageInterceptor.intercept(MultiLanguageInterceptor.java:77) ~[hzero-starter-mybatis-mapper-1.8.1.RELEASE.jar!/:1.8.1.RELEASE]
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.5.6.jar!/:1.8.1.RELEASE]
    at com.sun.proxy.$Proxy344.update(Unknown Source) ~[na:na]
    at sun.reflect.GeneratedMethodAccessor514.invoke(Unknown Source) ~[na:na]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_332]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_332]
    at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49) ~[mybatis-3.5.6.jar!/:1.8.1.RELEASE]
    at org.hzero.mybatis.security.DataSecurityInterceptor.intercept(DataSecurityInterceptor.java:83) ~[hzero-starter-mybatis-mapper-1.8.1.RELEASE.jar!/:1.8.1.RELEASE]
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.5.6.jar!/:1.8.1.RELEASE]
    at com.sun.proxy.$Proxy344.update(Unknown Source) ~[na:na]
    at sun.reflect.GeneratedMethodAccessor514.invoke(Unknown Source) ~[na:na]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_332]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_332]
    at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49) ~[mybatis-3.5.6.jar!/:1.8.1.RELEASE]
    at org.hzero.mybatis.security.SecurityTokenInterceptor.intercept(SecurityTokenInterceptor.java:36) ~[hzero-starter-mybatis-mapper-1.8.1.RELEASE.jar!/:1.8.1.RELEASE]
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:61) ~[mybatis-3.5.6.jar!/:1.8.1.RELEASE]
    at com.sun.proxy.$Proxy344.update(Unknown Source) ~[na:na]
    at sun.reflect.GeneratedMethodAccessor514.invoke(Unknown Source) ~[na:na]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_332]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_332]
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) ~[mybatis-3.5.6.jar!/:1.8.1.RELEASE]
    at com.sun.proxy.$Proxy344.update(Unknown Source) ~[na:na]
    at sun.reflect.GeneratedMethodAccessor514.invoke(Unknown Source) ~[na:na]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_332]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_332]
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) ~[mybatis-3.5.6.jar!/:1.8.1.RELEASE]
    at com.sun.proxy.$Proxy344.update(Unknown Source) ~[na:na]
    at sun.reflect.GeneratedMethodAccessor514.invoke(Unknown Source) ~[na:na]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_332]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_332]
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:63) ~[mybatis-3.5.6.jar!/:1.8.1.RELEASE]
    at com.sun.proxy.$Proxy344.update(Unknown Source) ~[na:na]
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) ~[mybatis-3.5.6.jar!/:3.5.6]
    at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:1.8.0_332]
    at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:1.8.0_332]
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_332]
    at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_332]
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427) ~[mybatis-spring-2.0.6.jar!/:2.0.6]
    ... 150 common frames omitted
Caused by: io.choerodon.core.exception.CommonException: BuildRecords cannot get column meta: DEFECT_TREATMENT_ID
    at io.seata.rm.datasource.sql.struct.TableRecords.buildRecords(TableRecords.java:190) ~[classes!/:1.4.2]
    at io.seata.rm.datasource.exec.BaseTransactionalExecutor.buildTableRecords(BaseTransactionalExecutor.java:363) ~[seata-all-1.4.2.jar!/:1.4.2]
    at io.seata.rm.datasource.exec.UpdateExecutor.beforeImage(UpdateExecutor.java:76) ~[classes!/:1.4.2]
    at io.seata.rm.datasource.exec.AbstractDMLBaseExecutor.executeAutoCommitFalse(AbstractDMLBaseExecutor.java:99) ~[seata-all-1.4.2.jar!/:1.4.2]
    at io.seata.rm.datasource.exec.AbstractDMLBaseExecutor.doExecute(AbstractDMLBaseExecutor.java:84) ~[seata-all-1.4.2.jar!/:1.4.2]
    at io.seata.rm.datasource.exec.BaseTransactionalExecutor.execute(BaseTransactionalExecutor.java:113) ~[seata-all-1.4.2.jar!/:1.4.2]
    at io.seata.rm.datasource.exec.ExecuteTemplate.execute(ExecuteTemplate.java:111) ~[seata-all-1.4.2.jar!/:1.4.2]
    ... 213 common frames omitted

Ⅲ. Describe what you expected to happen

希望能够兼容 mysql8.0.22 版本数据库列大小写。例如：

  // seata\rm-datasource\src\main\java\io\seata\rm\datasource\sql\struct\TableMeta.java

  public ColumnMeta getColumnMeta(String colName) {
        LOGGER.info("[TableMeta] get column meta of colName =  {} ", colName);
        ColumnMeta columnMeta = allColumns.get(colName);
        if(columnMeta != null) {
            return columnMeta;
        }
        LOGGER.warn("The {} case of the column name does not match the SQL statement, and case is ignored here", colName);
        Map<String, ColumnMeta> caseInsensitiveColumnNameToColumnMeata = new LinkedCaseInsensitiveMap<>();
        caseInsensitiveColumnNameToColumnMeata.putAll(allColumns);
        columnMeta = caseInsensitiveColumnNameToColumnMeata.get(colName);
        if(columnMeta != null) {
            return columnMeta;
        } else {
            LOGGER.error("[TableMeta] get column meta not find colName = {} ", colName);
            return columnMeta;
        }
    }

Ⅳ. How to reproduce it (as minimally and precisely as possible)

  1. 安装 seata1.4.2 版本和 mysql8.0.22及以上版本
  2. 任意执行一个 update 语句，需SQL中的列名与数据库中大小写不一致
  3. 此时会报 NPE 异常

Ⅴ. Anything else we need to know?

Ⅵ. Environment:

funky-eyes commented 2 years ago

请使用1.5.1版本测试

azengqiang commented 2 years ago

请使用1.5.1版本测试

你好，请问是 1.5.1 版本兼容了数据库列大小写这个问题了吗？

funky-eyes commented 2 years ago

请使用1.5.1版本测试

你好，请问是 1.5.1 版本兼容了数据库列大小写这个问题了吗？

是的，建议先测试下