Bug #33255 Trigger using views and view ddl : corrupted triggers
Submitted: 14 Dec 2007 23:54 Modified: 20 Jun 2012 16:38
Reporter: Marc Alff Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S2 (Serious)
Version:5.0.56/5.1BK OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D2 (Serious) / R4 (High) / E4 (High)

[14 Dec 2007 23:54] Marc Alff
Description:
A table has a trigger
the trigger uses a view
After DDL on the view, the trigger is still cached and executed with the old view definition.

See related Bug #33000 (Triggers do not detect changes in meta-data.)

How to repeat:
--disable_warnings
drop table if exists t7_view;
drop table if exists t7_audit;
drop view if exists audit_view;
--enable_warnings

create table t7_view(a int);

create table t7_audit(old_a int, new_a int, reason varchar(50));

create view audit_view as select "view v1" as reason from dual;

create trigger t7_view_bi before insert on t7_view for each row
  insert into t7_audit values (NULL, NEW.a, (select reason from audit_view));

insert into t7_view(a) value (301);
insert into t7_view(a) value (302);

drop view audit_view;

create view audit_view as select "view v2" as reason from dual;

insert into t7_view(a) value (303);
insert into t7_view(a) value (304);

# Expecting:
# - NULL, 301, view v1
# - NULL, 302, view v1
# - NULL, 303, view v2
# - NULL, 304, view v2

# Actual:
# - NULL, 301, view v1
# - NULL, 302, view v1
# - NULL, 303, view v1 <-- bug
# - NULL, 304, view v1 <-- bug
select * from t7_audit order by new_a;

FLUSH TABLES;

insert into t7_view(a) value (305);
insert into t7_view(a) value (306);

# Now the trigger uses view v2,
# because of the flush
select * from t7_audit order by new_a;

drop table t7_view;
drop table t7_audit;
drop view audit_view;

Suggested fix:
Implement object dependency tracking and validation.
[16 Dec 2007 2:44] Miguel Solorzano
Thank you for the bug report. Verified as described.
[20 Jun 2012 16:38] Paul Dubois
Noted in 5.6.6 changelog.

Failure of triggers to notice metadata changes in objects accessed
within the program could cause trigger malfunction.