GreatSQL執行Update失敗案例分析

GreatSQL發表於2024-09-04
SQL

GreatSQL執行Update失敗案例分析

一 問題概述

業務反饋在應用核心庫的使用者基本資訊表執行部分update命令失敗，報錯如下：

update xxx.xxx_staffbasicinfo  set staffidstatus='04’ where staffid in (select * from  duyuanyu.tmp_d_xiaoyuan )  > 1265 Data  truncated for column 'NOTMODSTATUS at row 1

二 問題分析

經過分析表結構，沒有發現異常。

2.1 問題初步定位

$ perror 1265  MySQL error code  MY-001265 (WARN_DATA_TRUNCATED): Data truncated for column '%s' at row %ld

進一步分析對於該表的儲存過程、觸發器等，發現 xxx.xxx_staffbasicinfo 表上建了8個觸發器，其中有包括3個update型別觸發器。

file

分析每個update型別觸發器，發現xxx.xxx_STAFFBASICINFO_U 觸發器作用是在滿足指定條件時將xxx.xxx_staffbasicinfo原來記錄或者新的記錄insert 到xxx.xxx__STAFFBASICINFO_LOG表中

GreatSQL [information_schema]> show create trigger xxx.xxx_STAFFBASICINFO_U\G
*************************** 1. row ***************************
               Trigger: xxx_STAFFBASICINFO_U
              sql_mode: PIPES_AS_CONCAT,ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
SQL Original Statement: CREATE DEFINER=`icdpub`@`%` TRIGGER `TRG_T_UCP_STAFFBASICINFO_U` AFTER UPDATE ON `t_ucp_staffbasicinfo` FOR EACH ROW BEGIN
DECLARE   v_havenew   BOOLEAN DEFAULT FALSE;
DECLARE   v_haveold   BOOLEAN DEFAULT FALSE;
DECLARE   v_action    VARCHAR(32);
DECLARE   v_staffid_ct BIGINT;

select count(STAFFID) into v_staffid_ct from xxx.lpr_sys_staff where STAFFID=old.STAFFID;


      set v_havenew := TRUE;

      set v_action := 'UPDATE';
      set v_haveold := TRUE;
      
   IF TRUE = v_haveold and v_staffid_ct>0
   THEN
      INSERT INTO xxx.xxx_staffbasicinfo_log (STAFFID,
                  STAFFNAME,
                  STAFFSTATE,
                  STAFFIDSTATUS,
                  DLEVELID,
                  DLMODULUS,
                  SECONDPOST,
                  DUTYID,
                  SECONDDUTY,
                  PTEAMID,
                  ORGAID,
                  POSTID,
                  STAFFACCOUNT,
                  DISABLEBEGINDATE,
                  DISABLEENDDATE,
                  HOSTEDCCID,
                  PERSONALCFGID,
                  UPDATETIME,
                  BATCHNO,
                  STAFFTYPE,
                  ISMANAGER,
                  HRSTATUS,
                  CREATEDATE,
                  STATUSDATE,
                  REMARK,
                  REGION,
                  BEGINDATE,
                  ENDDATE,
                  RELESTAFFID,
                  WORKEFFICIENCY,
                  TELNO,
                  LOGINTYPE,
                  WORKTYPE,
                  AREAID,
                  EMPLOYEETYPE,
                  STAFFNUMBER,
                  STAFFIDUSE,
                  ISADMIN,
                  PETNAME,
                  ISMODIFYCONTROL,
                  RESPCITYID,
                  NOTMODSTATUS,
                  t_operator,
                  t_action,
                  t_date,
                  t_remark)
           VALUES (old.STAFFID,
                   old.STAFFNAME,
                   old.STAFFSTATE,
                   old.STAFFIDSTATUS,
                   old.DLEVELID,
                   old.DLMODULUS,
                   old.SECONDPOST,
                   old.DUTYID,
                   old.SECONDDUTY,
                   old.PTEAMID,
                   old.ORGAID,
                   old.POSTID,
                   old.STAFFACCOUNT,
                   old.DISABLEBEGINDATE,
                   old.DISABLEENDDATE,
                   old.HOSTEDCCID,
                   old.PERSONALCFGID,
                   old.UPDATETIME,
                   old.BATCHNO,
                   old.STAFFTYPE,
                   old.ISMANAGER,
                   old.HRSTATUS,
                   old.CREATEDATE,
                   old.STATUSDATE,
                   old.REMARK,
                   old.REGION,
                   old.BEGINDATE,
                   old.ENDDATE,
                   old.RELESTAFFID,
                   old.WORKEFFICIENCY,
                   old.TELNO,
                   old.LOGINTYPE,
                   old.WORKTYPE,
                   old.AREAID,
                   old.EMPLOYEETYPE,
                   old.STAFFNUMBER,
                   old.STAFFIDUSE,
                   old.ISADMIN,
                   old.PETNAME,
                   old.ISMODIFYCONTROL,
                   old.RESPCITYID,
                   old.NOTMODSTATUS,
                   USER(),
                   v_action,
                   SYSDATE(),
                   'old');
   END IF;

   IF TRUE = v_havenew and v_staffid_ct>0
   THEN
      INSERT INTO xxx.xxx_staffbasicinfo_LOG (STAFFID,
      STAFFNAME,STAFFSTATE,STAFFIDSTATUS,DLEVELID,DLMODULUS,SECONDPOST,
      DUTYID,SECONDDUTY,PTEAMID,ORGAID,POSTID,STAFFACCOUNT,
      DISABLEBEGINDATE,DISABLEENDDATE,HOSTEDCCID,
      PERSONALCFGID,UPDATETIME,BATCHNO,STAFFTYPE,ISMANAGER,HRSTATUS,CREATEDATE,
      STATUSDATE,REMARK,REGION,BEGINDATE,ENDDATE,RELESTAFFID,WORKEFFICIENCY,TELNO,
      LOGINTYPE,WORKTYPE,AREAID,EMPLOYEETYPE,STAFFNUMBER,STAFFIDUSE,ISADMIN,
      PETNAME,ISMODIFYCONTROL,RESPCITYID,NOTMODSTATUS,t_operator,t_action,
      t_date,t_remark)
           VALUES (new.STAFFID,
                   new.STAFFNAME,
                   new.STAFFSTATE,
                   new.STAFFIDSTATUS,
                   new.DLEVELID,
                   new.DLMODULUS,
                   new.SECONDPOST,
                   new.DUTYID,
                   new.SECONDDUTY,
                   new.PTEAMID,
                   new.ORGAID,
                   new.POSTID,
                   new.STAFFACCOUNT,
                   new.DISABLEBEGINDATE,
                   new.DISABLEENDDATE,
                   new.HOSTEDCCID,
                   new.PERSONALCFGID,
                   new.UPDATETIME,
                   new.BATCHNO,
                   new.STAFFTYPE,
                   new.ISMANAGER,
                   new.HRSTATUS,
                   new.CREATEDATE,
                   new.STATUSDATE,
                   new.REMARK,
                   new.REGION,
                   new.BEGINDATE,
                   new.ENDDATE,
                   new.RELESTAFFID,
                   new.WORKEFFICIENCY,
                   new.TELNO,
                   new.LOGINTYPE,
                   new.WORKTYPE,
                   new.AREAID,
                   new.EMPLOYEETYPE,
                   new.STAFFNUMBER,
                   new.STAFFIDUSE,
                   new.ISADMIN,
                   new.PETNAME,
                   new.ISMODIFYCONTROL,
                   new.RESPCITYID,
                   new.NOTMODSTATUS,
                   USER(),
                   v_action,
                   SYSDATE(),
                   'new');        
   END IF;                   
END
  character_set_client: utf8mb4
  collation_connection: utf8mb4_0900_ai_ci
    Database Collation: utf8mb4_0900_bin
               Created: 2022-04-13 00:32:05.13
1 row in set (0.01 sec)

但xxx.XXX_STAFFBASICINFO_LOG表NOTMODSTATUS欄位為 varchar(1) ，而 xxx.xxx_staffbasicinfo表NOTMODSTATUS欄位為 varchar(8),欄位長度不足導致insert失敗。

GreatSQL [information_schema]> desc xxx.XXX_STAFFBASICINFO_LOG    -> ;
+------------------+--------------+------+-----+---------+-------+
| Field            | Type         | Null | Key | Default | Extra |
+------------------+--------------+------+-----+---------+-------+
| STAFFID          | varchar(20)  | NO   |     | NULL    |       |
| STAFFNAME        | varchar(100) | NO   |     | NULL    |       |
| STAFFSTATE       | varchar(2)   | NO   |     | NULL    |       |
| STAFFIDSTATUS    | varchar(2)   | NO   |     | NULL    |       |
......
......
| ISADMIN          | varchar(1)   | YES  |     | NULL    |       |
| PETNAME          | varchar(100) | YES  |     | NULL    |       |
| ISMODIFYCONTROL  | varchar(1)   | YES  |     | NULL    |       |
| RESPCITYID       | varchar(40)  | YES  |     | NULL    |       |
| NOTMODSTATUS     | varchar(1)   | YES  |     | NULL    |       |
| T_OPERATOR       | varchar(100) | YES  |     | NULL    |       |
| T_ACTION         | varchar(100) | YES  |     | NULL    |       |
| T_DATE           | datetime     | YES  |     | NULL    |       |
| T_REMARK         | varchar(100) | YES  |     | NULL    |       |
+------------------+--------------+------+-----+---------+-------+
46 rows in set (0.01 sec)1 row in set (0.00 sec)

GreatSQL [information_schema]> desc xxx.xxx_staffbasicinfo;
+------------------+--------------+------+-----+-------------------+-------------------+
| Field            | Type         | Null | Key | Default           | Extra             |
+------------------+--------------+------+-----+-------------------+-------------------+
| STAFFID          | varchar(20)  | NO   | PRI | NULL              |                   |
| STAFFNAME        | varchar(100) | NO   |     | NULL              |                   |
| STAFFSTATE       | varchar(2)   | NO   |     | NULL              |                   |
....
| ISMODIFYCONTROL  | varchar(1)   | YES  |     | 0                 |                   |
| RESPCITYID       | varchar(40)  | YES  |     | NULL              |                   |
| NOTMODSTATUS     | varchar(8)   | YES  |     | NULL              |                   |
| CURRENTORGAID    | varchar(32)  | YES  | MUL | NULL              |                   |
| CURRENTREGION    | int          | YES  |     | NULL              |                   |
| SALESCENE        | varchar(2)   | YES  |     | NULL              |                   |
| CHANNELTYPE      | varchar(2)   | YES  |     | NULL              |                   |
| LOGINCHKPHOTO    | varchar(2)   | YES  |     | 0                 |                   |
| UPLOADPHOTO      | varchar(2)   | YES  |     | 0                 |                   |
| USERNAME         | varchar(100) | YES  |     | NULL              |                   |
| JKAPPROLE        | varchar(64)  | YES  |     | NULL              |                   |
| JKAPPLEVEL       | varchar(1)   | YES  |     | NULL              |                   |
| UPLOADPHOTODATE  | date         | YES  |     | NULL              |                   |
| UPLOADPHOTOOPER  | varchar(32)  | YES  |     | NULL              |                   |
+------------------+--------------+------+-----+-------------------+-------------------+
53 rows in set (0.01 sec)

以前長期執行過程中，未暴露此問題的原因是由於NOTMODSTATUS欄位在之前處理的記錄中全部為null。

GreatSQL [information_schema]> select NOTMODSTATUS ,count(*) from xxx.XXX_STAFFBASICINFO_LOG group by NOTMODSTATUS;
+--------------+----------+
| NOTMODSTATUS | count(*) |
+--------------+----------+
| NULL         |      762 |
+--------------+----------+
1 row in set (0.00 sec)

2.2 問題復現

greatsql> show create table students\G
*************************** 1. row ***************************
       Table: students
Create Table: CREATE TABLE `students` (
  `id` int NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  `chinese` int DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ind_chinese` (`chinese`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci STATS_PERSISTENT=0 STATS_AUTO_RECALC=0 STATS_SAMPLE_PAGES=100
1 row in set (0.00 sec)

greatsql> CREATE TABLE `students_hist` (
    ->   `id` int NOT NULL,
    ->   `name` varchar(10) DEFAULT NULL,
    ->   `chinese` int DEFAULT NULL,
    ->   PRIMARY KEY (`id`),
    ->   KEY `ind_chinese` (`chinese`)
    -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci STATS_PERSISTENT=0 STATS_AUTO_RECALC=0 STATS_SAMPLE_PAGES=100;
Query OK, 0 rows affected (0.41 sec)

greatsql> CREATE TRIGGER  tri_update_stu
    ->      BEFORE update
    ->     ON test.students FOR EACH ROW   
    ->     insert into test.students_hist (id,name,chinese) values (OLD.id,OLD.name,OLD.chinese);
Query OK, 0 rows affected (0.01 sec)

在students表上執行update語句

greatsql> select * from students;
+----+----------------+---------+
| id | name           | chinese |
+----+----------------+---------+
|  1 | yaojunz        |      99 |
|  5 | yaojunzhuo8000 |      72 |
|  6 | zhao           |      88 |
| 10 | xiao           |      90 |
+----+----------------+---------+
4 rows in set (0.00 sec)

greatsql> update students set name='yaojunzhuo80000' where id=5;
ERROR 1265 (01000): Data truncated for column 'name' at row 1

三 解決方案

將xxx.xxx_staffbasicinfo表上觸發器中所涉及表的表欄位和xxx.xxx_staffbasicinfo修改為一致，問題得到解決。

Enjoy GreatSQL 😃

關於 GreatSQL

GreatSQL是適用於金融級應用的國內自主開源資料庫，具備高效能、高可靠、高易用性、高安全等多個核心特性，可以作為MySQL或Percona Server的可選替換，用於線上生產環境，且完全免費併相容MySQL或Percona Server。

相關連結： GreatSQL社群 Gitee GitHub Bilibili

GreatSQL社群：

社群部落格有獎徵稿詳情：https://greatsql.cn/thread-100-1-1.html

image-20230105161905827

技術交流群：

微信：掃碼新增GreatSQL社群助手微信好友，傳送驗證資訊加群。

image-20221030163217640

相關文章