You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by "xrayw (via GitHub)" <gi...@apache.org> on 2023/02/23 16:09:56 UTC
[GitHub] [shardingsphere] xrayw opened a new issue, #24328: auto increment primary key backfill failed
xrayw opened a new issue, #24328:
URL: https://github.com/apache/shardingsphere/issues/24328
## Question
I have some table, such as
- user
- user_0
- user_1
I want always insert data to user and i'll move old data to user_0/user_1 manually at sometime.
but when i run my code, i got the error:
> org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.executor.ExecutorException: Error getting generated key or setting result to parameter object. Cause: org.apache.ibatis.executor.result.ResultMapException: Error attempting to get column #1 from result set. Cause: java.lang.NullPointerException: **ResultSet should call next or has no more data**.
my config:
```sql
CREATE TABLE `user` (
`id` bigint NOT NULL AUTO_INCREMENT,
`uu` varchar(255) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;
```
sharding.yml
```yml
mode:
type: Standalone
repository:
type: JDBC
dataSources:
ds_0:
dataSourceClassName: com.zaxxer.hikari.HikariDataSource
url: jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8
password: xxx
username: xxx
driver-class-name: com.mysql.jdbc.Driver
rules:
- !SHARDING
defaultKeyGenerateStrategy:
column: id
keyGeneratorName: autoinc
defaultDatabaseStrategy:
standard:
shardingColumn: id
shardingAlgorithmName: db_master
tables:
user:
actualDataNodes: ds_0.user
tableStrategy:
standard:
shardingColumn: id
shardingAlgorithmName: user_archive
keyGenerateStrategy:
column: id
keyGeneratorName: autoinc
keyGenerators:
autoinc:
type: AUTOINC
shardingAlgorithms:
db_master:
type: INLINE
props:
algorithm-expression: ds_0
user_archive:
type: CLASS_BASED
props:
strategy: STANDARD
algorithmClassName: xxxx.UserShardingAlgorithm
props:
sql-show: true
```
UserShardingAlgorithm
```java
public class UserShardingAlgorithm implements StandardShardingAlgorithm<Long> {
@Override
public String getType() {
return "USER_SHARD";
}
@Override
public String doSharding(Collection<String> availables, PreciseShardingValue<Long> shardingValue) {
return "user";
}
@Override
public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Long> rangeShardingValue) {
return null;
}
@Override
public Properties getProps() {
return null;
}
@Override
public void init(Properties properties) {
}
}
```
AutoIncrementKeyGeneratorAlgorithm.java
```java
public class AutoIncrementKeyGeneratorAlgorithm implements KeyGenerateAlgorithm {
@Override
public Comparable<?> generateKey() {
return null;
}
@Override
public Properties getProps() {
return null;
}
@Override
public String getType() {
return "AUTOINC";
}
@Override
public void init(Properties properties) {
}
}
```
entity
```java
@Data
public class User {
@TableId(type = IdType.AUTO)
private Long id;
private String uu;
}
```
My test code:
```java
@Autowired
private UserMapper userMapper;
@Test
public void testJDBC() {
User user = new User();
user.setUu("text_uu");
userMapper.insert(user); // error occurs here because can not get the last_insert_id()
System.out.println(user.getId());
}
```
UserMapper
```java
@Mapper
public interface UserMapper extends BaseMapper<User> {}
```
``` log
Logic SQL: insert into user (uu) values (?)
Actual SQL: ds_0 ::: insert into user (uu, id) values (?, ?) ::: [text_uu, null]
```
I want to know how i can get the auto increment paimary key's value.
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.
To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org.apache.org
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
[GitHub] [shardingsphere] xrayw commented on issue #24328: auto increment primary key backfill failed
Posted by "xrayw (via GitHub)" <gi...@apache.org>.
xrayw commented on issue #24328:
URL: https://github.com/apache/shardingsphere/issues/24328#issuecomment-1449182647
But i need move old data from user to archive tables like user_1, user_2, ...user_x
and to query data from all tables.
The `query from all tables` need sharding rule.
--
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] RaigorJiang commented on issue #24328: auto increment primary key backfill failed
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang commented on issue #24328:
URL: https://github.com/apache/shardingsphere/issues/24328#issuecomment-1581917057
This issue has been inactive for a long time, so I will close it.
If this problem persists, please reopen it or submit a new one.
--
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] xrayw commented on issue #24328: auto increment primary key backfill failed
Posted by "xrayw (via GitHub)" <gi...@apache.org>.
xrayw commented on issue #24328:
URL: https://github.com/apache/shardingsphere/issues/24328#issuecomment-1448184887
Example:
1. Insert 300 rows data to user, the max id in user is 300 now.
2. manually move rows 1-100 to user_1, 101-200 to user_2, **now user table have data 201-300**
Data alwasy insert to main table `user`, other table like ** user_1, user_2 ... user_x ** just for query
I can write a shardingRule such as:
```java
public class UserShardingAlgorithm implements StandardShardingAlgorithm<Long> {
@Override
public String getType() {
return "USER_SHARD";
}
@Override
public String doSharding(Collection<String> availables, PreciseShardingValue<Long> shardingValue) {
Long id = shardingValue.getValue();
if (id == null) {
// always insert to user
return "user";
}
// for query
List<IdTableRange> idTableMapping = someIdMappingLogic();
for (IdTableRange idTableRange : idTableMapping) {
if (idTableRange.contains(id)) {
return idTableRange.table;
}
}
throw new RuntimeException("unreachable");
}
@Override
public Collection<String> doSharding(Collection<String> collection, RangeShardingValue<Long> rangeShardingValue) {
return null;
}
@Override
public Properties getProps() {
return null;
}
@Override
public void init(Properties properties) {
}
}
```
--
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] xrayw commented on issue #24328: auto increment primary key backfill failed
Posted by "xrayw (via GitHub)" <gi...@apache.org>.
xrayw commented on issue #24328:
URL: https://github.com/apache/shardingsphere/issues/24328#issuecomment-1447670275
Do you need more information?
--
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] xrayw commented on issue #24328: auto increment primary key backfill failed
Posted by "xrayw (via GitHub)" <gi...@apache.org>.
xrayw commented on issue #24328:
URL: https://github.com/apache/shardingsphere/issues/24328#issuecomment-1442659033
@strongduanmu Hi, I found the table i configed at the sharding.yml will always run the `KeyGenerateAlgorithm#generateKey()` when i execute insert sql , So i return `null/0` to use db's auto_increment primary key..
Or something was wrong with my usage about how to use auto_increment primary key when table is configed in the sharding.yml.
--
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 #24328: auto increment primary key backfill failed
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #24328:
URL: https://github.com/apache/shardingsphere/issues/24328#issuecomment-1442619580
Hi @xrayw, why is your `AutoIncrementKeyGeneratorAlgorithm# generateKey` method returns is null? In the Sharding feature, generateKey is called to generate the distributed id.
--
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] RaigorJiang closed issue #24328: auto increment primary key backfill failed
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang closed issue #24328: auto increment primary key backfill failed
URL: https://github.com/apache/shardingsphere/issues/24328
--
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] xrayw commented on issue #24328: auto increment primary key backfill failed
Posted by "xrayw (via GitHub)" <gi...@apache.org>.
xrayw commented on issue #24328:
URL: https://github.com/apache/shardingsphere/issues/24328#issuecomment-1487852845
Hi, Is there any solution for this scenario?? 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.
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 #24328: auto increment primary key backfill failed
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #24328:
URL: https://github.com/apache/shardingsphere/issues/24328#issuecomment-1447849330
From your configuration, it seems you don't need sharding?
```yaml
tables:
user:
actualDataNodes: ds_0.user
tableStrategy:
standard:
shardingColumn: id
shardingAlgorithmName: user_archive
keyGenerateStrategy:
column: id
keyGeneratorName: autoinc
```
--
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 #24328: auto increment primary key backfill failed
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #24328:
URL: https://github.com/apache/shardingsphere/issues/24328#issuecomment-1449114781
If the user table is a single table, there is no need to configure sharding rules and keyGenerateStrategy.
--
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] RaigorJiang commented on issue #24328: auto increment primary key backfill failed
Posted by "RaigorJiang (via GitHub)" <gi...@apache.org>.
RaigorJiang commented on issue #24328:
URL: https://github.com/apache/shardingsphere/issues/24328#issuecomment-1579863855
It looks like the problem is because the key output by the AUTOINC algorithm is null.
For your scenario, you can find a way to control the global self-increment of this value, and let the AUTOINC algorithm output the value according to the expectation.
--
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] xrayw commented on issue #24328: auto increment primary key backfill failed
Posted by "xrayw (via GitHub)" <gi...@apache.org>.
xrayw commented on issue #24328:
URL: https://github.com/apache/shardingsphere/issues/24328#issuecomment-1447868378
I need!
> I want always insert data to user table and i'll move old data to user_0/user_1 manually at sometime.
Always insert to user, and read from multi table such as user, user_0, user_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.
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 #24328: auto increment primary key backfill failed
Posted by "strongduanmu (via GitHub)" <gi...@apache.org>.
strongduanmu commented on issue #24328:
URL: https://github.com/apache/shardingsphere/issues/24328#issuecomment-1447900763
I can't get your point. If you need to use the sharding, it means that the local id is not globally unique. At this time, a globally unique id must be generated through sharding key generator, such as the snowflake algorithm.
--
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