Bug #23553 SBR needs to recognize appropriate cross-database DML
Submitted: 23 Oct 2006 14:32 Modified: 31 Oct 2006 15:16
Reporter: Shawn Green Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Replication Severity:S4 (Feature request)
Version:5.0.26 OS:Any (Any)
Assigned to: Assigned Account CPU Architecture:Any
Tags: bfsm_2006_11_02, cross, database, delete, insert, replication, sbr, UPDATE
Triage: Triaged: D5 (Feature request)

[23 Oct 2006 14:32] Shawn Green
Description:
The INSERT, UPDATE, and DELETE statements (DML) that only rely on data from replicated databases are not being replicated if they originate from a non-replicated database. 

Statement-based replication (SBR) would obviously break if you attempted to replicate statements that rely on information from a non-replicated databatase. However, SBR is not detecting and replicating even those statements that are independent from any data from a non-replicated database.

I recognize this is a well-documented, physical shortcoming of SBR when you implement either "replicate-do..." or "replicate-ignore..." settings to control the volume of your replication traffic.  However the request is to enable the replication engine to sort out what is and is not replicable based on the entire statement, not just based on the context from which it is executed.

How to repeat:
Setup two databases: db1 and db2

In the config file:
[mysqld]
replicate-do-db=db1

In the CLI:
mysql> use db2; UPDATE db1.t1 ...  WHERE id1= ...

Suggested fix:
Decide if a statement is replicable not based solely on its database of origin but on the list of databases actually used by the statement.