List:Replication« Previous MessageNext Message »
From:ewen fortune Date:October 1 2008 9:13am
Subject:Re: Replication Stopping (foreign key issue)
View as plain text  
Hi,

There are a number of ways statement based replication can fail, I
blogged about one such problem here
http://www.mysqlperformanceblog.com/2008/09/29/why-audit-logging-with-triggers-in-mysql-is-bad-for-replication/
But perhaps a more common scenario is for a statement to be
non-deterministic and produce a different result on a different
machine (ie the slave). Baron (the author of maatkit) blogged about
that here
http://www.xaprb.com/blog/2007/11/08/how-mysql-replication-got-out-of-sync/

There is also another scenario that I have seen, which is where a
statement makes use of a temporary table and the tmp table gets
dropped on the slave due to maintenance (or whatever) and causes
replication to fail.


Cheers,

Ewen




On Wed, Oct 1, 2008 at 4:14 AM, Jesse <jc@stripped> wrote:
>> I've been looking at maatkit, but I find that the documentation is  very
>> hard to fathom - lots of specification with no real examples or scenarios,
>> like the all too common foreign key clash. I find I have no choice but to do
>> a complete rebuild of the DB whenever I get one, but unfortunately it takes
>> 29 hours to import the database from scratch, during which time I have no
>> replicated backup... I fail to see how a slave could end up with content not
>> on the master, and thus cause the clash - if there are no writes to the
>> slave and the replication is  only of committed transactions from the
>> master, where can the clash  possibly come from? I can understand that some
>> kind of deadlock  occurring on the slave could result in data being missing
>> from the  slave (though again, I find that unlikely - with a single thread
>> doing  the updating, what is there to deadlock with?), but that would never
>>  cause a key clash - only additional data not on the master could cause
>>  that.
>
> EXACTLY!  In my particular case, there is data on the master that isn't on
> the slave, and that's what caused the foreign key issue.  It tried to add
> the child record with no parent record present on the slave.  How did that
> happen?  I have no idea!
>
> Jesse
>
> --
> MySQL Replication Mailing List
> For list archives: http://lists.mysql.com/replication
> To unsubscribe:
>  http://lists.mysql.com/replication?unsub=1
>
>
Thread
Replication Stopping (foreign key issue)Jesse30 Sep
  • RE: Replication Stopping (foreign key issue)Rick James30 Sep
  • Re: Replication Stopping (foreign key issue)Simon J Mudd30 Sep
    • Re: Replication Stopping (foreign key issue)Marcus Bointon30 Sep
  • Re: Replication Stopping (foreign key issue)Jesse1 Oct
    • Re: Replication Stopping (foreign key issue)ewen fortune1 Oct
  • Re: Replication Stopping (foreign key issue)Jesse1 Oct
  • Re: Replication Stopping (foreign key issue)Jesse2 Oct
    • Re: Replication Stopping (foreign key issue)Eber Duarte2 Oct