PolarDB:Limits

Limits and precautions

• If your database is in AUTO mode, the auto-increment column in a table can be associated with only a NEW sequence.
• If your database is in DRDS mode, the system does not associate non-partitioned tables with sequences. If you want to associate a non-partitioned table with a sequence, you must specify a sequence for the table.
• If you do not specify a primary key for a table when you create the table, PolarDB-X automatically creates an implicit primary key by using the AUTO_INCREMENT keyword. PolarDB-X also creates a GROUP sequence and then associates the GROUP sequence with the implicit primary key.
• If the auto-increment column in a table is associated with a TIME sequence, the data type of the auto-increment column must be BIGINT.
• If the INSERT statement contains a hint that is used to specify a database shard, such as a INSERT INTO ... VALUES ... or a INSERT INTO ... SELECT ..., and the destination table contains an auto-increment column, PolarDB-X does not optimize the statement and pushes down the statement to the data node layer for execution. As a result, the configuration of the sequence that is associated with the auto-increment column does not take effect. In this case, PolarDB-X uses the values in the auto-increment column in the physical table shard on the data node as the values of the auto-increment column in the destination table.
• You can use the ALTER SEQUENCE statement to add or delete an auto-increment column or change values in an auto-increment column.
• PolarDB-X does not support custom step sizes for sequences. The value of the auto_increment_increment variable is 1, and the auto_increment_offset variable is not supported.
• In a NEW sequence, Global Meta Service (GMS) is used to allocate the value of AUTO_INCREMENT. To ensure that the performance is as expected, you must cache values in GMS. If GMS performs operations such as upgrade, switchover, configuration change, and migration, the values that are cached in the memory are discarded before the operations are performed. As a result, the values are discontinuous.

Troubleshoot issues of duplicate primary key values

In specific scenarios, duplicate primary key values may be generated. For example, the ID column is used as the primary key column of a table. When an SQL statement that includes a hint is pushed down to the data node layer for execution, physical table shards generate values for the ID column. In this case, the sequence that is associated with the table cannot obtain the values that are generated by the physical table shards. As a result, the sequence generates duplicate values for the ID column. You can perform the following steps to resolve this issue:

1. Execute the SHOW SEQUENCES statement to query the existing sequences. Sequences whose names contain the AUTO_SEQ_ prefix are implicit sequences. An implicit sequence is a sequence that is automatically created based on the auto-increment column when you create the table.

   SHOW SEQUENCES;

   Sample result:

   +---------------------+-------+--------------+------------+-----------+-------+-------+ 
   | 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. Query the existing maximum value in the primary key column of the PolarDB-X table. In this example, the primary key column of the xkv_t_item table is id, and the table contains duplicate primary key values. You can execute the following statement to query the existing maximum value in the id column:

   SELECT MAX(id) FROM xkv_t_item;

   Sample result:

   +-----------+ 
   | MAX(id)   | 
   +-----------+ 
   | 8231      | 
   +-----------+ 
   1 row in set (0.01 sec)

3. Change the start value of the sequence to a value that is greater than the existing maximum value of the primary key. In this example, the existing maximum value of the primary key is 8231. You can change the start value of the sequence to 9000. After the start value of the sequence is changed, the sequence generates subsequent values that are greater than or equal to 9000. This way, when you execute new statements to insert data, the sequence does not generate duplicate primary key values.

   ALTER SEQUENCE AUTO_SEQ_xkv_t_item START WITH 9000;