Help in mysql transaction

cirno · #1 (**permalink**) 01-24-11, 23:24

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

it-iss.com · #2 (**permalink**) 01-25-11, 03:02

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.

cirno · #3 (**permalink**) 01-25-11, 20:50

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   |

it-iss.com · #4 (**permalink**) 01-26-11, 03:25

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech