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

Limits and precautions

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 a different type.
  • You cannot convert a unit group sequence to a different type or convert a sequence of specific type to a unit group sequence. You also 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 sequence space must have the same number of units but have different unit indexes.
  • If the INSERT statement is executed in a non-sharded PolarDB-X database or a sharded database that has only non-sharded tables but no broadcast tables, PolarDB-X automatically optimizes and sends the statement. This way, you do not need to use an optimizer to allocate sequence values. In this case, the INSERT INTO ... VALUES (seq.nextval, ...) statement is not supported. We recommend that you use the auto-increment column feature provided by ApsaraDB RDS instead.
  • If a hint that is used to specify database shards is contained in an INSERT statement such as INSERT INTO ... VALUES ... or INSERT INTO... SELECT ..., and the target table for which the INSERT statement is executed uses a sequence, PolarDB-X bypasses the optimizer and directly sends the statement to be executed. In this case, the sequence does not take effect and the auto-increment column in the table that is stored in ApsaraDB RDS is used to generate IDs in the target table.
  • You can use a sequence or the auto-increment feature provided by ApsaraDB RDS to generate auto-increment IDs. Make sure that you use the same method to generate auto-increment IDs in the same table. If both methods are used in the same table, duplicate IDs can 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 not a sequence value generated by PolarDB-X, the primary key value that is automatically generated by PolarDB-X 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 whose names are prefixed with AUTO_SEQ_ are implicit sequences. To create an implicit sequence, you must specify the AUTO_INCREMENT parameter in the statement that is executed to create a table.

    Execute the following statement in your CLI:

    mysql> SHOW SEQUENCES;

    The following code provides an example of the returned information:

    +---------------------+-------+--------------+------------+-----------+-------+-------+ 
    | 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 PolarDB-X.

    Execute the following statement in your CLI:

    mysql> SELECT MAX(id) FROM xkv_t_item;

    The following code provides an example of the returned information:

    +-----------+ 
    | 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. After the value is changed, no error is returned for auto-increment primary keys that are generated when you execute the INSERT statement.

    Execute the following statement in your CLI:

    mysql> ALTER SEQUENCE AUTO_SEQ_xkv_t_item START WITH 9000;