shardingjdbc 4.0.0 seata分布式事务Failed to fetch schema问题

报错

12-18 15:18:35.931 [ERROR] [i.s.r.d.s.s.cache.AbstractTableMetaCache:63  ]  [traceId:][spanId:] - get table meta of the table `wh_stock_log` error: Failed to fetch schema of xxx
java.sql.SQLException: Failed to fetch schema of wh_stock_logat io.seata.rm.datasource.sql.struct.cache.MysqlTableMetaCache.fetchSchema(MysqlTableMetaCache.java:86)at io.seata.rm.datasource.sql.struct.cache.AbstractTableMetaCache.lambda$getTableMeta$0(AbstractTableMetaCache.java:61)at com.github.benmanes.caffeine.cache.BoundedLocalCache.lambda$doComputeIfAbsent$14(BoundedLocalCache.java:2344)at java.base/java.util.concurrent.ConcurrentHashMap.compute(ConcurrentHashMap.java:1908)at com.github.benmanes.caffeine.cache.BoundedLocalCache.doComputeIfAbsent(BoundedLocalCache.java:2342)at com.github.benmanes.caffeine.cache.BoundedLocalCache.computeIfAbsent(BoundedLocalCache.java:2325)at com.github.benmanes.caffeine.cache.LocalCache.computeIfAbsent(LocalCache.java:108)at com.github.benmanes.caffeine.cache.LocalManualCache.get(LocalManualCache.java:62)at io.seata.rm.datasource.sql.struct.cache.AbstractTableMetaCache.getTableMeta(AbstractTableMetaCache.java:59)at io.seata.rm.datasource.AbstractConnectionProxy.prepareStatement(AbstractConnectionProxy.java:115)at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:86)at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59)at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)

解决

shardingjdbc 4.0.0 升级到shardingjdbc 4.1.0
官方修复的pr：
https://github.com/apache/shardingsphere/pull/5027/commits

源码分析

shardingjdbc 4.0.0

Failed to fetch schema 报错是由seata的MysqlTableMetaCache 类的fetchSchema方法

    @Overrideprotected TableMeta fetchSchema(Connection connection, String tableName) throws SQLException {String sql = "SELECT * FROM " + ColumnUtils.addEscape(tableName, JdbcConstants.MYSQL) + " LIMIT 1";try (Statement stmt = connection.createStatement();ResultSet rs = stmt.executeQuery(sql)) {return resultSetMetaToSchema(rs.getMetaData(), connection.getMetaData());} catch (SQLException sqlEx) {throw sqlEx;} catch (Exception e) {throw new SQLException(String.format("Failed to fetch schema of %s", tableName), e);}}

进一步看代码，resultSetMetaToSchema方法，位于MysqlTableMetaCache类，问题在于dbmd.getColumns

    private TableMeta resultSetMetaToSchema(ResultSetMetaData rsmd, DatabaseMetaData dbmd)throws SQLException {...try (ResultSet rsColumns = dbmd.getColumns(catalogName, schemaName, tableName, "%");ResultSet rsIndex = dbmd.getIndexInfo(catalogName, schemaName, tableName, false, true)) {while (rsColumns.next()) {...

进一步看代码，因为我代码用的是hikari，最后调用hikari的ProxyDatabaseMetaData类的getColumns方法

   public ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern) throws SQLException {ResultSet resultSet = delegate.getColumns(catalog, schemaPattern, tableNamePattern, columnNamePattern);Statement statement = resultSet.getStatement();if (statement != null) {statement = ProxyFactory.getProxyStatement(connection, statement);}return ProxyFactory.getProxyResultSet(connection, (ProxyStatement) statement, resultSet);}

通过debug断点可知，catalogName值和schemaName都为logic_db

shardingjdbc 4.1.0

seata的MysqlTableMetaCache 类的fetchSchema方法

    protected TableMeta fetchSchema(Connection connection, String tableName) throws SQLException {String sql = "SELECT * FROM " + ColumnUtils.addEscape(tableName, JdbcConstants.MYSQL) + " LIMIT 1";try (Statement stmt = connection.createStatement();ResultSet rs = stmt.executeQuery(sql)) {return resultSetMetaToSchema(rs.getMetaData(), connection.getMetaData());} catch (SQLException sqlEx) {throw sqlEx;} catch (Exception e) {throw new SQLException(String.format("Failed to fetch schema of %s", tableName), e);}}

进一步看代码，resultSetMetaToSchema方法，位于MysqlTableMetaCache类，问题在于dbmd.getColumns

    private TableMeta resultSetMetaToSchema(ResultSetMetaData rsmd, DatabaseMetaData dbmd)throws SQLException {...try (ResultSet rsColumns = dbmd.getColumns(catalogName, schemaName, tableName, "%");ResultSet rsIndex = dbmd.getIndexInfo(catalogName, schemaName, tableName, false, true)) {while (rsColumns.next()) {...

注意看这里，进一步看代码，MultipleDatabaseMetaData类的getColumns方法

    public final ResultSet getColumns(final String catalog, final String schemaPattern, final String tableNamePattern, final String columnNamePattern) throws SQLException {return createDatabaseMetaDataResultSet(getDatabaseMetaData().getColumns(getActualCatalog(catalog), getActualSchema(schemaPattern), getActualTableNamePattern(tableNamePattern), columnNamePattern));}

getActualCatalog方法

    private String getActualCatalog(final String catalog) {return null != catalog && catalog.contains(DefaultSchema.LOGIC_NAME) ? shardingSphereMetaData.getDataSources().getDataSourceMetaData(getDataSourceName()).getCatalog() : catalog;}

getActualSchema方法

    private String getActualSchema(final String schema) {return null != schema && schema.contains(DefaultSchema.LOGIC_NAME) ? shardingSphereMetaData.getDataSources().getDataSourceMetaData(getDataSourceName()).getSchema() : schema;}

getDatabaseMetaData().getColumns 下一步调用HikariProxyDatabaseMetaData的

    public ResultSet getColumns(String var1, String var2, String var3, String var4) throws SQLException {try {return super.getColumns(var1, var2, var3, var4);} catch (SQLException var6) {throw this.checkException(var6);}}

最后也调用hikari的ProxyDatabaseMetaData 的getColumns方法

   public ResultSet getColumns(String catalog, String schemaPattern, String tableNamePattern, String columnNamePattern) throws SQLException {ResultSet resultSet = delegate.getColumns(catalog, schemaPattern, tableNamePattern, columnNamePattern);Statement statement = resultSet.getStatement();if (statement != null) {statement = ProxyFactory.getProxyStatement(connection, statement);}return ProxyFactory.getProxyResultSet(connection, (ProxyStatement) statement, resultSet);}

catalog 取到的是正确的值，而不是logic_db

题外：catalog获取源码分析

shardingjdbc 4.1.0 源码中，catalog是在项目启动时，shardingjdbc在ShardingDataSourceFactory.createDataSource创建数据源时，
在shardingsphere-common的MySQLDatabaseType类getDataSourceMetaData方法获取

    public MySQLDataSourceMetaData getDataSourceMetaData(final String url, final String username) {return new MySQLDataSourceMetaData(url);}

MySQLDataSourceMetaData 类的MySQLDataSourceMetaData构造方法

    public MySQLDataSourceMetaData(final String url) {Matcher matcher = pattern.matcher(url);if (!matcher.find()) {throw new UnrecognizedDatabaseURLException(url, pattern.pattern());}hostName = matcher.group(4);port = Strings.isNullOrEmpty(matcher.group(5)) ? DEFAULT_PORT : Integer.valueOf(matcher.group(5));catalog = matcher.group(6);schema = null;}

最终，实际就是通过配置的url分析出catalog

总结

综上所述，出现 Failed to fetch schema 的错误主要是由于 Sharding-JDBC 4.0.0 在逻辑数据库处理上的不足，导致无法正确获取元数据。升级到 Sharding-JDBC 4.1.0 后，相关处理逻辑得到了改进，从而解决了这一问题。