search

apache / shardingsphere

Distributed SQL transaction & query engine for data sharding, scaling, encryption, and more - on any database.
Apache License 2.0
19.8k stars 6.7k forks source link

Sharding-JDBC 5.4.1 Automatic sharding Table or view does not exist. #29499

Closed niaucz closed 9 months ago

niaucz commented 9 months ago

Bug Report

Which version of ShardingSphere did you use?

Sharding-JDBC 5.4.1 SpringBoot 3.1.5

Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?

ShardingSphere-JDBC

Expected behavior

success

Actual behavior

Table or view nx_data does not exist.

Reason analyze (If you can)

Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.

database: MySQL application.yml

datasource:
driver-class-name: org.apache.shardingsphere.driver.ShardingSphereDriver
url: jdbc:shardingsphere:classpath:shardingsphere.yaml

shardingsphere.yaml


databaseName: xxxxx

mode: type: Standalone

dataSources: write_ds: dataSourceClassName: com.zaxxer.hikari.HikariDataSource driverClassName: com.mysql.cj.jdbc.Driver jdbcUrl: jdbc:mysql://xxxxx-server:9306/xxxxx?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=Asia/Shanghai username: xxxxx password: xxxxx maxLifetime: 120000 maximumPoolSize: 20 read_ds_0: dataSourceClassName: com.zaxxer.hikari.HikariDataSource driverClassName: com.mysql.cj.jdbc.Driver jdbcUrl: jdbc:mysql://xxxxx-server:9306/xxxxx?useUnicode=true&characterEncoding=utf8&zeroDateTimeBehavior=convertToNull&useSSL=true&serverTimezone=Asia/Shanghai username: xxxxx password: xxxxx readOnly: true maxLifetime: 120000 maximumPoolSize: 20 rules:

RaigorJiang commented 9 months ago

Hi @niaucz, what is your SQL statement?

niaucz commented 9 months ago

@RaigorJiang 

INSERT INTO nx_data ( id, point_guid, tag, `value`, `timestamp` ) VALUES ( ?, ?, ?, ?, ? )
Creating a new SqlSession
SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7930cce1] was not registered for synchronization because synchronization is not active
JDBC Connection [HikariProxyConnection@297833448 wrapping org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection@422d4e38] will not be managed by Spring
==>  Preparing: INSERT INTO nx_data ( id, point_guid, tag, `value`, `timestamp` ) VALUES ( ?, ?, ?, ?, ? )
Closing non transactional SqlSession [org.apache.ibatis.session.defaults.DefaultSqlSession@7930cce1]
2023-12-23T11:32:30.075+08:00 ERROR 16284 --- [   scheduling-1] o.s.s.s.TaskUtils$LoggingErrorHandler    : Unexpected error occurred in scheduled task

org.mybatis.spring.MyBatisSystemException: null
    at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:97) ~[mybatis-spring-2.1.1.jar:2.1.1]
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:439) ~[mybatis-spring-2.1.1.jar:2.1.1]
    at jdk.proxy2/jdk.proxy2.$Proxy76.insert(Unknown Source) ~[na:na]
    at org.mybatis.spring.SqlSessionTemplate.insert(SqlSessionTemplate.java:272) ~[mybatis-spring-2.1.1.jar:2.1.1]
    at com.baomidou.mybatisplus.core.override.MybatisMapperMethod.execute(MybatisMapperMethod.java:59) ~[mybatis-plus-core-3.5.4.1.jar:3.5.4.1]
    at com.baomidou.mybatisplus.core.override.MybatisMapperProxy$PlainMethodInvoker.invoke(MybatisMapperProxy.java:152) ~[mybatis-plus-core-3.5.4.1.jar:3.5.4.1]
    at com.baomidou.mybatisplus.core.override.MybatisMapperProxy.invoke(MybatisMapperProxy.java:89) ~[mybatis-plus-core-3.5.4.1.jar:3.5.4.1]
    at jdk.proxy2/jdk.proxy2.$Proxy80.insert(Unknown Source) ~[na:na]
    at xxxxx.xxxxx.xxxxx.config.TestTask.myMethod(TestTask.java:42) ~[classes/:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[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:568) ~[na:na]
    at org.springframework.scheduling.support.ScheduledMethodRunnable.run(ScheduledMethodRunnable.java:84) ~[spring-context-6.0.13.jar:6.0.13]
    at org.springframework.scheduling.support.DelegatingErrorHandlingRunnable.run(DelegatingErrorHandlingRunnable.java:54) ~[spring-context-6.0.13.jar:6.0.13]
    at java.base/java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:539) ~[na:na]
    at java.base/java.util.concurrent.FutureTask.runAndReset$$$capture(FutureTask.java:305) ~[na:na]
    at java.base/java.util.concurrent.FutureTask.runAndReset(FutureTask.java) ~[na:na]
    at java.base/java.util.concurrent.ScheduledThreadPoolExecutor$ScheduledFutureTask.run(ScheduledThreadPoolExecutor.java:305) ~[na:na]
    at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) ~[na:na]
    at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) ~[na:na]
    at java.base/java.lang.Thread.run(Thread.java:833) ~[na:na]
Caused by: org.apache.ibatis.executor.ExecutorException: Error preparing statement.  Cause: org.apache.shardingsphere.infra.exception.TableNotExistsException: Table or view `nx_data` does not exist.
    at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:99) ~[mybatis-3.5.13.jar:3.5.13]
    at org.apache.ibatis.executor.statement.RoutingStatementHandler.prepare(RoutingStatementHandler.java:60) ~[mybatis-3.5.13.jar:3.5.13]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[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:568) ~[na:na]
    at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49) ~[mybatis-3.5.13.jar:3.5.13]
    at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:106) ~[mybatis-plus-extension-3.5.4.1.jar:3.5.4.1]
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:59) ~[mybatis-3.5.13.jar:3.5.13]
    at jdk.proxy2/jdk.proxy2.$Proxy116.prepare(Unknown Source) ~[na:na]
    at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:90) ~[mybatis-3.5.13.jar:3.5.13]
    at org.apache.ibatis.executor.SimpleExecutor.doUpdate(SimpleExecutor.java:49) ~[mybatis-3.5.13.jar:3.5.13]
    at org.apache.ibatis.executor.BaseExecutor.update(BaseExecutor.java:117) ~[mybatis-3.5.13.jar:3.5.13]
    at org.apache.ibatis.executor.CachingExecutor.update(CachingExecutor.java:76) ~[mybatis-3.5.13.jar:3.5.13]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[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:568) ~[na:na]
    at org.apache.ibatis.plugin.Invocation.proceed(Invocation.java:49) ~[mybatis-3.5.13.jar:3.5.13]
    at com.baomidou.mybatisplus.extension.plugins.MybatisPlusInterceptor.intercept(MybatisPlusInterceptor.java:106) ~[mybatis-plus-extension-3.5.4.1.jar:3.5.4.1]
    at org.apache.ibatis.plugin.Plugin.invoke(Plugin.java:59) ~[mybatis-3.5.13.jar:3.5.13]
    at jdk.proxy2/jdk.proxy2.$Proxy115.update(Unknown Source) ~[na:na]
    at org.apache.ibatis.session.defaults.DefaultSqlSession.update(DefaultSqlSession.java:197) ~[mybatis-3.5.13.jar:3.5.13]
    at org.apache.ibatis.session.defaults.DefaultSqlSession.insert(DefaultSqlSession.java:184) ~[mybatis-3.5.13.jar:3.5.13]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[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:568) ~[na:na]
    at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:425) ~[mybatis-spring-2.1.1.jar:2.1.1]
    ... 20 common frames omitted
Caused by: org.apache.shardingsphere.infra.exception.TableNotExistsException: Table or view `nx_data` does not exist.
    at org.apache.shardingsphere.infra.binder.segment.from.impl.SimpleTableSegmentBinder.lambda$checkTableExists$4(SimpleTableSegmentBinder.java:146) ~[shardingsphere-infra-binder-5.4.1.jar:5.4.1]
    at org.apache.shardingsphere.infra.exception.core.ShardingSpherePreconditions.checkState(ShardingSpherePreconditions.java:41) ~[shardingsphere-infra-exception-core-5.4.1.jar:5.4.1]
    at org.apache.shardingsphere.infra.binder.segment.from.impl.SimpleTableSegmentBinder.checkTableExists(SimpleTableSegmentBinder.java:143) ~[shardingsphere-infra-binder-5.4.1.jar:5.4.1]
    at org.apache.shardingsphere.infra.binder.segment.from.impl.SimpleTableSegmentBinder.bind(SimpleTableSegmentBinder.java:82) ~[shardingsphere-infra-binder-5.4.1.jar:5.4.1]
    at org.apache.shardingsphere.infra.binder.statement.dml.InsertStatementBinder.lambda$bind$0(InsertStatementBinder.java:58) ~[shardingsphere-infra-binder-5.4.1.jar:5.4.1]
    at java.base/java.util.Optional.ifPresent(Optional.java:178) ~[na:na]
    at org.apache.shardingsphere.infra.binder.statement.dml.InsertStatementBinder.bind(InsertStatementBinder.java:58) ~[shardingsphere-infra-binder-5.4.1.jar:5.4.1]
    at org.apache.shardingsphere.infra.binder.statement.dml.InsertStatementBinder.bind(InsertStatementBinder.java:48) ~[shardingsphere-infra-binder-5.4.1.jar:5.4.1]
    at org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bindDMLStatement(SQLBindEngine.java:103) ~[shardingsphere-infra-binder-5.4.1.jar:5.4.1]
    at org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bind(SQLBindEngine.java:80) ~[shardingsphere-infra-binder-5.4.1.jar:5.4.1]
    at org.apache.shardingsphere.infra.binder.engine.SQLBindEngine.bind(SQLBindEngine.java:71) ~[shardingsphere-infra-binder-5.4.1.jar:5.4.1]
    at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:202) ~[shardingsphere-jdbc-core-5.4.1.jar:5.4.1]
    at org.apache.shardingsphere.driver.jdbc.core.statement.ShardingSpherePreparedStatement.<init>(ShardingSpherePreparedStatement.java:167) ~[shardingsphere-jdbc-core-5.4.1.jar:5.4.1]
    at org.apache.shardingsphere.driver.jdbc.core.connection.ShardingSphereConnection.prepareStatement(ShardingSphereConnection.java:83) ~[shardingsphere-jdbc-core-5.4.1.jar:5.4.1]
    at com.zaxxer.hikari.pool.ProxyConnection.prepareStatement(ProxyConnection.java:328) ~[HikariCP-5.1.0.jar:na]
    at com.zaxxer.hikari.pool.HikariProxyConnection.prepareStatement(HikariProxyConnection.java) ~[HikariCP-5.1.0.jar:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) ~[na:na]
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:77) ~[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:568) ~[na:na]
    at org.apache.ibatis.logging.jdbc.ConnectionLogger.invoke(ConnectionLogger.java:53) ~[mybatis-3.5.13.jar:3.5.13]
    at jdk.proxy3/jdk.proxy3.$Proxy121.prepareStatement(Unknown Source) ~[na:na]
    at org.apache.ibatis.executor.statement.PreparedStatementHandler.instantiateStatement(PreparedStatementHandler.java:88) ~[mybatis-3.5.13.jar:3.5.13]
    at org.apache.ibatis.executor.statement.BaseStatementHandler.prepare(BaseStatementHandler.java:90) ~[mybatis-3.5.13.jar:3.5.13]
    ... 48 common frames omitted
RaigorJiang commented 9 months ago

@niaucz I used a similar configuration to yours and can't reproduce the problem. Can you provide a reproducible demo project?

rules

rules:
- !READWRITE_SPLITTING
  dataSources:
    readwrite_ds:
      writeDataSourceName: write_ds
      readDataSourceNames:
        - read_ds_0
      loadBalancerName: random
  loadBalancers:
    random:
      type: RANDOM
- !SHARDING
  autoTables:
    t_order:
      actualDataSources: readwrite_ds
      shardingStrategy:
        standard:
          shardingColumn: order_id
          shardingAlgorithmName: hash-mod-module-algorithm
  shardingAlgorithms:
    hash-mod-module-algorithm:
      type: MOD
      props:
        sharding-count: 4
- !SINGLE
  tables:
    - "*.*"

log

image
niaucz commented 9 months ago

@RaigorJiang

https://github.com/niaucz/ss-test.git

RaigorJiang commented 9 months ago

@niaucz I saw your project. It seems that create table is not executed through ShardingSphere? In this case, there are no tables required for sharding in your DB.

I submitted a PR, you can execute testCreateTable first and then testInsert https://github.com/niaucz/ss-test/pull/1

Logic SQL: INSERT INTO nx_data ( id, point_guid, tag, `value`, `timestamp` ) VALUES ( ?, ? , ? , ? , ?  )
Actual SQL: write_ds ::: INSERT INTO nx_data_2 ( id, point_guid, tag, `value`, `timestamp` ) VALUES (?, ?, ?, ?, ?) ::: [1735191437835788289, 21F272A4-6D8A-4638-9C9F-07D2B12E4B14, 205021001003, 8158.21, 2023-12-26 14:31:56.0]
niaucz commented 9 months ago

@RaigorJiang thank you👍

prefect1005 commented 9 months ago

@RaigorJiang i have the same question, in my project ,maybe have more than one database,just like "select from db1.table" "select form db2.table2" use 5.4.1 if we config "databaseName: db1", then db2.table2 will TableNotExistsException so what can i do to fix it

shijie-328931589 commented 5 months ago

@niaucz I saw your project. It seems that create table is not executed through ShardingSphere? In this case, there are no tables required for sharding in your DB.

I submitted a PR, you can execute testCreateTable first and then testInsert niaucz/ss-test#1

Logic SQL: INSERT INTO nx_data ( id, point_guid, tag, `value`, `timestamp` ) VALUES ( ?, ? , ? , ? , ?  )
Actual SQL: write_ds ::: INSERT INTO nx_data_2 ( id, point_guid, tag, `value`, `timestamp` ) VALUES (?, ?, ?, ?, ?) ::: [1735191437835788289, 21F272A4-6D8A-4638-9C9F-07D2B12E4B14, 205021001003, 8158.21, 2023-12-26 14:31:56.0]

I don't think it's a good idea。 In the case of use sharding table，SimpleTableSegmentBinder checkTableExists is earlier than SQLRouter，we use logic table name in the sql, when checkTableExists，must require a logical table name in the database。