Message-ID: <4360D44B.9020605@archonet.com>
Date: Thu, 27 Oct 2005 14:21:15 +0100
From: Richard Huxton <dev@archonet.com>
User-Agent: Mozilla Thunderbird 1.0.7 (X11/20051013)
MIME-Version: 1.0
To: mario.splivalo@mobart.hr
Cc: pgsql-sql@postgresql.org
Subject: Re: why vacuum
References: <200510261015.17623.lawgon@thenilgiris.com>	
	<1130339980.2872.12.camel@state.g2switchworks.com>	
	<435FAA21.3070400@Yahoo.com> <1130401371.31435.17.camel@ekim>
In-Reply-To: <1130401371.31435.17.camel@ekim>
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: quoted-printable

Mario Splivalo wrote:
> On Wed, 2005-10-26 at 12:09 -0400, Jan Wieck wrote:
>=20
>>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=20
>>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.
>=20
> 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 pose=
d
> 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=20
potentially could have updated it (for example, a DELETE which matched=20
no rows). Statements are stored in the form of =E2=80=9Cevents=E2=80=9D t=
hat describe=20
the modifications."

So - if your statement contains something non-deterministic that isn't=20
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=3D1 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=20
people. But it does have limitations.

--
   Richard Huxton
   Archonet Ltd