auto increment primary key backfill failed

[xrayw]

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:

CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `uu` varchar(255) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

sharding.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

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

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

@Data
public class User {
    @TableId(type = IdType.AUTO)
    private Long id;
    private String uu;
}

My test code:

@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

@Mapper
public interface UserMapper extends BaseMapper<User> {}

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

shardingjdbc version: 5.3.1
mybatisplus version: 3.5.3.1

[strongduanmu]

Hi @xrayw, why is your AutoIncrementKeyGeneratorAlgorithm# generateKey method returns is null? In the Sharding feature, generateKey is called to generate the distributed id.

[xrayw]

@strongduanmu Hi, thanks for your reply.

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.

[xrayw]

Do you need more information?

[strongduanmu]

From your configuration, it seems you don't need sharding?

  tables:
    user:
      actualDataNodes: ds_0.user
      tableStrategy:
        standard:
          shardingColumn: id
          shardingAlgorithmName: user_archive
      keyGenerateStrategy:
        column: id
        keyGeneratorName: autoinc

[xrayw]

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

[strongduanmu]

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.

[xrayw]

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 always insert to main table user, other table like user_1, user_2 ... user_x just for query,
So the id is globally unique.

I can write a shardingRule such as:

public class UserShardingAlgorithm implements StandardShardingAlgorithm<Long> {
    @Override
    public String getType() {
        return "USER_SHARD";
    }

      /**
     * [
     *     {
     * 		range: 1 - 100,
     * 		table: user_1
     *        },
     *    {
     * 		range: 101 - 200,
     * 		table: user_2
     *    }
     * ]
     */
    @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) {

    }
}

[strongduanmu]

If the user table is a single table, there is no need to configure sharding rules and keyGenerateStrategy.

[xrayw]

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.

[xrayw]

Hi, Is there any solution for this scenario?? thanks

[RaigorJiang]

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.

[RaigorJiang]

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.