Globally distinct auto increment keys in server

[zachmu]

Right now, auto increment key values are tracked on a per-branch basis in a server. So two clients issuing INSERT statements on the same branch will not get overlapping values, but two clients on different branches will.

This needs to be configurable so that the values are tracked globally across all branches, so that no two clients are ever given the same auto increment value for a column regardless of which branch they are connected to.

[pbowyer]

@zachmu I'm confused because I thought from https://www.reddit.com/r/programming/comments/rj9au6/comment/hp36uiw/ that it was now a global branch independent counter?

The single server limitation is a problem and what prompted today's Googling to see if there's an easy migration path to Dolt with an existing application using numeric auto_increment primary keys.

[zachmu]

Hi @pbowyer,

I'm not sure what I was talking about in that comment, I must have been confused. I just verified that this feature request is not implemented yet. Auto increment keys are still handed out on a per-branch basis in a running server.

This is pretty easy to change, we just haven't prioritized the product work to make this configurable. But this would for sure be limited to the scope of a single server: 2 clones of a database running on separate hosts would not know anything about each other's key spaces and would hand out the same keys. Can you expand on your comment about this limitation being a problem for your use case? There are some viable workarounds we could suggest.

[timsehn]

Could you change the keys to a randomly generated GUID? That model works better for Dolt than auto increment.

[pbowyer]

Hi @zachmu

This is pretty easy to change, we just haven't prioritized the product work to make this configurable. But this would for sure be limited to the scope of a single server: 2 clones of a database running on separate hosts would not know anything about each other's key spaces and would hand out the same keys. Can you expand on your comment about this limitation being a problem for your use case? There are some viable workarounds we could suggest.

So my aim is to take an existing content management system and try running it on Dolt (which is why GUID is not an easy option @timsehn - though I have sketched out an additional ID to GUID mapping schema that could be applied on every merge...)

Global auto increment keys (per-table) on a server would be great for branching in production e.g. I branch to add a new website section, meanwhile blog posts are added to the live site, and so when we merge back we don't have two pages with the same ID.

In the multi server case the scenario I have in mind is development/staging/production environments, each with their own copy of the DB, but sometimes wanting to promote data between them (and pull back the latest changes).

10+ years ago I remember using auto_increment_increment on MySQL; I forget the finer details but for my example above with a known number of servers this would work, so one generates IDs 1, 4, 7.., the next 2, 5, 8... the third 3, 6, 9... Hacky but a work-around.

I'd love to hear your suggestions for workarounds.

[bpf120]

Hi @pbowyer , thanks again for filing and your additional comments. It might be easier to have the conversation over a quick video call or on our Discord..

Shoot me an email if you want to coordinate something.

[zachmu]

I am not positive that we implement auto_increment_increment, but if not it's an easy addition

The other workaround I would suggest is just seeding the key space for every table with large numbers a few billion apart, which would keep the different servers in segregated key spaces. This only works if merges only go one direction, as soon as you merge those key spaces together on any server you will need to repeat the process with a new large number for each server.

[zachmu]

We've come around to the idea that Dolt auto_increment keys should be global on a database, and this is now the behavior on main. Merged in this PR:

https://github.com/dolthub/dolt/pull/4071

If you want to try it out now you can build from main. Otherwise this will get into a release in a few days and we'll cut a release then.

[timsehn]

Released!