MySQL Bugs: #647: Selete For Update

Bug #647	Selete For Update
Submitted:	12 Jun 2003 19:59	Modified:	30 Jun 2004 17:27
Reporter:	RaguNathan T	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51	OS:	Windows (windows xp and 98)
Assigned to:	Dean Ellis	CPU Architecture:	Any

Description:
I have a table that contains one auto increment field and i set that field as primary key. I'm using Visual basic 6 and ado 2.5.

if i'm going update particular record in that using that primary field by following,

set rs=new adodb.recordset
rs.open "Select Code from Ledger where AutoNo=1 For Update" ,db,adopenkeyset,adlockpessimistic
rs!code=text1.text
rs.update
set rs=nothing

the above updates all the records in Ledger. this is very serious bug.please correct it as early as possible.

How to repeat:
I have a table that contains one auto increment field and i set that field as primary key. I'm using Visual basic 6 and ado 2.5.

if i'm going update particular record in that using that primary field by following,

set rs=new adodb.recordset
rs.open "Select Code from Ledger where AutoNo=1 For Update" ,db,adopenkeyset,adlockpessimistic
rs!code=text1.text
rs.update
set rs=nothing

the above updates all the records in Ledger. this is very serious bug.please correct it as early as possible.

Hi !!

I couldn't able to repeat your behavior. Note that, as long as you have defined a primary key column, then there is no means that driver can update all rows when using server side cursor.

You can find the same example from here, which does the rs.update:
http://www.mysql.com/products/myodbc/manual.html#Connector/ODBC_With_VB

Also, I noticed that still you are referencing to ADO 2.5, but better you use ADO 2.7 or 2.8 as 2.5 is pretty old.

Thanks
Venu

Hello sir,
as per your advice we changed the ado reference  from 2.5 to 2.7. below is coding i have done. in this sortorder is the primary key field. but this also affects all the rows in my table
------------------------------------------------------------------------------
dim db as New ADODB.Connection,rs as New ADODB.Recordset
db.open "DSN=Cooltex;Option=3"
rs.CursorLocation = adUseServer
rs.Open "select billed from producttransaction where  sortorder=1 for Update", db, adOpenForwardOnly, adLockOptimistic
rs!billed = 5
rs.Update
rs.Close
Set rs = Nothing
------------------------------------------------------------------------------
the above code updates all the rows. then i tried with clientsidecursor option. on that that replies "-2147467259 - Key column information is insufficient or incorrect. Too many rows were affected by update."
but sortorder field is primary key and it is auto increment field. please provide a solution immediately. becos we are using only recordset updates and inserts.

I've had exact the same Problem on XP. 
Unwanted mass update on all records using ADO's rs.update
Im using ADO and MYSQL with ODBC with VBSCRIPT.

Tried all cursortypes, all options, etc. and nothing helped but this one:

instead of:

 "Select yourfield from yourtable where yourkey=123"
 rs("yourfield")=NewValue : rs.update

to fetch the recordset I wanted to change,  I used:

 "Select * from yourtable where yourkey=123"
 rs("yourfield")=NewValue : rs.update

and this time it  worked-

Strange, seems to be a bug...

I'd bet that as long as you retrieved the pkey field in your original recordset, it would work out properly as well.  This way, you wouldn't have to retrieve the entire row.  It might be a bug in the MyODBC driver not retrieving a key field properly.

Cheers!

When the key field is included, it will indeed work. But when the key is over 2 or more columns the problem persists. I thing the only way will be using an 'UPDATE' query instead.

I am unable to repeat this using MDAC 2.8 and MyODBC 3.51.08.  The row is correctly updated whether or not I use a composite key and whether or not I select the key column(s).

Perhaps it is something particular to your schema?

I am getting the same error message when trying to call rst.update using an ADO 2.1 recordset in Access 2000 with a client-side cursor and a table with a multi-field primary key. 

I instantly fixed the error by simply switching to a server-side cursor (myConn.CursorLocation = adUseServer)  So this appears to be a bug when using ADO 2.1 in MS Access 2000 VBA, too.

Same here,
MySQL-4.1.12
MyODBC 3.51.12 (same using 3.51.6)
ADO 2.7 (same under 2.5)
I am using VBA with Option 3 connect string.

The only way to avoid this is to "SELECT *" or "SELECT key_field,"...

The same bug is also reported under bug #5634:
http://bugs.mysql.com/bug.php?id=5634

I believe SQL server returns some silent fields when requesting by ADO using recordset. 
One would think MyODBC needs to include the fields in the WHERE clause when updating recordsets.

Add me to this list.  I am running MySQL 5 with the latest ODBC driver (as of this date - just downloaded and installed yesterday).

My SQL: "SELECT catCount FROM dbCategories WHERE catID = " & NewID
I open my RS this way: RS.Open fSQL, Conn, 1, 3

If I try RS(0) = CInt(RS(0)) + 1, ALL rows in the table are affected.

If, however, I do as a previous post mentions and ALSO ask for the key field:
"SELECT catCount, catID FROM dbCategories WHERE catID = " & NewID

THEN it works as expected.

ihave the same problem..key column information is insufficient of incorrect...
i solved the problem just last night...

instead of:

SubSql "Select on_inventory from tblStockList where lot_num = '" & cboLot.Text & "' and p_code = '" & lvwList.ListItems(i).SubItems(1) & "'"
           subrs.fields(0) = 1
           subrs.update

i do it this way...

SubSql "Select * from tblStockList where lot_num = '" & cboLot.Text & "' and p_code = '" & lvwList.ListItems(i).SubItems(1) & "'"
           subrs.fields!on_inventory = 1
           subrs.update

and it worked fine...

i was able to solve it by reading this thread...thanks a lot!