Bug #56351 sql_safe_updates=1 on slave
Submitted: 29 Aug 2010 21:15 Modified: 10 Jan 2013 12:42
Reporter: Serge Kozlov Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: Row Based Replication ( RBR ) Severity:S3 (Non-critical)
Version:5.1+ OS:Any
Assigned to: Alfranio Junior CPU Architecture:Any
Tags: replication, sql_safe_updates

[29 Aug 2010 21:15] Serge Kozlov
Description:
There is a replication master->slave where slave has enabled system variable SQL_SAFE_UPDATES. It means that UPDATE/DELETE statements on the slave should be aborted if WHERE clause is empty.

But the current state is following:

1. For Row-Based replication SQL_SAFE_UPDATES ignored.

2. For Mixed replication SQL_SAFE_UPDATES used by SQL thread except statements with functions when replication temporary switched to RBR (e.g. UUID(), VERSION(), etc).

The issues of current state:

1. For RBR at least should be documented behavior of slave with enabled SQL_SAFE_UPDATES.

2. For mixed replication enabled SQL_SAFE_UPDATES can be ignored for statements with special function like UUID(), VERSION(), etc. Probably each update/delete event in RBR should be marked as unsafe if SQL_SAFE_UPDATES = 1.

How to repeat:
Set safe mode in slave:

SET GLOBAL SQL_SAFE_UPDATES=1;

Use dump below on master:

CREATE TABLE t1 (a INT, b CHAR(50));
INSERT INTO t1 (a) VALUES (1), (2), (3);
UPDATE t1 SET a = 5, b = UUID();
UPDATE t1 SET a = 4;
[10 Jan 2013 12:42] Erlend Dahl
Internal analysis from Luís Soares:

In RBR, there is already an implicit WHERE clause for each row.

When the slave handles the Delete_rows or Update_rows event, it iterates
over the set of rows, in the event, that are to be deleted or updated.
This procedure consists on finding the row on the slave's storage
engine (SE) and then deleting it (removing it from the SE) or updating
it (replaying the changes).

Every delete/update event has a before image (BI) of the row that is to be
deleted/updated. The BI is used to find the exact row on the slave, thus
it works as an implicit WHERE (eg, find the row on the slave's SE
WHERE se.row=event.bi). Should no row in the SE match the row in the event,
replication will stop.

As such, I don't think that we should mark statements unsafe for RBR or
MIXED because in RBR the consistency check is even more tight. Also, seems
that this option is mostly targeting end users (and not replication threads,
notice its alias: --i-am-a-dummy), and it aims at protecting those that
issue statements without a proper WHERE clause: eg:

  "DELETE FROM t1" <= oopss the user later found out that he wanted to add
                      WHERE clause, but he didn't

Hence closing as "not a bug".