Sharding-JDBC 5.3.2 Automatic sharding NPE

[ReyYang]

Bug Report

Please answer these questions before submitting your issue. Thanks!

Which version of ShardingSphere did you use?

version:5.3.2

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

ShardingSphere-JDBC

Expected behavior

select success

Actual behavior

The NPE exception occurs when the select statement written by the user is used to query the table that needs to be divided into tables

Reason analyze (If you can)

private MergedResult build(final List<QueryResult> queryResults, final SelectStatementContext selectStatementContext,
                               final Map<String, Integer> columnLabelIndexMap, final ShardingSphereDatabase database) throws SQLException {
        String defaultSchemaName = DatabaseTypeEngine.getDefaultSchemaName(selectStatementContext.getDatabaseType(), database.getName());
       // schema return default value: public
        ShardingSphereSchema schema = selectStatementContext.getTablesContext().getSchemaName()
                .map(database::getSchema).orElseGet(() -> database.getSchema(defaultSchemaName));
        if (isNeedProcessGroupBy(selectStatementContext)) {
            return getGroupByMergedResult(queryResults, selectStatementContext, columnLabelIndexMap, schema);
        }
        if (isNeedProcessDistinctRow(selectStatementContext)) {
            setGroupByForDistinctRow(selectStatementContext);
            return getGroupByMergedResult(queryResults, selectStatementContext, columnLabelIndexMap, schema);
        }
        if (isNeedProcessOrderBy(selectStatementContext)) {
            return new OrderByStreamMergedResult(queryResults, selectStatementContext, schema);
        }
        return new IteratorStreamMergedResult(queryResults);
    }

private boolean getValueCaseSensitiveFromTables(final QueryResult queryResult,
                                                    final SelectStatementContext selectStatementContext, final ShardingSphereSchema schema, final int columnIndex) throws SQLException {
        for (SimpleTableSegment each : selectStatementContext.getAllTables()) {
            String tableName = each.getTableName().getIdentifier().getValue();
            // table returns null, causing NPE
            ShardingSphereTable table = schema.getTable(tableName);
            Map<String, ShardingSphereColumn> columns = table.getColumns();
            String columnName = queryResult.getMetaData().getColumnName(columnIndex);
            if (columns.containsKey(columnName)) {
                return columns.get(columnName).isCaseSensitive();
            }
        }
        return false;
    }

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

database: PostgreSQL sharding-jdbc.yaml

mode:
  type: Standalone
  repository:
    type: JDBC
# 数据源配置
dataSources:
  ds0:
    dataSourceClassName: com.alibaba.druid.pool.DruidDataSource
    driverClassName: org.postgresql.Driver
    url: jdbc:postgresql://*.*.*.*:5432/zzpush?currentSchema=zzpush&useSSL=false
    username: root
    password: z*****
    async-init: true
    initial-size: 1
    max-active: 5
    max-open-prepared-statements: 20
    max-wait: 6000
    min-evictable-idle-time-millis: 300000
    min-idle: 1
    pool-prepared-statements: true
    test-on-borrow: false
    test-on-return: false
    test-while-idle: true
    time-between-eviction-runs-millis: 60000
    validation-query: select 1
# 单库分表规则配置
rules:
- !SHARDING
  autoTables:
    push_message:
      actualDataSources: ds0
      shardingStrategy:
        standard:
          shardingColumn: msg_id
          shardingAlgorithmName: hash-mod-module-algorithm
  shardingAlgorithms:
    hash-mod-module-algorithm:
      type: HASH_MOD
      props:
        sharding-count: 8

props:
  sql-show: true

application.properties

# 配置 DataSource Driver
spring.datasource.driver-class-name=org.apache.shardingsphere.driver.ShardingSphereDriver
# 指定 YAML 配置文件
spring.datasource.url=jdbc:shardingsphere:classpath:sharding-jdbc.yaml

[ReyYang]

exception stack

### Cause: java.lang.NullPointerException
    org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:92)
    org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:440)
    com.sun.proxy.$Proxy105.selectOne(Unknown Source)
    org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:159)
    org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:87)
    org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:152)
    org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:85)
    com.sun.proxy.$Proxy107.selectTotalCount(Unknown Source)
    cn.tzt.service.impl.PushMessageServiceImpl.list(PushMessageServiceImpl.java:307)
    cn.tzt.controller.PushMessageController.list(PushMessageController.java:84)
    cn.tzt.controller.PushMessageController$$FastClassBySpringCGLIB$$cb568fa.invoke(<generated>)
    org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:218)
    org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:771)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:163)
    org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
    org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:95)
    org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186)
    org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749)
    org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691)
    cn.tzt.controller.PushMessageController$$EnhancerBySpringCGLIB$$330b3485.list(<generated>)
    sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    java.lang.reflect.Method.invoke(Method.java:498)
    org.springframework.web.method.support.InvocableHandlerMethod.doInvoke(InvocableHandlerMethod.java:190)
    org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:138)
    org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:105)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:879)
    org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:793)
    org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:87)
    org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:1040)
    org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:943)
    org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:1006)
    org.springframework.web.servlet.FrameworkServlet.doPost(FrameworkServlet.java:909)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:660)
    org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:883)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:741)
    org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
    org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53)
    org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:100)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
    org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    org.springframework.web.filter.FormContentFilter.doFilterInternal(FormContentFilter.java:93)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
    org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:201)
    org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
    org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:202)
    org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
    org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:541)
    org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
    org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
    org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
    org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
    org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:373)
    org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
    org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
    org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1590)
    org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    java.lang.Thread.run(Thread.java:748)

[ReyYang]

SQL statement

11:07:29.398 system-push [http-nio-8080-exec-1] INFO  ShardingSphere-SQL - Logic SQL: select
        count(m.id) as totalCount
        from push_message m
         WHERE  m.is_template = ?

                AND m.msg_status IN
                (
                    '3'
                )
11:07:29.398 system-push [http-nio-8080-exec-1] INFO  ShardingSphere-SQL - Actual SQL: ds0 ::: select
        count(m.id) as totalCount
        from push_message_0 m
         WHERE  m.is_template = ?

                AND m.msg_status IN
                (
                    '3'
                ) UNION ALL select
        count(m.id) as totalCount
        from push_message_1 m
         WHERE  m.is_template = ?

                AND m.msg_status IN
                (
                    '3'
                ) UNION ALL select
        count(m.id) as totalCount
        from push_message_2 m
         WHERE  m.is_template = ?

                AND m.msg_status IN
                (
                    '3'
                ) UNION ALL select
        count(m.id) as totalCount
        from push_message_3 m
         WHERE  m.is_template = ?

                AND m.msg_status IN
                (
                    '3'
                ) UNION ALL select
        count(m.id) as totalCount
        from push_message_4 m
         WHERE  m.is_template = ?

                AND m.msg_status IN
                (
                    '3'
                ) UNION ALL select
        count(m.id) as totalCount
        from push_message_5 m
         WHERE  m.is_template = ?

                AND m.msg_status IN
                (
                    '3'
                ) UNION ALL select
        count(m.id) as totalCount
        from push_message_6 m
         WHERE  m.is_template = ?

                AND m.msg_status IN
                (
                    '3'
                ) UNION ALL select
        count(m.id) as totalCount
        from push_message_7 m
         WHERE  m.is_template = ?

                AND m.msg_status IN
                (
                    '3'
                ) ::: [0, 0, 0, 0, 0, 0, 0, 0]

[ReyYang]

Can anyone help me？

[sandynz]

        // table returns null, causing NPE
       ShardingSphereTable table = schema.getTable(tableName);

Looks table could not be got from schema. Could you debug and take a screenshot of schema field Map<String, ShardingSphereTable> tables?

mode: type: Standalone repository: type: JDBC

Looks Standalone mode JDBC repository's jdbc_url is not defined, the H2 database is shared by default. Could you try to define jdbc_url and test it again? e.g.

mode:
  type: Standalone
  repository:
    type: JDBC
    props:
      provider: H2
      jdbc_url: jdbc:h2:mem:config_random12345;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;MODE=MYSQL
      username: sa
      password:

And also define different jdbc_url for different sharding-jdbc.yaml files.

[ReyYang]

        // table returns null, causing NPE
       ShardingSphereTable table = schema.getTable(tableName);

Looks table could not be got from schema. Could you debug and take a screenshot of schema field Map<String, ShardingSphereTable> tables?

mode: type: Standalone repository: type: JDBC

Looks Standalone mode JDBC repository's jdbc_url is not defined, the H2 database is shared by default. Could you try to define jdbc_url and test it again? e.g.

mode:
  type: Standalone
  repository:
    type: JDBC
    props:
      provider: H2
      jdbc_url: jdbc:h2:mem:config_random12345;DB_CLOSE_DELAY=-1;DATABASE_TO_UPPER=false;MODE=MYSQL
      username: sa
      password:

And also define different jdbc_url for different sharding-jdbc.yaml files.

Hi @sandynz Here is a screenshot of the schema field map tables

You can see that the table I need isn't in this list, I think it's because I didn't get the schema I needed when I got theschema

About the jdbc_urlof H2 I see that a default address is configured for it in the JDBCRepository, is it still needed here?

[sandynz]

About the jdbc_url of H2 I see that a default address is configured for it in the JDBCRepository, is it still needed here?

Define a new jdbc_url for isolation, the default one is shared by all ShardingSphereDataSource s.

And does it work on ZooKeeper repository?

[ReyYang]

About the jdbc_url of H2 I see that a default address is configured for it in the JDBCRepository, is it still needed here?

Define a new jdbc_url for isolation, the default one is shared by all ShardingSphereDataSource s.

And does it work on ZooKeeper repository?

Hi @sandynz

Do you mean I need to create a new h2 database? and I tried using the zookeeper cluster method to start the project, the problem still exists, I don't understand why the HikariPool is still started using cluster mode.

There are logs.

There are config

In order to better solve this problem, this is the metadata information in the zookeeper.

The metadata has the schema I need, but somehow it's not being used in the code.

[github-actions[bot]]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.

[github-actions[bot]]

There hasn't been any activity on this issue recently, and in order to prioritize active issues, it will be marked as stale.