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 2022/01/28 13:34:50 UTC
[GitHub] [shardingsphere] homeway88 opened a new issue #15160: how to use key generator?
homeway88 opened a new issue #15160:
URL: https://github.com/apache/shardingsphere/issues/15160
## Question
I setup a table with column `id` to use key generator, but I can not insert data with sql of `insert into person (name, age) values ('demo', 1);`, the unexpected actual sql is `INSERT INTO person_01 (name,age) VALUES (?, ?); ::: [demo, 1]`.
`INSERT INTO person_01 (id, name,age) VALUES (?, ?); ::: [xxxxx, demo, 1]` is what I want;
by the way, I find it very strange of `GeneratedKeyContextEngine#findGenerateKeyColumn` function, it compares logical table names and actual table names, which make no sense, is this a bug or my usage is wrong?
<img width="1050" alt="截屏2022-01-28 下午9 27 06" src="https://user-images.githubusercontent.com/4954638/151554919-82a293f5-11c1-4e85-b2d8-47e81da57289.png">
# version of ShardingSphere-jdbc
* 5.0.0
# application.properties
```properties
# datasource
# datasource connection config
spring.shardingsphere.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
spring.shardingsphere.datasource.ds1.driver-class-name=org.h2.Driver
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:h2:mem:test
spring.shardingsphere.datasource.ds1.username=root
spring.shardingsphere.datasource.ds1.password=123qwe
spring.shardingsphere.datasource.names=ds1
spring.shardingsphere.props.sql-show=true
# sharding-algorithms
spring.shardingsphere.rules.sharding.sharding-algorithms.sharding-person.type=HASH_MOD
spring.shardingsphere.rules.sharding.sharding-algorithms.sharding-person.props.sharding-count=2
# key generator
spring.shardingsphere.rules.sharding.key-generators.uuid.type=UUID
spring.shardingsphere.rules.sharding.key-generators.snowflake.type=SNOWFLAKE
spring.shardingsphere.rules.sharding.key-generators.snowflake.props.worker-id=1014
# sharding config
## person
spring.shardingsphere.rules.sharding.tables.person.actual-data-nodes=ds1.person_0$->{0..1}
spring.shardingsphere.rules.sharding.tables.person.table-strategy.standard.sharding-column=name
spring.shardingsphere.rules.sharding.tables.person.table-strategy.standard.sharding-algorithm-name=sharding-person
spring.shardingsphere.rules.sharding.tables.person.key-generate-strategy.column=id
spring.shardingsphere.rules.sharding.tables.person.key-generate-strategy.key-generator-name=type
```
# table of person
```sql
create table if not exists person_00 (
id bigint primary key,
name varchar(32),
age int
);
create table if not exists person_01 (
id bigint primary key,
name varchar(32),
age int
);
```
--
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
[GitHub] [shardingsphere] homeway88 closed issue #15160: how to use key generator?
Posted by GitBox <gi...@apache.org>.
homeway88 closed issue #15160:
URL: https://github.com/apache/shardingsphere/issues/15160
--
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
[GitHub] [shardingsphere] strongduanmu commented on issue #15160: how to use key generator?
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #15160:
URL: https://github.com/apache/shardingsphere/issues/15160#issuecomment-1024258943
@homeway88 Dose `person_00` and `person_01` exist in ds1 dataSource?
--
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
[GitHub] [shardingsphere] strongduanmu commented on issue #15160: how to use key generator?
Posted by GitBox <gi...@apache.org>.
strongduanmu commented on issue #15160:
URL: https://github.com/apache/shardingsphere/issues/15160#issuecomment-1024775857
>
@homeway88 I guess you executed the create table statement on the native database after starting the JDBC program, so that JDBC cannot perceive the newly added table.
There are two ways to solve this problem, write the code to create the table through JDBC, so that the newly added table can be loaded into the metadata. A better way is to use both JDBC and Proxy through the Mode feature. Proxy is responsible for managing configuration and DDL operations, and JDBC reads these configurations through Mode.
--
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
[GitHub] [shardingsphere] homeway88 commented on issue #15160: how to use key generator?
Posted by GitBox <gi...@apache.org>.
homeway88 commented on issue #15160:
URL: https://github.com/apache/shardingsphere/issues/15160#issuecomment-1028857477
> @homeway88 I guess you executed the create table statement on the native database after starting the JDBC program, so that JDBC cannot perceive the newly added table.
>
> There are two ways to solve this problem, write the code to create the table through JDBC, so that the newly added table can be loaded into the metadata. A better way is to use both JDBC and Proxy through the Mode feature. Proxy is responsible for managing configuration and DDL operations, and JDBC reads these configurations through Mode.
I figured out the reason of this case is because missing table metadata of newly created table. So I solve this problem with these two steps:
* first , use sharding JDBC to create real tables. so I change ddl sql into this:
```sql
create table if not exists person (
id bigint primary key,
name varchar(32),
age int
);
```
* second, change h2 database to mysql mode. So the meta data of the table can be loaded.
```properties
# datasource connection config
spring.shardingsphere.datasource.ds1.jdbc-url=jdbc:h2:mem:test;MODE=MYSQL
```
--
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
[GitHub] [shardingsphere] homeway88 commented on issue #15160: how to use key generator?
Posted by GitBox <gi...@apache.org>.
homeway88 commented on issue #15160:
URL: https://github.com/apache/shardingsphere/issues/15160#issuecomment-1024274418
> @homeway88 Dose `person_00` and `person_01` exist in ds1 dataSource?
Works fine if connecting to a database with table person_00 and person_01 exists.
But failed if connecting to an empty database and then create table person_00 and person_01 afterwards.
--
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