Why the org.apache.shardingsphere.dialect.exception.syntax.table.NoSuchTableException Error Occurs When Accessing the Actual Table

[Pik1031]

Question

For English only, other languages will not accept.

Before asking a question, make sure you have:

• Googled your question.
• Searched open and closed GitHub issues.
• Read documentation: ShardingSphere Doc.

When I run a SQL statement to access the actual table, the following error occurs:

Sharding -jdbc 5.40 `org.springframework.jdbc.datasource.init.UncategorizedScriptException: Failed to execute database script from resource [class path resource [init.sql]]; nested exception is org.apache.shardingsphere.dialect.exception.syntax.table.NoSuchTableException

at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:304)
at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.populate(ResourceDatabasePopulator.java:254)
at org.springframework.jdbc.datasource.init.DatabasePopulatorUtils.execute(DatabasePopulatorUtils.java:49)
at org.springframework.jdbc.datasource.init.ResourceDatabasePopulator.execute(ResourceDatabasePopulator.java:269)
at org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener.lambda$executeSqlScripts$4(SqlScriptsTestExecutionListener.java:263)
at org.springframework.transaction.support.TransactionOperations.lambda$executeWithoutResult$0(TransactionOperations.java:68)
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
at org.springframework.transaction.support.TransactionOperations.executeWithoutResult(TransactionOperations.java:67)
at org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener.executeSqlScripts(SqlScriptsTestExecutionListener.java:263)
at org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener.lambda$executeSqlScripts$0(SqlScriptsTestExecutionListener.java:185)
at java.lang.Iterable.forEach(Iterable.java:75)
at org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener.executeSqlScripts(SqlScriptsTestExecutionListener.java:185)
at org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener.executeSqlScripts(SqlScriptsTestExecutionListener.java:147)
at org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener.beforeTestMethod(SqlScriptsTestExecutionListener.java:117)
at org.springframework.test.context.TestContextManager.beforeTestMethod(TestContextManager.java:293)
at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:74)
at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:86)
at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:84)
at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:366)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:251)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:97)
at org.junit.runners.ParentRunner$4.run(ParentRunner.java:331)
at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:79)
at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:329)
at org.junit.runners.ParentRunner.access$100(ParentRunner.java:66)
at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:293)
at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:70)
at org.junit.runners.ParentRunner$3.evaluate(ParentRunner.java:306)
at org.junit.runners.ParentRunner.run(ParentRunner.java:413)
at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:190)
at org.junit.runner.JUnitCore.run(JUnitCore.java:137)
at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:69)
at com.intellij.rt.junit.IdeaTestRunner$Repeater$1.execute(IdeaTestRunner.java:38)
at com.intellij.rt.execution.junit.TestsRepeater.repeat(TestsRepeater.java:11)
at com.intellij.rt.junit.IdeaTestRunner$Repeater.startRunnerWithArgs(IdeaTestRunner.java:35)
at com.intellij.rt.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:232)
at com.intellij.rt.junit.JUnitStarter.main(JUnitStarter.java:55)

Caused by: org.apache.shardingsphere.dialect.exception.syntax.table.NoSuchTableException at org.apache.shardingsphere.sharding.route.engine.condition.engine.InsertClauseShardingConditionEngine.lambda$appendMissingShardingConditions$1(InsertClauseShardingConditionEngine.java:97) at org.apache.shardingsphere.infra.util.exception.ShardingSpherePreconditions.checkState(ShardingSpherePreconditions.java:41) at org.apache.shardingsphere.sharding.route.engine.condition.engine.InsertClauseShardingConditionEngine.appendMissingShardingConditions(InsertClauseShardingConditionEngine.java:97) at org.apache.shardingsphere.sharding.route.engine.condition.engine.InsertClauseShardingConditionEngine.createShardingConditionsWithInsertValues(InsertClauseShardingConditionEngine.java:89) at org.apache.shardingsphere.sharding.route.engine.condition.engine.InsertClauseShardingConditionEngine.createShardingConditions(InsertClauseShardingConditionEngine.java:74) at org.apache.shardingsphere.sharding.route.engine.condition.engine.ShardingConditionEngine.createShardingConditions(ShardingConditionEngine.java:53) at org.apache.shardingsphere.sharding.route.engine.ShardingSQLRouter.createShardingConditions(ShardingSQLRouter.java:80) at org.apache.shardingsphere.sharding.route.engine.ShardingSQLRouter.createRouteContext0(ShardingSQLRouter.java:65) at org.apache.shardingsphere.sharding.route.engine.ShardingSQLRouter.createRouteContext(ShardingSQLRouter.java:59) at org.apache.shardingsphere.sharding.route.engine.ShardingSQLRouter.createRouteContext(ShardingSQLRouter.java:47) at org.apache.shardingsphere.infra.route.engine.impl.PartialSQLRouteExecutor.route(PartialSQLRouteExecutor.java:69) at org.apache.shardingsphere.infra.route.engine.SQLRouteEngine.route(SQLRouteEngine.java:57) at org.apache.shardingsphere.infra.connection.kernel.KernelProcessor.route(KernelProcessor.java:60) at org.apache.shardingsphere.infra.connection.kernel.KernelProcessor.generateExecutionContext(KernelProcessor.java:51) at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.createExecutionContext(ShardingSphereStatement.java:510) at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.execute0(ShardingSphereStatement.java:441) at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSphereStatement.execute(ShardingSphereStatement.java:376) at org.springframework.jdbc.datasource.init.ScriptUtils.executeSqlScript(ScriptUtils.java:260) ... 37 more

`

Please pay attention on issues you submitted, because we maybe need more details. If no response anymore and we cannot reproduce it on current information, we will close it.

why？and how？

[strongduanmu]

If you want to access actual table, you should use /* SHARDINGSPHERE_HINT: DATA_SOURCE_NAME=ds_0 */ SELECT * FROM t_order; to pass sql to some datasource. For more details, you can refer - https://shardingsphere.apache.org/document/current/en/user-manual/common-config/sql-hint/#datasource-pass-through

[Pik1031]

If you want to access actual table, you should use /* SHARDINGSPHERE_HINT: DATA_SOURCE_NAME=ds_0 */ SELECT * FROM t_order; to pass sql to some datasource. For more details, you can refer - https://shardingsphere.apache.org/document/current/en/user-manual/common-config/sql-hint/#datasource-pass-through Thanks for the answer, but it didn't work.

[Pik1031]

`dataSources: backup: dataSourceClassName: xxxx driverClassName: xxx url: xxxx username: xxx password: xx filters: stat initialSize: 5 minIdle: 3 maxActive: 60 maxWait: 60000 timeBetweenEvictionRunsMillis: 60000 minEvictableIdleTimeMillis : 300000 validationQuery: SELECT 'x' testWhileIdle: true testOnBorrow: false testOnReturn: false poolPreparedStatements: true maxPoolPreparedStatementPerConnectionSize: 20 removeAbandoned: true removeAbandonedTimeout: 200 validationQueryTimeout: 200 logAbandoned: true

rules:

!SINGLE tables: backup.* defaultDataSource: backup !SHARDING tables: t_user: actualDataNodes : backup.tuser$->{1..19} tableStrategy: hint: shardingAlgorithmName: hint_rule shardingAlgorithms: hint_rule: type: HINT_SHARD props: sql-show: true ` this is my yaml

[clay-f]

same error here. I'm learn about latest shardingsphere-jdbc and do some test follow tutorial can't get running

uncategorized SQLException; SQL state [HY000]; error code [30000]; Unknown exception: null

read document from t_order

config.yml

mode:
  type: Standalone
  repository:
    type: JDBC

schemaName: SCOTT

dataSources:
  ds_0:
    dataSourceClassName: com.zaxxer.hikari.HikariDataSource
    driverClassName: oracle.jdbc.OracleDriver
    jdbcUrl: jdbc:oracle:thin:@//127.0.0.1:1521/orcl
    username: scott
    password: 123456

rules:
  - !SINGLE
    tables:
      - "*.*"
    defaultDataSource: db_0
  - !SHARDING
    tables:
      t_order:
        actualDataNodes: ds_0.t_order_$->{0..1}
        tableStrategy:
          standard:
            shardingColumn: id
            shardingAlgorithmName: t_order_inline
    bindingTables:
      - t_order
    defaultDatabaseStrategy:
      standard:
        shardingColumn: id
        shardingAlgorithmName: database_inline
    defaultTableStrategy:
      none:

    shardingAlgorithms:
      database_inline:
        type: INLINE
        props:
          algorithm-expression: ds_0->{id % 2}
      t_order_inline:
        type: INLINE
        props:
          algorithm-expression: t_order_$->{id % 2}

props:
  sql-show: true

application.yml

spring:
  datasource:
    driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
    url: jdbc:shardingsphere:classpath:config.yml

  main:
    banner-mode: off

mybatis:
  type-aliases-package: com.f.sharedingjdbc.domain
  mapper-locations: classpath:mapper/*.xml

logging:
  level:
    com.f.sharedingjdbc.mapper: debug

order table sql xml file

    <?xml version="1.0" encoding="UTF-8" ?>
    <!DOCTYPE mapper
            PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
            "http://mybatis.org/dtd/mybatis-3-mapper.dtd">
    <mapper namespace="com.f.sharedingjdbc.mapper.OrderMapper">

        <select id="list" resultType="com.f.sharedingjdbc.domain.TOrder">
           SELECT * FROM t_order
        </select>
    </mapper>

database have three table used for test query

1. t_order
2. t_order_0
3. t_order_1

[clay-f]

I'm found problem, oracle needs change table uppercase for me situation😂