mysql 修复函数表_MYSQL数据库一个BUG的修复

MYSQL数据库一个BUG的修复

环境

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

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

CURRENT_DATE (),

INTERVAL 5 DAY

)

);

DELETE

FROM

partdevice

WHERE

Barcode in (

SELECT

Barcode

FROM

maintable

WHERE

Mfgtime

CURRENT_DATE (),

INTERVAL 5 DAY

)

);

DELETE

FROM

maintable

WHERE

Mfgtime

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 partdevicehis 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

@N,

INTERVAL 30 DAY

)

);

DELETE FROM datatable

WHERE Barcode in (

SELECT Barcode FROM maintable

WHERE

Mfgtime

@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

@N,

INTERVAL 30 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(

@N,

INTERVAL 30 DAY

);

DELETE

FROM

maintable

WHERE

Mfgtime

@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$$

DELIMITER ;

通过查询t_event_history表，我们就知道event何时执行，执行是否成功，执行时长，出错时的错误信息，为管理我们日常调度计划提供很大方便。