Hi, I'm designing a master-to-master replication architecture. I wonder what the best way is to make sure both databases generate unique row ID's, so there won't be ID conflicts when replicating both directions.
I read on forums about pro's and con's using UUID's, also about setting the *auto-increment-increment *and *auto-increment-offset* system variables. I'm not a fan of UUID's for this purpose. They are two big, slow to generate, and they don't 100% guarantee global uniqueness anyway. On the other hand, the *auto-increment-offset *trick limits the number of possible databases in the system in advance, which is not so nice. So, I'm thinking about a composite unique ID system, where the value of the *server-id *system variable is always a part of the ID. The server-id has to be set uniquely anyway for any replication architecture in MySQL. A sample would look like this: CREATE TABLE SampleParents ( ID INT UNSIGNED NOT NULL AUTO_INCREMENT, SID SMALLINT UNSIGNED NOT NULL, SampleData VARCHAR(50) NOT NULL, PRIMARY KEY (ID, SID)) ENGINE=InnoDB; CREATE TABLE SampleChildren ( ID INT UNSIGNED NOT NULL AUTO_INCREMENT, SID SMALLINT UNSIGNED NOT NULL, SampleParentID INT UNSIGNED NOT NULL, SampleParentSID SMALLINT UNSIGNED NOT NULL, SampleData VARCHAR(50) NOT NULL, PRIMARY KEY (ID, SID), KEY (SampleParentID, SampleParentSID), CONSTRAINT FOREIGN KEY (SampleParentID, SampleParentSID) REFERENCES SampleParents (ID, SID)) ENGINE=InnoDB; Where SID is always filled with the value of the @@server_id global variable according to the server where the record is created. How I see it is that it has the following advantages. - The ID+SID pair guarantees pripary key uniqueness within the replication array. - Auto_increment is a magnitude faster than generating UUID's. - SID adds only 2 bytes in this case to the size of the primary key item. It can be even 1 byte if I'm sure I'll never exceed maximum 255 servers. But anyhow, it is still way smaller than the 16 byte of a UUID field, even if using BIGINT's. - Keeps the efficiency of the primary key indexing, because the key is still very close to a strictly incremental value. Well, you can see what the obvious disadvantage is: - Primary and foreign keys are always double fields. This is not so convinient when you are joining tables and add the WHERE clauses to your queries. It might even negatively affect the evaluation speed of join conditions, although I have no idea yet, how much. (My gut feeling is that it's not a big issue, due to the good query optimizer of MySQL.) My question is. Does anyone have any better idea, how to approach this problem? Thanks,
