首页 > 数据库 >GreatSQL执行Update失败案例分析

GreatSQL执行Update失败案例分析

时间:2024-09-04 10:14:40浏览次数:10  
标签:old xxx GreatSQL Update varchar 案例 new YES NULL

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

标签:old,xxx,GreatSQL,Update,varchar,案例,new,YES,NULL
From: https://www.cnblogs.com/greatsql/p/18395931

相关文章

  • 数字政务行业ITSM案例分析报告
    一、项目背景随着电子政务的快速发展,政府机构面对海量数据和服务请求的压力不断增加。信息中心作为承载这些服务的核心部门,其运维现状表现出以下几个特点:信息化建设虽然推进迅速,但运维服务却相对滞后,运维体系尚未形成系统化、规范化的管理,尤其是在应对日益增长的服务请求时显得力......
  • 【Linux系列】SH 与 BASH 的区别:深入解析与使用案例
    ......
  • 案例 | 稳石机器人赋能日化企业内部物流提质增效
    近年来,日化产业高速发展,以“清洁类”及“化妆品类”为主的日化品在人们生活中扮演着不可或缺的角色。然而在发展过程中,诸多难点也开始显头,招工难用工贵、生产速度受到掣肘等难题,都对日化企业可持续发展构成挑战。为应对这些挑战,这家国内知名日化企业选择与稳石机器人合作,引入稳石多......
  • 服务器数据恢复—infortrend存储中RAID6阵列多块硬盘离线的数据恢复案例
    服务器存储数据恢复环境:一台infortrend存储中有一组由12块硬盘组建的RAID6阵列。RAID6阵列空间划分了一个LUN,映射到WINDOWSSERVER系统上。WINDOWSSERVER系统上划分了一个GPT分区。  服务器存储故障:存储在运行过程中突然无法访问。管理员查看后发现raid6阵列中有3块盘离线......
  • MONAILabel in 3D Slicer 案例1: 在腹部CT中自动分割脾脏
    MONAILabelin3DSlicer案例1:在腹部CT中自动分割脾脏导读本系列涵盖从3DSlicer医学图像查看器的基础使用到高级自动分割扩展程序的内容(从入门到高阶!),具体包括软件安装、基础使用教程,自动分割扩展(totalsegmentator,monailabel)快速标注数据。在本系列第三部分中,我们在工作站......
  • Exchange 2016部署实施案例篇-03.Exchange部署篇(下)
    昨天我们一起准备完成了ExchangeServer2016的先决条件,今天我们一起来看下如何部署ExchangeServer2016.最近想了想,决定该篇使用2种方式部署ExchangeServer2016,这样可能会让大家对ExchangeServer2016的部署更了解些,废话不多说,开始今天的内容。图形化界面部署......
  • Java高级编程—Stream流(从0到1详解,附有代码+案例)
    文章目录Stream流26.1获取Stream流26.2Stream流中间方法26.3Stream流终结方法(一)26.4Stream流终结方法(二)Stream流相当于把数据源加载到一条流水线上,即在一条传输管道中对数据进行操作,26.1获取Stream流获取方式方法名说明单列集合defaultStreamstream()Col......
  • Exchange 2016部署实施案例篇-03.Exchange部署篇(中)
    上一章《Exchange2016部署实施案例篇-03.Exchange部署篇(上)》我们对部署ExchangeServer2016的先决条件做了简单的讲解,接下来我们进入先决条件准备工作。先简单说下环境:服务器名称IP地址系统作用ADSrv01192.168.1.10Win2016GC(已部署完成)ADSrv02192.168.1.20......
  • Exchange 2016部署实施案例篇-03.Exchange部署篇(上)
    距离上一篇《Exchange2016部署实施案例篇-02.活动目录部署篇》博文更新已经过去快一周了,最近一直在忙项目上的事情和软考,整的真心有点身心俱疲啊,最近看了下上一篇博文不知道为什么访问量一直上不去,真心有点心寒啊。希望大家能多多提出宝贵意见,看看如何能让访问量上去。......
  • Exchange 2016部署实施案例篇-02.活动目录部署篇
    其实在写这篇博文之前纠结了好久,到底是该写部署1台AD演示下,还是部署2台活动目录那,比较这个专家还是以Exchang为主,但思来想去最终决定还是部署一主一辅吧,毕竟部署主与辅助还是稍微在步骤上有些不同的,废话不多说,接下来我们开始我们今天的话题,活动目录部署,请大家耐心读奥,有福利奥......