Shardingsphere: Throw NPE at ExecutionContextBuilder.getPrimaryKeyColumns when execute sql

[lwtdev]

Throw NPE at ExecutionContextBuilder.getPrimaryKeyColumns when execute sql

Which version of ShardingSphere did you use?

5.0.0-RC1-SNAPSHOT

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

ShardingSphere-Proxy

Expected behavior

Actual behavior

mysql> select * from customer where id = 3;
ERROR 10002 (C1000): 2Unknown exception: [null]

log

java.lang.NullPointerException: null
        at org.apache.shardingsphere.infra.executor.sql.context.ExecutionContextBuilder.getPrimaryKeyColumns(ExecutionContextBuilder.java:129)
        at org.apache.shardingsphere.infra.executor.sql.context.ExecutionContextBuilder.getSQLRuntimeContext(ExecutionContextBuilder.java:81)
        at org.apache.shardingsphere.infra.executor.sql.context.ExecutionContextBuilder.build(ExecutionContextBuilder.java:71)
        at org.apache.shardingsphere.infra.executor.sql.context.ExecutionContextBuilder.build(ExecutionContextBuilder.java:57)
        at org.apache.shardingsphere.infra.context.kernel.KernelProcessor.generateExecutionContext(KernelProcessor.java:53)
        at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:67)
        at org.apache.shardingsphere.proxy.backend.text.query.QueryBackendHandler.execute(QueryBackendHandler.java:64)
        at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:62)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:100)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:76)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)

Reason analyze (If you can)

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

• prepare sql

create database spsqltest_sharding_00;
create database spsqltest_sharding_01;

• config-test.yaml

schemaName: test
#
dataSourceCommon:
  username: root
  password: root135
  connectionTimeoutMilliseconds: 30000
  idleTimeoutMilliseconds: 60000
  maxLifetimeMilliseconds: 1800000
  maxPoolSize: 50
  minPoolSize: 1
  maintenanceIntervalMilliseconds: 30000
#
dataSources:
  ds_00:
    url: jdbc:mysql://127.0.0.1:3306/spsqltest_sharding_00?serverTimezone=UTC&useSSL=false
  ds_01:
    url: jdbc:mysql://127.0.0.1:3306/spsqltest_sharding_01?serverTimezone=UTC&useSSL=false
#
rules:
- !SHARDING
  tables:
    customer:
      actualDataNodes: ds_00.customer_000${0..1},ds_01.customer_000${2..3}
      databaseStrategy:
        standard:
          shardingColumn: id
          shardingAlgorithmName: database_inline
      tableStrategy:
        standard:
          shardingColumn: id
          shardingAlgorithmName: customer_inline
    customer_email:
      actualDataNodes: ds_00.customer_email_000${0..1},ds_01.customer_email_000${2..3}
      databaseStrategy:
        standard:
          shardingColumn: id
          shardingAlgorithmName: database_inline
      tableStrategy:
        standard:
          shardingColumn: id
          shardingAlgorithmName: customer_email_inline
  bindingTables:
    - customer,customer_email
  defaultDatabaseStrategy:
    none:
  defaultTableStrategy:
    none:

  shardingAlgorithms:
    database_inline:
      type: INLINE
      props:
        algorithm-expression: ds_0${id % 2}
    customer_inline:
      type: INLINE
      props:
        algorithm-expression: customer_000${id % 4}
    customer_email_inline:
      type: INLINE
      props:
        algorithm-expression: customer_email_000${id % 4}

• execute sql

select * from customer where id = 3;

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

[kimmking]

hi, @lwtdev
two points need to clearify:

1. when you create your table, before or after proxy started?
2. your inline expression is wrong, maybe route to ds_01.customer_0001, that doesn't exist.

[tristaZero]

Hi @lwtdev
Thanks for your detailed issue.
Firstly, I also wonder how you created the customer table, which will help position the problem.
Secondly, I have to say this is a bad example of defensive programming. 😞

[kimmking]

the same reason with https://github.com/apache/shardingsphere/issues/6165

[kimmking]

Hi @lwtdev
Thanks for your detailed issue.
Firstly, I also wonder how you created the customer table, which will help position the problem.
Secondly, I have to say this is a band example of defensive programming. 😞

@xbkaishui has tested create-table case before proxy started work well.

[lwtdev]

hi, @lwtdev
two points need to clearify:

1. when you create your table, before or after proxy started?
2. your inline expression is wrong, maybe route to ds_01.customer_0001, that doesn't exist.

1. I did not create any tables;
   In fact , the sql I want to execute was sctl:explain select * from customer where id = 3;
   And It's also cause a exception.

mysql> sctl:explain select * from customer where id = 3;
ERROR 10002 (C1000): 2Unknown exception: [null]

I test it in 4.1.1 and works well:

mysql> sctl:explain select * from customer_email where id = 3;
+-----------------+------------------------------------------------+
| datasource_name | sql                                            |
+-----------------+------------------------------------------------+
| ds_01           | select * from customer_email_0003 where id = 3 |
+-----------------+------------------------------------------------+
1 row in set (0.24 sec)

1. Thanks for your patient to figure out this problem
   The right config of database_inlineshould be

    database_inline:
      type: INLINE
      props:
        algorithm-expression: ds_0${(id % 4 ).intdiv(2)}

[lwtdev]

Hi @lwtdev
Thanks for your detailed issue.
Firstly, I also wonder how you created the customer table, which will help position the problem.
Secondly, I have to say this is a band example of defensive programming. 😞

Yes, This is not a good config example （and it was wrong...）. it's was a example simplify from config as follow(in 4.x):

      databaseStrategy:
        inline:
          shardingColumn: customer_id
          algorithmExpression: ds_${String.format("%02d",new BigDecimal(customer_id).abs().divideAndRemainder(4)[1].longValue().intdiv(2))}
      tableStrategy:
        inline:
          shardingColumn: customer_id
          algorithmExpression: customer_email_${String.format("%04d",new BigDecimal(customer_id).abs().divideAndRemainder(4)[1].longValue())}

[lwtdev]

I have just try create table after proxy start, it's also cause an npe

 create table customer(id bigint(23) primary key );
ERROR 10002 (C1000): 2Unknown exception: [null]

• log

java.lang.NullPointerException: null
        at org.apache.shardingsphere.infra.executor.sql.context.ExecutionContextBuilder.getPrimaryKeyColumns(ExecutionContextBuilder.java:129)
        at org.apache.shardingsphere.infra.executor.sql.context.ExecutionContextBuilder.getSQLRuntimeContext(ExecutionContextBuilder.java:81)
        at org.apache.shardingsphere.infra.executor.sql.context.ExecutionContextBuilder.build(ExecutionContextBuilder.java:71)
        at org.apache.shardingsphere.infra.executor.sql.context.ExecutionContextBuilder.build(ExecutionContextBuilder.java:57)
        at org.apache.shardingsphere.infra.context.kernel.KernelProcessor.generateExecutionContext(KernelProcessor.java:53)
        at org.apache.shardingsphere.proxy.backend.communication.jdbc.JDBCDatabaseCommunicationEngine.execute(JDBCDatabaseCommunicationEngine.java:67)
        at org.apache.shardingsphere.proxy.backend.text.query.QueryBackendHandler.execute(QueryBackendHandler.java:64)
        at org.apache.shardingsphere.proxy.frontend.mysql.command.query.text.query.MySQLComQueryPacketExecutor.execute(MySQLComQueryPacketExecutor.java:62)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.executeCommand(CommandExecutorTask.java:100)
        at org.apache.shardingsphere.proxy.frontend.command.CommandExecutorTask.run(CommandExecutorTask.java:76)
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
        at java.lang.Thread.run(Thread.java:748)

In proxy 4.1.1 it was works well

mysql> create table customer(id bigint(23) primary key );
Query OK, 0 rows affected (0.14 sec)

mysql> show tables;
+---------------------------------+
| Tables_in_spsqltest_sharding_00 |
+---------------------------------+
| customer                        |

[tristaZero]

Hi @lwtdev
So, if you create a logic table customer and execute select * from customer where id = 3;, does everything go well?

Plus, if there is a logic table, do you think the following error will still happen?

mysql> sctl:explain select * from customer where id = 3;
ERROR 10002 (C1000): 2Unknown exception: [null]

[lwtdev]

Hi @lwtdev
So, if you create a logic table customer and execute select * from customer where id = 3;, does everything go well?

Plus, if there is a logic table, do you think the following error will still happen?

mysql> sctl:explain select * from customer where id = 3;
ERROR 10002 (C1000): 2Unknown exception: [null]

Can not create logic table customer after proxy started.

 create table customer(id bigint(23) primary key );
ERROR 10002 (C1000): 2Unknown exception: [null]

If create physical tables customer_000{0...3} before proxy started. It works well:

mysql> sctl:explain select * from customer where id = 3;
+-----------------+------------------------------------------+
| datasource_name | sql                                      |
+-----------------+------------------------------------------+
| ds_01           | select * from customer_0003 where id = 3 |
+-----------------+------------------------------------------+
1 row in set (0.01 sec)

mysql>  select * from customer where id = 3;
Empty set (0.07 sec)

[xbkaishui]

This is same case I came across, the accumption is table must be already exists when use proxy (in master branch)

[kimmking]

We will fix it soon.

[tristaZero]

Hi, @xbkaishui @lwtdev Thanks for your feedback, from which I summarize the likely issues below. Moreover, could you give them a double-check that there is any mistake with my understanding?

1. If there are no actual tables for customer table rule. It is possible for users to execute create table customer in ShardingProxy to create these actual customer_xx tables (Or we can interpret it as a logic table customer). However, currently, This function can not work well, can it?
2. If there are no actual tables for the customer table rule, which actually means there is no logic table named customer in ShardingProxy, then any queries on this customer table should return the error like ERROR 1146 (42S02): Table 'customer' doesn't exist. At present, NPE is unexpected error info, right.

BTW, @kimmking Are you working on fixing this issue now?

We will fix it soon.

[xbkaishui]

In my side , this is not only happened in proxy side, other components (jdbc, master-slave) has error too, when the table not exists in system, ( create or drop table if not exists sql) throws NullPointerException

[lwtdev]

@tristaZero

1. If there are no actual tables for customer table rule. It is possible for users to execute create table customer in ShardingProxy to create these actual customer_xx tables (Or we can interpret it as a logic table customer). However, currently, This function can not work well, can it?

Thats' the problem. This function can not work well when I test yestday.

1. If there are no actual tables for the customer table rule, which actually means there is no logic table named customer in ShardingProxy, then any queries on this customer table should return the error like ERROR 1146 (42S02): Table 'customer' doesn't exist. At present, NPE is unexpected error info, right.

Yes, and stcl:explain sql should return sql route information.

[tristaZero]

Hi, @xbkaishui @lwtdev Thanks for your reply.
Your feedback convinces us that we have two bugs in the master branch.

We have to fix them before 5.x release.

BTW, is anyone interested in fixing one or two of them?

[xbkaishui]

please assign to me

[tristaZero]

@xbkaishui Good luck :-)

[xbkaishui]

mysql> desc t_order;
ERROR 1146 (42S02): Table 'demo_ds_0.t_order_0' doesn't exist
mysql> explain select * from t_order;
ERROR 1235 (42000): This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `explain select * from t_order`'

Seems ShardingProxy not support explain

[strongduanmu]

mysql> desc t_order;
ERROR 1146 (42S02): Table 'demo_ds_0.t_order_0' doesn't exist
mysql> explain select * from t_order;
ERROR 1235 (42000): This version of ShardingProxy doesn't yet support this SQL. 'Unsupported SQL of `explain select * from t_order`'

Seems ShardingProxy not support explain

@xbkaishui You can try sctl:explain select * from t_order;

[xbkaishui]

Yes, sctl:explain select * from t_order; that's ok for me.

[tristaZero]

Hi @lwtdev
Thanks to @xbkaishui , this issue has been fixed. Could you give the latest master branch a test to cover your scenarios?

Very appreciated.

[lwtdev]

Hi @tristaZero @xbkaishui I have test my scenarios , that work well.

• Test select 、 create table and drop table when table not exsits before ss start.

mysql> show tables;
Empty set (0.01 sec)

mysql> sctl:explain select * from customer where id = 3;
+-----------------+------------------------------------------+
| datasource_name | sql                                      |
+-----------------+------------------------------------------+
| ds_01           | select * from customer_0003 where id = 3 |
+-----------------+------------------------------------------+
1 row in set (0.00 sec)

mysql> select * from customer where id = 3;
ERROR 1146 (42S02): Table 'spsqltest_sharding_01.customer_0003' doesn't exist
mysql> CREATE TABLE `customer` (
    ->   `id` bigint(20) NOT NULL COMMENT '主键ID',
    ->   `party_id` bigint(20) NOT NULL COMMENT '用户ID',
    ->    `status` smallint(6) DEFAULT NULL COMMENT '状态：1-有效，0-无效  待删除字段',
    ->   PRIMARY KEY (`id`),
    ->   KEY `party_id_index` (`party_id`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='客户表';
Query OK, 0 rows affected (0.44 sec)

mysql> show tables;
+---------------------------------+
| Tables_in_spsqltest_sharding_01 |
+---------------------------------+
| customer                        |
+---------------------------------+
1 row in set (0.01 sec)

mysql> insert into customer (id, party_id, status) values (1, 1, 1), (2, 1, 2), (3, 1, 3);
Query OK, 3 rows affected (0.03 sec)

mysql> sctl:explain select * from customer where id = 3;
+-----------------+------------------------------------------+
| datasource_name | sql                                      |
+-----------------+------------------------------------------+
| ds_01           | select * from customer_0003 where id = 3 |
+-----------------+------------------------------------------+
1 row in set (0.01 sec)

mysql> select * from customer where id = 3;
+----+----------+--------+
| id | party_id | status |
+----+----------+--------+
|  3 |        1 |      3 |
+----+----------+--------+
1 row in set (0.01 sec)

mysql> drop table customer;
Query OK, 0 rows affected (0.05 sec)

mysql> show tables;
Empty set (0.01 sec)

mysql> sctl:explain select * from customer where id = 3;
+-----------------+------------------------------------------+
| datasource_name | sql                                      |
+-----------------+------------------------------------------+
| ds_01           | select * from customer_0003 where id = 3 |
+-----------------+------------------------------------------+
1 row in set (0.01 sec)

mysql> select * from customer where id = 3;
ERROR 1146 (42S02): Table 'spsqltest_sharding_01.customer_0003' doesn't exist

• Test select 、 create table and drop table when table exsits before ss start.

mysql> show tables;
+---------------------------------+
| Tables_in_spsqltest_sharding_01 |
+---------------------------------+
| customer                        |
+---------------------------------+
1 row in set (0.18 sec)

mysql> sctl:explain select * from customer where id = 3;
+-----------------+------------------------------------------+
| datasource_name | sql                                      |
+-----------------+------------------------------------------+
| ds_01           | select * from customer_0003 where id = 3 |
+-----------------+------------------------------------------+
1 row in set (0.28 sec)

mysql> select * from customer where id = 3;
+----+----------+--------+
| id | party_id | status |
+----+----------+--------+
|  3 |        1 |      3 |
+----+----------+--------+
1 row in set (0.01 sec)

mysql> drop table customer;
Query OK, 0 rows affected (0.11 sec)

mysql> show tables;
Empty set (0.01 sec)

mysql> sctl:explain select * from customer where id = 3;
+-----------------+------------------------------------------+
| datasource_name | sql                                      |
+-----------------+------------------------------------------+
| ds_01           | select * from customer_0003 where id = 3 |
+-----------------+------------------------------------------+
1 row in set (0.01 sec)

mysql> select * from customer where id = 3;
ERROR 1146 (42S02): Table 'spsqltest_sharding_01.customer_0003' doesn't exist

[tristaZero]

@lwtdev Very appreciated your feedback!