Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2011
    Posts
    4

    Unanswered: Help in mysql transaction

    Hello and good day to you, sir.

    I'm having problem with my transaction. I want to be able to update 3 tables then rollback the changes at the end of the transaction.

    Code:
    SET autocommit = 0;
    START TRANSACTION;
    
    BEGIN;
    
    UPDATE memberinfor SET mPoint = 0, mAction = 9, mSubCardNo1 = '', mSubCardNo2 = '', mUserID = 'ADMIN', dataStatus = 'N', mModifiedOutlet = '004' 
    WHERE mAccNo = 2880040017090;
    
    UPDATE mcardlist SET STATUS = '10', remark = '10', canRenew = '0'
    WHERE mAccNo = '2880040017090' AND STATUS = '2' AND mCardNo = '1234547567345232' OR mcardno = '' AND cDate = '2011-01-24'
    
    UPDATE memstatustrans SET mRemarks = 'Is Void' 
    WHERE maccNo = '2880040017090' AND mRemarks LIKE '%99000590%' AND cDate = '2011-01-24'
    
    ROLLBACK;
    but never mind rolling back, my query did not go pass updating the first table, and to make things more confusing, when my mouse cursor is clicked at any of the update table statement, only that update statement is run.

    Aaaa it's so confusing and depressing for a newb in mysql like me

    please, please help me...

    Thanks in advance

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    Can you provide the error message you got? Can you also provide the table definitions for the updates? This will give us more information to be able to help you on this.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Jan 2011
    Posts
    4
    There was no error message, only a message shows like this;

    Code:
    (0 row(s) affected)
    Execution Time : 00:00:00:000
    Transfer Time  : 00:00:00:015
    Total Time     : 00:00:00:015
    memberinfor:

    Code:
      maccno	|	mcardno	   | maction | mpoint | mcardexpired |   msubcardno1    | msubcardno2 | muserid | datastatus | mModifiedOutlet
    ----------------+------------------+---------+--------+--------------+------------------+-------------+---------+------------+----------------
     2880040017090  | 5248452214221421 |	1    |    1   |   01/2012    | 1234547567345232 |	      |  ADMIN  |	N    |      004
    memstatustrans

    Code:
       mAccNo      | mCardNo	  |   mNameOnIC	  | mRemarks	       |   mModifiedDT 	  |
    ---------------+------------------+---------------+--------------------+------------------+
     2880040017090 | 5248452214221420 | SATU DUA TIGA | 01/2012 - 99000590 | 25/01/2011 12:21 |
    mcardlist

    Code:
      mAccNo      |     mCardNo      |status| cdate    | mdate    |remark|oldCardNo|  mModifiedDate	   |canRenew|
    --------------+------------------+------+----------+----------+------+---------+-------------------+--------+
    2880040017090 | 1234547567345232 |   2  |2011-01-24|2011-01-24|	2    |	       |2011-01-24 14:06:31|	1   |
    2880040017090 | 5248452214221421 |   1  |2011-01-24|2011-01-24|	1    |	       |2011-01-24 14:06:31|	1   |

  4. #4
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    What tool are you using to perform the updates? Could it be that when you click on the update statement it is executing the entire set of commands in the transaction in which case the results are as you would expect them? If it performed the 3 update statements and then rolled back the transaction then you would see no changes in the database when you attempt to go back and review them.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

Posting Permissions

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