replication breaks with garbage column name not found
Using MySQL 5.0.18 with replication:
I set up my first trigger. It inserts a new row into a table when a row in another table is updated. On the master the trigger works fine, but when the update query is replicated, replication breaks, intermittently, with odd errors. The error usually says it can't find a column - but then it gives a column name that is either a made up name "like column X. " or sometimes a name from a completely unrelated table that is not involved in either the update query that starts the trigger, or the insert query that gets triggered. Sometimes the column name will have garbage characters in it. But then sometimes it will work fine.
I'm sure the problem is not with replicating the update query - because update queries like this are running all the time and replication only started breaking when I put in the trigger.
If it helps, here's the trigger:
CREATE TRIGGER MailListStudent_Update AFTER UPDATE ON MailListStudent
FOR EACH ROW
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE ClassID VARCHAR(20);
DECLARE cur1 CURSOR FOR SELECT CM.ClassID
FROM tblClassMembers CM
JOIN Class C USING (ClassID)
JOIN ClassDefinitions CD USING (ClassDefinitionID)
WHERE CM.StudentID = NEW.StudentID
AND CD.LocationTypeCode = 'O'
AND C.Status <> 2
AND C.IsOpen = 1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
IF OLD.Email <> NEW.Email THEN
OPEN cur1;
curLoop:LOOP
FETCH cur1 INTO ClassID;
IF done THEN
LEAVE curLoop;
END IF;
INSERT INTO tblSyncActionLog (`TimeStamp`,`Table`,ActionType,ScriptName,PrimaryKeys,DataString,NeedsApproval,DoStaging,DoLive)
VALUES (NOW(),'MailListStudent','Update','sync-UpdateClassMailList.pl',CONCAT('ClassID|',ClassID),CONCAT('Email|',NEW.Email,'|Email-old|',OLD.Email),0,0,1);
END LOOP;
CLOSE cur1;
END IF;
END;//
Lastly - I just want to make sure I'm understanding how this whole process works:
1. when I create the trigger on the master, it gets replicated to the slave. now there is a trigger on both machines
2. the update query runs on the master and it triggers an insert statement on the master
3. the update query gets written to the binary log. But the insert query does not.
4. the update query (and only the update query) gets replicated to the slave
5. when the update query gets executed on the slave it starts the trigger on the slave, which runs the insert query on the slave.
is that how the process works?