AGGREGATOR SYNC AUTO_
On this page
Sets the auto-increment counter on the aggregators (master or child).
AGGREGATOR SYNC AUTO_INCREMENT [ON <database>[.<table>]] [ALL]
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.
ALLwill run the sync on all aggregators, instead of just the current aggregator.
This command causes implicit commits.
See COMMIT for more information.
Refer to the Permission Matrix for the required permission.
When a new table is created with an auto_
In order to get the auto_
AGGREGATOR SYNC AUTO_ on each aggregator after loading the data.
Create a table and insert an explicit value in the field with
AUTO_ enabled, to simulate previously generated data.
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
INSERT INTO roll VALUES(null);SELECT * FROM roll;
+----+ | id | +----+ | 1 | | 5 | +----+
The value generated by the
AUTO_ 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.
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.
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.
Last modified: January 9, 2023