NPE on SELECT INFORMATION_SCHEMA.PARAMETERS

mzzsfy commented 2 years ago

Bug Report

For English only, other languages will not accept.

Before report a bug, make sure you have:

Searched open and closed GitHub issues.
Read documentation: ShardingSphere Doc.

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.

Please answer these questions before submitting your issue. Thanks!

Which version of ShardingSphere did you use?

5.2

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

ShardingSphere-Proxy in docker

Expected behavior

SQL result

Actual behavior

java.lang.NullPointerException: Cannot invoke "org.apache.shardingsphere.infra.federation.optimizer.context.parser.OptimizerParserContext.getDialectProps()" because the return value of "java.util.Map.get(Object)" is null at org.apache.shardingsphere.infra.federation.executor.original.OriginalFederationExecutor.createConnection(OriginalFederationExecutor.java:92) at org.apache.shardingsphere.infra.federation.executor.original.OriginalFederationExecutor.executeQuery(OriginalFederationExecutor.java:83) at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.doExecuteFederation(JDBCDatabaseCommunicationEngine.java:165) at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:123) at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:94) at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:110) at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:77) at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1136) at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:635) at java.base/java.lang.Thread.run(Thread.java:833)

Reason analyze (If you can)

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

SELECT SPECIFIC_SCHEMA AS PROCEDURE_CAT, NULL AS `PROCEDURE_SCHEM`, SPECIFIC_NAME AS `PROCEDURE_NAME`, IFNULL(PARAMETER_NAME, '') AS `COLUMN_NAME`, CASE WHEN PARAMETER_MODE = 'IN' THEN 1 WHEN PARAMETER_MODE = 'OUT' THEN 4 WHEN PARAMETER_MODE = 'INOUT' THEN 2 WHEN ORDINAL_POSITION = 0 THEN 5 ELSE 0 END AS `COLUMN_TYPE`, CASE  WHEN UPPER(DATA_TYPE)='DECIMAL' THEN 3 WHEN UPPER(DATA_TYPE)='DECIMAL UNSIGNED' THEN 3 WHEN UPPER(DATA_TYPE)='TINYINT' THEN -6 WHEN UPPER(DATA_TYPE)='TINYINT UNSIGNED' THEN -6 WHEN UPPER(DATA_TYPE)='BOOLEAN' THEN 16 WHEN UPPER(DATA_TYPE)='SMALLINT' THEN 5 WHEN UPPER(DATA_TYPE)='SMALLINT UNSIGNED' THEN 5 WHEN UPPER(DATA_TYPE)='INT' THEN 4 WHEN UPPER(DATA_TYPE)='INT UNSIGNED' THEN 4 WHEN UPPER(DATA_TYPE)='FLOAT' THEN 7 WHEN UPPER(DATA_TYPE)='FLOAT UNSIGNED' THEN 7 WHEN UPPER(DATA_TYPE)='DOUBLE' THEN 8 WHEN UPPER(DATA_TYPE)='DOUBLE UNSIGNED' THEN 8 WHEN UPPER(DATA_TYPE)='NULL' THEN 0 WHEN UPPER(DATA_TYPE)='TIMESTAMP' THEN 93 WHEN UPPER(DATA_TYPE)='BIGINT' THEN -5 WHEN UPPER(DATA_TYPE)='BIGINT UNSIGNED' THEN -5 WHEN UPPER(DATA_TYPE)='MEDIUMINT' THEN 4 WHEN UPPER(DATA_TYPE)='MEDIUMINT UNSIGNED' THEN 4 WHEN UPPER(DATA_TYPE)='DATE' THEN 91 WHEN UPPER(DATA_TYPE)='TIME' THEN 92 WHEN UPPER(DATA_TYPE)='DATETIME' THEN 93 WHEN UPPER(DATA_TYPE)='YEAR' THEN 91 WHEN UPPER(DATA_TYPE)='VARCHAR' THEN 12 WHEN UPPER(DATA_TYPE)='VARBINARY' THEN -3 WHEN UPPER(DATA_TYPE)='BIT' THEN -7 WHEN UPPER(DATA_TYPE)='JSON' THEN -1 WHEN UPPER(DATA_TYPE)='ENUM' THEN 1 WHEN UPPER(DATA_TYPE)='SET' THEN 1 WHEN UPPER(DATA_TYPE)='TINYBLOB' THEN -3 WHEN UPPER(DATA_TYPE)='TINYTEXT' THEN 12 WHEN UPPER(DATA_TYPE)='MEDIUMBLOB' THEN -4 WHEN UPPER(DATA_TYPE)='MEDIUMTEXT' THEN -1 WHEN UPPER(DATA_TYPE)='LONGBLOB' THEN -4 WHEN UPPER(DATA_TYPE)='LONGTEXT' THEN -1 WHEN UPPER(DATA_TYPE)='BLOB' THEN -4 WHEN UPPER(DATA_TYPE)='TEXT' THEN -1 WHEN UPPER(DATA_TYPE)='CHAR' THEN 1 WHEN UPPER(DATA_TYPE)='BINARY' THEN -2 WHEN UPPER(DATA_TYPE)='GEOMETRY' THEN -2 WHEN UPPER(DATA_TYPE)='UNKNOWN' THEN 1111 WHEN UPPER(DATA_TYPE)='POINT' THEN -2 WHEN UPPER(DATA_TYPE)='LINESTRING' THEN -2 WHEN UPPER(DATA_TYPE)='POLYGON' THEN -2 WHEN UPPER(DATA_TYPE)='MULTIPOINT' THEN -2 WHEN UPPER(DATA_TYPE)='MULTILINESTRING' THEN -2 WHEN UPPER(DATA_TYPE)='MULTIPOLYGON' THEN -2 WHEN UPPER(DATA_TYPE)='GEOMETRYCOLLECTION' THEN -2 WHEN UPPER(DATA_TYPE)='GEOMCOLLECTION' THEN -2 ELSE 1111 END  AS `DATA_TYPE`, UPPER(CASE WHEN LOCATE('UNSIGNED', UPPER(DTD_IDENTIFIER)) != 0 AND LOCATE('UNSIGNED', UPPER(DATA_TYPE)) = 0 AND LOCATE('SET', UPPER(DATA_TYPE)) <> 1 AND LOCATE('ENUM', UPPER(DATA_TYPE)) <> 1 THEN CONCAT(DATA_TYPE, ' UNSIGNED') WHEN UPPER(DATA_TYPE)='POINT' THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE)='LINESTRING' THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE)='POLYGON' THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE)='MULTIPOINT' THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE)='MULTILINESTRING' THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE)='MULTIPOLYGON' THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE)='GEOMETRYCOLLECTION' THEN 'GEOMETRY' WHEN UPPER(DATA_TYPE)='GEOMCOLLECTION' THEN 'GEOMETRY' ELSE UPPER(DATA_TYPE) END) AS TYPE_NAME, CASE WHEN LCASE(DATA_TYPE)='date' THEN 0 WHEN LCASE(DATA_TYPE)='time' OR LCASE(DATA_TYPE)='datetime' OR LCASE(DATA_TYPE)='timestamp' THEN DATETIME_PRECISION WHEN UPPER(DATA_TYPE)='MEDIUMINT' AND LOCATE('UNSIGNED', UPPER(DTD_IDENTIFIER)) != 0 THEN 8 WHEN UPPER(DATA_TYPE)='JSON' THEN 1073741824 ELSE NUMERIC_PRECISION END AS `PRECISION`, CASE WHEN LCASE(DATA_TYPE)='date' THEN 10 WHEN LCASE(DATA_TYPE)='time' THEN 8+(CASE WHEN DATETIME_PRECISION>0 THEN DATETIME_PRECISION+1 ELSE DATETIME_PRECISION END) WHEN LCASE(DATA_TYPE)='datetime' OR LCASE(DATA_TYPE)='timestamp'  THEN 19+(CASE WHEN DATETIME_PRECISION>0 THEN DATETIME_PRECISION+1 ELSE DATETIME_PRECISION END) WHEN UPPER(DATA_TYPE)='MEDIUMINT' AND LOCATE('UNSIGNED', UPPER(DTD_IDENTIFIER)) != 0 THEN 8 WHEN UPPER(DATA_TYPE)='JSON' THEN 1073741824 WHEN CHARACTER_MAXIMUM_LENGTH IS NULL THEN NUMERIC_PRECISION WHEN CHARACTER_MAXIMUM_LENGTH > 2147483647 THEN 2147483647 ELSE CHARACTER_MAXIMUM_LENGTH END AS LENGTH,NUMERIC_SCALE AS `SCALE`, 10 AS RADIX,1 AS `NULLABLE`, NULL AS `REMARKS`, NULL AS `COLUMN_DEF`, NULL AS `SQL_DATA_TYPE`, NULL AS `SQL_DATETIME_SUB`, CASE WHEN CHARACTER_OCTET_LENGTH > 2147483647 THEN 2147483647 ELSE CHARACTER_OCTET_LENGTH END AS `CHAR_OCTET_LENGTH`, ORDINAL_POSITION, 'YES' AS `IS_NULLABLE`, SPECIFIC_NAME FROM INFORMATION_SCHEMA.PARAMETERS WHERE SPECIFIC_SCHEMA = 'test' AND SPECIFIC_NAME LIKE '%' ORDER BY SPECIFIC_SCHEMA, SPECIFIC_NAME, ROUTINE_TYPE, ORDINAL_POSITION

Example codes for reproduce this issue (such as a github link).

Use only READWRITE_SPLITTING

mysql: 8.0.27

server.yml

mode:
 type: Cluster
 repository:
    type: ZooKeeper
    props:
     namespace: dev
     server-lists: zookeeper:2181
     retryIntervalMilliseconds: 500
     timeToLiveSeconds: 60
     maxRetries: 3
     operationTimeoutMilliseconds: 500
 overwrite: false

rules:
  - !AUTHORITY
    users:
      - root@%:root
    provider:
      type: ALL_PERMITTED

props:
  sql-simple: true
  check-table-metadata-enabled: true
  check-duplicate-table-enabled: true
  proxy-mysql-default-version: 8.0.27

config-test.yaml

schemaName: test
dataSources:
  write_ds_0:
    url: jdbc:mysql://mysql:3306/test?serverTimezone=UTC&allowPublicKeyRetrieval=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 100
    minPoolSize: 1
  read_ds_0: 
    url: jdbc:mysql://mysql:3307/test?serverTimezone=UTC&allowPublicKeyRetrieval=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 1000
    minPoolSize: 1

rules:
  - !READWRITE_SPLITTING
    dataSources:
      readwrite_ds:
        staticStrategy:
          writeDataSourceName: write_ds_0
          readDataSourceNames:
            - read_ds_0
        loadBalancerName: random
    loadBalancers:
      random:
        type: RANDOM

mzzsfy commented 2 years ago

By adding an intermediate configuration, this problem has been resolved

config-mate.yaml

schemaName: information_schema
dataSources:
  ds:
    url: jdbc:mysql://mysql:3306/information_schema?serverTimezone=UTC&allowPublicKeyRetrieval=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 1
    minPoolSize: 1

If I have a lot of data source, it will be very stupid,could you tell me is there a better solution

ZZemptypoint commented 1 year ago

By adding an intermediate configuration, this problem has been resolved

config-mate.yaml
schemaName: information_schema
dataSources:
  ds:
    url: jdbc:mysql://mysql:3306/information_schema?serverTimezone=UTC&allowPublicKeyRetrieval=true&useSSL=false&useUnicode=true&characterEncoding=UTF-8
    username: root
    password: root
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 1
    minPoolSize: 1
If I have a lot of data source, it will be very stupid,could you tell me is there a better solution

it's worked, but why? Does filename "config-mate.yaml" is special?

mzzsfy commented 1 year ago

it's worked, but why? Does filename "config-mate.yaml" is special?

Explicitly specify how sharding-proxy should route

filename "config-mate.yaml" not special information_schema is a special schema

ZZemptypoint commented 1 year ago

it's worked, but why? Does filename "config-mate.yaml" is special?

Explicitly specify how sharding-proxy should route

ok, thx

strongduanmu commented 1 year ago

Hi @mzzsfy, thank you for your feedback, I think we can optimize the route logic when user only config one group readwrite splitting datasource.

apache / shardingsphere