| Bug #20140 | UPDATE query is affecting rows it shouldn't affect | ||
|---|---|---|---|
| Submitted: | 30 May 2006 9:24 | Modified: | 30 May 2006 10:07 |
| Reporter: | [ name withheld ] | Email Updates: | |
| Status: | Can't repeat | Impact on me: | |
| Category: | MySQL Server | Severity: | S2 (Serious) |
| Version: | 5.0.13 | OS: | Linux (linux) |
| Assigned to: | CPU Architecture: | Any | |
[30 May 2006 10:07]
Tonci Grgin
Hi Anders, thanks for your problem report.
I was unable to verify with latest bk on Suse 10.0, MyISAM and InnoDB with script you provided:
mysql> select version();
+--------------+
| version() |
+--------------+
| 5.0.23-debug |
+--------------+
1 row in set (0.00 sec)
mysql> select * from tblTransactions\G
*************************** 1. row ***************************
iTransactionId: 1000
iUserId: 201
iAmount: 50
vFirstName: NULL
vLastName: NULL
cCountryCode: NULL
vAddress: NULL
vCity: NULL
vZipCode: NULL
vEmail: NULL
vReferenceNr: NULL
vSecondaryReferenceNr: NULL
iIPAddressUser: 3232235681
dTime: 2006-05-30 11:44:21
iNumberOfAttempts: 1
eTransactionState: PENDING
vTransactionResultCode: NULL
dRecurringNextDate: NULL
eRecurringInterval: NONE
iCreditCardType: NULL
iRecieptMailSent: 0
*************************** 2. row ***************************
iTransactionId: 1001
iUserId: 201
iAmount: 50
vFirstName: NULL
vLastName: NULL
cCountryCode: NULL
vAddress: NULL
vCity: NULL
vZipCode: NULL
vEmail: NULL
vReferenceNr: NULL
vSecondaryReferenceNr: NULL
iIPAddressUser: 3232235681
dTime: 2006-05-30 11:44:21
iNumberOfAttempts: 1
eTransactionState: PENDING
vTransactionResultCode: NULL
dRecurringNextDate: NULL
eRecurringInterval: NONE
iCreditCardType: NULL
iRecieptMailSent: 0
2 rows in set (0.00 sec)
mysql> UPDATE tblTransactions SET cCountryCode='se' WHERE iTransactionId=1001;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tblTransactions\G
*************************** 1. row ***************************
iTransactionId: 1000
iUserId: 201
iAmount: 50
vFirstName: NULL
vLastName: NULL
cCountryCode: NULL
vAddress: NULL
vCity: NULL
vZipCode: NULL
vEmail: NULL
vReferenceNr: NULL
vSecondaryReferenceNr: NULL
iIPAddressUser: 3232235681
dTime: 2006-05-30 11:44:21
iNumberOfAttempts: 1
eTransactionState: PENDING
vTransactionResultCode: NULL
dRecurringNextDate: NULL
eRecurringInterval: NONE
iCreditCardType: NULL
iRecieptMailSent: 0
*************************** 2. row ***************************
iTransactionId: 1001
iUserId: 201
iAmount: 50
vFirstName: NULL
vLastName: NULL
cCountryCode: se
vAddress: NULL
vCity: NULL
vZipCode: NULL
vEmail: NULL
vReferenceNr: NULL
vSecondaryReferenceNr: NULL
iIPAddressUser: 3232235681
dTime: 2006-05-30 11:44:21
iNumberOfAttempts: 1
eTransactionState: PENDING
vTransactionResultCode: NULL
dRecurringNextDate: NULL
eRecurringInterval: NONE
iCreditCardType: NULL
iRecieptMailSent: 0
2 rows in set (0.00 sec)
mysql> UPDATE tblTransactions SET vReferenceNr='', eTransactionState='REDIRECT
',vTransactionResultCode='', vSecondaryReferenceNr='' WHERE iTransactionId=1001;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from tblTransactions\G
*************************** 1. row ***************************
iTransactionId: 1000
iUserId: 201
iAmount: 50
vFirstName: NULL
vLastName: NULL
cCountryCode: NULL
vAddress: NULL
vCity: NULL
vZipCode: NULL
vEmail: NULL
vReferenceNr: NULL
vSecondaryReferenceNr: NULL
iIPAddressUser: 3232235681
dTime: 2006-05-30 11:44:21
iNumberOfAttempts: 1
eTransactionState: PENDING
vTransactionResultCode: NULL
dRecurringNextDate: NULL
eRecurringInterval: NONE
iCreditCardType: NULL
iRecieptMailSent: 0
*************************** 2. row ***************************
iTransactionId: 1001
iUserId: 201
iAmount: 50
vFirstName: NULL
vLastName: NULL
cCountryCode: se
vAddress: NULL
vCity: NULL
vZipCode: NULL
vEmail: NULL
vReferenceNr:
vSecondaryReferenceNr:
iIPAddressUser: 3232235681
dTime: 2006-05-30 11:44:21
iNumberOfAttempts: 1
eTransactionState: REDIRECTED
vTransactionResultCode:
dRecurringNextDate: NULL
eRecurringInterval: NONE
iCreditCardType: NULL
iRecieptMailSent: 0
2 rows in set (0.01 sec)
mysql>
Description: When I run (in command line) an update query: UPDATE tblTransactions SET vReferenceNr='', eTransactionState='REDIRECTED', vTransactionResultCode='', vSecondaryReferenceNr='' WHERE iTransactionId=1001; 2 rows are changed, row with id=1001 is updated correct, but row with id=1000 is also changed, it's iAmount column is set to -2147483598. This update is not written to the mysql-log. The problem disapears if I remove "not null" from the iAmount-column. How to repeat: DROP TABLE IF EXISTS tblTransactions; CREATE TABLE tblTransactions ( iTransactionId int(11) not null primary key auto_increment, iUserId int(11), iAmount int(11) not null, vFirstName varchar(255), vLastName varchar(255), cCountryCode char(2), vAddress varchar(255), vCity varchar(255), vZipCode varchar(255), vEmail varchar(255), vReferenceNr varchar(255), vSecondaryReferenceNr varchar(255), iIPAddressUser int(11) unsigned not null, dTime datetime not null, iNumberOfAttempts int(11) not null default 1, eTransactionState enum('PENDING', 'REDIRECTED', 'ADDED', 'FINISHED', 'FAILED') NOT NULL DEFAULT 'PENDING', vTransactionResultCode varchar(255), dRecurringNextDate date, eRecurringInterval enum('NONE', 'WEEK', 'TWO_WEEKS', 'MONTH', 'QUARTER', 'YEAR', 'ENDED') DEFAULT 'NONE', iCreditCardType int(11) default null, iRecieptMailSent tinyint(1) not null default 0, INDEX id_date_ind (iTransactionId, dTime), INDEX userid_date_ind (iUserId, dTime), INDEX userid_ind (iUserId), INDEX ref_nr (vReferenceNr) ) ENGINE=InnoDB, AUTO_INCREMENT = 1000; INSERT INTO tblTransactions (iUserId, iAmount, iIPAddressUser, dTime, eRecurringInterval) VALUES (201,50,3232235681,now(), 'NONE'); INSERT INTO tblTransactions (iUserId, iAmount, iIPAddressUser, dTime, eRecurringInterval) VALUES (201,50,3232235681,now(), 'NONE'); UPDATE tblTransactions SET cCountryCode='se' WHERE iTransactionId=1001; UPDATE tblTransactions SET vReferenceNr='', eTransactionState='REDIRECTED', vTransactionResultCode='', vSecondaryReferenceNr='' WHERE iTransactionId=1001;