AGGREGATOR SYNC AUTO_INCREMENT

Syntax

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

Remarks

This command is used to fix one or more auto_increment counters that are lower than the currently inserted values in a table. Run this command on the aggregator that has a broken counter.
It updates the auto_increment counter by querying the leaves for the next appropriate value. This value is used if it does not decrease the counter.
Specifying ALL will run the sync on all aggregators, instead of just the current aggregator.
This command causes implicit commits. Refer to COMMIT for more information.
Refer to the Permission Matrix for the required permission.

Example AGGREGATOR SYNC AUTO_INCREMENT Scenario

When a new table is created with an auto_increment using existing data (that already has auto_increment values assigned), the new table must generate a new auto_increment counter that does not collide with the existing data. But on sharded tables, the auto_increment counter completely ignores the inserts and does not update the counter.

In order to get the auto_increment values to start from the existing data, run AGGREGATOR SYNC AUTO_INCREMENT on each aggregator after loading the data.

Examples

Create a table and insert an explicit value in the field with AUTO_INCREMENT enabled, to simulate previously generated data.

SQL

CREATE TABLE roll (id BIGINT PRIMARY KEY AUTO_INCREMENT);
INSERT INTO roll VALUES (5);

SELECT * FROM roll;

+----+
| id |
+----+
|  5 |
+----+

Now add a new row to the table, without adding values to the field with AUTO_INCREMENT enabled.

SQL

INSERT INTO roll VALUES(null);

SELECT * FROM roll;

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

The value generated by the AUTO_INCREMENT counter for the new row is 1, because in sharded tables, explicitly inserted values do not affect the automatically generated values.

Now, run the following command independently on each aggregator.

SQL

AGGREGATOR SYNC AUTO_INCREMENT;
INSERT INTO roll VALUES (null);

SELECT * FROM roll;

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

The new row has a value of 6.

You can also make the counter (new automatically generated values) for inserts on the master aggregator start from a higher value. The following example starts the counter from 100.

SQL

INSERT INTO roll VALUES (99);
AGGREGATOR SYNC AUTO_INCREMENT;
DELETE FROM roll WHERE id = 99;
INSERT INTO roll VALUES (null);

SELECT * FROM roll;

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

This approach will only increase the counter if there is data higher than the current counter value; it will not decrease the counter.