Bug #9010 Bug or made-by-design
Submitted: 7 Mar 2005 8:10 Modified: 9 Mar 2005 12:21
Reporter: Lars Annerstedt Email Updates:
Status: Duplicate Impact on me:
None 
Category:Connector / ODBC Severity:S3 (Non-critical)
Version: OS:Microsoft Windows (Win2000)
Assigned to: CPU Architecture:Any

[7 Mar 2005 8:10] Lars Annerstedt
Description:
A table contains 5 different entries with an Id and a Counter value.
If the Id=3 and Counter=3 for some of the other entries all entries will be updated to Counter=4, when Counter is incremented, if Id is omitted in the select clause.

(Server 4.1.9, MyODBC 3.51.11-2, VB6)

How to repeat:
CREATE TABLE `test` (
  `Id` smallint(6) NOT NULL default '0',
  `Counter` int(11) NOT NULL default '0',
  PRIMARY KEY  (`Id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `test`
 (`Id`,`Counter`) 
VALUES 
 (1,3),
 (2,3),
 (3,0),
 (4,3),
 (5,3);

*** Following code snippet will then make all entries having Counter = 4!
queryS = "SELECT Counter FROM TEST WHERE Id = 3"
Set queryRS = New ADODB.Recordset
With queryRS
   .Open queryS, SQLConnection, adOpenStatic, adLockOptimistic
   !Counter = !Counter + 1
   .Update
   .Close
End With

Suggested fix:
Bug or made-by-design?
(Works fine with MS SQL).
[9 Mar 2005 12:21] Aleksey Kishkin
Please do not submit the same bug more than once. An existing
bug report already describes this very problem. Even if you feel
that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments
to the original bug instead.

Thank you for your interest in MySQL.

Additional info:

duplicate of #9009