Geo Carncross wrote:
>>>3. MySQL supports sequences as well- older versions might have an issue,
>>>but those versions won't support replication _either_.
>>
>>This whole discussion started from the assumption that mysql's auto_increment
>>fields may very well collide in a clustered multi-master environment.
>
>
> I wasn't suggesting the use of mysql's auto_increment- but it would
> require transactions. You need transactions (anyway) if you're doing
> replication, so that's not an unreasonable requirement.
If you're not talking about auto_increment, what do you mean by mysql
supporting sequences. That's the only kind I know of in mysql.
> Fortunately, if auto_increment were also UNIQUE I would consider it a
> bug in MySQL if it allowed the UNIQUE constraint to be violated just
> because of replication.
They are unique, and mysql doesn't allow violation. That's what
triggered this whole discussion.
>
> Does MySQL even support native replication. Is this worth it? Pg and
> Oracle support native replication, maybe an Oracle patch would be
> useful :)
Of course mysql supports native replication. Again, that's the whole
idea behind this thread: multi-master replication:
a -> b -> c -> a
where each node is read-write. People are reporting successful setups
using mysql in a multi-master setup. But I'm guessing those are used by
just a few concurrent users.
> The need is unique values that are useful as UID numbers, correct? They
> must conform to RFC2060 which means they have certain requirements.
We need unique row keys across the cluster. If we have those we can
derive the uids. They really don't have to be mapped 1-1. In fact they
can't be.
> The replication that we can support (and still support RFC2060) is one
> of:
> * single-master
> * single-lock
>
> There are no other kinds of replication possible (unless this UID
> requirement could go away).
Wrong if we can avoid primary key collisions.
> {{ if you need to see why, I'm happy to explain- but it's quite
> wordy :) }}
I have a headache (lack of sleep) so have mercy!
> single-master is what openldap does- you have one node that makes
> changes and pushes it out to slaves which are read-only.
Which is what mysql/dbmail support just fine out of the box right now.
> single-lock means that when a resource is taken, the other replicants
> are made aware and not allowed to touch that data. essentially we have a
> serialized step. GFS and Oracle do this. PostgreSQL should be able to do
> this.
>
> now, we actually need FULL synchrony on a mailbox when inserting a
> message because those UID numbers must NEVER EVER jump backwards.
Not if we can make sure that each node in the cluster uses it's own
subset of ids so they will never collide with ids generated by other
nodes. But I think we have established that this will lead us to run out
of ids pretty fast. And reordering for rfc compliance will be a bitch.
> this is why multi-master lockless synchronization is impossible.
>
> If the DB can't provide this- that is- if mysql really can break UNIQUE
> referential integrity (during replication), then we should toss UIDs
> altogether - make the UID==ID and set the UIDVALIDITY to the unique-
> identifier as you suggest (host number+timestamp)
currently UID==message_idnr, UIDVALIDITY==mailbox_idnr simple as that.
Ok. So now I'm beginning to suspect we can't do multi-master replication
safely without breaking the rfc. Even if we get the guid generation
right, we will still have to deal with making sure the message uids are
derived correctly. That will be very difficult. And even then we haven't
dealt with simultaneous updates on the same row on different masters.
For that reason alone, the mysql docs strongly advise a single master
setup.
Perhaps we should fix the dbmail db api to use different db_params for
reads and writes. That way, we can use a single master for writes, and
the master or one of the slaves for reads.
--
________________________________________________________________
Paul Stevens paul at nfg.nl
NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31
The Netherlands________________________________http://www.nfg.nl
