2. DbForge Studio for MySQL 6.3.341 appears to have a serious bug where it will not compile a procedure or trigger for debugging / single stepping.
Had to downgrade to 6.0.151 as our most recent previous version..
I was not prepared to attempt to run a trigger that was wrongly compiled, as it could do serious damage to our very precious data.
You Replied:
Could you please provide us the CREATE definition of the object you were experiencing the issue with and a screenshot of any possible error message?
CREATE DEFINITION:
NOTE that this is shown as a stored procedure. The actual After Insert Trigger code is from body:BEGIN onward.
Neither compiles for debug. Both give the same compile error with 6.3.341, but compile perfectly, and can be debugged on 6.0.151. (Because you can't save a backup copy of a trigger, I "save" trigger code in a spare procedure while editing and working on the trigger, and then copy and paste just the trigger portion back into the appropriate trigger to make it "live".)
Code: Select all
CREATE DEFINER = 'root'@'%'
PROCEDURE YesDb.SPC_Data_ai_bak()
body:BEGIN
DECLARE stnn TEXT;
DECLARE brdid INT;
DECLARE brdsn15 TEXT;
DECLARE panid INT;
DECLARE woid INT;
DECLARE varid INT;
DECLARE vVarName TEXT;
DECLARE tsid INT;
DECLARE tsseq INT;
DECLARE stationid INT;
DECLARE stationseq INT;
DECLARE vMachTID int;
DECLARE vOperatorID INT;
DECLARE breason TEXT;
DECLARE bstat INT;
DECLARE stamp TEXT;
DECLARE brdsn TEXT;
DECLARE vOperatorName TEXT;
DECLARE brdcnt INT;
DECLARE brdfail INT;
DECLARE brdsuffix TEXT;
Declare pansn text;
Declare SN text;
DECLARE panno int;
DECLARE brdno text;
DECLARE partno text;
DECLARE tpperpp int;
DECLARE bdsperpan int;
DECLARE panincr int;
DECLARE wosn text;
DECLARE assy text;
DECLARE cmd CHAR(255); # sys_exec command
DECLARE rslt int; # sys_exec result code
# LEAVE body;
SET SN = upper(IFNULL(NEW.SN, 0));
SET stamp = IFNULL(NEW.IDate, 0);
SET stnn = upper(IFNULL(NEW.SystemID, 0));
SET brdcnt = IFNULL(NEW.BrdCount, 0);
SET vOperatorName = upper(IFNULL(NEW.Inspector, 0));
SET breason = IFNULL(NEW.DefectCode, 0);
SET brdfail = IFNULL(NEW.BrdFail, 0);
SET assy = IFNULL(NEW.Assy, 0);
# only process AOI records where the Board Count is 1
# this is the summary line of which we get only one per board
# FOR AFTER UPDATE, only process AOI records where the Board Count HAS BEEN CHANGED TO 1
# (e.g. by the KyDB AutoExport_ai.php trigger
# This is the summary line of which we get only one per board
# THIS NEXT LINE IS THE ONLY THING DIFFERENT FROM THE SPC_Data_ai CODE
# IF OLD.BrdCount = 0 AND NEW.BrdCount = 1 THEN # SPC_Data_au
# only on a change of board count do we save data to pippa,ResultSets
# THIS NEXT LINE IS THE ONLY THING DIFFERENT FROM THE SPC_Data_ai CODE
# IF OLD.BrdCount = 0 AND NEW.BrdCount = 1 THEN # SPC_Data_au
IF NEW.BrdCount = 1 THEN # SPC_Data_ai
IF NEW.BrdFail = 1 THEN
SELECT
DefectCode.Description
INTO
breason
FROM
DefectCode
WHERE
(DefectCode.Code = NEW.DefectCode);
SET bstat = 3;
ELSE
SET breason = "Pass";
SET bstat = 1;
END IF;
SET brdsuffix = RIGHT(SN, LOCATE('-', REVERSE(SN)) - 1);
IF LENGTH(brdsuffix)= 0 THEN
# set it up as board 1
SET SN = CONCAT(SN, "-1");
SET brdsuffix = "001";
END IF;
SET brdno = 1*brdsuffix;
SET pansn = LEFT(SN, LOCATE('-', SN) - 1);
SET wosn = LEFT(pansn,LENGTH(pansn)-4);
IF LENGTH(wosn) = 8 THEN
SET pansn = CONCAT('0',pansn); # add 0 to make new known length for pippa
SET wosn = CONCAT('0',wosn);
END IF;
set panno = SUBSTR(pansn, -4, 4);
# find Test Panels per Production panel, and Boards per Tester Panel
# from Panel SN we want Part Number
SELECT Parts.PartNo
INTO partno
FROM ((pippa.WOrders INNER JOIN pippa.Variants ON WOrders.VarID = Variants.VarID) INNER JOIN pippa.Parts ON Variants.PartID = Parts.PartID) INNER JOIN pippa.Panels ON WOrders.WOrderID = Panels.WOrderID
WHERE (((Panels.PanelSerialNo)=pansn));
IF partno IS NOT NULL THEN # did we get a good part number?
# yes - look up the panel layout
SELECT WOMDb.WOMTstPnlsPerSMDPnl, WOMDb.WOMPartBdsPerPan
INTO tpperpp, bdsperpan
FROM pippa.WOMDb
WHERE WOMDb.WOMPartNo=partno;
ELSE
# no - invent some harmless but obviously wrong numbers
SET tpperpp=1;
SET bdsperpan=1;
END IF;
IF tpperpp > 1 THEN # translate board numbers as needed
# more than 1 Test Panels per Production Panel, we may need to adjust board and panel nos
# Where there are 2 or more testing panels per production panel, the reported board numbers just continue beyond
# the number of boards per panel. eg if bpp=4, we can get boardnos 5, 6, 7, 8 which belong to the next panel number
SET panincr = brdno DIV bdsperpan;
SET brdno = brdno MOD bdsperpan;
SET panno = panno + panincr; # use te panel increment to correct the panel no
END IF; # else leave boar and panel numbers un touched
# now assemble real board serial number
SET brdsn = CONCAT(wosn, RIGHT(CONCAT('0000', panno), 4), RIGHT(CONCAT('000', brdno), 3));
SET brdsn15 = RIGHT(brdsn,15); # 15 chars long for hella db wothout the leading 0
SELECT
BoardID
INTO
brdid
FROM
pippa.Boards
WHERE
BoardSerialNo = brdsn;
IF brdid IS NULL THEN
INSERT
INTO hella.aoi (StnName, BoardSerial, AoiTStamp, AoiStatus, AoiReason)
VALUES (stnn, brdsn15, stamp, bstat, breason);
ELSE
SELECT
OperatorID
INTO
vOperatorID
FROM
pippa.Operators
WHERE
upper(Operators.OperatorName) = upper(vOperatorName);
IF vOperatorID IS NULL THEN
SET vOperatorID := 1;
END IF;
SELECT
StnID, MachTID
INTO
stationid, vMachTID
FROM
pippa.Stations
WHERE
Stations.StnName = stnn;
IF stationid IS NULL THEN
SET stationid := 1;
END IF;
SELECT
PanelID
INTO
panid
FROM
pippa.Boards
WHERE
BoardID = brdid;
IF panid IS NULL THEN
INSERT
INTO pippa.Logs (LogType, LogMsg)
VALUES
("Error", IFNULL(CONCAT(stnn, ": YesDb.SPC_Data_ai Error 5 - Board SerNo ", brdsn, " does not belong to a panel"), 5));
ELSE
SELECT
pippa.WOrders.WOrderID
INTO
woid
FROM
pippa.Panels INNER JOIN pippa.WOrders ON pippa.Panels.WOrderID = pippa.WOrders.WOrderID
WHERE
(Panels.PanelID = panid);
IF woid IS NULL THEN
INSERT
INTO pippa.Logs (LogType, LogMsg)
VALUES
("Error", IFNULL(CONCAT(stnn, ": YesDb.SPC_Data_ai Error 7 - Board SerNo ", brdsn, " not part of a known current order"), 7));
ELSE
SELECT
Variants.VarID, Variants.VarName
INTO
varid, vVarName
FROM
pippa.Variants INNER JOIN pippa.WOrders ON Variants.VarID = WOrders.VarID
WHERE
(WOrders.WOrderID = woid);
IF varid IS NULL THEN
INSERT
INTO pippa.Logs (LogType, LogMsg)
VALUES
("Error", IFNULL(CONCAT(stnn, ": YesDb.SPC_Data_ai Error 9 - Board SerNo ", brdsn, " Work Order WOrderID=", woid, " has no matching Variant"), 9));
ELSE
# we have a valid variant
SET tsseq = 50; # inspection
# Do we have an approved Test Set to hang this result on?
# we need to treat TOP and BOTTOM inspections from the AOI as different test sets.
# The rule from Tim, Sebastian, confirmed by Karthik on 2015-04-08 is that where there is a top and a bottom recipe for the AOI, the BOTTOM one ALWAYS CONTAINS the word BOTTOM
# Thus we look for the word 'BOTTOM' in the Assembly info, and if it is there, we make sure we use and/or create a TestSet with BOTTOM in its name.
IF LOCATE("BOTTOM",assy) = 0 THEN
# not found, is a TOP or an only, so use the simple TSet name
SELECT
TestSets.TSetID
INTO
tsid
FROM
pippa.TestSets
WHERE
(((TestSets.StnId = stationid) AND ((TestSets.VarID) = varid) AND ((TestSets.TSetSeq) = tsseq)
AND ((TestSets.TSetApproved) IS NOT NULL) AND ((TestSets.TSetRetired) IS NULL)))
LIMIT 1;
IF tsid IS NULL THEN
# changed 2015-03-19 to create a matching TestSet for AOInspection
/* Only need to create an AOI Inspection TSet if none exist already for this variant of the part at this Station */
INSERT INTO pippa.TestSets
(StnID,MachTID,VarID,TSetName,
TSetDesc,
TSetVersionNo,TSetSeq,TSetTacitPass,CRsnID,
CTStamp,TSetApproved,TSetApprover,
TSetRetired,TSetRetirer)
VALUES /* Must choose StnID and MachTid because if Foreign Keys. Ignore them in later processing since this is a generic */
(stationid /* StnID */,vMachTID /* MachTID */,varid /* VarID */,CONCAT('Post Sold AOI ',partno,' ',vVarName) /* TSetName */,
CONCAT(partno,' ', vVarName, ' Post Solder AOI') /* TSetDesc */,
'0.0' /* TSetVersionNo */,tsseq /* TSetSeq */,0 /* TSetTacitPass */,1 /* CRsnID */,
NOW() /* CTStamp */,NOW() /* TSetApproved */,16 /* TSetApprover=Auto */,
NULL /* TSetRetired */,NULL /* TSetRetirer */);
SET tsid = LAST_INSERT_ID(); # get last insert ID as test set id to use below
# 2015-04-08 # Now retire any and all Manual Inspection Test Sets for this Variant
# UPDATE `pippa`.`TestSets`
# SET `TSetRetired` = NOW(), `TSetRetirer` = '12'
# WHERE VarID = varid AND StnID = 28 AND TSetSeq=tsseq AND LOCATE('AOI',TSetName)=0; # post solder manual inspection, excluding any AOIs
#
# # alert Jeremy that TSet(s) have been retired
# INSERT INTO pippa.EMails
# (EMFrom, EMTo, EMSubj, EMBody)
# VALUES
# ('SPC_Data_ai', '[email protected]',
# CONCAT('Non-AOI Test Set(s) retired by YesDb.SPC_Data_ai triggerWHERE VarID=',varid,' AND StnID=28 AND TSetSeq=',tsseq,
# ' by BoardSN=',brdsn, ' at ', stnn, ', IDate=',stamp) );
END IF;
ELSE
# 'BOTTOM' found in recipe name, So look for and create a test set with 'AOI BOTTOM' in the name
SELECT
TestSets.TSetID
INTO
tsid
FROM
pippa.TestSets
WHERE
(TestSets.StnId = stationid) AND (TestSets.VarID = varid) AND (TestSets.TSetSeq = tsseq)
AND (TestSets.TSetApproved IS NOT NULL) AND (TestSets.TSetRetired IS NULL)
AND (TestSets.TSetName LIKE '%AOI BOTTOM%')
LIMIT 1;
IF tsid IS NULL THEN
# changed 2015-03-19 to create a matching TestSet for AOInspection
/* Only need to create an AOI Inspection TSet if none exist already for this variant of the part at this Station */
INSERT INTO pippa.TestSets
(StnID,MachTID,VarID,TSetName,
TSetDesc,
TSetVersionNo,TSetSeq,TSetTacitPass,CRsnID,
CTStamp,TSetApproved,TSetApprover,
TSetRetired,TSetRetirer)
VALUES /* Must choose StnID and MachTid because if Foreign Keys. Ignore them in later processing since this is a generic */
(stationid /* StnID */,vMachTID /* MachTID */,varid /* VarID */,CONCAT('Post Sold AOI BOTTOM ',partno,' ',vVarName) /* TSetName */,
CONCAT(partno,' ', vVarName, ' Post Solder AOI BOTTOM') /* TSetDesc */,
'0.0' /* TSetVersionNo */,tsseq /* TSetSeq */,0 /* TSetTacitPass */,1 /* CRsnID */,
NOW() /* CTStamp */,NOW() /* TSetApproved */,16 /* TSetApprover=Auto */,
NULL /* TSetRetired */,NULL /* TSetRetirer */);
SET tsid = LAST_INSERT_ID(); # get last insert ID as test set id to use below
# 2015-04-08 # Now retire any and all Manual Inspection Test Sets for this Variant
# UPDATE `pippa`.`TestSets`
# SET `TSetRetired` = NOW(), `TSetRetirer` = '12'
# WHERE VarID = varid AND StnID = 28 AND TSetSeq=tsseq AND LOCATE('AOI',TSetName)=0; # post solder manual inspection, excluding any AOIs
#
# # alert Jeremy that TSet(s) have been retired
# INSERT INTO pippa.EMails
# (EMFrom, EMTo, EMSubj, EMBody)
# VALUES
# ('SPC_Data_ai', '[email protected]',
# CONCAT('Non-AOI Test Set(s) retired by YesDb.SPC_Data_ai triggerWHERE VarID=',varid,' AND StnID=28 AND TSetSeq=',tsseq,
# ' by BoardSN=',brdsn, ' at ', stnn, ', IDate=',stamp) );
END IF;
END IF;
# and now save a result set for the test set found or created
# test set found - create a result set
# create a result set entry for the relevant AOI
REPLACE
INTO pippa.ResultSets (StnID, TSetID, BoardID, WOrderID, TSetSeq, RSetTStamp, RSetStatus, RSetReason, OperatorID)
VALUES
(stationid, tsid, brdid, woid, tsseq, stamp, bstat, breason, vOperatorID);
# update where this board has now been seen - i.e. here
UPDATE
pippa.Boards
SET
StnID = stationid, BoardTStamp = stamp
WHERE
BoardID = brdid;
# Set any result sets from further down the line to out of sequence (if any - note 3rd condition)
UPDATE
pippa.ResultSets
SET
RSetStatus = 4, RSetReason = concat("OOSeq: ", RSetReason)
WHERE
(WOrderID = woid) AND
(BoardID = brdid) AND
(TSetSeq > tsseq);
# run the yesdb to gainseeker php script
# launch as new child process so the insert completes immediately. i.e. end the command with <space><amprsand>
# CAN'T DO THAT - IT TAKES ~ 250 MSEC TO LAUNCH - F A R TOO SLOW. uSE A CRON TRIGGER EVERY MINUTE INSTEAD
# SET cmd=CONCAT('(php -f /var/www/yesdb_to_gainseeker.php ',NEW.SPC_ID, ' 2>&1 | mail -s "KyDB Autoexport_ai" [email protected] ) &');
# SET cmd=CONCAT('(php -f /var/www/yesdb_to_gainseeker.php ',NEW.SPC_ID, ' 2>&1 ) &');
# SET rslt = sys_exec(cmd); # result code
END IF;
END IF;
END IF;
END IF;
END IF;
END
Your Move
P.S. It is very annoying having to copy images to my own personal website because your Bulletin Board has uploads disabled...
.