Mario Splivalo wrote:
> On Wed, 2005-10-26 at 12:09 -0400, Jan Wieck wrote:
>
>>You must have missed the FAQ and other side notes about replication in
>>the MySQL manual. Essentially MySQL replication is nothing but a query
>>duplicating system, with the added sugar of taking care of now() and
>>some other non-deterministic things, but not all of them.
>>
>>Non-deterministic user defined procedures, functions and triggers will
>>simply blow MySQL's sophisticated replication apart.
>
> That is just not true. I haven't tried MySQL 4 and above, but in 3.5.x
> replication wos working as expected, and the load that replication posed
> was insignificant. The only TRUE problem was that replication was
> unidirectional. That SAME problem has Slony, and other 'replication
> systems' available for postgres.
I think you're missing the "non-deterministic" bit. Mysql replication is
based on shipping statements AFAICT
http://dev.mysql.com/doc/refman/5.0/en/binary-log.html
"The binary log contains all statements which updated data or
potentially could have updated it (for example, a DELETE which matched
no rows). Statements are stored in the form of “events” that describe
the modifications."
So - if your statement contains something non-deterministic that isn't
catered for in Mysql's code then it will break.
At it's simplest - if I write a function my_random() and then do: UPDATE foo SET a=1 WHERE b < my_random();
IF my_random() returns different results on different machines, then the
replication will be broken. See the manual entry below:
http://dev.mysql.com/doc/refman/5.0/en/replication-features.html
That's not to say the system is worthless - it works fine for many
people. But it does have limitations.
-- Richard Huxton Archonet Ltd