MySQL Bugs: #18715: replicate-wild-ignore-table prevents CREATE VIEW from replicating

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

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.

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    |
        +------+

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.

Fixed in 5.0.21, 5.1.10-beta

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.

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