sharding-jdbc 5.0.0 单库分表使用,使用mysql的information_schema查询表是否存在,抛出异常SchemaNotExistedException: Schema 'information_schema' doesn't exist

sharding-jdbc 5.0.0 单库分表接入,使用information_schema查询mysql数据库是否存在表时,抛出异常 Cause: org.apache.shardingsphere.infra.exception.SchemaNotExistedException: Schema ‘information_schema’ doesn’t exist.,请问怎么解决?

# 数据源配置
dataSources:
  ds0:
    dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
    driverClassName: com.mysql.cj.jdbc.Driver
    url: jdbc:mysql://test.server.com:3306/sharding-jdbc-demo?tinyInt1isBit=false&useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=false&serverTimezone=GMT%2B8&allowMultiQueries=true&useOldAliasMetadataBehavior=true&autoReconnect=true&failOverReadOnly=false
    username: root
    password: 123456
    # 初始连接数
    initialSize: 20
    # 最小连接池数量
    minIdle: 20
    # 最大连接池数量
    maxActive: 300

# 分表规则配置
rules:
- !SHARDING
  tables:
    # 逻辑表
    b_device_issue_msg_history:
      # 真实分片表
      actualDataNodes: ds0.b_device_issue_msg_history_20210${1..4}
      # 分表策略
      tableStrategy:
        standard:
          # 分片键
          shardingColumn: create_time
          # 分片算法关联
          shardingAlgorithmName: b_device_issue_msg_history_inline

  # 分片算法配置
  shardingAlgorithms:
    b_device_issue_msg_history_inline:
      # 分片算法类型
      type: INTERVAL
      # 分片算法属性配置
      props:
        datetime-pattern: 'yyyy-MM-dd HH:mm:ss'
        datetime-lower: '2021-01-01 00:00:00'
        datetime-upper: '2021-12-31 23:59:59'
        sharding-suffix-pattern: 'yyyyQQ'
        datetime-interval-amount: '3'
        datetime-interval-unit: 'months'
# sharding-jdbc 属性配置
props:
  # 是否打印sql输出控制台
  sql-show: true

查询语句(在未接入sharding-jdbc前可以正常查询)

SELECT COUNT(1)
FROM information_schema.tables
WHERE table_name = #{tableName} and table_schema = 'gb_manage_os'

异常信息

org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
### Error querying database.  Cause: org.apache.shardingsphere.infra.exception.SchemaNotExistedException: Schema 'information_schema' doesn't exist.
### The error may exist in file [D:\DevCodeHome\gb-manage-os\target\classes\cn\greenbon\account\dao\AuthFansMapper.xml]
### The error may involve defaultParameterMap
### The error occurred while setting parameters
### SQL: SELECT COUNT(1)         FROM information_schema.tables         WHERE         table_name = ?         and table_schema = 'gb_manage_os'
### Cause: org.apache.shardingsphere.infra.exception.SchemaNotExistedException: Schema 'information_schema' doesn't exist.
	at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92)
	at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
	at com.sun.proxy.$Proxy348.selectOne(Unknown Source)
	at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:159)
	at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:108)
	at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:96)
	at com.sun.proxy.$Proxy428.checkTableExists(Unknown Source)
	at cn.greenbon.account.service.impl.AuthFansServiceImpl.checkTableExists(AuthFansServiceImpl.java:73)
	at cn.greenbon.account.service.impl.AuthFansServiceImpl.checkAndCreateNewTable(AuthFansServiceImpl.java:80)
	at cn.greenbon.account.service.impl.AuthFansServiceImpl.initAuthTable(AuthFansServiceImpl.java:100)
	at cn.greenbon.account.service.impl.AuthFansServiceImpl$$FastClassBySpringCGLIB$$e62e48e6.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:367)
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
	at cn.greenbon.account.service.impl.AuthFansServiceImpl$$EnhancerBySpringCGLIB$$9be1ab0d.initAuthTable(<generated>)
	at cn.greenbon.main.InitWxConfiguration.run(InitWxConfiguration.java:30)
	at org.springframework.boot.SpringApplication.callRunner(SpringApplication.java:786)
	at org.springframework.boot.SpringApplication.callRunners(SpringApplication.java:776)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:322)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1237)
	at org.springframework.boot.SpringApplication.run(SpringApplication.java:1226)
	at cn.greenbon.main.ManageApplication.main(ManageApplication.java:39)

你好,欢迎来到中文社区。
还请提供以下信息帮助分析问题:

  • 从日志中提取该查询的 Logic SQL 和 Actual SQL;

你好,上述问题有解决方案吗?

你好,如果讨论过程中要提出新的问题,请另开新帖,不要通过修改问题来操作。。。这已经让人看不懂上下文了。
性能那个问题是否略过了?
那么接下来,关于 information_schema

  1. 由于 ShardingSphere-JDBC 使用逻辑库+逻辑表名来为应用提供服务,是不支持在操作中指定其他数据库名称的。
  2. 在分片场景下,单个数据库中的 information_schema 数据不能表示完整的分布式数据,如 table 名称、table 数量。

因此,最好不要依赖 information_schema 来进行业务逻辑的判断。

原先的性能问题解决了,现在遇到新问题,如何在逻辑库判断指定表是否存在,用于动态创建表查询

目前使用 ShardingSphere-JDBC 还做不到。
ShardingSphere-Proxy 已经支持了新的 DistSQL 语法 show sharding table nodes,可以查询逻辑表对应的各个节点:

未来 ShardingSphere-JDBC 也会支持 DistSQL,使用起来就会方便很多。

京ICP备2021015875号