Cannot compile for debugging 6.3.341 Solved in 6.3.358
Posted: Thu 28 May 2015 05:06
(Because our IT person who forwarded to you my multi-issue email has moved on, I will re-post here individually, thanks.)
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".)
STATUS:
Your Move
P.S. It is very annoying having to copy images to my own personal website because your Bulletin Board has uploads disabled...
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...
.