MySQL Bugs: #24478: DROP TRIGGER is not caught by replicate-*-table filters

Bug #24478	DROP TRIGGER is not caught by replicate-*-table filters
Submitted:	21 Nov 2006 16:11	Modified:	9 Mar 2007 15:06
Reporter:	Shawn Green	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Replication	Severity:	S3 (Non-critical)
Version:	5.0.21,5.0.28	OS:	Any (any)
Assigned to:	Alexander Barkov	CPU Architecture:	Any
Tags:	bfsm_2006_12_07, Drop, filter, replication, trigger

Description:
DROP TRIGGER statements are not filtered out on the slave by replication-wild-do-table settings.

How to repeat:
Setup replication between two servers, A and B. 

On server A:
binlog-ignore-db=test2

DROP DATABASE IF EXISTS test1,test2;
CREATE DATABASE test1;
USE test1;
CREATE TABLE aaa(iCol int);

CREATE DATABASE test2;
USE test2;
CREATE TABLE bbb(iCol int);

DELIMITER $$
CREATE TRIGGER trig_db2 BEFORE INSERT 
ON bbb FOR EACH ROW BEGIN
  iCol = ABS(NEW.iCol);
END;
$$

DELIMITER ;

Setup server B to be the slave of A. Only setup database`test1` on server B as `test2` is not to be replicated. Also on server B, use the replication filter option:

replicate-wild-do-table=test1.%

Initiate replication. Then on SERVER A issue the command from within the test2 database:

DROP TRIGGER trig_db2;

Suggested fix:
The replicate-*-table statement filter logic needs to be enhanced so that it recognizes that triggers as table-specific entities. DROP TRIGGER (and all other trigger related DML statements) should be filtered according to the same rules.

Thank you for a problem report. Currently triggers are schema (database) level objects (http://dev.mysql.com/doc/refman/5.0/en/using-triggers.html):

"Trigger names exist in the schema namespace, meaning that all triggers must have unique names within a schema. Triggers in different schemas can have the same name."

So, please, describe the results you expect in this case, as it looks like a feature request for me.

Trigers may be stored at the schema level but they are intrinsically table-dependent objects. If a table is dropped then any triggers defined for that table should also be dropped. If a table is renamed, then all of the triggers should be pointed to the new table name. If we do not allow users to create triggers for tables that do not exist then we also shouldn't allow users to change the tables pointed to by triggers so that the triggers become invalid.

Likewise, if a slave system receives a command to act on a trigger that belongs to a table that is not being processed, then the command should also be ignored.

The decision of where to physically group triggers (at the schema level) should not affect the logical operations of the replication filters as the logical parent to a trigger is the table for which it is defined.

I expect that if there is a replicate-ignore-table filter defined for tableZ then that should cause any DDL statements for triggers defined on tableZ to also be ignored. By using replicate-do-table or replicate-wild-do-table filters, you create an implicit "ignore" condition for all other tables. This ignore should also include all trigger DDL.

The suggested fix is to check the table-level replication filters for a trigger's parent table before executing any DDL for that trigger just as you are for INSERT, UPDATE, and DELETE statements. Now, I recognize that DROP TRIGGER may require a second check to determine the parent table of the trigger being dropped. Not performing that check means that our current replication filters are broken.

Either way, the replication behavior one expects (table-level filters will block/allow all changes to table-specific objects or data) is not happening. I do not see this as a new feature but as correcting an incomplete implementation (bug).

--Shawn

A revised test case with verification:

SETUP:
Two servers, A and B, replicating A->B

Server A: no replication filters

Server B: replicate-wild-do-table:test1.%;

On Server A, run this script:
DROP DATABASE IF EXISTS test1;
DROP DATABASE IF EXISTS test2;

CREATE DATABASE test1;
USE test1;
CREATE TABLE aaa(iCol int);

CREATE DATABASE test2;
USE test2;
CREATE TABLE bbb(iCol int);

DELIMITER $$
CREATE TRIGGER trig_db2 BEFORE INSERT 
ON bbb FOR EACH ROW BEGIN
  SET NEW.iCol = ABS(NEW.iCol);
END;
$$

DELIMITER ;

DROP TRIGGER trig_db2;

::Check status on Server B::

Database test1 exists and contains the table aaa
Database test2 does not exist. 

In the MySQL error log:
##### date-stamp [ERROR] Slave: Error 'Trigger does not exist' on query. Default datbase: 'test2'. Query: 'drop trigger trig_db2', Error_code: 1360

The replicate-wild-do-table filter on Sever B was able to filter out the CREATE DATABASE, CREATE TABLE, and CREATE TRIGGER statements but did not filter out the DROP TRIGGER statement.

Tested with the slave running: 5.0.28-enterprise-gpl-nt-log MySQL Enterprise Server (GPL)

I think one possible solution to this problem might be to finally implement DROP TRIGGER IF EXISTS.

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/17937

Patch looks fine. Explanations and solution are good.

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/20560

Pushed into 5.0.38
Pushed into 5.1.17

pushed to 5.0.38,5.1.17-beta

A note has been added to the 5.0.38 and 5.1.17 changelogs.