Shardingsphere: How to configure no sharding schema in 5.0.0-RC1

[lwtdev]

Question

How to configure no sharding schema in 5.0.0-RC1 ?

In 4.1.1, my configuration is like this:

schemaName: spsqltest_nosharding
#
dataSources:
  ds_0:
    url: jdbc:mysql://127.0.0.1:3306/spsqltest_nosharding?serverTimezone=UTC&useSSL=false
    username: root
    password: root135
    connectionTimeoutMilliseconds: 30000
    idleTimeoutMilliseconds: 60000
    maxLifetimeMilliseconds: 1800000
    maxPoolSize: 50

shardingRule:
  defaultDataSourceName: ds_0
  defaultDatabaseStrategy:
    none:
  defaultTableStrategy:
    none:

In 5.0.0-RC1 :

schemaName: spsqltest_nosharding
#
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_nosharding?serverTimezone=UTC&useSSL=false

#
rules:
- !SHARDING
  defaultDatabaseStrategy:
    none:
  defaultTableStrategy:
    none:

But the effect is different for some SQL, such as the following:

ShardingProxy(4.1.1)No Sharding
dropTable[hasShardingKey:none];  Support:true;  SQL: drop table customer;
ds_0|drop table customer|


ShardingProxy(5.0.0.RC1)No Sharding
dropTable[hasShardingKey:none];  Support:false; SQL: drop table customer;
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: This version of ShardingProxy doesn't yet support this SQL. 'Cannot find table rule with logic table: 'customer''

[tristaZero]

@lwtdev Every time I see your issue, I guess there may be a bug likely. :-) Believe me, you are definitely a good QA engineer. 😃
Back to this issue, no sharding schema you mentioned means defaultDataSourceName: ds_0, doesn't it?
We remove the defaultDataSource setting, which means ShardingSphere will help the user manage all the data sources.
In your case, does customer exist in any data source? That way, users do not need to configure any defaultDataSourceName, since ShardingSphere cache the metadata of all the tables (non-sharding tables included).
Consequently, If you have a table customer in a certain data source, ShardingSphere will know its position after startup. Next, you execute drop table customer, it will position this table and run your SQL.

What do you think? Welcome your feedback kindly.

Best,
Trista

[lwtdev]

no sharding schema you mentioned means defaultDataSourceName: ds_0, doesn't it?

Yes, Some of the databases in our project need to be sharding and some do not, we want to manage them all with ShardingSphere
proxy.

In your case, does customer exist in any data source?

Yes, table customer alrealy exist before ShardingSphere proxy started.

mysql> show tables;
+--------------------------------+
| Tables_in_spsqltest_nosharding |
+--------------------------------+
| customer                       |
| customer_email                 |
| full_table                     |
+--------------------------------+
3 rows in set (0.01 sec)

mysql> select * from customer;
Empty set (0.01 sec)

mysql> drop table customer;
ERROR 1235 (42000): This version of ShardingProxy doesn't yet support this SQL. 'Cannot find table rule with logic table: 'customer''

Consequently, If you have a table customer in a certain data source, ShardingSphere will know its position after startup. Next, you execute drop table customer, it will position this table and run your SQL.
What do you think?

Because before we used additional database version management tools to manage the table structure.
So I have to learn more about this.

• What is the situation of ShardingSphere Jdbc.
• Whether to support online change table structure (add, modify, delete)
• How to synchronize the metadata of each node of the ShardingSphere Proxy cluster.
• Is there a synchronization method for modifying the external table structure of ShardingSphere without new startup (such as timing, manual or monitoring)?

[lwtdev]

@lwtdev Every time I see your issue, I guess there may be a bug likely. :-)

In fact, I found a lot of SQL with execution problems, but I don't think it should be, so I want to confirm whether the configuration is correct...

[tristaZero]

How to reproduce this bug

• Create a single table customer in any database.
• Start ShardingProxy
• Execute the following SQLs

mysql> show tables;
+--------------------------------+
| Tables_in_spsqltest_nosharding |
+--------------------------------+
| customer                       |
| customer_email                 |
| full_table                     |
+--------------------------------+
3 rows in set (0.01 sec)

mysql> select * from customer;
Empty set (0.01 sec)

mysql> drop table customer;
ERROR 1235 (42000): This version of ShardingProxy doesn't yet support this SQL. 'Cannot find table rule with logic table: 'customer''

Why does it happen

• There is no branch to handle non-sharding table at LINE 75 in ShardingRouteEngineFactory.

How to fix it

• If it is DDL SQL for a non-sharding table, return ShardingUnconfiguredTablesRoutingEngine
• If it is DDL SQL for sharding table, stay the original returned value
• getDALRoutingEngine() of ShardingRouteEngineFactory will give you a good example

[tristaZero]

• What is the situation of ShardingSphere Jdbc.
• Whether to support online change table structure (add, modify, delete)
• How to synchronize the metadata of each node of the ShardingSphere Proxy cluster.

You can consider governance feature to cover the questions above.

• Is there a synchronization method for modifying the external table structure of ShardingSphere without new startup (such as timing, manual or monitoring)?

How to understand modifying the external table structure? All the tables in all the configured data sources of ShardingProxy will be part of ShardingProxy. That way, SQL is the best way to modify them.

@lwtdev

[jiang2015]

let me try it，thanks。

[lwtdev]

You can consider governance feature to cover the questions above.

Thanks, I will confirm these later

How to understand modifying the external table structure? All the tables in all the configured data sources of ShardingProxy will be part of ShardingProxy. That way, SQL is the best way to modify them.

@tristaZero What I mean is not to modify the table structure through ShardingProxy. In this case, how to update the table metadata of ShardingProxy？

[tristaZero]

@jiang2015 Welcome. :) I assigned it to u.

[lwtdev]

@tristaZero @jiang2015

In fact, using the same configuration, not only DDL but other SQL examples are also have problem:

ShardingProxy(4.1.1)No Sharding
deleteMultiTable[hasShardingKey:all];    Support:true;  SQL: delete customer, customer_email  from customer , customer_email where customer.status = 3 and customer.id = 3 and customer_email.id = 3;
ds_0|delete customer, customer_email  from customer , customer_email where customer.status = 3 and customer.id = 3 and customer_email.id = 3|


ShardingProxy(5.0.0.RC1)No Sharding
deleteMultiTable[hasShardingKey:all];    Support:false; SQL: delete customer, customer_email  from customer , customer_email where customer.status = 3 and customer.id = 3 and customer_email.id = 3;
java.sql.SQLException: 2Unknown exception: [Cannot support Multiple-Table for 'org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.dml.MySQLDeleteStatement@6e5eca13'.]

ShardingProxy(4.1.1)No Sharding
updateMultiTable[hasShardingKey:all];    Support:true;  SQL: update customer, customer_email set customer.party_id = party_id + 1, customer_email.status = cusotmer.status where customer.id = 3 and customer_email.id = 3;
ds_0|update customer, customer_email set customer.party_id = party_id + 1, customer_email.status = cusotmer.status where customer.id = 3 and customer_email.id = 3|


ShardingProxy(5.0.0.RC1)No Sharding
updateMultiTable[hasShardingKey:all];    Support:false; SQL: update customer, customer_email set customer.party_id = party_id + 1, customer_email.status = cusotmer.status where customer.id = 3 and customer_email.id = 3;
java.sql.SQLException: 2Unknown exception: [Cannot support Multiple-Table for 'org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.dml.MySQLUpdateStatement@4f8e14c4'.]

• Table init sql

-- @title:createTableCustomer
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='客户表';

-- @title:createTableCustomerEmail
CREATE TABLE customer_email (
  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='客户邮箱表';

 -- @title:createFullFieldTypeTable
create table IF NOT EXISTS full_table
(
    id bigint(15) not null auto_increment primary key comment 'primary key',
    name varchar(255)  default 'tom' COLLATE utf8_bin comment 'name',
    class_id bigint(15) references class(id) on delete cascade,
    age int,
    t_bl bool,
    t_ti tinyint(1),
    t_si smallint(2),
    t_mi middleint(10),
    t_it int(10),
    t_bi bigint(20),
    t_dec decimal(15,2),
    t_ft float(5),
    t_db double(10,2),
    t_dt date,
    t_te time,
    t_de datetime,
    t_ts timestamp,
    t_yr year,
    t_ch char(10),
    t_vh varchar(255),
    t_by binary(2),
    t_vb varbinary(25),
    t_tb tinyblob,
    t_mb mediumblob,
    t_bb blob,
    t_lb longblob,
    t_tt tinytext,
    t_mt mediumtext,
    t_tx text,
    t_lt longtext,
    t_em enum('a', 'b') character set utf8 collate utf8_bin,
    t_st set('a', 'b'),
    t_gy geometry,
    t_pt point,
    t_ls linestring,
    t_pn polygon,
    t_mp multipoint,
    t_ml multilinestring,
    t_mn multipolygon,
    t_gn geometrycollection,
    t_jn json,
    index indx_name_and_class using hash (class_id, name(20) desc) ,
    index idx_class_id using btree (class_id asc) ,
    constraint unique key (age),
    check (age > 0)
) engine InnoDB  CHARACTER SET utf8 COLLATE utf8_bin
auto_increment = 100 checksum 1
compression = 'none'
delay_key_write = 0
max_rows = 1000
min_rows = 1
pack_keys = 0
password = 'abc'
STATS_AUTO_RECALC  = 0
STATS_PERSISTENT  = 1
STATS_SAMPLE_PAGES  = 4
ROW_FORMAT=REDUNDANT
union (class);

[jiang2015]

I cna study the code by this case, thanks.

[tristaZero]

Thanks, I like your report! @lwtdev
Hi @jiang2015, Just focus on bug-1

I guess @strongduanmu will be a right person to take charge of other improvements. :-) Could you have a look at the following issues?

[tristaZero]

From @lwtdev 's feedback below,

ShardingProxy(4.1.1)No Sharding
deleteMultiTable[hasShardingKey:all];    Support:true;  SQL: delete customer, customer_email  from customer , customer_email where customer.status = 3 and customer.id = 3 and customer_email.id = 3;
ds_0|delete customer, customer_email  from customer , customer_email where customer.status = 3 and customer.id = 3 and customer_email.id = 3|


ShardingProxy(5.0.0.RC1)No Sharding
deleteMultiTable[hasShardingKey:all];    Support:false; SQL: delete customer, customer_email  from customer , customer_email where customer.status = 3 and customer.id = 3 and customer_email.id = 3;
java.sql.SQLException: 2Unknown exception: [Cannot support Multiple-Table for 'org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.dml.MySQLDeleteStatement@6e5eca13'.]

ShardingProxy(4.1.1)No Sharding
updateMultiTable[hasShardingKey:all];    Support:true;  SQL: update customer, customer_email set customer.party_id = party_id + 1, customer_email.status = cusotmer.status where customer.id = 3 and customer_email.id = 3;
ds_0|update customer, customer_email set customer.party_id = party_id + 1, customer_email.status = cusotmer.status where customer.id = 3 and customer_email.id = 3|


ShardingProxy(5.0.0.RC1)No Sharding
updateMultiTable[hasShardingKey:all];    Support:false; SQL: update customer, customer_email set customer.party_id = party_id + 1, customer_email.status = cusotmer.status where customer.id = 3 and customer_email.id = 3;
java.sql.SQLException: 2Unknown exception: [Cannot support Multiple-Table for 'org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.dml.MySQLUpdateStatement@4f8e14c4'.]

There are two improvements for your consideration.

1. Remove or change the exposed error info
   org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.dml.MySQLUpdateStatement@4f8e14c4' is supposed to hide from end-users.
2. Fine-grained checking for validateMultipleTable
   When there are a multi-table SQL, maybe we could consider distinguishing the tables, like bind-table or broadcast-table, single-table. IMO, some of these cases are likely to be executed. BTW, postValidate() is another way to handle these cases.

[strongduanmu]

Thanks, I like your report! @lwtdev
Hi @jiang2015, Just focus on bug-1

I guess @strongduanmu will be a right person to take charge of other improvements. :-) Could you have a look at the following issues?

@tristaZero I will investigate and resolve this issue. 😀

[jiang2015]

Thanks, I like your report! @lwtdev
Hi @jiang2015, Just focus on bug-1
I guess @strongduanmu will be a right person to take charge of other improvements. :-) Could you have a look at the following issues?

@tristaZero I will investigate and resolve this issue. 😀

thanks for analysis this problems details，I can try to focus on bug-1

[kimmking]

It sounds delicious.