MySQL Bugs: #33000: Triggers do not detect changes in meta-data.

Bug #33000	Triggers do not detect changes in meta-data.
Submitted:	5 Dec 2007 14:31	Modified:	20 Jun 2012 16:40
Reporter:	Alexander Nozdrin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S3 (Non-critical)
Version:	5.0/5.1, 5.1.24-BK	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
The trigger cache does not detect when meta-data of the underlying objects
is changed. So, if a trigger uses a table and the table was changed
since the trigger was loaded in the cache, the trigger will operate
with the outdated meta-data.

In particular:
  - if a trigger uses a table (not the base table, but another one),
    and a new column is added to the table after the trigger was firstly
    executed, further activations of the trigger will not detect the new
    column.

  - if a trigger uses a table, and a column is dropped after the trigger
    was firstly activated, the trigger will return an error on further
    executions.

This bug is similar to BUG#32868 with regard to triggers.

Triggers and stored routines use different caches, so although the problems
seem the same, different fixes are required.

How to repeat:
A new column is added after the first execution:

-- (re)start the server (from scratch)

> CREATE TABLE t1(c INT); -- base table for a trigger

> CREATE TABLE t2(a INT, b INT); -- table, which will be used in the trigger;
> INSERT INTO t2 VALUES (1, 2);

> CREATE TRIGGER t1_ai AFTER INSERT ON t1
    FOR EACH ROW
      SELECT * FROM t2 INTO @a, @b;

> SELECT @a, @b;
+------+------+
| @a   | @b   |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)

> INSERT INTO t1 VALUES (1); -- activate the trigger
Query OK, 1 row affected (0.00 sec)

> SELECT @a, @b;
+------+------+
| @a   | @b   |
+------+------+
|    1 |    2 |
+------+------+
1 row in set (0.00 sec)

> ALTER TABLE t2 ADD COLUMN c INT;

-- restart the server to flush the trigger cache.

> > SELECT @a, @b;
+------+------+
| @a   | @b   |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)

> INSERT INTO t1 VALUES (2); -- activate the trigger
ERROR 1222 (21000): The used SELECT statements have a different number of columns

> SELECT @a, @b;
+------+------+
| @a   | @b   |
+------+------+
| NULL | NULL |
+------+------+
1 row in set (0.00 sec)

The similar thing is with a column removal.

Thank you for the bug report. Verified as described.

CREATE TABLE t1(c INT); -- base table for a trigger
CREATE TABLE t2(a INT, b INT); 
INSERT INTO t2 VALUES (1, 2);
CREATE TRIGGER t1_ai AFTER INSERT ON t1
FOR EACH ROW
SELECT * FROM t2 INTO @a, @b;
SELECT @a, @b;
INSERT INTO t1 VALUES (1); -- activate the trigger
SELECT @a, @b;
ALTER TABLE t2 ADD COLUMN c INT;
-- restart the server to flush the trigger cache.

SELECT @a, @b;
INSERT INTO t1 VALUES (2); -- activate the trigger
SELECT @a, @b;

The bug is still repeatable with latest 5.1.24-BK.

Another test case (more severe):

create table t1 (a int);
create table t2 (a int unique);
create table t3 (a int unique);
create view v1 as select a from t2;
create trigger t1_ai after insert on t1 for each row
  insert into v1 (a) values (new.a);
insert into t1 (a) values (5);
select * from t2;
select * from t3;

mysql> insert into t1 (a) values (6);
ERROR 1146 (42S02): Table 'test.t2' doesn't exist

(Which is weird to say the least).
drop view v1;
create view v1 as select a from t3;
insert into t1 (a) values (6);

This yields an error. Should insert into t3.

Workaround: issue FLUSH TABLE/FLUSH TABLES

Correct test case:
create table t1 (a int);
create table t2 (a int unique);
create table t3 (a int unique);
create view v1 as select a from t2;
create trigger t1_ai after insert on t1 for each row
  insert into v1 (a) values (new.a);
insert into t1 (a) values (5);
select * from t2;
select * from t3;
dro pview v1;
create view v1 as select a from t3;

The weird error message is a funny side effect of the prelocking algorithm.

Noted in 5.6.6 changelog.

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

One instance of the bug still hits 5.6, 5.7.
Bug 18596756 - FAILED PREPARING OF TRIGGER ON TRUNCATED TABLES CAUSE ERROR 1054