Maintaining AUTO_INCREMENT on sharded tables

It appears that you cannot set the starting AUTO_INCREMENT value on sharded tables. However, many changes require recreating a table, which resets the AUTO_INCREMENT value. Is there any way to maintain the value when recreating a table? I tried performing a CREATE TABLE AS SELECT from the original table and it still reset the AUTO_INCREMENT value. If not, then I don’t think it’s a very good option to use.

The way to do this is, after recreating the table and reloading the data, run the command AGGREGATOR SYNC AUTO_INCREMENT on each aggregator. This will update the auto_inc counter based on the data currently in the tables, so that newly generated values will start from the highest value from the relevant aggregator + 1.

We have a ticket open to add this to the documentation btw, I just checked on it and hopefully it will be added soon.

1 Like