MYSQL数据库一个BUG的修复

最新推荐文章于 2022-09-21 11:19:41 发布

梨麦科技

最新推荐文章于 2022-09-21 11:19:41 发布

阅读量276

点赞数

分类专栏：数据库 MYSQL

本文链接：https://blog.csdn.net/g18397/article/details/111402035

版权

MYSQL 同时被 2 个专栏收录

4 篇文章 0 订阅

订阅专栏

数据库

3 篇文章 0 订阅

订阅专栏

MYSQL数据库一个BUG的修复

环境
问题
查找问题原因
DEBUG

环境

MYSQL 5.6
MAINTABLE 有唯一键BARCODE
PARTDEVICE
DATATABLE
MAINTABLE_HIS 有唯一键BARCODE
PARTDEVICE
DATATABLE
每天将5天前的数据从以上三个表中拷贝到历史数据表MAINTABLE_HIS/PARTDEVICE_HIS/DATATABLE_HIS中，以供查询，提高生产的效率

问题

生产节拍变慢
PARTDEVICE_HIS / DATATABLE_HIS 数据达到1亿多条

查找问题原因

PARTDEVICE_HIS / DATATABLE_HIS 中有大量重复数据
MAINTABLE / PARTDEVICE / DATATABLE中存在几个月前的数据
每天转存到历史数据表中的事件有问题

函数内容如下：

BEGIN
INSERT INTO datatablehis (
Barcode,
DeviceID,
DatiumName,
DatiumType,
DatiumValue,
DatiumTopLimit,
DatiumBottomLimit,
PartDeviceID
)SELECT
Barcode,
DeviceID,
DatiumName,
DatiumType,
DatiumValue,
DatiumTopLimit,
DatiumBottomLimit,
PartDeviceID
FROM
datatable
WHERE
Barcode in (
SELECT
Barcode
FROM
maintable
WHERE
Mfgtime <DATE_SUB(
CURRENT_DATE (),
INTERVAL 5 DAY
)
);
INSERT INTO partdevicehis (
Barcode,
DeviceID,
OperatorID,
StartTime,
EndTime,
Result,
DefectID
)SELECT
Barcode,
DeviceID,
OperatorID,
StartTime,
EndTime,
Result,
DefectID
Import
FROM
partdevice
WHERE
StartTime <
DATE_SUB(
CURRENT_DATE (),
INTERVAL 5 DAY
);

INSERT INTO maintablehis (
Barcode,
PartID,
ChuteBarcode,
SkinBarcode,
CustomerBarcode,
ProcessID,
CurrentProcess,
CncID,
MfgStatusID,
MfgResultID,
DeviceFailureReasonID,
MfgTime,
Import
)SELECT
Barcode,
PartID,
ChuteBarcode,
SkinBarcode,
CustomerBarcode,
ProcessID,
CurrentProcess,
CncID,
MfgStatusID,
MfgResultID,
DeviceFailureReasonID,
MfgTime,
Import
FROM
maintable
WHERE
MfgTime <
DATE_SUB(
CURRENT_DATE (),
INTERVAL 5 DAY
);

DELETE
FROM
datatable
WHERE
Barcode in (
SELECT
Barcode
FROM
maintable
WHERE
Mfgtime <DATE_SUB(
CURRENT_DATE (),
INTERVAL 5 DAY
)
);

DELETE
FROM
partdevice
WHERE
Barcode in (
SELECT
Barcode
FROM
maintable
WHERE
Mfgtime <DATE_SUB(
CURRENT_DATE (),
INTERVAL 5 DAY
)
);

DELETE
FROM
maintable
WHERE

Mfgtime <DATE_SUB(
CURRENT_DATE (),
INTERVAL 5 DAY
);

END

修改事件，增加日志功能：

BEGIN
DECLARE v_starttime DATETIME DEFAULT NOW();
DECLARE v_randno INTEGER DEFAULT FLOOR(RAND()*100001);
DECLARE r_code CHAR(5) DEFAULT ‘00000’;
DECLARE r_msg TEXT;
DECLARE v_error INTEGER;
INSERT INTO t_event_history (starttime,issuccess,randno) VALUES(v_starttime,0,v_randno);
BEGIN
#异常处理段
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
SET v_error = 1;
GET DIAGNOSTICS CONDITION 1 r_code = RETURNED_SQLSTATE , r_msg = MESSAGE_TEXT;
END;
#此处为实际调用的用户程序过程
CALL CopyDelete();
END;
UPDATE t_event_history SET endtime=NOW(),issuccess=1,duration=TIMESTAMPDIFF(SECOND,starttime,NOW()),
errormessage=CONCAT(‘error=’,r_code,’, message=’,r_msg),randno=NULL WHERE starttime=v_starttime AND randno=v_randno;
END

发现错误日志，在插入MAINTABLE_HIS时有重复记录，无法插入，因此，后面的删除也没有执行。使用的是当前时间，所以在删除的时候有滞后，导致在MAINTABLE表中有记录没有删除。

DEBUG

首先删除MAINTABLE_HIS中与MAINTABLE中重复的记录

DELETE FROM maintablehis
WHERE BARCODE IN
(SELECT BARCODE FROM maintable)

删除PARTDEVICE_HIS / DATATABLE_HIS中重复的记录，只保留一条记录

首先在PARTDEVICE_HIS / DATATABLE_HIS 上建立一个新的索引，提高速度

ALTER TABLE partdevicehis ADD INDEX BARCODE_DEVICEID (BARCODE(50),DEVICEID(20))
用时883秒

ALTER TABLE datatablehis ADD INDEX BARCODE_DEVICEID_DATIUMNAME (BARCODE(50),DEVICEID(20),DatiumName(20))
用时863秒

删除PARTDEVICE_HIS / DATATABLE_HIS中重复的数据

用时几个小时了

DELETE FROM partdevicehis
WHERE ID NOT IN
(SELECT DTC.MIN_ID FROM
(SELECT MIN(ID) AS MIN_ID FROM partdevicehis GROUP BY BARCODE,DeviceID) DTC )

DELETE FROM datatablehis
WHERE ID NOT IN
(SELECT DTC.MIN_ID FROM
(SELECT MIN(ID) AS MIN_ID FROM datatablehis GROUP BY BARCODE,DeviceID,DatiumName) DTC )

修改函数

BEGIN
START TRANSACTION;
set @N := (now());

INSERT INTO datatablehis (
Barcode,
DeviceID,
DatiumName,
DatiumType,
DatiumValue,
DatiumTopLimit,
DatiumBottomLimit,
PartDeviceID
)SELECT
Barcode,
DeviceID,
DatiumName,
DatiumType,
DatiumValue,
DatiumTopLimit,
DatiumBottomLimit,
PartDeviceID
FROM
datatable
WHERE
Barcode in (
SELECT
Barcode
FROM
maintable
WHERE
Mfgtime <DATE_SUB(
@N,
INTERVAL 30 DAY
)
);

DELETE FROM datatable
WHERE Barcode in (
SELECT Barcode FROM maintable
WHERE
Mfgtime <DATE_SUB(
@N,
INTERVAL 30 DAY
)
);

INSERT INTO partdevicehis (
Barcode,
DeviceID,
OperatorID,
StartTime,
EndTime,
Result,
DefectID
)SELECT
Barcode,
DeviceID,
OperatorID,
StartTime,
EndTime,
Result,
DefectID
Import
FROM
partdevice
WHERE
StartTime <
DATE_SUB(
@N,
INTERVAL 30 DAY
);

DELETE
FROM
partdevice
WHERE
Barcode in (
SELECT
Barcode
FROM
maintable
WHERE
Mfgtime <DATE_SUB(
@N,
INTERVAL 30 DAY
)
);

DELETE
FROM
maintable
WHERE

Mfgtime <DATE_SUB(
@N,
INTERVAL 30 DAY
);
COMMIT;
END

建立监控表

CREATE TABLE t_event_history (
id INT UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
dbname VARCHAR(128) NOT NULL DEFAULT ‘cat’,
eventname VARCHAR(128) NOT NULL DEFAULT ‘BakupCat’,
starttime DATETIME NOT NULL DEFAULT ‘2000-01-01 00:00:00’,
endtime DATETIME DEFAULT NULL,
issuccess INT(11) DEFAULT NULL,
duration INT(11) DEFAULT NULL,
errormessage VARCHAR(512) DEFAULT NULL,
randno INT(11) DEFAULT NULL
) ENGINE=INNODB DEFAULT CHARSET=utf8;

修改事件

DELIMITER $$

create DEFINER=root@% EVENT job_move_to_history ON SCHEDULE EVERY 1 DAY STARTS ‘2016-09-07 01:00:00’ ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
DECLARE v_starttime DATETIME DEFAULT NOW();
DECLARE v_randno INTEGER DEFAULT FLOOR(RAND()*100001);
DECLARE r_code CHAR(5) DEFAULT ‘00000’;
DECLARE r_msg TEXT;
DECLARE v_error INTEGER;
INSERT INTO t_event_history (starttime,issuccess,randno) VALUES(v_starttime,0,v_randno);
BEGIN
#异常处理段
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
SET v_error = 1;
GET DIAGNOSTICS CONDITION 1 r_code = RETURNED_SQLSTATE , r_msg = MESSAGE_TEXT;
END;
#此处为实际调用的用户程序过程
CALL COPYDELETE();
END;
UPDATE t_event_history SET endtime=NOW(),issuccess=1,duration=TIMESTAMPDIFF(SECOND,starttime,NOW()),
errormessage=CONCAT(‘error=’,r_code,’, message=’,r_msg),randno=NULL WHERE starttime=v_starttime AND randno=v_randno;
END$$