MySQL Bugs: #9009: ODBC/ADO problem

Bug #9009	ODBC/ADO problem
Submitted:	7 Mar 2005 8:10	Modified:	24 May 2005 8:59
Reporter:	Lars Annerstedt	Email Updates:
Status:	Won't fix	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:		OS:	Windows (Win2000)
Assigned to:	Bugs System	CPU Architecture:	Any

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).

Sorry I don't understand well what you meant:

Your initial rows are:

mysql> select * from test;
+----+---------+
| Id | Counter |
+----+---------+
|  1 |       3 |
|  2 |       3 |
|  3 |       0 |
|  4 |       3 |
|  5 |       3 |
+----+---------+
5 rows in set (0.00 sec)

Then in your code you do:

queryS = "SELECT Counter FROM TEST WHERE Id = 3"

Then you can expect only one row in the result set: id = 3
That it was I got when I did twice your code:

mysql> select * from test;
+----+---------+
| Id | Counter |
+----+---------+
|  1 |       3 |
|  2 |       3 |
|  3 |       1 |
|  4 |       3 |
|  5 |       3 |
+----+---------+
5 rows in set (0.00 sec)

mysql> select * from test;
+----+---------+
| Id | Counter |
+----+---------+
|  1 |       3 |
|  2 |       3 |
|  3 |       2 |
|  4 |       3 |
|  5 |       3 |
+----+---------+
5 rows in set (0.00 sec)

Now I'm running 4.1.10a and localhost (at home). Else like before.

You stopped too early!

I increment the !Counter value so all entries are 3, then when I increment one more time all entries become = 4, if I then increment one more time again all entries will be 5, then 6, then 7, 8, 9, 10 and so on. 
I have a small VB6-program to verify it.

Thank you for the feedback.  I was able to repeat.

mysql> select * from test;
+----+---------+
| Id | Counter |
+----+---------+
|  1 |       4 |
|  2 |       4 |
|  3 |       4 |
|  4 |       4 |
|  5 |       4 |
+----+---------+
5 rows in set (0.00 sec)

I think You should consider this beeing a serious bug.

Lars - Can you attach the VB6 testing program to "Files"  of bug report system for this bug ?

Testprogram (need to change ODBC)

Attachment: myBug.zip (application/zip, text), 10.67 KiB.

I attached my code on Apr 7!

I've got your test program. Thanks a lot !

I tried this test on MS SQL and got same result. Maybe It is a bug of VB ? Maybe did I miss some connect options for MS SQL ? Can you write me what options were when you tested on MS SQL ?

Ok - i'll try, the problem is that I have substituted MS SQL for MySQL (it is much easier to test against MySQL than MS SQL).

I've been using MS SQL7 for about 5 years previous to MySQL and the VB-code has been the same. I discovered the 'bug' a while after converting to MySQL.
Since it can be avoided by SELECT * or SELECT 'the key' it's not a problem anymore but still a potentional problem for others, not aware of this.

Now I also has verified the same behaviour with Microsoft SQL Server.
I didn't have quite the same configuration as before, I ran the test in a machine with both SQL7 and SQL2000. Wheter this matters or not I don't know.
So from now I also suspect VB/ADO because it was exactly the same behaviour in MySQL as MS SQL.

So...if the bug is VB/ADO bug, We are not able to fix it