Case: there are multiple tables all with a primary, auto increment id. This primary id/key is a foreign key within the multiple tables to relate to each other. This works great when there is no replication or just master - slave replication.
But what happens when you want a master - master replication? When inserting a new record, it doesn't get for example ID of 1, but 1 on master 1, 2 on master 2 because of the offset.
If that is the case, how can one ever do relational tables/data (like MySQL is intended for) on a master master then and why is offset required? master - master should be (in my opinion) a mirror of each other.
If a customer has an ID of 2 and I query from master 1, I would get wrong data because in master 1 the customer id is 1?!
Only logical solution for me would be to create an extra column, like "customer_id". And what if you need to update something, you would need to know the ID in master 1 and the other ID in master 2 in the where clause.
Can someone please shed some light on how this works.
I think what you are missing is that the replicated insert does not use auto increment, it has the id from the master where the original insert occurred.
Note that your proposed customer_id would have the same problem.