# AGGREGATOR SYNC AUTO\_INCREMENT

The `AGGREGATOR SYNC AUTO_INCREMENT` command sets the auto-increment counter on the aggregators (master or child).

## 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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/commit.md) for more information.
* Refer to the [Permissions Matrix](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/permissions-matrix.md) for the required permissions.

## 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;

```

```output

+----+
| 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;

```

```output

+----+
| 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;

```

```output

+----+
| 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;

```

```output

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

***

Modified at: June 11, 2026

Source: [/db/v9.1/reference/sql-reference/data-manipulation-language-dml/aggregator-sync-auto-increment/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/data-manipulation-language-dml/aggregator-sync-auto-increment/)

(An index of the documentation is available at /llms.txt)