List:Replication« Previous MessageNext Message »
From:Jesse Date:October 1 2008 9:46pm
Subject:Re: Replication Stopping (foreign key issue)
View as plain text  
> 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/

Maybe one determining factor here is that I am using a function to add the 
school record. The reason is that I need to the ID that was created when the 
school record was added, so I have an AddSchool() function.that adds the 
school record, then returns the LAST_INSERT_ID();  The term "deterministic" 
is always confusing to me.  From what I can read, a function can be 
considered "deterministic" if it always returns the same value for the same 
input. Well, if I AddSchool('ABC'), then do an AddSchool('ABC') again, it 
will not produce the same output.  The LAST_INSERT_ID() will be whatever ID 
is next assigned.  So, the function should be considered Not deterministic, 
I guess, right?  That could be my problem.  I've noticed that I've got the 
function set to Deterministic.  From another perspective, however, you could 
say that it always returns LAST_INSERT_ID(), which is a variable, but 
none-the less, that doesn't change...


> 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.

I'm not using a temporary file, so I know that's not the issue.

I'm also using different versions.  My slave is version 
5.0.67-community-nt-log.  My master is version 5.0.17-nt-log.  USUALLY, 
versions are backwards compatible, so I would assume I'm OK in this 
instance, but maybe not?  I hesitate to upgrade, because of possible issues. 
This is a production server that stays pretty busy too, and finding a time 
to be down for a bit may be difficult.

Jesse

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