All Products
Search
Document Center

Limits and notes

Last Updated: Aug 06, 2020

Limits and notes

  • The START WITH parameter must be set when the type of a sequence is changed to another.
  • You cannot convert the type of a sequence from unit group sequence to another or from another type to unit group sequence. In addition, you cannot change the parameter values of a unit group sequence except the START WITH parameter.
  • Unit group sequences that belong to the same Distributed Relational Database Service (DRDS) sequence must have the same number of units but different unit indexes.
  • Assume that an INSERT statement is executed on a non-partition DRDS database that has only one attached ApsaraDB RDS for MySQL database or on a partitioned DRDS database where each attached ApsaraDB RDS for MySQL database has only one table and the table is not a broadcast table. DRDS automatically optimizes and pushes down the statement to the attached ApsaraDB RDS for MySQL database for processing instead of using the sequence allocated by the optimizer. In this case, INSERT INTO ... VALUES (seq.nextval, ...)is not supported. We recommend that you use the auto-increment column feature of ApsaraDB RDS for MySQL.
  • If the hint for a specific database shard is used by the INSERT statement such as INSERT INTO … VALUES … or INSERT INTO … SELECT … and the target table uses a sequence, DRDS bypasses the optimizer and directly pushes down the statement so that the sequence does not take effect. The target table creates an ID by using the auto-increment feature of the ApsaraDB RDS for MySQL table.
  • The auto-increment ID allocation method for the same table must be consistent, which may be based on DRDS sequences or the auto-increment column of ApsaraDB RDS for MySQL. If both of the two allocation methods are used for the same table, duplicate IDs may be created, making location difficult.
  • When a time-based sequence is used in the auto-increment column of a table, the column must be of the BIGINT type.

Troubleshoot primary key conflicts

For example, if data is directly written to the ApsaraDB RDS for MySQL database but the primary key value is not the sequence value generated by the DRDS database, the primary key automatically generated by the DRDS database may conflict with the previously written data. You can take the following steps to solve this problem:

  1. You can execute the SHOW SEQUENCES statement to view existing sequences. A sequence prefixed with AUTO_SEQ_ is an implicit sequence. This sequence is generated when a table is created with the AUTO_INCREMENT parameter.

    1. mysql> SHOW SEQUENCES;
    2. +---------------------+-------+--------------+------------+-----------+-------+-------+
    3. | NAME | VALUE | INCREMENT_BY | START_WITH | MAX_VALUE | CYCLE | TYPE |
    4. +---------------------+-------+--------------+------------+-----------+-------+-------+
    5. | AUTO_SEQ_xkv_t_item | 0 | N/A | N/A | N/A | N/A | GROUP |
    6. | AUTO_SEQ_xkv_shard | 0 | N/A | N/A | N/A | N/A | GROUP |
    7. +---------------------+-------+--------------+------------+-----------+-------+-------+
    8. 2 rows in set (0.04 sec)
  2. For example, the xkv_t_item table has a conflict and its primary key is ID. Query the maximum primary key value of the table from the DRDS database:

    1. mysql> SELECT MAX(id) FROM xkv_t_item;
    2. +-----------+
    3. | MAX(id) |
    4. +-----------+
    5. | 8231 |
    6. +-----------+
    7. 1 row in set (0.01 sec)
  3. Update the corresponding value in the DRDS sequence table. In this example, the value is updated to a value greater than 8231, for example, 9000. After the update, no error is returned for the auto-increment primary key generated by the INSERT statement.

    1. mysql> ALTER SEQUENCE AUTO_SEQ_xkv_t_item START WITH 9000;
    2. Query OK, 1 row affected (0.01 sec)