List:Replication« Previous MessageNext Message »
From:Josh Miller Date:July 22 2008 6:58pm
Subject:Re: Disable SQL safe-update mode on a replication slave in version
5.0.27
View as plain text  
Josh Miller wrote:
> I have recently come across an issue while running a stored procedure 
> from a replication master where the slave has erred out with the 
> following message:
> 
> Last_Error: Error 'You are using safe update mode and you tried to 
> update a table without a WHERE that uses a KEY column' on query. Default 
> database: ...
> 
> Is there a way to disable this safe-update mode on the slave?  I have 
> commented out safe-updates in the my.cnf on both the master and slave 
> (with a subsequent restart) but this is not working for replication.
> 
> (Btw, the master is version 5.0.24a, the slave is 5.0.27.)

Now that I fully understand the error and safe updates in MySQL, I have 
fixed the real problem, which was to update the stored procedure with 
delete statements that reflect all columns in the row to delete.

Original statement:

   delete from <table1> where id not in (
     select id from <table2>);

Modified, working statement:

   delete r.* from table1 r
     left join table2 a
       on r.id = a.id
     where a.id is NULL ;


Thanks,
-- 
Josh Miller, RHCE/VCP
Seattle, WA
Linux Solutions Provider
http://itsecureadmin.com/
Thread
Disable SQL safe-update mode on a replication slave in version 5.0.27Josh Miller22 Jul
  • Re: Disable SQL safe-update mode on a replication slave in version5.0.27Josh Miller22 Jul