You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "carlors (via GitHub)" <gi...@apache.org> on 2024/10/22 08:01:05 UTC

[I] ShardingSphere-JDBC got NullPointerException error with postgresql 14 version [shardingsphere]

carlors opened a new issue, #33347:
URL: https://github.com/apache/shardingsphere/issues/33347

   ## Bug Report
   
   ### Which version of ShardingSphere did you use?
   5.1.2
   ### Which project did you use? ShardingSphere-JDBC or ShardingSphere-Proxy?
   ShardingSphere-JDBC
   ### Expected behavior
   Execute select statement and return query result successfully when I query the sharding tables.
   ### Actual behavior
   The ShardingSphere-JDBC return an error:
   org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException: 
   Error querying database.  Cause: java.lang.NullPointerException
   The error occurred while handling results
   SQL: select                      id, uid, status,ctime from table_name                         WHERE uid =?   order by ctime desc;
   Cause: java.lang.NullPointerException
   at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:97)
   at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:439)
   at com.sun.proxy.$Proxy356.selectList(Unknown Source)
   at org.mybatis.spring.SqlSessionTemplate.selectList(SqlSessionTemplate.java:224)
   at org.apache.ibatis.binding.MapperMethod.executeForMany(MapperMethod.java:147)
   at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:80)
   at org.apache.ibatis.binding.MapperProxy$PlainMethodInvoker.invoke(MapperProxy.java:145)
   at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:86)
   at com.sun.proxy.$Proxy406.selectByExample(Unknown Source)
   ### Reason analyze (If you can)
   The ShardingSphere-JDBC can not get shard table's meta information for aws aurora postgresql 14.11 version when the db user is not same with the table owner.Since everything works well with postgresql 12.8 before I upgrade the db to postgresql 14.11 version.
   ### Steps to reproduce the behavior, such as: SQL to execute, sharding rule configuration, when exception occur etc.
   1.Create one aws aurora postgresql cluster which engine version is 14.11,and create two database db1 and db2;
   2.Create a table both in db1 and db2, change the table owner to user2;
   alter table owner to user2;
   3.Set  ShardingSphere-JDBC db user as user1
   ```yaml
   dataSources:
     ds_0:
       url: jdbc:postgresql://xxx.cluster-ckcvjkzxh8bd.ap-northeast-1.rds.amazonaws.com:5432/db1?prepareThreshold=0
       username: user1
       password: user1password
       connectionTimeoutMilliseconds: 30000
       idleTimeoutMilliseconds: 60000
       maxLifetimeMilliseconds: 1800000
       maxPoolSize: 50
       minPoolSize: 1
     ds_1:
       url: jdbc:postgresql://xxx.cluster-ckcvjkzxh8bd.ap-northeast-1.rds.amazonaws.com:5432/db2?prepareThreshold=0
       username: user1
       password: user1password
       connectionTimeoutMilliseconds: 30000
       idleTimeoutMilliseconds: 60000
       maxLifetimeMilliseconds: 1800000
       maxPoolSize: 50
       minPoolSize: 1
   ```
   4.Config table shard rule the same as offcial example and start the application and execute the query.
   ### Example codes for reproduce this issue (such as a github link).
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] ShardingSphere-JDBC got NullPointerException error with postgresql 14 version [shardingsphere]

Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu closed issue #33347: ShardingSphere-JDBC got NullPointerException error with postgresql 14 version
URL: https://github.com/apache/shardingsphere/issues/33347


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] ShardingSphere-JDBC got NullPointerException error with postgresql 14 version [shardingsphere]

Posted by "github-actions[bot] (via GitHub)" <gi...@apache.org>.
github-actions[bot] commented on issue #33347:
URL: https://github.com/apache/shardingsphere/issues/33347#issuecomment-3599342332

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


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org


Re: [I] ShardingSphere-JDBC got NullPointerException error with postgresql 14 version [shardingsphere]

Posted by "terrymanu (via GitHub)" <gi...@apache.org>.
terrymanu commented on issue #33347:
URL: https://github.com/apache/shardingsphere/issues/33347#issuecomment-3565435140

   • Problem Understanding
   
     - On ShardingSphere-JDBC 5.1.2 with Aurora PostgreSQL 14.11, application user user1 is not the table owner. Querying sharded tables raises an NPE in MyBatis, indicating metadata fetch failure.
   
     Root Cause
   
     - ShardingSphere loads PG metadata by querying information_schema.role_table_grants and information_schema.columns. If the current user lacks explicit privileges on the tables/sequences, these information-
       schema views filter out the tables, so ShardingSphere cannot obtain column/index/constraint metadata, leading to NPE in later binding/result handling.
     - Aurora PostgreSQL 14 behaves like community PostgreSQL 14 for information-schema visibility. Because Aurora has no true superuser (rds_superuser is still constrained by ACL), missing grants more readily
       results in “metadata not visible.”
   
     Analysis
   
     - Grant the connecting user explicit schema/table/sequence privileges. Minimal example (replace schema/table as needed):
   
       GRANT USAGE ON SCHEMA public TO user1;
       GRANT SELECT, REFERENCES ON ALL TABLES IN SCHEMA public TO user1;
       GRANT USAGE, SELECT ON ALL SEQUENCES IN SCHEMA public TO user1;
       ALTER DEFAULT PRIVILEGES FOR ROLE user2 IN SCHEMA public GRANT SELECT, REFERENCES ON TABLES TO user1;
     - Verify visibility using the connecting user:
   
       SELECT table_schema, table_name, privilege_type
       FROM information_schema.role_table_grants
       WHERE grantee = current_user AND table_name = '<logical_table_name>';
   
       SELECT table_schema, table_name
       FROM information_schema.columns
       WHERE table_name = '<logical_table_name>';
       If no rows are returned, privileges are insufficient.
     - ShardingSphere targets standard PostgreSQL semantics; if the target database diverges from standard PG behavior, custom extensions may be needed to adapt to its privilege/catalog behavior.
   
     Conclusion
   
     - The observed NPE stems from missing privileges causing information-schema invisibility, not from a confirmed product defect. Please grant the privileges above and retry.
     - Official documentation: PostgreSQL 14 information schema and visibility https://www.postgresql.org/docs/14/information-schema.html , privilege management https://www.postgresql.org/docs/14/ddl-priv.html .
     - If NPE persists after granting, please share the full stack trace (including ShardingSphere class names) and the results of the two verification queries above for further diagnosis.
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org