翼度科技»论坛 编程开发 mysql 查看内容

GreatSQL执行Update失败案例分析

6

主题

6

帖子

18

积分

新手上路

Rank: 1

积分
18
GreatSQL执行Update失败案例分析

一 问题概述

业务反馈在应用核心库的用户基本信息表执行部分update命令失败,报错如下:
  1. 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 问题初步定位
  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表中
  1. GreatSQL [information_schema]> show create trigger xxx.xxx_STAFFBASICINFO_U\G
  2. *************************** 1. row ***************************
  3.                Trigger: xxx_STAFFBASICINFO_U
  4.               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
  5. SQL Original Statement: CREATE DEFINER=`icdpub`@`%` TRIGGER `TRG_T_UCP_STAFFBASICINFO_U` AFTER UPDATE ON `t_ucp_staffbasicinfo` FOR EACH ROW BEGIN
  6. DECLARE   v_havenew   BOOLEAN DEFAULT FALSE;
  7. DECLARE   v_haveold   BOOLEAN DEFAULT FALSE;
  8. DECLARE   v_action    VARCHAR(32);
  9. DECLARE   v_staffid_ct BIGINT;
  10. select count(STAFFID) into v_staffid_ct from xxx.lpr_sys_staff where STAFFID=old.STAFFID;
  11.       set v_havenew := TRUE;
  12.       set v_action := 'UPDATE';
  13.       set v_haveold := TRUE;
  14.       
  15.    IF TRUE = v_haveold and v_staffid_ct>0
  16.    THEN
  17.       INSERT INTO xxx.xxx_staffbasicinfo_log (STAFFID,
  18.                   STAFFNAME,
  19.                   STAFFSTATE,
  20.                   STAFFIDSTATUS,
  21.                   DLEVELID,
  22.                   DLMODULUS,
  23.                   SECONDPOST,
  24.                   DUTYID,
  25.                   SECONDDUTY,
  26.                   PTEAMID,
  27.                   ORGAID,
  28.                   POSTID,
  29.                   STAFFACCOUNT,
  30.                   DISABLEBEGINDATE,
  31.                   DISABLEENDDATE,
  32.                   HOSTEDCCID,
  33.                   PERSONALCFGID,
  34.                   UPDATETIME,
  35.                   BATCHNO,
  36.                   STAFFTYPE,
  37.                   ISMANAGER,
  38.                   HRSTATUS,
  39.                   CREATEDATE,
  40.                   STATUSDATE,
  41.                   REMARK,
  42.                   REGION,
  43.                   BEGINDATE,
  44.                   ENDDATE,
  45.                   RELESTAFFID,
  46.                   WORKEFFICIENCY,
  47.                   TELNO,
  48.                   LOGINTYPE,
  49.                   WORKTYPE,
  50.                   AREAID,
  51.                   EMPLOYEETYPE,
  52.                   STAFFNUMBER,
  53.                   STAFFIDUSE,
  54.                   ISADMIN,
  55.                   PETNAME,
  56.                   ISMODIFYCONTROL,
  57.                   RESPCITYID,
  58.                   NOTMODSTATUS,
  59.                   t_operator,
  60.                   t_action,
  61.                   t_date,
  62.                   t_remark)
  63.            VALUES (old.STAFFID,
  64.                    old.STAFFNAME,
  65.                    old.STAFFSTATE,
  66.                    old.STAFFIDSTATUS,
  67.                    old.DLEVELID,
  68.                    old.DLMODULUS,
  69.                    old.SECONDPOST,
  70.                    old.DUTYID,
  71.                    old.SECONDDUTY,
  72.                    old.PTEAMID,
  73.                    old.ORGAID,
  74.                    old.POSTID,
  75.                    old.STAFFACCOUNT,
  76.                    old.DISABLEBEGINDATE,
  77.                    old.DISABLEENDDATE,
  78.                    old.HOSTEDCCID,
  79.                    old.PERSONALCFGID,
  80.                    old.UPDATETIME,
  81.                    old.BATCHNO,
  82.                    old.STAFFTYPE,
  83.                    old.ISMANAGER,
  84.                    old.HRSTATUS,
  85.                    old.CREATEDATE,
  86.                    old.STATUSDATE,
  87.                    old.REMARK,
  88.                    old.REGION,
  89.                    old.BEGINDATE,
  90.                    old.ENDDATE,
  91.                    old.RELESTAFFID,
  92.                    old.WORKEFFICIENCY,
  93.                    old.TELNO,
  94.                    old.LOGINTYPE,
  95.                    old.WORKTYPE,
  96.                    old.AREAID,
  97.                    old.EMPLOYEETYPE,
  98.                    old.STAFFNUMBER,
  99.                    old.STAFFIDUSE,
  100.                    old.ISADMIN,
  101.                    old.PETNAME,
  102.                    old.ISMODIFYCONTROL,
  103.                    old.RESPCITYID,
  104.                    old.NOTMODSTATUS,
  105.                    USER(),
  106.                    v_action,
  107.                    SYSDATE(),
  108.                    'old');
  109.    END IF;
  110.    IF TRUE = v_havenew and v_staffid_ct>0
  111.    THEN
  112.       INSERT INTO xxx.xxx_staffbasicinfo_LOG (STAFFID,
  113.       STAFFNAME,STAFFSTATE,STAFFIDSTATUS,DLEVELID,DLMODULUS,SECONDPOST,
  114.       DUTYID,SECONDDUTY,PTEAMID,ORGAID,POSTID,STAFFACCOUNT,
  115.       DISABLEBEGINDATE,DISABLEENDDATE,HOSTEDCCID,
  116.       PERSONALCFGID,UPDATETIME,BATCHNO,STAFFTYPE,ISMANAGER,HRSTATUS,CREATEDATE,
  117.       STATUSDATE,REMARK,REGION,BEGINDATE,ENDDATE,RELESTAFFID,WORKEFFICIENCY,TELNO,
  118.       LOGINTYPE,WORKTYPE,AREAID,EMPLOYEETYPE,STAFFNUMBER,STAFFIDUSE,ISADMIN,
  119.       PETNAME,ISMODIFYCONTROL,RESPCITYID,NOTMODSTATUS,t_operator,t_action,
  120.       t_date,t_remark)
  121.            VALUES (new.STAFFID,
  122.                    new.STAFFNAME,
  123.                    new.STAFFSTATE,
  124.                    new.STAFFIDSTATUS,
  125.                    new.DLEVELID,
  126.                    new.DLMODULUS,
  127.                    new.SECONDPOST,
  128.                    new.DUTYID,
  129.                    new.SECONDDUTY,
  130.                    new.PTEAMID,
  131.                    new.ORGAID,
  132.                    new.POSTID,
  133.                    new.STAFFACCOUNT,
  134.                    new.DISABLEBEGINDATE,
  135.                    new.DISABLEENDDATE,
  136.                    new.HOSTEDCCID,
  137.                    new.PERSONALCFGID,
  138.                    new.UPDATETIME,
  139.                    new.BATCHNO,
  140.                    new.STAFFTYPE,
  141.                    new.ISMANAGER,
  142.                    new.HRSTATUS,
  143.                    new.CREATEDATE,
  144.                    new.STATUSDATE,
  145.                    new.REMARK,
  146.                    new.REGION,
  147.                    new.BEGINDATE,
  148.                    new.ENDDATE,
  149.                    new.RELESTAFFID,
  150.                    new.WORKEFFICIENCY,
  151.                    new.TELNO,
  152.                    new.LOGINTYPE,
  153.                    new.WORKTYPE,
  154.                    new.AREAID,
  155.                    new.EMPLOYEETYPE,
  156.                    new.STAFFNUMBER,
  157.                    new.STAFFIDUSE,
  158.                    new.ISADMIN,
  159.                    new.PETNAME,
  160.                    new.ISMODIFYCONTROL,
  161.                    new.RESPCITYID,
  162.                    new.NOTMODSTATUS,
  163.                    USER(),
  164.                    v_action,
  165.                    SYSDATE(),
  166.                    'new');        
  167.    END IF;                  
  168. END
  169.   character_set_client: utf8mb4
  170.   collation_connection: utf8mb4_0900_ai_ci
  171.     Database Collation: utf8mb4_0900_bin
  172.                Created: 2022-04-13 00:32:05.13
  173. 1 row in set (0.01 sec)
复制代码
但xxx.XXX_STAFFBASICINFO_LOG表NOTMODSTATUS字段为 varchar(1) ,而 xxx.xxx_staffbasicinfo表NOTMODSTATUS字段为 varchar(8),字段长度不足导致insert失败。
  1. GreatSQL [information_schema]> desc xxx.XXX_STAFFBASICINFO_LOG    -> ;
  2. +------------------+--------------+------+-----+---------+-------+
  3. | Field            | Type         | Null | Key | Default | Extra |
  4. +------------------+--------------+------+-----+---------+-------+
  5. | STAFFID          | varchar(20)  | NO   |     | NULL    |       |
  6. | STAFFNAME        | varchar(100) | NO   |     | NULL    |       |
  7. | STAFFSTATE       | varchar(2)   | NO   |     | NULL    |       |
  8. | STAFFIDSTATUS    | varchar(2)   | NO   |     | NULL    |       |
  9. ......
  10. ......
  11. | ISADMIN          | varchar(1)   | YES  |     | NULL    |       |
  12. | PETNAME          | varchar(100) | YES  |     | NULL    |       |
  13. | ISMODIFYCONTROL  | varchar(1)   | YES  |     | NULL    |       |
  14. | RESPCITYID       | varchar(40)  | YES  |     | NULL    |       |
  15. | NOTMODSTATUS     | varchar(1)   | YES  |     | NULL    |       |
  16. | T_OPERATOR       | varchar(100) | YES  |     | NULL    |       |
  17. | T_ACTION         | varchar(100) | YES  |     | NULL    |       |
  18. | T_DATE           | datetime     | YES  |     | NULL    |       |
  19. | T_REMARK         | varchar(100) | YES  |     | NULL    |       |
  20. +------------------+--------------+------+-----+---------+-------+
  21. 46 rows in set (0.01 sec)1 row in set (0.00 sec)
  22. GreatSQL [information_schema]> desc xxx.xxx_staffbasicinfo;
  23. +------------------+--------------+------+-----+-------------------+-------------------+
  24. | Field            | Type         | Null | Key | Default           | Extra             |
  25. +------------------+--------------+------+-----+-------------------+-------------------+
  26. | STAFFID          | varchar(20)  | NO   | PRI | NULL              |                   |
  27. | STAFFNAME        | varchar(100) | NO   |     | NULL              |                   |
  28. | STAFFSTATE       | varchar(2)   | NO   |     | NULL              |                   |
  29. ....
  30. | ISMODIFYCONTROL  | varchar(1)   | YES  |     | 0                 |                   |
  31. | RESPCITYID       | varchar(40)  | YES  |     | NULL              |                   |
  32. | NOTMODSTATUS     | varchar(8)   | YES  |     | NULL              |                   |
  33. | CURRENTORGAID    | varchar(32)  | YES  | MUL | NULL              |                   |
  34. | CURRENTREGION    | int          | YES  |     | NULL              |                   |
  35. | SALESCENE        | varchar(2)   | YES  |     | NULL              |                   |
  36. | CHANNELTYPE      | varchar(2)   | YES  |     | NULL              |                   |
  37. | LOGINCHKPHOTO    | varchar(2)   | YES  |     | 0                 |                   |
  38. | UPLOADPHOTO      | varchar(2)   | YES  |     | 0                 |                   |
  39. | USERNAME         | varchar(100) | YES  |     | NULL              |                   |
  40. | JKAPPROLE        | varchar(64)  | YES  |     | NULL              |                   |
  41. | JKAPPLEVEL       | varchar(1)   | YES  |     | NULL              |                   |
  42. | UPLOADPHOTODATE  | date         | YES  |     | NULL              |                   |
  43. | UPLOADPHOTOOPER  | varchar(32)  | YES  |     | NULL              |                   |
  44. +------------------+--------------+------+-----+-------------------+-------------------+
  45. 53 rows in set (0.01 sec)
复制代码
以前长期运行过程中,未暴露此问题的原因是由于NOTMODSTATUS字段在之前处理的记录中全部为null。
  1. GreatSQL [information_schema]> select NOTMODSTATUS ,count(*) from xxx.XXX_STAFFBASICINFO_LOG group by NOTMODSTATUS;
  2. +--------------+----------+
  3. | NOTMODSTATUS | count(*) |
  4. +--------------+----------+
  5. | NULL         |      762 |
  6. +--------------+----------+
  7. 1 row in set (0.00 sec)
复制代码
2.2 问题复现
  1. greatsql> show create table students\G
  2. *************************** 1. row ***************************
  3.        Table: students
  4. Create Table: CREATE TABLE `students` (
  5.   `id` int NOT NULL,
  6.   `name` varchar(20) DEFAULT NULL,
  7.   `chinese` int DEFAULT NULL,
  8.   PRIMARY KEY (`id`),
  9.   KEY `ind_chinese` (`chinese`)
  10. ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci STATS_PERSISTENT=0 STATS_AUTO_RECALC=0 STATS_SAMPLE_PAGES=100
  11. 1 row in set (0.00 sec)
  12. greatsql> CREATE TABLE `students_hist` (
  13.     ->   `id` int NOT NULL,
  14.     ->   `name` varchar(10) DEFAULT NULL,
  15.     ->   `chinese` int DEFAULT NULL,
  16.     ->   PRIMARY KEY (`id`),
  17.     ->   KEY `ind_chinese` (`chinese`)
  18.     -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci STATS_PERSISTENT=0 STATS_AUTO_RECALC=0 STATS_SAMPLE_PAGES=100;
  19. Query OK, 0 rows affected (0.41 sec)
  20. greatsql> CREATE TRIGGER  tri_update_stu
  21.     ->      BEFORE update
  22.     ->     ON test.students FOR EACH ROW   
  23.     ->     insert into test.students_hist (id,name,chinese) values (OLD.id,OLD.name,OLD.chinese);
  24. Query OK, 0 rows affected (0.01 sec)
复制代码
在students表上执行update语句
  1. greatsql> select * from students;
  2. +----+----------------+---------+
  3. | id | name           | chinese |
  4. +----+----------------+---------+
  5. |  1 | yaojunz        |      99 |
  6. |  5 | yaojunzhuo8000 |      72 |
  7. |  6 | zhao           |      88 |
  8. | 10 | xiao           |      90 |
  9. +----+----------------+---------+
  10. 4 rows in set (0.00 sec)
  11. greatsql> update students set name='yaojunzhuo80000' where id=5;
  12. ERROR 1265 (01000): Data truncated for column 'name' at row 1
复制代码
三 解决方案

将xxx.xxx_staffbasicinfo表上触发器中所涉及表的表字段和xxx.xxx_staffbasicinfo修改为一致,问题得到解决。

Enjoy GreatSQL
来源:https://www.cnblogs.com/greatsql/p/18395931
免责声明:由于采集信息均来自互联网,如果侵犯了您的权益,请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容,谢谢合作!

本帖子中包含更多资源

您需要 登录 才可以下载或查看,没有账号?立即注册

x

举报 回复 使用道具