Results 1 to 4 of 4

Thread: MySQL , Why this procedure not rolls back

  1. #1

    Thread Starter
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india/Hubli
    Posts
    2,208

    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:
    1. DROP PROCEDURE IF EXISTS `DataSplitter`;
    2.  
    3. CREATE DEFINER = `root`@`localhost` PROCEDURE `DataSplitter`(
    4. ipModifiedDEGN SMALLINT, ipALKM_NON_LEAVE SMALLINT, ipALKM_LEAVE SMALLINT, ipRRA SMALLINT,
    5. ipBOR SMALLINT, ipNDA SMALLINT, ipNHA SMALLINT, ipRUN_KM SMALLINT, ipOFF1_KMS SMALLINT, ipOFF2_KMS SMALLINT, ipNON_RUN_KM SMALLINT, ipOSRA SMALLINT,
    6.  ipPFnumber VARCHAR(20), ipPAY_PERIOD_V VARCHAR(10), ipDeo varchar(60))
    7.  
    8.  
    9. BEGIN
    10.    # used in front end class DataSplitter_SplitSupport
    11.   # Returns data table
    12.  
    13.  
    14. -- Updaters variance
    15.  
    16.  
    17.  
    18.  
    19. DECLARE Success BIT DEFAULT 0 ;
    20.  
    21.  
    22.  
    23.  
    24.   DECLARE EXIT HANDLER FOR SQLEXCEPTION  
    25.     BEGIN
    26.   SET Success = 0 ;
    27.     ROLLBACK ;
    28.     END ;
    29.  
    30.  
    31.   DECLARE EXIT HANDLER FOR SQLWARNING  
    32.     BEGIN
    33.     SET Success = 0;
    34.     ROLLBACK ;
    35.     END ;
    36.  
    37.   START TRANSACTION ;
    38. DROP TABLE IF EXISTS Splitter_Origin ;
    39. DROP TABLE if EXISTS Splitter_Modified ;
    40.  
    41.  
    42. CREATE  TABLE Splitter_Origin (
    43. CREW_ID_V    varchar    (15) ,
    44. EMPLOYEE_NO    varchar    (15) ,
    45. PF_CODE_N    varchar    (25) ,
    46. PAY_PERIOD_V    varchar    (6) ,
    47. MILEAGE_N    int    (11) ,
    48. ALKM_NON_LEAVE    int    (11) ,
    49. ALKM_LEAVE    int    (11) ,
    50. RRA    decimal    (3) ,
    51. BOR    int    (11) ,
    52. NDA    int    (11) ,
    53. NHA    int    (11) ,
    54. SHUNT_COUNT    int    (11) ,
    55. CREW_CADRE    varchar    (2) ,
    56. CREWNAME    varchar    (30) ,
    57. DUTY_HRS    int    (11) ,
    58. RUN_KM    int    (11) ,
    59. OFF1_KMS    int    (11) ,
    60. OFF2_KMS    int    (11) ,
    61. NON_RUN_KM    int    (11) ,
    62. TRIP_COUNT    int    (11) ,
    63. OSRA    int    (11) ,
    64. TOTAL_OVERTIME    int    (11) ,
    65. LOW_OT    int    (11) ,
    66. HIGH_OT    int    (11) ,
    67. OT_PERIOD_COUNT    int    (11) ,
    68. DepotId    smallint    (6)
    69.  );
    70.  
    71.  
    72. -- INSERT SUM VALUE
    73. INSERT INTO Splitter_Origin (
    74. SELECT
    75. crewregistry.CrisId  ,
    76. crewregistry.Designation as EMPLOYEE_NO  ,
    77. primaryholder.PF_CODE_N ,
    78. primaryholder.PAY_PERIOD_V,
    79. SUM(primaryholder.MILEAGE_N) AS MILEAGE_N ,
    80. SUM(primaryholder.ALKM_NON_LEAVE) AS ALKM_NON_LEAVE ,
    81. SUM(primaryholder.ALKM_LEAVE) AS ALKM_LEAVE ,
    82. SUM(primaryholder.RRA) AS RRA ,
    83. SUM(primaryholder.BOR) AS BOR ,
    84. SUM(primaryholder.NDA) AS NDA ,
    85. SUM(primaryholder.NHA) AS NHA ,
    86. SUM(primaryholder.SHUNT_COUNT) AS SHUNT_COUNT ,
    87. primaryholder.CREW_CADRE ,
    88. crewregistry.CrewName ,
    89. SUM(primaryholder.DUTY_HRS) AS DUTY_HRS  ,
    90. SUM(primaryholder.RUN_KM) AS RUN_KM  ,
    91. SUM(primaryholder.OFF1_KMS) AS OFF1_KMS ,
    92. SUM(primaryholder.OFF2_KMS) AS OFF2_KMS ,
    93. SUM(primaryholder.NON_RUN_KM) AS NON_RUN_KM ,
    94. SUM(primaryholder.TRIP_COUNT) AS TRIP_COUNT ,
    95. SUM(primaryholder.OSRA) AS OSRA ,
    96. SUM(primaryholder.TOTAL_OVERTIME) AS TOTAL_OVERTIME ,
    97. SUM(primaryholder.LOW_OT) AS LOW_OT ,
    98. SUM(primaryholder.HIGH_OT) AS HIGH_OT ,
    99. SUM(primaryholder.OT_PERIOD_COUNT) AS OT_PERIOD_COUNT ,
    100. crewregistry.DepotId
    101. FROM
    102. primaryholder
    103. JOIN
    104. crewregistry ON primaryholder.PF_CODE_N = crewregistry.PFnumber
    105. WHERE crewregistry.PFnumber = ipPFnumber  AND primaryholder.PAY_PERIOD_V = ipPAY_PERIOD_V
    106. GROUP BY crewregistry.PFnumber , primaryholder.PAY_PERIOD_V );
    107.  
    108.  
    109. -- create table Splitter_Deductedleft
    110. CREATE  TABLE Splitter_Modified (
    111. CREW_ID_V    varchar    (15) ,
    112. EMPLOYEE_NO    varchar    (15) ,
    113. PF_CODE_N    varchar    (25) ,
    114. PAY_PERIOD_V    varchar    (6) ,
    115. MILEAGE_N    int    (11) ,
    116. ALKM_NON_LEAVE    int    (11) ,
    117. ALKM_LEAVE    int    (11) ,
    118. RRA    decimal    (3) ,
    119. BOR    int    (11) ,
    120. NDA    int    (11) ,
    121. NHA    int    (11) ,
    122. SHUNT_COUNT    int    (11) ,
    123. CREW_CADRE    varchar    (2) ,
    124. CREWNAME    varchar    (30) ,
    125. DUTY_HRS    int    (11) ,
    126. RUN_KM    int    (11) ,
    127. OFF1_KMS    int    (11) ,
    128. OFF2_KMS    int    (11) ,
    129. NON_RUN_KM    int    (11) ,
    130. TRIP_COUNT    int    (11) ,
    131. OSRA    int    (11) ,
    132. TOTAL_OVERTIME    int    (11) ,
    133. LOW_OT    int    (11) ,
    134. HIGH_OT    int    (11) ,
    135. OT_PERIOD_COUNT    int    (11) ,
    136. DepotId    smallint    (6) ,
    137. Deo varchar(50) ,
    138. ImportKey VARCHAR(50) ,
    139. Ret DATETIME
    140.  );
    141.  
    142.  
    143. -- insert deducted origin degn values left over
    144. INSERT INTO Splitter_Modified (
    145. SELECT
    146. splitter_origin.CREW_ID_V,
    147. splitter_origin.EMPLOYEE_NO ,
    148. splitter_origin.PF_CODE_N  ,
    149. splitter_origin.PAY_PERIOD_V ,
    150. splitter_origin.MILEAGE_N - ( ipRUN_KM + ipOFF1_KMS + ipOFF2_KMS + ipNON_RUN_KM + ipOSRA ) ,
    151. splitter_origin.ALKM_NON_LEAVE - ipALKM_NON_LEAVE ,
    152. splitter_origin.ALKM_LEAVE - ipALKM_LEAVE ,
    153. splitter_origin.RRA - ipRRA ,
    154. splitter_origin.BOR - ipBOR ,
    155. splitter_origin.NDA - ipNDA ,
    156. splitter_origin.NHA - ipNHA ,
    157. splitter_origin.SHUNT_COUNT,
    158. splitter_origin.CREW_CADRE,
    159. splitter_origin.CREWNAME,
    160. splitter_origin.DUTY_HRS,
    161. splitter_origin.RUN_KM - ipRUN_KM ,
    162. splitter_origin.OFF1_KMS - ipOFF1_KMS ,
    163. splitter_origin.OFF2_KMS - ipOFF2_KMS ,
    164. splitter_origin.NON_RUN_KM - ipNON_RUN_KM ,
    165. splitter_origin.TRIP_COUNT,
    166. splitter_origin.OSRA - ipOSRA ,
    167. splitter_origin.TOTAL_OVERTIME,
    168. splitter_origin.LOW_OT,
    169. splitter_origin.HIGH_OT,
    170. splitter_origin.OT_PERIOD_COUNT,
    171. splitter_origin.DepotId ,
    172. ipDeo ,
    173. CONCAT(splitter_origin.CREW_ID_V,"-",ipPAY_PERIOD_V,"-",splitter_origin.EMPLOYEE_NO,"-sp") as ImportKey ,
    174. NULL
    175. FROM
    176. splitter_origin ) ;
    177.  
    178.  
    179. -- insert split designation VALUES
    180.  
    181.  
    182. INSERT INTO splitter_modified (
    183. SELECT
    184. splitter_origin.CREW_ID_V,
    185. ipModifiedDEGN ,
    186. splitter_origin.PF_CODE_N  ,
    187. splitter_origin.PAY_PERIOD_V ,
    188. ( ipRUN_KM + ipOFF1_KMS + ipOFF2_KMS + ipNON_RUN_KM + ipOSRA ) ,
    189.  ipALKM_NON_LEAVE ,
    190. ipALKM_LEAVE ,
    191. ipRRA ,
    192. ipBOR ,
    193.  ipNDA ,
    194. ipNHA ,
    195. splitter_origin.SHUNT_COUNT,
    196. splitter_origin.CREW_CADRE,
    197. splitter_origin.CREWNAME,
    198. splitter_origin.DUTY_HRS,
    199.  ipRUN_KM ,
    200. ipOFF1_KMS ,
    201. ipOFF2_KMS ,
    202. ipNON_RUN_KM ,
    203. splitter_origin.TRIP_COUNT,
    204.  ipOSRA ,
    205. splitter_origin.TOTAL_OVERTIME,
    206. splitter_origin.LOW_OT,
    207. splitter_origin.HIGH_OT,
    208. splitter_origin.OT_PERIOD_COUNT,
    209. splitter_origin.DepotId ,
    210. ipDeo ,
    211. CONCAT(splitter_origin.CREW_ID_V,"-",ipPAY_PERIOD_V,"-",ipModifiedDEGN,"-sp") as ImportKey ,
    212. NULL
    213. FROM
    214. splitter_origin ) ;
    215.  
    216.  
    217. -- Delete primaryholder values on PF and payperiod vars
    218. DELETE FROM primaryholder WHERE primaryholder.PF_CODE_N = ipPFnumber AND primaryholder.PAY_PERIOD_V =  ipPAY_PERIOD_V ;
    219.  
    220.  
    221. -- INSERT MODIFIED SPLIT VALUES TO primaryholder
    222. INSERT INTO primaryholder (SELECT * FROM Splitter_Modified) ;
    223.  
    224.  
    225. -- BACKUP monitoring
    226. INSERT INTO primaryholdersplitmonitor (SELECT
    227. splitter_origin.CREW_ID_V,
    228. splitter_origin.EMPLOYEE_NO ,
    229. splitter_origin.PF_CODE_N  ,
    230. splitter_origin.PAY_PERIOD_V ,
    231. splitter_origin.MILEAGE_N  ,
    232. splitter_origin.ALKM_NON_LEAVE ,
    233. splitter_origin.ALKM_LEAVE ,
    234. splitter_origin.RRA ,
    235. splitter_origin.BOR ,
    236. splitter_origin.NDA ,
    237. splitter_origin.NHA  ,
    238. splitter_origin.SHUNT_COUNT,
    239. splitter_origin.CREW_CADRE,
    240. splitter_origin.CREWNAME,
    241. splitter_origin.DUTY_HRS,
    242. splitter_origin.RUN_KM  ,
    243. splitter_origin.OFF1_KMS  ,
    244. splitter_origin.OFF2_KMS  ,
    245. splitter_origin.NON_RUN_KM  ,
    246. splitter_origin.TRIP_COUNT,
    247. splitter_origin.OSRA  ,
    248. splitter_origin.TOTAL_OVERTIME,
    249. splitter_origin.LOW_OT,
    250. splitter_origin.HIGH_OT,
    251. splitter_origin.OT_PERIOD_COUNT,
    252. splitter_origin.DepotId ,
    253. ipDeo ,
    254. NOW()
    255. FROM
    256. splitter_origin ) ;
    257.  
    258.  
    259. INSERT INTO primaryholdersplitmonitor(SELECT
    260. splitter_modified.CREW_ID_V,
    261. splitter_modified.EMPLOYEE_NO,
    262. splitter_modified.PF_CODE_N,
    263. splitter_modified.PAY_PERIOD_V,
    264. splitter_modified.MILEAGE_N,
    265. splitter_modified.ALKM_NON_LEAVE,
    266. splitter_modified.ALKM_LEAVE,
    267. splitter_modified.RRA,
    268. splitter_modified.BOR,
    269. splitter_modified.NDA,
    270. splitter_modified.NHA,
    271. splitter_modified.SHUNT_COUNT,
    272. splitter_modified.CREW_CADRE,
    273. splitter_modified.CREWNAME,
    274. splitter_modified.DUTY_HRS,
    275. splitter_modified.RUN_KM,
    276. splitter_modified.OFF1_KMS,
    277. splitter_modified.OFF2_KMS,
    278. splitter_modified.NON_RUN_KM,
    279. splitter_modified.TRIP_COUNT,
    280. splitter_modified.OSRA,
    281. splitter_modified.TOTAL_OVERTIME,
    282. splitter_modified.LOW_OT,
    283. splitter_modified.HIGH_OT,
    284. splitter_modified.OT_PERIOD_COUNT,
    285. splitter_modified.DepotId,
    286. ipdeo,
    287. NOW()
    288. FROM
    289. splitter_modified
    290. ) ;
    291.  
    292.  
    293.  
    294.  SET Success = 1 ;
    295.  
    296.  
    297. COMMIT ;
    298.  
    299.  
    300.  
    301.  
    302.  
    303.  
    304. SELECT Success ;
    305.  
    306.  
    307. END;
    Last edited by make me rain; Oct 23rd, 2014 at 05:35 AM. Reason: with PHP tag code appeared a continues line
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

  2. #2
    Addicted Member
    Join Date
    Oct 2008
    Location
    Califorina
    Posts
    235

    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.

  3. #3

    Thread Starter
    PowerPoster make me rain's Avatar
    Join Date
    Sep 2008
    Location
    india/Hubli
    Posts
    2,208

    Re: MySQL , Why this procedure not rolls back

    thanks for reply smen
    MySQL wont allow any DECLARE statement within START TRANSACTION
    The averted nuclear war
    My notes:

    PrOtect your PC. MSDN Functions .OOP LINUX forum
    .LINQ LINQ videous
    If some one helps you please rate them with out fail , forum doesn't expects any thing other than this

  4. #4
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    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
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width