Bug #18715 replicate-wild-ignore-table prevents CREATE VIEW from replicating
Submitted: 2 Apr 2006 5:24 Modified: 3 May 2006 9:42
Reporter: Kolbe Kegel Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Replication Severity:S1 (Critical)
Version:5.0.20-bk OS:Linux (Linux)
Assigned to: Andrei Elkin CPU Architecture:Any

[2 Apr 2006 5:24] Kolbe Kegel
Description:
CREATE VIEW statements are not applied to the slave if there is any replicate-wild-ignore-table rule in effect.

How to repeat:
* Set up a simple replication setup
* Apply a replicate-wild-ignore-table rule such as replicate-wild-ignore-table='test.X'
* Execute these statements on the master:

CREATE TABLE t1 (id int);
CREATE VIEW v1 (id int);
SHOW TABLES;

* Execute these statements on the slave:

SHOW TABLES;

* On Master:

DROP VIEW v1;

* On Slave:

SHOW SLAVE STATUS\G

Last_Error: Error 'Unknown table 'test.v1'' on query. Default database: 'test'. Query: 'drop view v1'

Suggested fix:
replicate-wild-ignore-table should of course work for views the same way it works for tables.
[11 Apr 2006 12:33] Lars Thalmann
After this bug is fixed, the following text should (most likely :-) be
added to the general manual:

Replication of views
--------------------
Views are replicated regardless of whether they are updating views or
not (it is not possible to specify this in the CREATE VIEW or ALTER
VIEW statements).
  
Views are filtered by their own name, not by the tables they refer to.
This means that a view can be replicated to the slave even if it
refers to a table that is filtered out by replicate-ignore-table
rules.  So, it does not make sense to use replication filtering for
security reasons.
  
EXAMPLE
  
start slave with --replicate-wild-ignore-table='test.x'

master: CREATE TABLE x (id int);
slave:  CREATE TABLE x (id int);
  
master: CREATE VIEW vx AS SELECT * FROM x;
        INSERT INTO vx VALUES (1);
slave:  SELECT * FROM x;
        +------+
        | id   |
        +------+
        | 1    |
        +------+
  
master: INSERT INTO x VALUES (2);
slave:  SELECT * FROM x;
        +------+
        | id   |
        +------+
        | 1    |
        +------+
[12 Apr 2006 12:07] Andrei Elkin
Kostja made the first review of the patch mailed with subject:
bk commit - 5.0 tree (aelkin:1.2149)
I am sorry the ChangeSet left with wrong prefix `#BUG18715' and was not uploaded to
this page.
[23 Apr 2006 6:40] Andrei Elkin
Fixed in 5.0.21, 5.1.10-beta
[3 May 2006 9:42] MC Brown
Documented in 5.0 and 5.1 changelogs.

Also added a small note to the replication problems section that details the issue and made a note to extend the information as part of the replication chapter update.
[12 May 2006 17:10] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/6315