shardingsphere从4.1.1升级到5.2.1但是还没有完结,因为在执行存储过程的时候,系统提示错误如下。shardingsphere是不支持存储过程呢,但项目中不能避免使用存储过程,因为有大量的数据需要初始化,这种情况该如何应对?
### SQL: {call init_data( ?, ?, ? ) }
Caused by: java.sql.SQLFeatureNotSupportedException: prepareCall
at org.apache.shardingsphere.driver.jdbc.unsupported.AbstractUnsupportedOperationConnection.prepareCall(AbstractUnsupportedOperationConnection.java:43)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:55)
at com.sun.proxy.$Proxy170.prepareCall(Unknown Source)
at org.apache.ibatis.executor.statement.CallableStatementHandler.instantiateStatement(CallableStatementHandler.java:87)
at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59)
at sun.reflect.GeneratedMethodAccessor358.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)
既然存储过程并不涉及分片的表,为何shardingsphere还会干预呢?
使用Hikari
可以看到下面的日志
JDBC Connection [HikariProxyConnection@289895964 wrapping com.mysql.cj.jdbc.ConnectionImpl@164ef602] will not be managed by Spring
如果被shardingsphere管理则可以看到下面的日志
JDBC Connection [org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@6e64e072] will not be managed by Spring
因为shardingsphere不支持存储过程及一些语句,所以产生了动态数据源的需求,在网上找的例子配置,Mybatis-plus@DS实现动态切换数据源应用
import com.baomidou.dynamic.datasource.DynamicRoutingDataSource;
import com.baomidou.dynamic.datasource.provider.AbstractDataSourceProvider;
import com.baomidou.dynamic.datasource.provider.DynamicDataSourceProvider;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DataSourceProperty;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceAutoConfiguration;
import com.baomidou.dynamic.datasource.spring.boot.autoconfigure.DynamicDataSourceProperties;
import org.apache.shardingsphere.driver.jdbc.adapter.AbstractDataSourceAdapter;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.SpringBootConfiguration;
import org.springframework.boot.autoconfigure.AutoConfigureBefore;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.context.annotation.Primary;
import org.springframework.context.annotation.Lazy;
import javax.annotation.Resource;
import javax.sql.DataSource;
import java.util.Map;
@Configuration
@AutoConfigureBefore({DynamicDataSourceAutoConfiguration.class,
SpringBootConfiguration.class})
public class DataSourceConfig {
/**
* 分表数据源名称
*/
private static final String SHARDING_DATA_SOURCE_NAME = "acc_sharding";
@Autowired
private DynamicDataSourceProperties properties;
@Lazy
@Resource(name = "shardingDataSource")
AbstractDataSourceAdapter shardingDataSource;
@Bean
public DynamicDataSourceProvider dynamicDataSourceProvider() {
Map<String, DataSourceProperty> datasourceMap = properties.getDatasource();
return new AbstractDataSourceProvider() {
@Override
public Map<String, DataSource> loadDataSources() {
Map<String, DataSource> dataSourceMap = createDataSourceMap(datasourceMap);
// 将 shardingjdbc 管理的数据源也交给动态数据源管理
dataSourceMap.put(SHARDING_DATA_SOURCE_NAME, shardingDataSource);
return dataSourceMap;
}
};
}
@Primary
@Bean
public DataSource dataSource(DynamicDataSourceProvider dynamicDataSourceProvider) {
DynamicRoutingDataSource dataSource = new DynamicRoutingDataSource();
dataSource.setPrimary(properties.getPrimary());
dataSource.setStrict(properties.getStrict());
dataSource.setStrategy(properties.getStrategy());
dataSource.setProvider(dynamicDataSourceProvider);
dataSource.setP6spy(properties.getP6spy());
dataSource.setSeata(properties.getSeata());
return dataSource;
}
}
上面的java配置,还有一个问题没有解决,那就是shardingDataSource
这个数据源是怎么初始化的。因为shardingsphere5.2.1
没有默认数据源,因此只好先降到5.1.1
,Shardingsphere5.1.1 整合druid动态数据源,
在5.1.1
中需要去掉下面的配置,因为不支持
shardingsphere5.x整合springboot+dynamic-datasource多数据源实战
切到5.1.1
上面的动态数据源代码是有问题的,因为5.1.1版本自动装载的shardingSphereDataSource beanName=“shardingSphereDataSource”
动态数据源是好的,但带来的问题却是数据库初始化连接过多,如下面的配置,每个系统就有两份数据库连接池配置,这个需要注意,在k8s扩展服务的时候应该考虑到这一点
@Lazy
@Autowired
private DataSource shardingDataSource;
如果按照这个结论,那么将版本再切到5.2.1
会怎么样呢?猜想有些地方应该还是会继承把。这个猜想是对的。
配置如下
spring:
datasource:
dynamic:
hikari: # 全局hikariCP参数
pool-name: Retail_HikariCP #连接池名称
minimum-idle: 5 #最小空闲连接数量
idle-timeout: 120000 #空闲连接存活最大时间,默认600000(10分钟)
maximum-pool-size: 10 #连接池最大连接数,默认是10
auto-commit: true #此属性控制从池返回的连接的默认自动提交行为,默认值:true
max-lifetime: 1800000 #此属性控制池中连接的最长生命周期,值0表示无限生命周期,默认1800000即30分钟
connection-timeout: 30000 #数据库连接超时时间,默认30秒,即30000
connection-test-query: SELECT 1
datasource:
master:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
username: root
password: ENC(xxx)
url: jdbc:mysql://127.0.0.1:3306/acc?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&allowMultiQueries=true
# 数据库配置
shardingsphere:
mode:
type: Standalone
repository:
type: JDBC
datasource:
names: acc1
acc1:
type: com.zaxxer.hikari.HikariDataSource
driver-class-name: com.mysql.cj.jdbc.Driver
username: root
password: ENC(xxx)
jdbcUrl: jdbc:mysql://127.0.0.1:3306/acc?useUnicode=true&characterEncoding=UTF-8&useSSL=false&serverTimezone=Asia/Shanghai&allowMultiQueries=true
pool-name: Retail_HikariCP #连接池名称
minimum-idle: 5 #最小空闲连接数量
#idle-timeout: 600000 #空闲连接存活最大时间,默认600000(10分钟)
maximum-pool-size: 10 #连接池最大连接数,默认是10
auto-commit: true #此属性控制从池返回的连接的默认自动提交行为,默认值:true
#max-lifetime: 600000 #此属性控制池中连接的最长生命周期,值0表示无限生命周期,默认1800000即30分钟
connection-timeout: 300000 #数据库连接超时时间,默认30秒,即30000
connection-test-query: SELECT 1
props:
# 这个跟4.1.1配置有区别
sql-show: true
sharding:
default-data-source-name: acc1
rules:
sharding:
tables:
# 科目
acc_account_subject:
actual-data-nodes: acc1.acc_account_subject_$->{0..49}
table-strategy:
standard:
sharding-column: as_id
sharding-algorithm-name: acc_account_subject-inline
# 科目余额
acc_account_balance:
actual-data-nodes: acc1.acc_account_balance_$->{0..49}
table-strategy:
standard:
sharding-column: as_id
sharding-algorithm-name: acc_account_balance-inline
# 期初
acc_initial_balance:
actual-data-nodes: acc1.acc_initial_balance_$->{0..49}
table-strategy:
standard:
sharding-column: as_id
sharding-algorithm-name: acc_initial_balance-inline
# 凭证
acc_voucher:
actual-data-nodes: acc1.acc_voucher_$->{0..49}
table-strategy:
standard:
sharding-column: as_id
sharding-algorithm-name: acc_voucher-inline
# 凭证分录
acc_voucher_entry:
actual-data-nodes: acc1.acc_voucher_entry_$->{0..49}
table-strategy:
standard:
sharding-column: as_id
sharding-algorithm-name: acc_voucher_entry-inline
bindingTables:
- acc_account_subject,acc_account_balance,acc_initial_balance,acc_voucher,acc_voucher_entry
sharding-algorithms:
acc_account_subject-inline:
type: inline
props:
algorithm-expression: acc_account_subject_$->{as_id%50}
acc_account_balance-inline:
type: inline
props:
algorithm-expression: acc_account_balance_$->{as_id%50}
acc_initial_balance-inline:
type: inline
props:
algorithm-expression: acc_initial_balance_$->{as_id%50}
acc_voucher-inline:
type: inline
props:
algorithm-expression: acc_voucher_$->{as_id%50}
acc_voucher_entry-inline:
type: inline
props:
algorithm-expression: acc_voucher_entry_$->{as_id%50}
在java侧,针对分表的还需要加上注解@DS
,一般用在mapper、service上
否则会提示
### Cause: java.sql.SQLSyntaxErrorException: Table 'acc.acc_account_subject' doesn't exist
; bad SQL grammar []; nested exception is java.sql.SQLSyntaxErrorException: Table 'acc.acc_account_subject' doesn't exist
at java.util.concurrent.CompletableFuture.encodeThrowable(CompletableFuture.java:273)
at java.util.concurrent.CompletableFuture.completeThrowable(CompletableFuture.java:280)
at java.util.concurrent.CompletableFuture$AsyncSupply.run(CompletableFuture.java:1592)
at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
at java.lang.Thread.run(Thread.java:748)
Caused by: org.springframework.jdbc.BadSqlGrammarException:
### Error querying database. Cause: java.sql.SQLSyntaxErrorException: Table 'acc.acc_account_subject' doesn't exist
### The error may exist in com/whty/acc/setting/dao/AccStatementAsMapper.java (best guess)
分表的数据直接在service上加上注解
关联方,在具体的方法上加,这些第一感觉有些侵入性,但是也还好,提醒其他工程师,这个代码要注意分表,这样好像也没有错。
再怎么原生的mybatis,比shardingsphere再转义一道,还是要快些。
一般情况是没有啥问题,但是二班就一定了,看表面的sql中,为什么sql语句查询了50条呢,是因为acc_account_subject
做了50张分表,因此查询出来了50条,实际是有问题的
==> Preparing: SELECT COUNT(*) AS total FROM acc_fund_account t LEFT JOIN acc_account_subject t1 ON (t1.id = t.sub_id AND t1.as_id = t.as_id AND t1.status = ?) LEFT JOIN acc_foreign_currency t2 ON (t2.id = t.fc_id AND t2.as_id = t.as_id AND t2.status = ?) WHERE (t.as_id = ? AND t.status = ? AND t.category = ?)
==> Parameters: 01(String), 01(String), 27024(Integer), 01(String), 01(String)
<== Columns: total
<== Row: 50
<== Total: 1
再看原来生产库执行出的脚本,也就是说原来4.1.1
版本的分表是正确的,那为何5.2.1
就不行了呢,难道是退步了?
打印出sql可以看到,他干了件蠢事,遍历了所有表,难道是因为分表是left join
导致的?
调整为inner join
,可以看到他找他具体的分表了,这样问题就可以定位到left join
的问题,那么为什么会出现这个问题呢?
不可能版本越高越倒退,但可能是因为版本越高灵活性可能就没有以前那么好,因为要兼顾的东西太多,故而设置了不少规范,因此有些代码应该可以规范的方式去调整,既然acc_account_subject
是分片的,那么它的关联查询就应该带上分表字段,因此下方对应位置,关联表时应传入对应的参数。而不能像之前一样了
接下来意味着所有与分片有关的leftJoin
,您都需要检查一遍,确保没有问题.
接着实现另外一个问题,官方不支持下面的场景
但实际业务中需要批量复制数据,依然可以手工来实现分表
public static String getActualTable(Integer asId,String logicTable){
Integer suffix = asId % TABLE_SHARDING_COUNT;
return logicTable + "_"+ suffix;
}
接着使用mybatis来实现
public boolean copyByAsId(Integer newAsId, Integer copyAsId) {
String newTableName = ShardingUtils.getActualTable(newAsId, "acc_account_subject");
String copyTableName = ShardingUtils.getActualTable(copyAsId, "acc_account_subject");
return extAccAccountSubjectMapper.copyByAsId(newAsId, copyAsId, newTableName, copyTableName);
}
还有一种情况没有考虑,因为shardingsphere不支持存储过程,如果同一个service中出现,一部分代码需要执行存储过程,另外一部分代码需要执行分表,遇到这种情况如果外层增加注解就会报错,之前的工程师通过代码来手工切换数据源,那么使用@DS是否能完成呢?
Caused by: java.sql.SQLFeatureNotSupportedException: prepareCall
at org.apache.shardingsphere.driver.jdbc.unsupported.AbstractUnsupportedOperationConnection.prepareCall(AbstractUnsupportedOperationConnection.java:43)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:55)
at com.sun.proxy.$Proxy380.prepareCall(Unknown Source)
at org.apache.ibatis.executor.statement.CallableStatementHandler.instantiateStatement(CallableStatementHandler.java:87)
at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:88)
at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:59)
at sun.reflect.GeneratedMethodAccessor300.invoke(Unknown Source)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
at java.lang.reflect.Method.invoke(Method.java:498)
at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49)
如下种情况该如何应对?
SpringBoot+MyBatis-Plus多数据源@DS注解失效的解决方法,如果他说的是对的,那为何我这么操作却没有生效呢?
有方案说使用在最外层添加@DSTransactional
,然后再不同数据源添加@Transactional(propagation = Propagation.REQUIRES_NEW, rollbackFor = Exception.class)
,实际应用会有问题,会提示NullPointerException
错误
org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.lang.NullPointerException
at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:309)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.startTransaction(AbstractPlatformTransactionManager.java:400)
at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373)
at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:595)
at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:382)
at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:119)
at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
解决方案是加所用到的service上增加@DS("master")
,否则就会出错,我的天哪,这样的话,那改造的工作量还真是大,那为何自身不能默认的数据源呢?
不过调研并分析问题,也是一件有趣的事情,例如mybatis-plus实战—多数据源@DS和@Transactional冲突 ,这个人在controller上添加@Transactional,虽然并没有不能这么写的限制,但是却是少见,这种在我的团队估计我会训。
通过上面的结论@DSTransactional这条路是行不通的。
有一种方式肯定是是能解决的
@Override
public void excelImportNew(MultipartFile accountBalanceFile, MultipartFile ledgerFile, MultipartFile voucherFile, ImportAccountBalanceReq req) throws IOException {
//校验表
checkTable(accountBalanceFile, ledgerFile, voucherFile, req.getSoftwareId());
// //校验请求参数
this.checkParam(req);
Integer asId = getAsId(req);
try {
importData(asId, accountBalanceFile, ledgerFile, voucherFile, req);
} catch (Exception e) {
removeAccountSet(asId);
throw new RuntimeException(e);
}
}
这种方式实际使用了两个事务。