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
Triage: Triaged: D2 (Serious)

[5 Dec 2007 14:31] Alexander Nozdrin
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.
[5 Dec 2007 16:05] Miguel Solorzano
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;
[25 Feb 2008 18:42] Valeriy Kravchuk
The bug is still repeatable with latest 5.1.24-BK.
[30 Mar 2008 21:00] Konstantin Osipov
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.
[30 Mar 2008 21:01] Konstantin Osipov
Workaround: issue FLUSH TABLE/FLUSH TABLES
[30 Mar 2008 21:11] Konstantin Osipov
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;
[30 Mar 2008 21:15] Konstantin Osipov
The weird error message is a funny side effect of the prelocking algorithm.
[20 Jun 2012 16:40] 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.
[16 Apr 2014 7:04] Shane Bester
One instance of the bug still hits 5.6, 5.7.
Bug 18596756 - FAILED PREPARING OF TRIGGER ON TRUNCATED TABLES CAUSE ERROR 1054