From: Marcus Bointon
Date: November 2 2011 9:35pm
Subject: Re: sql_log_bin
List-Archive: http://lists.mysql.com/replication/2283
Message-Id: <157C406B-84E0-488A-8DF4-852805CA9E83@synchromedia.co.uk>
MIME-Version: 1.0 (Apple Message framework v1251.1)
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: quoted-printable

On 2 Nov 2011, at 22:04, Ricardo wrote:

> For instance run a cron every night to just replicate inserts, not =
deletes - of course this would not be a real-time slaving

As said before, simply doing this won't always work. Take this sequence:

INSERT INTO mytable SET id =3D 1, field1 =3D 'foo';
INSERT INTO mytable SET id =3D 2, field1 =3D 'bar';
DELETE FROM mytable WHERE id =3D 2; (delete the one we just made)
INSERT INTO mytable SET id =3D 2, field1 =3D 'foobar'; (insert another =
one to replace it using the same ID)

That's fine with normal replication, but if you skip deletes you get =
this:

INSERT INTO mytable SET id =3D 1, field1 =3D 'foo';
INSERT INTO mytable SET id =3D 2, field1 =3D 'bar';
INSERT INTO mytable SET id =3D 2, field1 =3D 'foobar';

Which just won't work. This is just a simple (and somewhat obtuse) =
example, but it's not unreasonable and there are a zillion other more =
subtle ways you could do something similar. Anything working around this =
would have to be very clever and complex, and thus probably not worth =
the overhead.

As an alternative solution, how about marking records as deleted, and/or =
moving them to another table (perhaps using a storage engine more suited =
to archiving) rather than actually deleting them. That way you simply =
avoid the problem.

Marcus
--=20
Marcus Bointon
Synchromedia Limited: Creators of http://www.smartmessages.net/
UK info@hand CRM solutions
marcus@stripped | http://www.synchromedia.co.uk/