List:Replication« Previous MessageNext Message »
From:Jesse Date:October 2 2008 12:08pm
Subject:Re: Replication Stopping (foreign key issue)
View as plain text  
OK, here's some new information.  I believe I figured out why replication is 
breaking, but I haven't figured out why I'm not getting an error on the 
master in the first place, and why the slave isn't giving an error either. 
Here's the situation.  I have a field named OverrideAdvisor in a table named 
schools.  That field used to be a BIT(1) field.  However, I recently decided 
to change it to a VARCHAR(1) field, and changed it from 1/0 to Y/N.  I DID 
NOT, however, change my AddSchool() function.  It was still defined as 
follows:

CREATE FUNCTION `AddSchool`(cName VARCHAR(65),cLvl 
VARCHAR(1),bOverrideAdvisor BIT(1),nNextStudentNo INT(11))
    RETURNS int(11)
    DETERMINISTIC
    CONTAINS SQL
    SQL SECURITY DEFINER
    COMMENT ''
BEGIN
   INSERT INTO Schools (`Name`,`Lvl`,`OverrideAdvisor`,`NextStudentNo`) 
VALUES
     (cName,cLvl,bOverrideAdvisor,nNextStudentNo);
   RETURN LAST_INSERT_ID();
END;

When I execute this function, passing "N" instead of 0 to the 
bOverrideAdvisor parameter, it was not giving an error of any kind.  It was 
going through as if nothing was wrong.  The OverrideAdvisor field is set to 
it's default value of "N" on the new record (which probably hardly ever 
changes anyway), and all seemed to be well on the master.  However, while 
the record was added on the master, it was NOT added on the slave.  Nor did 
the slave give an error when it attempted to execute the function.

Why would the master server not give an error of some sort here when I 
passed a character value "N" in the bOverrideAdvisor paramater when it was 
expecting a BIT parameter?

I have changed the function to use the right type, and will attempt to 
re-set replication again in the morning, but I just wanted to pass this by 
everyone to see if you know why no error is given?  I'm running MySQL 
version 5.0.17-nt-log on the master, and version 5.0.67-community-nt-log on 
the slave.

Thanks,
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