You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2020/10/10 03:41:29 UTC
[GitHub] [shardingsphere] lwtdev opened a new issue #7731: How to configure no sharding schema in 5.0.0-RC1
lwtdev opened a new issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731
## Question
## How to configure no sharding schema in 5.0.0-RC1 ?
In 4.1.1, my configuration is like this:
```yaml
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 :
```yaml
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:
```sql
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''
```
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev commented on issue #7731: How to configure no sharding schema in 5.0.0-RC1
Posted by GitBox <gi...@apache.org>.
lwtdev commented on issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731#issuecomment-706511640
> 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.
```sql
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 cusomter;
ERROR 1235 (42000): This version of ShardingProxy doesn't yet support this SQL. 'Cannot find table rule with logic table: 'cusomter''
```
> 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)?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev commented on issue #7731: How to configure no sharding schema in 5.0.0-RC1
Posted by GitBox <gi...@apache.org>.
lwtdev commented on issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731#issuecomment-707007288
@tristaZero @jiang2015
In fact, using the same configuration in addition to DDL and other SQL examples are also has problem:
```sql
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**
```sql
use test;
DROP table if exists customer;
drop table if exists customer_email;
drop table if exists full_table;
-- @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);
```
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jiang2015 commented on issue #7731: How to configure no sharding schema in 5.0.0-RC1
Posted by GitBox <gi...@apache.org>.
jiang2015 commented on issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731#issuecomment-707774805
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
> > Thanks, I like your report! @lwtdev
> > Hi @jiang2015, Just focus on [bug-1](https://github.com/apache/shardingsphere/issues/7731#issuecomment-706967050)
> > 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. 😀
@strongduanmu @tristaZero thanks for analysis this problem，I can focus on bug-1。
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero edited a comment on issue #7731: How to configure no sharding schema in 5.0.0-RC1
Posted by GitBox <gi...@apache.org>.
tristaZero edited a comment on issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731#issuecomment-706972149
- 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](https://shardingsphere.apache.org/document/current/cn/features/governance/management/) 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
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev edited a comment on issue #7731: How to configure no sharding schema in 5.0.0-RC1
Posted by GitBox <gi...@apache.org>.
lwtdev edited a comment on issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731#issuecomment-706511640
> 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.
```sql
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)?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev commented on issue #7731: How to configure no sharding schema in 5.0.0-RC1
Posted by GitBox <gi...@apache.org>.
lwtdev commented on issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731#issuecomment-706512911
>@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...
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jiang2015 commented on issue #7731: How to configure no sharding schema in 5.0.0-RC1
Posted by GitBox <gi...@apache.org>.
jiang2015 commented on issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731#issuecomment-707014500
I cna study the code by this case, thanks.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #7731: How to configure no sharding schema in 5.0.0-RC1
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731#issuecomment-707490910
From @lwtdev 's feedback below,
```sql
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.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #7731: How to configure no sharding schema in 5.0.0-RC1
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731#issuecomment-707486311
Thanks, I like your report! @lwtdev
@jiang2015 Just focus on [bug-1](https://github.com/apache/shardingsphere/issues/7731#issuecomment-706967050)
I guess @strongduanmu will be a right person to take charge of other improvements. :-)
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #7731: How to configure no sharding schema in 5.0.0-RC1
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731#issuecomment-706967050
### How to reproduce this bug
1. Create a single table `customer` in any database.
2. Start ShardingProxy
3. Execute the following SQLs
```sql
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
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #7731: How to configure no sharding schema in 5.0.0-RC1
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731#issuecomment-706505728
@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
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev commented on issue #7731: How to configure no sharding schema in 5.0.0-RC1
Posted by GitBox <gi...@apache.org>.
lwtdev commented on issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731#issuecomment-706996912
> 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？
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero edited a comment on issue #7731: How to configure no sharding schema in 5.0.0-RC1
Posted by GitBox <gi...@apache.org>.
tristaZero edited a comment on issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731#issuecomment-706972149
- 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](https://shardingsphere.apache.org/document/current/cn/features/governance/management/) 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.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jiang2015 commented on issue #7731: How to configure no sharding schema in 5.0.0-RC1
Posted by GitBox <gi...@apache.org>.
jiang2015 commented on issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731#issuecomment-706983372
let me try it，thanks。
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev edited a comment on issue #7731: How to configure no sharding schema in 5.0.0-RC1
Posted by GitBox <gi...@apache.org>.
lwtdev edited a comment on issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731#issuecomment-707007288
@tristaZero @jiang2015
In fact, using the same configuration not only DDL but other SQL examples are also have problem:
```sql
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**
```sql
use test;
DROP table if exists customer;
drop table if exists customer_email;
drop table if exists full_table;
-- @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);
```
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #7731: How to configure no sharding schema in 5.0.0-RC1
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731#issuecomment-706972149
- 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 use [governance feature](https://shardingsphere.apache.org/document/current/cn/features/governance/management/) 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.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jiang2015 removed a comment on issue #7731: How to configure no sharding schema in 5.0.0-RC1
Posted by GitBox <gi...@apache.org>.
jiang2015 removed a comment on issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731#issuecomment-707774805
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
> > Thanks, I like your report! @lwtdev
> > Hi @jiang2015, Just focus on [bug-1](https://github.com/apache/shardingsphere/issues/7731#issuecomment-706967050)
> > 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. 😀
@strongduanmu @tristaZero thanks for analysis this problem，I can focus on bug-1。
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero edited a comment on issue #7731: How to configure no sharding schema in 5.0.0-RC1
Posted by GitBox <gi...@apache.org>.
tristaZero edited a comment on issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731#issuecomment-707486311
Thanks, I like your report! @lwtdev
Hi @jiang2015, Just focus on [bug-1](https://github.com/apache/shardingsphere/issues/7731#issuecomment-706967050)
I guess @strongduanmu will be a right person to take charge of other improvements. :-) Could you have a look at the following issues?
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero edited a comment on issue #7731: How to configure no sharding schema in 5.0.0-RC1
Posted by GitBox <gi...@apache.org>.
tristaZero edited a comment on issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731#issuecomment-706967050
### How to reproduce this bug
- Create a single table `customer` in any database.
- Start ShardingProxy
- Execute the following SQLs
```sql
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
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] kimmking closed issue #7731: How to configure no sharding schema in 5.0.0-RC1
Posted by GitBox <gi...@apache.org>.
kimmking closed issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev edited a comment on issue #7731: How to configure no sharding schema in 5.0.0-RC1
Posted by GitBox <gi...@apache.org>.
lwtdev edited a comment on issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731#issuecomment-707007288
@tristaZero @jiang2015
In fact, using the same configuration, not only DDL but other SQL examples are also have problem:
```sql
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**
```sql
use test;
DROP table if exists customer;
drop table if exists customer_email;
drop table if exists full_table;
-- @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);
```
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] jiang2015 commented on issue #7731: How to configure no sharding schema in 5.0.0-RC1
Posted by GitBox <gi...@apache.org>.
jiang2015 commented on issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731#issuecomment-707775260
> > Thanks, I like your report! @lwtdev
> > Hi @jiang2015, Just focus on [bug-1](https://github.com/apache/shardingsphere/issues/7731#issuecomment-706967050)
> > 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
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] strongduanmu commented on issue #7731: How to configure no sharding schema in 5.0.0-RC1
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731#issuecomment-707677095
> Thanks, I like your report! @lwtdev
> Hi @jiang2015, Just focus on [bug-1](https://github.com/apache/shardingsphere/issues/7731#issuecomment-706967050)
>
> 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. 😀
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] tristaZero commented on issue #7731: How to configure no sharding schema in 5.0.0-RC1
Posted by GitBox <gi...@apache.org>.
tristaZero commented on issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731#issuecomment-707000254
@jiang2015 Welcome. :) I assigned it to u.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] lwtdev edited a comment on issue #7731: How to configure no sharding schema in 5.0.0-RC1
Posted by GitBox <gi...@apache.org>.
lwtdev edited a comment on issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731#issuecomment-707007288
@tristaZero @jiang2015
In fact, using the same configuration, not only DDL but other SQL examples are also have problem:
```sql
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**
```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);
```
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] kimmking commented on issue #7731: How to configure no sharding schema in 5.0.0-RC1
Posted by GitBox <gi...@apache.org>.
kimmking commented on issue #7731:
URL: https://github.com/apache/shardingsphere/issues/7731#issuecomment-718408476
It sounds delicious.
----------------------------------------------------------------
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.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org