This topic describes the limits on using sequences. This topic also describes how to troubleshoot primary key conflicts.

Limits and additional considerations

When you use a sequence, take note of the following points:

  • You must specify the START WITH parameter when you convert a sequence from one type to another.
  • You cannot convert a unit group sequence to another type or convert a sequence of another type to a unit group sequence. In addition, you cannot change the parameter values of a unit group sequence, except the value of the START WITH parameter.
  • Unit group sequences that belong to the same globally unique sequence space must have the same number of units but have different unit indexes.
  • Whenthe INSERT statement is executed in a non-sharded Distributed Relational Database Service (DRDS) database or in a sharded database that has only non-sharded tables but no broadcast tables, DRDS automatically optimizes and sends the statement. This way, you do not need to use an optimizer to allocate sequence values. The INSERT INTO ... VALUES (seq.nextval, ...) statementis not supported. We recommend that you use the auto-increment column feature provided by ApsaraDB RDS instead.
  • If a hint for a specific database shard is used in the INSERT statement such as INSERT INTO ... VALUES ... or INSERT INTO ...SELECT ..., and the destination table uses a sequence,DRDS bypasses the optimizer and directly sends the statement for execution. This way, the sequence does not take effect and the auto-increment column in the ApsaraDB RDS table is used to generate IDs in the destination table.
  • Make sure that the auto-increment IDs in the same table are generated by using the same method: a sequence or the auto-increment feature provided by ApsaraDB RDS. If the two methods are used in the same table, duplicate IDs may be generated. Duplicate IDs are difficult to identify.
  • If you want to use a time-based sequence in an auto-increment column of a table, make sure that the data type of the column is BIGINT.

Troubleshoot primary key conflicts

If a data record is directly written to ApsaraDB RDS and the corresponding primary key value is nota sequence value generated by DRDS, the primary key value automatically generated by DRDS may conflict with the primary key value that corresponds to the data record. To solve this issue, perform the following steps:

  1. Execute the SHOW SEQUENCES statement to view the existing sequences. Sequences prefixed with AUTO_SEQ_ are implicit sequences. To create an implicit sequence, you must specify the AUTO_INCREMENT parameter in the statement executed to create a table.

    Execute the following statement in your CLI:

    mysql> SHOW SEQUENCES;

    The following query result is returned:

    +---------------------+-------+--------------+------------+-----------+-------+-------+ 
    | NAME                | VALUE | INCREMENT_BY | START_WITH | MAX_VALUE | CYCLE | TYPE  | 
    +---------------------+-------+--------------+------------+-----------+-------+-------+ 
    | AUTO_SEQ_xkv_t_item | 0     | N/A          | N/A        | N/A       | N/A   | GROUP | 
    | AUTO_SEQ_xkv_shard  | 0     | N/A          | N/A        | N/A       | N/A   | GROUP | 
    +---------------------+-------+--------------+------------+-----------+-------+-------+ 
    2 rows in set (0.04 sec)
  2. If the primary key of the xkv_t_item table is ID and duplicate primary key values exist in this table,query the maximum primary key value of this table from DRDS.

    Execute the following statement in your CLI:

    mysql> SELECT MAX(id) FROM xkv_t_item;

    The following query result is returned:

    +-----------+ 
    | MAX(id)   | 
    +-----------+ 
    | 8231      | 
    +-----------+ 
    1 row in set (0.01 sec)
  3. Change the maximum sequence value in the table to a value greater than 8231, such as 9000. Then, no error is returned for the subsequently generated auto-increment primary key values when you execute the INSERT statement.

    Execute the following statement in your CLI:

    mysql> ALTER SEQUENCE AUTO_SEQ_xkv_t_item START WITH 9000;