DML操作報列不存在？

作者：王權富貴 2024-12-13 08:25:59

數據庫其他數據庫

如果出現在執行DML操作時報錯，并且報錯跟當前表沒有什么關系時可以考慮是否有觸發器與當前表有關聯，檢查一下觸發器中涉及的SQL語句。

背景概述

客戶在測試時發現執行某些DML語句時，出現了異常情況，報表不存在或者列不匹配的情況；

我在做數據遷移測試的時候也出現此問題，遷移數據時報 unknow column；

看到這種情況的時候很奇怪，查看表結構時也能看到當前執行的SQL語句涉及的表及列是存在的；

經過排查，最終發現當前這張表涉及觸發器，報錯的也不是這張表，而是其他表。

問題復現

本次測試基于 GreatSQL 8.0.32

1.創建測試表

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.創建觸發器

# 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)

可以看到在創建觸發器的時候，不會去判斷語句中涉及的表或者列是否存在。

3.執行測試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

此時報錯c1列不存在，但沒有顯示是具體那張表的c1列，因此對我們產生誤導，明明t1表存在c1列，但是還是報錯c1列不存在；

4.故障排查

遇到上述問題時，我們可以打開通用日志，觀察一下日志中記錄的語句

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)
...

可以看到當我們執行了 INSERT INTO test.t1 語句后緊接著自動執行 INSERT INTO t2(c1) 語句，因為t2表沒有c1列，所以報錯 Unknown column 'c1'。

5.查看當前表涉及的觸發器

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)

當出現上面的問題時，可以查看一下這張表是否涉及觸發器；如果涉及則檢查一下對應觸發器的ACTION_STATEMENT字段中的SQL語句涉及的表是否包含報錯的字段。

總結