CREATE TABLE with an AUTO_INCREMENT Column

AUTO_INCREMENT in Sharded Tables

On a sharded (distributed) table, AUTO_INCREMENT can only be used on a BIGINT column (as they usually use the entire 64 bits). Each aggregator computes and tracks its own AUTO_INCREMENT values and uses those values when new rows are added to a table. The values on each aggregator are usually, but not always, sequential; therefore, inserts on an individual aggregator generate values which are unique and usually sequential. And because each aggregator manages its own AUTO_INCREMENT values, the automatically-generated values from inserts across multiple aggregators are only unique, never sequential.

Here is an example to illustrate how AUTO_INCREMENT values are generated across aggregators in a cluster as new rows are inserted into table ct_tb:

SELECT * FROM ct_tb ORDER BY b;

+-------------------+------+------------+
| a                 | b    | c          |
+-------------------+------+------------+
|                 1 |    1 | from MA    |
|                 2 |    2 | from MA    |
|                 3 |    3 | from MA    |
|                 4 |    4 | from MA    |
|                 5 |    5 | from MA    |
| 13510798882111489 |    6 | from CA 96 |
| 13510798882111490 |    7 | from CA 96 |
| 13510798882111491 |    8 | from CA 96 |
| 13510798882111492 |    9 | from CA 96 |
| 13510798882111493 |   10 | from CA 96 |
| 14636698788954113 |   11 | from CA 20 |
| 14636698788954114 |   12 | from CA 20 |
| 14636698788954115 |   13 | from CA 20 |
| 14636698788954116 |   14 | from CA 20 |
| 14636698788954117 |   15 | from CA 20 |
|                 6 |   16 | from MA    |
| 15762598695796737 |   17 | from CA 17 |
| 13510798882111494 |   18 | from CA 96 |
|                 7 |   19 | from MA    |
| 14636698788954118 |   20 | from CA 20 |
+-------------------+------+------------+

As shown in the example above, automatically-generated AUTO_INCREMENT values can differ depending on which aggregator you run the inserts on. Of course, if you ran some inserts on one aggregator and some inserts on another aggregator, you would get different automatically generated values. Also note that automatically-generated values and explicitly-set values can collide in sharded tables.

AUTO_INCREMENT in Reference Tables

The AUTO_INCREMENT value for a reference table is tracked by the master aggregator. It is guaranteed that the next AUTO_INCREMENT value will always be greater than any value previously seen in this column. These generated values are usually sequential, but not always. Contrarily to the behavior for sharded tables, explicitly setting a value in an INSERT or UPDATE statement will not create a collision with future automatically generated values.

The next example shows some queries using AUTO_INCREMENT fields on reference tables.

CREATE REFERENCE TABLE ct_ref_1(id INT AUTO_INCREMENT PRIMARY KEY);
INSERT INTO ct_ref_1 VALUES();
INSERT INTO ct_ref_1 VALUES(5);
INSERT INTO ct_ref_1 VALUES();

SELECT id FROM ct_ref_1 ORDER BY id;

+----+
| id |
+----+
|  1 |
|  5 |
|  6 |
+----+

UPDATE ct_ref_1 SET id = 9 WHERE id = 5;
INSERT INTO ct_ref_1();

SELECT id FROM ct_ref_1 ORDER BY id;

+----+
| id |
+----+
|  1 |
|  6 |
|  9 |
| 10 |
+----+

DELETE FROM ct_ref_1;
INSERT INTO ct_ref_1 VALUES();

SELECT id FROM ct_ref_1 ORDER BY id;

+----+
| id |
+----+
| 11 |
+----+

Setting AUTO_INCREMENT Starting Values

It is possible to override the starting AUTO_INCREMENT value for reference tables by setting the AUTO_INCREMENT option on a CREATE TABLE statement. The starting value cannot be set to 0.

The following example shows how to set the AUTO_INCREMENT start value during table creation:

CREATE REFERENCE TABLE ct_ref_2 (id int AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT = 7;
INSERT INTO ct_ref_2 VALUES (), ();

SELECT * FROM ct_ref_2;

+----+
| id |
+----+
|  7 |
|  8 |
+----+

This syntax has no effect on sharded tables. It will not return an error, for compatibility with external tools, but it will explicitly present a warning and no operation will be done.

AUTO_INCREMENT During Replication

When replicating data between clusters, the secondary cluster has all the replicated AUTO_INCREMENT values from the primary cluster. When you failover to a secondary cluster, SingleStore synchronizes the secondary cluster by looking for the maximum value in the range of AUTO_INCREMENT values on every aggregator.

CREATE TABLE IF NOT EXISTS my_MemSQL_table (id INT PRIMARY KEY AUTO_INCREMENT, v VARCHAR(10) NOT NULL);

AUTO_INCREMENT Data Type Requirements in Client Applications

When reading AUTO_INCREMENT values into client applications, ensure that the application-side data type can store the entire range of values generated by an AUTO_INCREMENT column. For example, in sharded tables, AUTO_INCREMENT can only be used on a BIGINT column and generated identifiers typically use the full 64-bit range. If an AUTO_INCREMENT column in a sharded table is declared with a smaller integer type, it is implicitly converted to BIGINT. Values that are truncated or rounded when read into a smaller or floating-point type may fail to correctly match the originally generated AUTO_INCREMENT value and can consequently lead to the following:

• duplicate key errors when they are written back to the database, or

• client-side overflow errors or other loss of information in the client application.

If your application uses ORMs, frameworks, or runtime libraries that abstract underlying SQL types, verify that the mapped type used for AUTO_INCREMENT identifiers can hold the generated values.

The following table specifies the recommended type for common programming languages:

If the application code or framework does not support 64-bit identifiers or if smaller identifier types are preferred, use the following:

• Generate identifiers in the application logic: Generate primary key values within the application using a scheme compatible with the application’s preferred identifier type. In this approach, AUTO_INCREMENT is not used for the primary key, and the application is responsible for ensuring uniqueness, handling concurrency, and preventing identifier conflicts.

SingleStore recommends using 64-bit integer types for AUTO_INCREMENT columns and in client applications to avoid precision loss, identifier range conflicts, and duplicate key errors.

AUTO_INCREMENT Best Practices

AGGREGATOR SYNC AUTO_INCREMENT [ON <database>[.<table>]] ALL;