-
Oct 23rd, 2014, 05:24 AM
#1
Thread Starter
PowerPoster
MySQL , Why this procedure not rolls back
i am having a Stored procedure which takes some values then does some operations , then inserts the data into another table
but if any error occurs then the procedure should suppose to roll back, but that's not happening here
orelse at when the procedure will not roll back ,
MySQL documentation states that the DDL statements can't be rolled back, but the bug was at the INSERT STATEMENT , why is this below failed to rollback the DELETE and INSERTS
any idea please
vb.net Code:
DROP PROCEDURE IF EXISTS `DataSplitter`; CREATE DEFINER = `root`@`localhost` PROCEDURE `DataSplitter`( ipModifiedDEGN SMALLINT, ipALKM_NON_LEAVE SMALLINT, ipALKM_LEAVE SMALLINT, ipRRA SMALLINT, ipBOR SMALLINT, ipNDA SMALLINT, ipNHA SMALLINT, ipRUN_KM SMALLINT, ipOFF1_KMS SMALLINT, ipOFF2_KMS SMALLINT, ipNON_RUN_KM SMALLINT, ipOSRA SMALLINT, ipPFnumber VARCHAR(20), ipPAY_PERIOD_V VARCHAR(10), ipDeo varchar(60)) BEGIN # used in front end class DataSplitter_SplitSupport # Returns data table -- Updaters variance DECLARE Success BIT DEFAULT 0 ; DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN SET Success = 0 ; ROLLBACK ; END ; DECLARE EXIT HANDLER FOR SQLWARNING BEGIN SET Success = 0; ROLLBACK ; END ; START TRANSACTION ; DROP TABLE IF EXISTS Splitter_Origin ; DROP TABLE if EXISTS Splitter_Modified ; CREATE TABLE Splitter_Origin ( CREW_ID_V varchar (15) , EMPLOYEE_NO varchar (15) , PF_CODE_N varchar (25) , PAY_PERIOD_V varchar (6) , MILEAGE_N int (11) , ALKM_NON_LEAVE int (11) , ALKM_LEAVE int (11) , RRA decimal (3) , BOR int (11) , NDA int (11) , NHA int (11) , SHUNT_COUNT int (11) , CREW_CADRE varchar (2) , CREWNAME varchar (30) , DUTY_HRS int (11) , RUN_KM int (11) , OFF1_KMS int (11) , OFF2_KMS int (11) , NON_RUN_KM int (11) , TRIP_COUNT int (11) , OSRA int (11) , TOTAL_OVERTIME int (11) , LOW_OT int (11) , HIGH_OT int (11) , OT_PERIOD_COUNT int (11) , DepotId smallint (6) ); -- INSERT SUM VALUE INSERT INTO Splitter_Origin ( SELECT crewregistry.CrisId , crewregistry.Designation as EMPLOYEE_NO , primaryholder.PF_CODE_N , primaryholder.PAY_PERIOD_V, SUM(primaryholder.MILEAGE_N) AS MILEAGE_N , SUM(primaryholder.ALKM_NON_LEAVE) AS ALKM_NON_LEAVE , SUM(primaryholder.ALKM_LEAVE) AS ALKM_LEAVE , SUM(primaryholder.RRA) AS RRA , SUM(primaryholder.BOR) AS BOR , SUM(primaryholder.NDA) AS NDA , SUM(primaryholder.NHA) AS NHA , SUM(primaryholder.SHUNT_COUNT) AS SHUNT_COUNT , primaryholder.CREW_CADRE , crewregistry.CrewName , SUM(primaryholder.DUTY_HRS) AS DUTY_HRS , SUM(primaryholder.RUN_KM) AS RUN_KM , SUM(primaryholder.OFF1_KMS) AS OFF1_KMS , SUM(primaryholder.OFF2_KMS) AS OFF2_KMS , SUM(primaryholder.NON_RUN_KM) AS NON_RUN_KM , SUM(primaryholder.TRIP_COUNT) AS TRIP_COUNT , SUM(primaryholder.OSRA) AS OSRA , SUM(primaryholder.TOTAL_OVERTIME) AS TOTAL_OVERTIME , SUM(primaryholder.LOW_OT) AS LOW_OT , SUM(primaryholder.HIGH_OT) AS HIGH_OT , SUM(primaryholder.OT_PERIOD_COUNT) AS OT_PERIOD_COUNT , crewregistry.DepotId FROM primaryholder JOIN crewregistry ON primaryholder.PF_CODE_N = crewregistry.PFnumber WHERE crewregistry.PFnumber = ipPFnumber AND primaryholder.PAY_PERIOD_V = ipPAY_PERIOD_V GROUP BY crewregistry.PFnumber , primaryholder.PAY_PERIOD_V ); -- create table Splitter_Deductedleft CREATE TABLE Splitter_Modified ( CREW_ID_V varchar (15) , EMPLOYEE_NO varchar (15) , PF_CODE_N varchar (25) , PAY_PERIOD_V varchar (6) , MILEAGE_N int (11) , ALKM_NON_LEAVE int (11) , ALKM_LEAVE int (11) , RRA decimal (3) , BOR int (11) , NDA int (11) , NHA int (11) , SHUNT_COUNT int (11) , CREW_CADRE varchar (2) , CREWNAME varchar (30) , DUTY_HRS int (11) , RUN_KM int (11) , OFF1_KMS int (11) , OFF2_KMS int (11) , NON_RUN_KM int (11) , TRIP_COUNT int (11) , OSRA int (11) , TOTAL_OVERTIME int (11) , LOW_OT int (11) , HIGH_OT int (11) , OT_PERIOD_COUNT int (11) , DepotId smallint (6) , Deo varchar(50) , ImportKey VARCHAR(50) , Ret DATETIME ); -- insert deducted origin degn values left over INSERT INTO Splitter_Modified ( SELECT splitter_origin.CREW_ID_V, splitter_origin.EMPLOYEE_NO , splitter_origin.PF_CODE_N , splitter_origin.PAY_PERIOD_V , splitter_origin.MILEAGE_N - ( ipRUN_KM + ipOFF1_KMS + ipOFF2_KMS + ipNON_RUN_KM + ipOSRA ) , splitter_origin.ALKM_NON_LEAVE - ipALKM_NON_LEAVE , splitter_origin.ALKM_LEAVE - ipALKM_LEAVE , splitter_origin.RRA - ipRRA , splitter_origin.BOR - ipBOR , splitter_origin.NDA - ipNDA , splitter_origin.NHA - ipNHA , splitter_origin.SHUNT_COUNT, splitter_origin.CREW_CADRE, splitter_origin.CREWNAME, splitter_origin.DUTY_HRS, splitter_origin.RUN_KM - ipRUN_KM , splitter_origin.OFF1_KMS - ipOFF1_KMS , splitter_origin.OFF2_KMS - ipOFF2_KMS , splitter_origin.NON_RUN_KM - ipNON_RUN_KM , splitter_origin.TRIP_COUNT, splitter_origin.OSRA - ipOSRA , splitter_origin.TOTAL_OVERTIME, splitter_origin.LOW_OT, splitter_origin.HIGH_OT, splitter_origin.OT_PERIOD_COUNT, splitter_origin.DepotId , ipDeo , CONCAT(splitter_origin.CREW_ID_V,"-",ipPAY_PERIOD_V,"-",splitter_origin.EMPLOYEE_NO,"-sp") as ImportKey , NULL FROM splitter_origin ) ; -- insert split designation VALUES INSERT INTO splitter_modified ( SELECT splitter_origin.CREW_ID_V, ipModifiedDEGN , splitter_origin.PF_CODE_N , splitter_origin.PAY_PERIOD_V , ( ipRUN_KM + ipOFF1_KMS + ipOFF2_KMS + ipNON_RUN_KM + ipOSRA ) , ipALKM_NON_LEAVE , ipALKM_LEAVE , ipRRA , ipBOR , ipNDA , ipNHA , splitter_origin.SHUNT_COUNT, splitter_origin.CREW_CADRE, splitter_origin.CREWNAME, splitter_origin.DUTY_HRS, ipRUN_KM , ipOFF1_KMS , ipOFF2_KMS , ipNON_RUN_KM , splitter_origin.TRIP_COUNT, ipOSRA , splitter_origin.TOTAL_OVERTIME, splitter_origin.LOW_OT, splitter_origin.HIGH_OT, splitter_origin.OT_PERIOD_COUNT, splitter_origin.DepotId , ipDeo , CONCAT(splitter_origin.CREW_ID_V,"-",ipPAY_PERIOD_V,"-",ipModifiedDEGN,"-sp") as ImportKey , NULL FROM splitter_origin ) ; -- Delete primaryholder values on PF and payperiod vars DELETE FROM primaryholder WHERE primaryholder.PF_CODE_N = ipPFnumber AND primaryholder.PAY_PERIOD_V = ipPAY_PERIOD_V ; -- INSERT MODIFIED SPLIT VALUES TO primaryholder INSERT INTO primaryholder (SELECT * FROM Splitter_Modified) ; -- BACKUP monitoring INSERT INTO primaryholdersplitmonitor (SELECT splitter_origin.CREW_ID_V, splitter_origin.EMPLOYEE_NO , splitter_origin.PF_CODE_N , splitter_origin.PAY_PERIOD_V , splitter_origin.MILEAGE_N , splitter_origin.ALKM_NON_LEAVE , splitter_origin.ALKM_LEAVE , splitter_origin.RRA , splitter_origin.BOR , splitter_origin.NDA , splitter_origin.NHA , splitter_origin.SHUNT_COUNT, splitter_origin.CREW_CADRE, splitter_origin.CREWNAME, splitter_origin.DUTY_HRS, splitter_origin.RUN_KM , splitter_origin.OFF1_KMS , splitter_origin.OFF2_KMS , splitter_origin.NON_RUN_KM , splitter_origin.TRIP_COUNT, splitter_origin.OSRA , splitter_origin.TOTAL_OVERTIME, splitter_origin.LOW_OT, splitter_origin.HIGH_OT, splitter_origin.OT_PERIOD_COUNT, splitter_origin.DepotId , ipDeo , NOW() FROM splitter_origin ) ; INSERT INTO primaryholdersplitmonitor(SELECT splitter_modified.CREW_ID_V, splitter_modified.EMPLOYEE_NO, splitter_modified.PF_CODE_N, splitter_modified.PAY_PERIOD_V, splitter_modified.MILEAGE_N, splitter_modified.ALKM_NON_LEAVE, splitter_modified.ALKM_LEAVE, splitter_modified.RRA, splitter_modified.BOR, splitter_modified.NDA, splitter_modified.NHA, splitter_modified.SHUNT_COUNT, splitter_modified.CREW_CADRE, splitter_modified.CREWNAME, splitter_modified.DUTY_HRS, splitter_modified.RUN_KM, splitter_modified.OFF1_KMS, splitter_modified.OFF2_KMS, splitter_modified.NON_RUN_KM, splitter_modified.TRIP_COUNT, splitter_modified.OSRA, splitter_modified.TOTAL_OVERTIME, splitter_modified.LOW_OT, splitter_modified.HIGH_OT, splitter_modified.OT_PERIOD_COUNT, splitter_modified.DepotId, ipdeo, NOW() FROM splitter_modified ) ; SET Success = 1 ; COMMIT ; SELECT Success ; END;
Last edited by make me rain; Oct 23rd, 2014 at 05:35 AM.
Reason: with PHP tag code appeared a continues line
-
Oct 23rd, 2014, 03:21 PM
#2
Addicted Member
Re: MySQL , Why this procedure not rolls back
I think you need to declare your exit handlers inside of the transaction. Declare them after the start transaction declaration, currently they're declared before you declare the start transaction. When I get some free time I'll take a look too.
-
Oct 23rd, 2014, 10:18 PM
#3
Thread Starter
PowerPoster
Re: MySQL , Why this procedure not rolls back
thanks for reply smen
MySQL wont allow any DECLARE statement within START TRANSACTION
-
Oct 23rd, 2014, 10:56 PM
#4
Re: MySQL , Why this procedure not rolls back
Ahh.... I wonder if your handler for sqlwarning is overwriting sqlexception.
According to the documentation:
SQLWARNING is shorthand for the class of SQLSTATE values that begin with '01'.
SQLEXCEPTION is shorthand for the class of SQLSTATE values that do not begin with '00', '01', or '02'.
http://dev.mysql.com/doc/refman/5.1/...e-handler.html
So it would seem that SQLEXCEPTION includes SQLWARNINGS, so you should have only one or the other, but not both. *shrug* Just a guess. If you take out your handler for SQLWARNING, leaving only the SQLEXCEPTION one, does it work then?
-tg
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|