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
what if someone dose not wants to recreate the entire table and wants the auto increment to pick from the maximum available value from the column.
i have a value that keeps on generating values like starting from 1125899907000000 even after syncing aggregators with command AGGREGATOR SYNC AUTO_INCREMENT;
@muhammadosama.hassan for a sharded table, you can’t make the new auto_increment values be less than before but you can make them be greater than before using AGGREGATOR SYNC AUTO_INCREMENT. The docs cover this: AGGREGATOR SYNC AUTO_INCREMENT · SingleStore Documentation
It seems to me that it does what you’re asking for. If I’m misunderstanding you, please give more details.