Hi,

As part of a system I'm designing we have a requirement to be able to insert rows on multiple servers and later merge them without id translation (effectively multi-master replication). We're using MySQL and NHibernate.

The options boil down to some equally unpleasant ones -
- a dual part key with an identity column and a location column, which as I understand NHibernate won't support and InnoDB doesn't support either

- use MySQL auto_increment_offset and treat as a normal identity column in NHibernate. This has the advantage of putting the onus on the database to make sure things work, but is fragile if the config is set up wrongly

- stored procedures to generate ids - this seems like reinventing the wheel for some reason

I can't find any definitive advice on this, but it must be a solved problem. Does anyone have any advice?

Thanks!

Steve

Is there a reason you don't want to use a UUID (supported by NHibernate)? It seems like it would address your situation.

I believe the two main reasons we were against it were bad performance under MySQL and because MySQL replication doesn't deal with UUIDs properly, but we might have to take another look at them.