DML操作報(bào)列不存在？
背景概述
客戶在測(cè)試時(shí)發(fā)現(xiàn)執(zhí)行某些DML語句時(shí)，出現(xiàn)了異常情況，報(bào)表不存在或者列不匹配的情況；
我在做數(shù)據(jù)遷移測(cè)試的時(shí)候也出現(xiàn)此問題，遷移數(shù)據(jù)時(shí)報(bào) unknow column；
看到這種情況的時(shí)候很奇怪，查看表結(jié)構(gòu)時(shí)也能看到當(dāng)前執(zhí)行的SQL語句涉及的表及列是存在的；
經(jīng)過排查，最終發(fā)現(xiàn)當(dāng)前這張表涉及觸發(fā)器，報(bào)錯(cuò)的也不是這張表，而是其他表。
問題復(fù)現(xiàn)
本次測(cè)試基于 GreatSQL 8.0.32
1.創(chuàng)建測(cè)試表
greatsql> CREATE TABLE t1 (c1 int,c2 int,c3 int,c4 int);
greatsql> INSERT INTO t1 VALUES (1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4);
greatsql> CREATE TABLE t2 (c5 int,c6 int,c7 int,c8 int);
greatsql> INSERT INTO t2 VALUES (1,1,1,1),(2,2,2,2),(3,3,3,3),(4,4,4,4);2.創(chuàng)建觸發(fā)器
# t2表不存在c1列
greatsql> CREATE TRIGGER test1
after INSERT on t1
FOR EACH ROW
INSERT INTO t2(c1) values(NEW.c1);
Query OK, 0 rows affected (0.02 sec)
greatsql> CREATE TRIGGER test2
after UPDATE on t1
FOR EACH ROW
UPDATE test.t2 SET c1=(NEW.c1)+1 WHERE c1=(NEW.c1);
Query OK, 0 rows affected (0.02 sec)
greatsql> CREATE TRIGGER test3
after DELETE on t1
FOR EACH ROW
DELETE FROM t2 WHERE c1=(OLD.c1);
Query OK, 0 rows affected (0.02 sec)
# t3表不存在
greatsql> CREATE TRIGGER test4
before UPDATE on t2
FOR EACH ROW
INSERT INTO t3(c1) values(NEW.c5);
Query OK, 0 rows affected (0.00 sec)可以看到在創(chuàng)建觸發(fā)器的時(shí)候，不會(huì)去判斷語句中涉及的表或者列是否存在。
3.執(zhí)行測(cè)試SQL
greatsql> INSERT INTO test.t1 values (1,1,1,1);
ERROR 1054 (42S22): Unknown column 'c1' in 'field list'
greatsql> UPDATE test.t1 SET c1=110 WHERE c1=1;
ERROR 1054 (42S22): Unknown column 'c1' in 'field list'
greatsql> DELETE FROM test.t1 WHERE c1=1;
ERROR 1054 (42S22): Unknown column 'c1' in 'where clause'
greatsql> UPDATE t2 SET c5=110 WHERE c5=1;
ERROR 1146 (42S02): Table 'test.t3' doesn't exist此時(shí)報(bào)錯(cuò)c1列不存在，但沒有顯示是具體那張表的c1列，因此對(duì)我們產(chǎn)生誤導(dǎo)，明明t1表存在c1列，但是還是報(bào)錯(cuò)c1列不存在；
4.故障排查
遇到上述問題時(shí)，我們可以打開通用日志，觀察一下日志中記錄的語句
shell> tail -f general5000.log
...
2024-10-14T16:21:16.837007+08:00 2651 Query INSERT INTO test.t1 values (1,1,1,1)
2024-10-14T16:21:16.839500+08:00 2651 Query INSERT INTO t2(c1) values(NEW.c1)
...可以看到當(dāng)我們執(zhí)行了 INSERT INTO test.t1 語句后緊接著自動(dòng)執(zhí)行 INSERT INTO t2(c1) 語句，因?yàn)閠2表沒有c1列，所以報(bào)錯(cuò) Unknown column 'c1'。
5.查看當(dāng)前表涉及的觸發(fā)器
greatsql> SELECT TRIGGER_SCHEMA,TRIGGER_NAME,EVENT_OBJECT_SCHEMA,EVENT_OBJECT_TABLE,ACTION_STATEMENT FROM INFORMATION_SCHEMA.TRIGGERS WHERE EVENT_OBJECT_TABLE='t1';
+----------------+--------------+---------------------+--------------------+----------------------------------------------------+
| TRIGGER_SCHEMA | TRIGGER_NAME | EVENT_OBJECT_SCHEMA | EVENT_OBJECT_TABLE | ACTION_STATEMENT |
+----------------+--------------+---------------------+--------------------+----------------------------------------------------+
| test | test1 | test | t1 | INSERT INTO t2(c1) values(NEW.c1) |
| test | test2 | test | t1 | UPDATE test.t2 SET c1=(NEW.c1)+1 WHERE c1=(NEW.c1) |
| test | test3 | test | t1 | DELETE FROM t2 WHERE c1=(OLD.c1) |
+----------------+--------------+---------------------+--------------------+----------------------------------------------------+
3 rows in set (0.00 sec)當(dāng)出現(xiàn)上面的問題時(shí)，可以查看一下這張表是否涉及觸發(fā)器；如果涉及則檢查一下對(duì)應(yīng)觸發(fā)器的ACTION_STATEMENT字段中的SQL語句涉及的表是否包含報(bào)錯(cuò)的字段。
總結(jié)
如果出現(xiàn)在執(zhí)行DML操作時(shí)報(bào)錯(cuò)，并且報(bào)錯(cuò)跟當(dāng)前表沒有什么關(guān)系時(shí)可以考慮是否有觸發(fā)器與當(dāng)前表有關(guān)聯(lián)，檢查一下觸發(fā)器中涉及的SQL語句。
