"dual master replication" and AUTO_INCREMENT column

[clarissa1996]

Hi all

I have configured a "dual master replication" with 2 host.
I have read the book "High Performance MySQL" Chapter 7.
It is me clear (I hope) the problem with the AUTO_INCREMENT.
I have understand (I hope) the possible (two) solutions to this problem.

I want to implement the following (third) solution:
on the master I use only odd values, and on the slave I use only even values for the AUTO_INCREMENT column.

Here my two questions:

1) Is this solution correct (is a good solution) ?

2) How can I *easily* change the following code (to implement my solution) ?
INSERT INTO Reservations VALUES(NULL, "blabla");

Thanks al lot for any help. Michelangelo

 

[ericbrunson]

1) I think it's a horrible solution. The autoincrement problem is indicative of why you should avoid the feature, it's non-standard SQL an poorly implemented. I don't know the intricacies of "dual master replication", I only to single master, myself. But, I use a table "sequence" that has a single field and my DB access objects handle selecting and incrementing from that table to generate unique ids.

2) Can't.

 

[clarissa1996]

Hallo Eric,

thanks a lot for your quick answer.

Can you explain me why is this a horrible solution ?
In the chapter "Safe Multi-Master Replication" the suggested solutions are:

- Multipart auto-increment unique keys
- Partitioned auto-increment fields

See

http://netmirror.org/mirror/mysql.com/books/hpmysql-excerpts/ch07.html

I think (but not sure) that my solution is very similar to the second solution (Partitioned auto-increment fields).
Note: my configuration is a two-master and not a multi-master.

Note: I am new with replication ... I only want to be sure to implement a good solution before changing a lot of code.

Best Regards. Michelangelo