GreatSQL 执行 Update 失败案例分析
作者：GreatSQL
- 2024-09-04 福建
本文字数：6219 字
阅读完需：约 20 分钟
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 类型触发器。
分析每个 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_SUBSTITUTIONSQL Original Statement: CREATE DEFINER=`icdpub`@`%` TRIGGER `TRG_T_UCP_STAFFBASICINFO_U` AFTER UPDATE ON `t_ucp_staffbasicinfo` FOR EACH ROW BEGINDECLARE 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.131 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: studentsCreate 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=1001 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 修改为一致，问题得到解决。
划线
评论
复制
发布于: 刚刚阅读数: 2
版权声明: 本文为 InfoQ 作者【GreatSQL】的原创文章。
原文链接:【http://xie.infoq.cn/article/fc51343ba17d149e709d9c145】。文章转载请联系作者。
GreatSQL
关注
GreatSQL社区 2023-01-31 加入
GreatSQL是由万里数据库维护的MySQL分支，专注于提升MGR可靠性及性能，支持InnoDB并行查询特性，是适用于金融级应用的MySQL分支版本。 社区：https://greatsql.cn/ Gitee: https://gitee.com/GreatSQL/GreatSQL
评论