Unobvious "Unknown column in 'field list'" error

Recently we got request from our customer that something is going wrong with their database and they're are getting strange errors after each insert or update to specific table. The strangeness caused by "Unknown column 'column-name' in 'field list'" message while this column was existing in this table. Our investigation shown that this was caused by trigger on the table they were trying to do the insert/update. This trigger did the insert to another table where the mentioned column didn't exist.

Let me show you example:

create test DB and 2 test tables:

create database test;
use test;

create table t1 (field1 int auto_increment not null, field2 varchar(10), field3 varchar(10), primary key(field1)) engine=innodb;
create table t2 (field1 int, field2 varchar(10)) engine=innodb;

Then create a new insert trigger on t1 table:

delimiter ##

create trigger insert_on_t1_to_t2
 after insert
 on t1 for each row
begin
 insert into t2 (field1,field2,field3) values (new.field1, new.field2, '1');
end##

delimiter ;

Insert to t1 table:

mysql> insert into t1(field2,field3) values("val2","val3");
ERROR 1054 (42S22): Unknown column 'field3' in 'field list'

From the query text and the output above it's pretty easy to assume that mysql is complaining about `c` column in `t1` table. But in fact it's complaining about `c` column in `t2` table because this query is running trigger and does insert into `t2` tables and its `(a,b,c)` columns but the latter column doesn't exist in `t2` table.

One of the simplest way to check if it's so is show triggers and grep result by table and column name:

mysql> pager grep -E "t1|field3"
PAGER set to 'grep -E "t1|field3”'

mysql> show triggers from test like 't1'G
             Trigger: insert_on_t1_to_t2
               Table: t1
 insert into t2 (field1,field2,field3) values (new.field1, new.field2, '1');
1 row in set (0.00 sec)

To solve the error I will need either rewrite trigger and remove `field3` column from insert command or alter table `t2` and add column `field3`.

In my test I did rewrite trigger:

drop trigger insert_on_t1_to_t2;

delimiter ##

create trigger insert_on_t1_to_t2
 after insert
 on t1 for each row
begin
 insert into t2 (field1,field2) values (new.field1, new.field2);
end##

delimiter ;

Now the insert works:

mysql> select * from t1;
+--------+--------+--------+
| field1 | field2 | field3 |
+--------+--------+--------+
|      2 | val20  | val30  |
|      3 | val21  | val31  |
+--------+--------+--------+
2 rows in set (0.00 sec)

mysql> select * from t2;
+--------+--------+
| field1 | field2 |
+--------+--------+
|      2 | val20  |
|      3 | val21  |
+--------+--------+
2 rows in set (0.00 sec)

Summary: if you're seeing error like this one that some of the column doesn't exist in the table but in fact it exists then one of the culprit could be a trigger on this table.

Blog