List:Replication« Previous MessageNext Message »
From:Marcus Bointon Date:November 2 2011 9:35pm
Subject:Re: sql_log_bin
View as plain text  
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 = 1, field1 = 'foo';
INSERT INTO mytable SET id = 2, field1 = 'bar';
DELETE FROM mytable WHERE id = 2; (delete the one we just made)
INSERT INTO mytable SET id = 2, field1 = '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 = 1, field1 = 'foo';
INSERT INTO mytable SET id = 2, field1 = 'bar';
INSERT INTO mytable SET id = 2, field1 = '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
-- 
Marcus Bointon
Synchromedia Limited: Creators of http://www.smartmessages.net/
UK info@hand CRM solutions
marcus@stripped | http://www.synchromedia.co.uk/



Thread
sql_log_binDiego Martínez2 Nov
  • Re: sql_log_binOleg Tsarev2 Nov
  • Re: sql_log_binJohan De Meersman2 Nov
    • Re: sql_log_binMarcus Bointon2 Nov
    • Re: sql_log_binMySQL)2 Nov
  • Re: sql_log_binRick James2 Nov
    • Re: sql_log_binMarcus Bointon2 Nov
      • Re: sql_log_binRicardo2 Nov
        • Re: sql_log_binMarcus Bointon2 Nov
          • Re: sql_log_binRicardo2 Nov
            • Re: sql_log_binDiego Martínez3 Nov