DistSQL：像数据库一样使用 Apache ShardingSphere

SphereEx官方君 · 2021 年9 月 24 日 16:00

Apache ShardingSphere 5.0.0-beta 深度解析的第一篇文章和大家一起重温了 ShardingSphere 的内核原理，并详细阐述了此版本在内核层面，特别是 SQL 能力方面的优化和提升。强大稳定的内核是 ShardingSphere 持续发展的基础，与此同时，ShardingSphere 在研发分布式数据库生态特性上也在努力摸索。本次 5.0.0-beta 版本发布的 DistSQL，用于搭配整个 ShardingSphere 分布式数据库体系，在提供更标准化的分布式数据库管理方式的同时，兼具灵活、便捷和优雅的特性。

本文将带领大家全面认识 DistSQL，并结合实战案例展示如何使用 DistSQL 一键管理 ShardingSphere 分布式数据库服务。

孟浩然

SphereEx 高级 Java 工程师

Apache ShardingSphere Committer

曾就职于京东科技，负责数据库产品研发，热爱开源，关注数据库生态，目前专注于 ShardingSphere 数据库中间件开发以及开源社区建设。

初识 DistSQL

相信大家对 SQL(Structured Query Language) 都不陌生，SQL 是一种数据查询和程序设计语言，同时作为关系数据库管理系统的标准语言，用于存取数据以及查询、更新和管理关系数据库系统。

和标准 SQL 类似，DistSQL（Distributed SQL），即分布式 SQL，是 ShardingSphere 特有的一种内置 SQL 语言，能够提供标准 SQL 之外的增量功能操作能力。借助于 ShardingSphere 强大的 SQL 解析引擎，DistSQL 提供了类似于标准 SQL 的语法结构和语法校验体系，在保证规范化的同时，也让 DistSQL 更加灵活。

ShardingSphere 提出的 Database Plus 理念，旨在打造兼具数据库且贴合实际业务需求的开源分布式数据库体系，而 DistSQL 正是在传统数据库上层构建，提供既贴合标准又拥有 ShardingSphere 功能特色的 SQL 能力，能更好的为传统数据库赋能。

DistSQL 设计初衷

ShardingSphere 快速发展的几年来，随着内核的逐步稳定，以及对核心功能的持续打磨，在数据库中间件领域，已然独树一帜。作为国内开源的佼佼者， ShardingSphere 在分布式数据库生态的探索中并没有停止脚步，打破中间件和数据库之间的界限，让开发者像使用数据库一样使用 Apache ShardingSphere，是 DistSQL 的设计目标，也是 ShardingSphere 从面向开发人员的框架和中间件转变为面向运维人员的基础设施产品不可或缺的能力。

DistSQL 语法体系

DistSQL 在设计之初，就以面向标准为目标，充分考虑数据库开发和运维人员的使用习惯，所以 DistSQL 的语法全面借鉴标准 SQL 语言，兼顾可读性和易用性的同时，最大程度保留 ShardingSphere 自身的特性，并为使用者提供最宽泛的自定义边界，以应对不同的业务场景。对于熟悉 SQL 和 ShardingSphere 的开发者，可以快速入手。

标准的 SQL 提供了如 DQL、DDL、DML、DCL 等不同类型的语法，用于定义不同功能的 SQL 语句，DistSQL 也定义了一套自己的语法体系。

在 ShardingSphere 中， DistSQL 的语法目前主要划分为 RDL、RQL 和 RAL 三种类型：

RDL（Resource & Rule Definition Language），资源规则定义语言，用于资源和规则的创建、修改和删除；
RQL（Resource & Rule Query Language），资源规则查询语言，用于资源和规则的查询和展现；
RAL（Resource & Rule Administrate Language），资源规则管理语言，用于 Hint、事务类型切换、分片执行计划查询等增量功能操作。

DistSQL 的语法体系为 ShardingSphere 迈向分布式数据库搭起了桥梁，目前还在持续完善中，随着更多的想法被实现，DistSQL 势必会越来越强大。也欢迎对此感兴趣的开发者加入 ShardingSphere，为 DistSQL 提供想法，贡献代码。

了解更详细的语法规则，可参考官方文档：https://shardingsphere.apache.org/document/current/cn/features/dist-sql/syntax/

DistSQL 实战

在了解了 DistSQL 的设计理念和语法体系后，接下来本文以数据分片为例，实战演示如何通过 DistSQL 来搭建一套基于 ShardingSphere 的数据分片服务。

环境准备

启动 MySQL 服务
创建用于分片的 MySQL 数据库
启动 Zookeeper 服务
打开分布式治理配置并启动 ShardingSphere-Proxy (ShardingSphere-Proxy :: ShardingSphere)

实战演示

使用 MySQL 命令行连接到启动的 ShardingSphere-Proxy
创建并查询分布式数据库 sharding_db

使用新创建的数据库

执行 RDL 配置 2 个用于分片的数据源资源 ds_1 和 ds_2

执行 RQL 查询新增加的数据源资源

执行 RDL 创建 t_order 表的分片规则

执行 RQL 查询分片规则

RQL 除了支持查询当前数据库下的所有分片规则，也可以通过下面的语句查询单个表的分片规则

SHOW SHARDING TABLE RULE t_order FROM sharding_db

创建并查询 t_order 分片表

在 ShardingSphere-Proxy 端成功创建分片表 t_order 后，通过客户端连接到底层数据库 ds_1 和 ds_2，ShardingSphere 已经根据 t_order 表的分片规则自动创建了分片表

分片表创建完成后，继续在 ShardingSphere-Proxy 端执行 SQL 语句插入数据

通过 RAL 查询执行计划

至此，使用 DistSQL 搭建 ShardingSphere 数据分片服务已经完成，对比之前版本以配置文件驱动的 ShardingSphere 代理端相比，DistSQL 对开发者更友好，对资源和规则的管理更加灵活，以 SQL 驱动的方式，更是实现了 DistSQL 和标准 SQL 的无缝对接。

以上的示例中，仅演示了小部分 DistSQL 语法的使用，除了通过 CREATE 和 SHOW 语句创建和查询资源、规则，DistSQL 同样提供了 ALTRE DROP 等更多的操作，且同时支持对数据分片、读写分离、数据加密、数据库发现核心功能的配置管控。

结语

作为 5.0.0-beta 版本发布的新特性之一，DistSQL 也将从此版本开始持续发力，构建更完善的语法体系，提供更强大的功能。

DistSQL 为 ShardingSphere 在分布式数据库领域的探索提供了无限可能，在后续的规划中，DistSQL 将作为纽带串联起更多的功能，提供一键式操作。如一键分析数据库整体状态，和弹性迁移对接，提供一键数据扩容、缩容；和管控对接，实现一键切换主从、改变数据库状态等等。

最后，欢迎大家多多关注社区，了解 ShardingSphere 项目最新进展。

感谢支持 OpenSEC

xieyc · 2022 年2 月 19 日 10:29

@SphereEx官方君

怎么我5.1.0执行这个RDL语句报版本不支持这个sql？

xieyc · 2022 年2 月 21 日 02:49

@SphereEx官方君还有我用navicat连接，报
这个异常

zjcnb · 2022 年2 月 21 日 05:01

貌似目前增加 resource , host 还不支持域名的方式, 修改成实际 IP 试试看

xieyc · 2022 年2 月 21 日 05:45

@zjcnb 确实是，什么时候支持呢？腾讯云的公网没有IP，只有域名

xieyc · 2022 年2 月 21 日 05:58

@zjcnb Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name ‘shardingDataSource’ defined in class path resource [com/xieyc/sharding/config/ShardingConfig.class]: Bean instantiation via factory method failed; nested exception is org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.sql.DataSource]: Factory method ‘shardingDataSource’ threw exception; nested exception is org.apache.shardingsphere.spi.exception.ServiceProviderNotFoundException: No implementation class load from SPI org.apache.shardingsphere.mode.repository.cluster.ClusterPersistRepository with type ZooKeeper.
at org.springframework.beans.factory.support.ConstructorResolver.instantiate(ConstructorResolver.java:656) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.beans.factory.support.ConstructorResolver.instantiateUsingFactoryMethod(ConstructorResolver.java:636) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.instantiateUsingFactoryMethod(AbstractAutowireCapableBeanFactory.java:1338) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBeanInstance(AbstractAutowireCapableBeanFactory.java:1177) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.doCreateBean(AbstractAutowireCapableBeanFactory.java:557) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.beans.factory.support.AbstractAutowireCapableBeanFactory.createBean(AbstractAutowireCapableBeanFactory.java:517) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.lambda$doGetBean$0(AbstractBeanFactory.java:323) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.beans.factory.support.DefaultSingletonBeanRegistry.getSingleton(DefaultSingletonBeanRegistry.java:226) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.doGetBean(AbstractBeanFactory.java:321) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.beans.factory.support.AbstractBeanFactory.getBean(AbstractBeanFactory.java:202) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.beans.factory.config.DependencyDescriptor.resolveCandidate(DependencyDescriptor.java:276) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.beans.factory.support.DefaultListableBeanFactory.doResolveDependency(DefaultListableBeanFactory.java:1306) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.beans.factory.support.DefaultListableBeanFactory.resolveDependency(DefaultListableBeanFactory.java:1226) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.beans.factory.support.ConstructorResolver.resolveAutowiredArgument(ConstructorResolver.java:885) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:789) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE]
… 60 common frames omitted
Caused by: org.springframework.beans.BeanInstantiationException: Failed to instantiate [javax.sql.DataSource]: Factory method ‘shardingDataSource’ threw exception; nested exception is org.apache.shardingsphere.spi.exception.ServiceProviderNotFoundException: No implementation class load from SPI org.apache.shardingsphere.mode.repository.cluster.ClusterPersistRepository with type ZooKeeper.
at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:185) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.beans.factory.support.ConstructorResolver.instantiate(ConstructorResolver.java:651) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE]
… 74 common frames omitted
Caused by: org.apache.shardingsphere.spi.exception.ServiceProviderNotFoundException: No implementation class load from SPI org.apache.shardingsphere.mode.repository.cluster.ClusterPersistRepository with type ZooKeeper.
at org.apache.shardingsphere.spi.typed.TypedSPIRegistry.getRegisteredService(TypedSPIRegistry.java:76) ~[shardingsphere-spi-5.0.0.jar:5.0.0]
at org.apache.shardingsphere.mode.manager.cluster.ClusterContextManagerBuilder.createClusterPersistRepository(ClusterContextManagerBuilder.java:118) ~[shardingsphere-cluster-mode-core-5.0.0.jar:5.0.0]
at org.apache.shardingsphere.mode.manager.cluster.ClusterContextManagerBuilder.beforeBuildContextManager(ClusterContextManagerBuilder.java:94) ~[shardingsphere-cluster-mode-core-5.0.0.jar:5.0.0]
at org.apache.shardingsphere.mode.manager.cluster.ClusterContextManagerBuilder.build(ClusterContextManagerBuilder.java:84) ~[shardingsphere-cluster-mode-core-5.0.0.jar:5.0.0]
at org.apache.shardingsphere.driver.jdbc.core.datasource.ShardingSphereDataSource.createContextManager(ShardingSphereDataSource.java:75) ~[shardingsphere-jdbc-core-5.0.0.jar:5.0.0]
at org.apache.shardingsphere.driver.jdbc.core.datasource.ShardingSphereDataSource.(ShardingSphereDataSource.java:54) ~[shardingsphere-jdbc-core-5.0.0.jar:5.0.0]
at org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory.createDataSource(ShardingSphereDataSourceFactory.java:51) ~[shardingsphere-jdbc-core-5.0.0.jar:5.0.0]
at org.apache.shardingsphere.driver.api.ShardingSphereDataSourceFactory.createDataSource(ShardingSphereDataSourceFactory.java:62) ~[shardingsphere-jdbc-core-5.0.0.jar:5.0.0]
at com.xieyc.sharding.config.ShardingConfig.shardingDataSource(ShardingConfig.java:33) ~[classes/:na]
at com.xieyc.sharding.config.ShardingConfig$$EnhancerBySpringCGLIB$$a3ddd9cc.CGLIB$shardingDataSource$0() ~[classes/:na]
at com.xieyc.sharding.config.ShardingConfig$$EnhancerBySpringCGLIB$$a3ddd9cc$$FastClassBySpringCGLIB$$d3d9f3db.invoke() ~[classes/:na]
at org.springframework.cglib.proxy.MethodProxy.invokeSuper(MethodProxy.java:244) ~[spring-core-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.context.annotation.ConfigurationClassEnhancer$BeanMethodInterceptor.intercept(ConfigurationClassEnhancer.java:331) ~[spring-context-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at com.xieyc.sharding.config.ShardingConfig$$EnhancerBySpringCGLIB$$a3ddd9cc.shardingDataSource() ~[classes/:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
at org.springframework.beans.factory.support.SimpleInstantiationStrategy.instantiate(SimpleInstantiationStrategy.java:154) ~[spring-beans-5.2.6.RELEASE.jar:5.2.6.RELEASE]
… 75 common frames omitted

@Bean
public ModeConfiguration modeConfiguration() {
Properties properties = new Properties();
properties.setProperty(“namespace”, “governance_ds”);
properties.setProperty(“server-lists”, “localhost:2181”);
properties.setProperty(“retryIntervalMilliseconds”, “500”);
properties.setProperty(“timeToLiveSeconds”, “60”);
properties.setProperty(“maxRetries”, “3”);
properties.setProperty(“operationTimeoutMilliseconds”, “500”);
ClusterPersistRepositoryConfiguration clusterPersistRepositoryConfiguration = new ClusterPersistRepositoryConfiguration(“ZooKeeper”, “governance_ds”, “localhost:2181”, properties);
return new ModeConfiguration(“Cluster”, clusterPersistRepositoryConfiguration, false);
}

@Bean
@DependsOn("modeConfiguration")
DataSource shardingDataSource(ModeConfiguration modeConfiguration) throws SQLException {
    return ShardingSphereDataSourceFactory.createDataSource(modeConfiguration);
}

这样配置，共享Zookeeper的规则配置，有什么问题吗？

zjcnb · 2022 年2 月 21 日 06:07

    <dependency>
        <groupId>org.apache.shardingsphere</groupId>
        <artifactId>shardingsphere-cluster-mode-repository-zookeeper-curator</artifactId>
    </dependency>

增加下 Zookeeper 的依赖吧.

xieyc · 2022 年2 月 21 日 06:09

@zjcnb 好的，谢谢，感觉文档写得不够清晰

zjcnb · 2022 年2 月 21 日 06:18

感觉那里的文档可以改进, 可以提交 pull request 一起完善它

xieyc · 2022 年2 月 21 日 06:19

@zjcnb 能再帮忙看下这个问题吗？
我在ss-proxy端添加了source，但是java-sharding-jdbc客户端拿到的jdbcurl为null，用户名密码是能拿到的。我是哪个环节出了问题？

xieyc · 2022 年2 月 21 日 06:32

@zjcnb 上面的问题解决了，要配置schemaName
但是现在又有如下错误：

Error querying database. Cause: java.lang.NullPointerException: Can not find transaction manager of `XA`

The error may exist in com/xieyc/sharding/mapper/OrderMapper.java (best guess)

The error may involve com.xieyc.sharding.mapper.OrderMapper.selectList

The error occurred while executing a query

Cause: java.lang.NullPointerException: Can not find transaction manager of `XA`] with root cause

java.lang.NullPointerException: Can not find transaction manager of XA
at com.google.common.base.Preconditions.checkNotNull(Preconditions.java:987) ~[guava-30.0-jre.jar:na]
at org.apache.shardingsphere.transaction.ShardingSphereTransactionManagerEngine.getTransactionManager(ShardingSphereTransactionManagerEngine.java:87) ~[shardingsphere-transaction-core-5.1.0.jar:5.1.0]
at org.apache.shardingsphere.transaction.ConnectionTransaction.(ConnectionTransaction.java:49) ~[shardingsphere-transaction-core-5.1.0.jar:5.1.0]
at org.apache.shardingsphere.transaction.ConnectionTransaction.(ConnectionTransaction.java:44) ~[shardingsphere-transaction-core-5.1.0.jar:5.1.0]
at org.apache.shardingsphere.driver.jdbc.core.connection.ConnectionManager.lambda$createConnectionTransaction$0(ConnectionManager.java:135) ~[shardingsphere-jdbc-core-5.1.0.jar:5.1.0]
at java.base/java.util.Optional.map(Optional.java:265) ~[na:na]
at org.apache.shardingsphere.driver.jdbc.core.connection.ConnectionManager.createConnectionTransaction(ConnectionManager.java:135) ~[shardingsphere-jdbc-core-5.1.0.jar:5.1.0]
at org.apache.shardingsphere.driver.jdbc.core.connection.ConnectionManager.(ConnectionManager.java:86) ~[shardingsphere-jdbc-core-5.1.0.jar:5.1.0]
at org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection.(ShardingSphereConnection.java:60) ~[shardingsphere-jdbc-core-5.1.0.jar:5.1.0]
at org.apache.shardingsphere.driver.state.ok.OKDriverState.getConnection(OKDriverState.java:33) ~[shardingsphere-jdbc-core-5.1.0.jar:5.1.0]
at org.apache.shardingsphere.driver.state.DriverStateContext.getConnection(DriverStateContext.java:48) ~[shardingsphere-jdbc-core-5.1.0.jar:5.1.0]
at org.apache.shardingsphere.driver.jdbc.core.datasource.ShardingSphereDataSource.getConnection(ShardingSphereDataSource.java:86) ~[shardingsphere-jdbc-core-5.1.0.jar:5.1.0]
at org.springframework.jdbc.datasource.DataSourceUtils.fetchConnection(DataSourceUtils.java:158) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:116) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:79) ~[spring-jdbc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.mybatis.spring.transaction.SpringManagedTransaction.openConnection(SpringManagedTransaction.java:80) ~[mybatis-spring-2.0.6.jar:2.0.6]
at org.mybatis.spring.transaction.SpringManagedTransaction.getConnection(SpringManagedTransaction.java:67) ~[mybatis-spring-2.0.6.jar:2.0.6]
at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:337) ~[mybatis-3.5.9.jar:3.5.9]
at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:86) ~[mybatis-3.5.9.jar:3.5.9]
at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:62) ~[mybatis-3.5.9.jar:3.5.9]
at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:325) ~[mybatis-3.5.9.jar:3.5.9]
at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:156) ~[mybatis-3.5.9.jar:3.5.9]
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:109) ~[mybatis-3.5.9.jar:3.5.9]
at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:89) ~[mybatis-3.5.9.jar:3.5.9]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:151) ~[mybatis-3.5.9.jar:3.5.9]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:145) ~[mybatis-3.5.9.jar:3.5.9]
at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:140) ~[mybatis-3.5.9.jar:3.5.9]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:427) ~[mybatis-spring-2.0.6.jar:2.0.6]
at com.sun.proxy.$Proxy204.selectList(Unknown Source) ~[na:na]
at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224) ~[mybatis-spring-2.0.6.jar:2.0.6]
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.executeForMany(MybatisMapperMethod.java:166) ~[mybatis-plus-core-3.5.1.jar:3.5.1]
at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:77) ~[mybatis-plus-core-3.5.1.jar:3.5.1]
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:148) ~[mybatis-plus-core-3.5.1.jar:3.5.1]
at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89) ~[mybatis-plus-core-3.5.1.jar:3.5.1]
at com.sun.proxy.$Proxy205.selectList(Unknown Source) ~[na:na]
at com.xieyc.sharding.service.impl.OrderServiceImpl.list(OrderServiceImpl.java:19) ~[classes/:na]
at com.xieyc.sharding.controller.OrderController.list(OrderController.java:21) ~[classes/:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62) ~[na:na]
at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:na]
at java.base/java.lang.reflect.Method.invoke(Method.java:566) ~[na:na]
at org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190) ~[spring-web-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138) ~[spring-web-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105) ~[spring-webmvc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:879) ~[spring-webmvc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:793) ~[spring-webmvc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87) ~[spring-webmvc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040) ~[spring-webmvc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943) ~[spring-webmvc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006) ~[spring-webmvc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:898) ~[spring-webmvc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:634) ~[tomcat-embed-core-9.0.34.jar:9.0.34]
at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883) ~[spring-webmvc-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at javax.servlet.http.HttpServlet.service(HttpServlet.java:741) ~[tomcat-embed-core-9.0.34.jar:9.0.34]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) ~[tomcat-embed-core-9.0.34.jar:9.0.34]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.34.jar:9.0.34]
at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-embed-websocket-9.0.34.jar:9.0.34]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.34.jar:9.0.34]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.34.jar:9.0.34]
at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100) ~[spring-web-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.34.jar:9.0.34]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.34.jar:9.0.34]
at org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93) ~[spring-web-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.34.jar:9.0.34]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.34.jar:9.0.34]
at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201) ~[spring-web-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119) ~[spring-web-5.2.6.RELEASE.jar:5.2.6.RELEASE]
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) ~[tomcat-embed-core-9.0.34.jar:9.0.34]
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) ~[tomcat-embed-core-9.0.34.jar:9.0.34]
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202) ~[tomcat-embed-core-9.0.34.jar:9.0.34]
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) ~[tomcat-embed-core-9.0.34.jar:9.0.34]
at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541) ~[tomcat-embed-core-9.0.34.jar:9.0.34]
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139) ~[tomcat-embed-core-9.0.34.jar:9.0.34]
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92) ~[tomcat-embed-core-9.0.34.jar:9.0.34]
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74) ~[tomcat-embed-core-9.0.34.jar:9.0.34]
at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343) ~[tomcat-embed-core-9.0.34.jar:9.0.34]
at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:373) ~[tomcat-embed-core-9.0.34.jar:9.0.34]
at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65) ~[tomcat-embed-core-9.0.34.jar:9.0.34]
at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868) ~[tomcat-embed-core-9.0.34.jar:9.0.34]
at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1590) ~[tomcat-embed-core-9.0.34.jar:9.0.34]
at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) ~[tomcat-embed-core-9.0.34.jar:9.0.34]
at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1128) ~[na:na]
at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:628) ~[na:na]
at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) ~[tomcat-embed-core-9.0.34.jar:9.0.34]
at java.base/java.lang.Thread.run(Thread.java:834) ~[na:na]

zjcnb · 2022 年2 月 21 日 06:39

麻烦提供下配置, server.yaml 以及其它的关于 ShardingSphere 的配置文件

xieyc · 2022 年2 月 21 日 06:50

@zjcnb
sharding-jdbc配置：

spring:
  shardingsphere:
    schema:
      name: sharding_db
    mode:
      type: Cluster
      repository:
        type: ZooKeeper
        props:
          namespace: governance_ds
          server-lists: localhost:2181
          retryIntervalMilliseconds: 500
          timeToLiveSeconds: 60
          maxRetries: 3
          operationTimeoutMilliseconds: 500
      overwrite: false

server.yml配置：

mode:
  type: Cluster
  repository:
    type: ZooKeeper
    props:
      namespace: governance_ds
      server-lists: localhost:2181
      retryIntervalMilliseconds: 500
      timeToLiveSeconds: 60
      maxRetries: 3
      operationTimeoutMilliseconds: 500
  overwrite: false

rules:
  - !AUTHORITY
    users:
      - root@%:root
      - sharding@:sharding
    provider:
      type: ALL_PRIVILEGES_PERMITTED
  - !TRANSACTION
    defaultType: XA
    providerType: Atomikos
  - !SQL_PARSER
    sqlCommentParseEnabled: true
    sqlStatementCache:
      initialCapacity: 2000
      maximumSize: 65535
      concurrencyLevel: 4
    parseTreeCache:
      initialCapacity: 128
      maximumSize: 1024
      concurrencyLevel: 4

props:
  max-connections-size-per-query: 1
  kernel-executor-size: 16  # Infinite by default.
  proxy-frontend-flush-threshold: 128  # The default value is 128.
  proxy-opentracing-enabled: false
  proxy-hint-enabled: true
  sql-show: false
  check-table-metadata-enabled: false
  show-process-list-enabled: false
    # Proxy backend query fetch size. A larger value may increase the memory usage of ShardingSphere Proxy.
    # The default value is -1, which means set the minimum value for different JDBC drivers.
  proxy-backend-query-fetch-size: -1
  check-duplicate-table-enabled: false
  proxy-frontend-executor-size: 0 # Proxy frontend executor size. The default value is 0, which means let Netty decide.
    # Available options of proxy backend executor suitable: OLAP(default), OLTP. The OLTP option may reduce time cost of writing packets to client, but it may increase the latency of SQL execution
    # and block other clients if client connections are more than `proxy-frontend-executor-size`, especially executing slow SQL.
  proxy-backend-executor-suitable: OLAP
  proxy-frontend-max-connections: 0 # Less than or equal to 0 means no limitation.
  sql-federation-enabled: false
    # Available proxy backend driver type: JDBC (default), ExperimentalVertx
  proxy-backend-driver-type: JDBC

xieyc · 2022 年2 月 22 日 01:45

@zjcnb 大佬，怎么说？看了没有

zjcnb · 2022 年2 月 22 日 02:17

您的 server.yaml 和 config-sharding.yaml 是不是复制错了?
如果您不打算使用 XA 事务的话, 可以将 :

 - !TRANSACTION
    defaultType: XA
    providerType: Atomikos

注释掉. 并且把 Zookeeper 上的配置修改下.

xieyc · 2022 年2 月 22 日 02:18

@zjcnb 能加个微信或者QQ吗？比较急

zjcnb · 2022 年2 月 22 日 02:21

说下您的微信吧.

xieyc · 2022 年2 月 22 日 02:24

@zjcnb lsf520forever

yangkang · 2022 年3 月 8 日 06:22

@SphereEx官方君 @zjcnb 我看文档 RAL :: ShardingSphere 这里用（set sharding hint database_value = yy 针对当前连接，设置 hint 仅对数据库分片有效，并添加分片值，yy：数据库分片值 set sharding hint database_value = 100）指定数据库分片字段。然后我在 proxy的配置中，启用了hit（proxy-hint-enabled: true），在java代码中 mybitsplus 执行的地方，改写了sql（String newSql = "set sharding hint database_value = demo_ds_0; " + oldSql;），但是执行好像没有返回数据。

zjcnb · 2022 年3 月 8 日 07:44

您好, 方便提供一个 demo 吗? 这样更容易排查问题