MySQL Bugs: #19065: Recordset-based update fails if blob field is queried

Bug #19065	Recordset-based update fails if blob field is queried
Submitted:	13 Apr 2006 1:01	Modified:	26 Feb 2008 13:31
Reporter:	Adrian Sandor
Status:	Closed
Category:	Connector/ODBC	Severity:	S2 (Serious)
Version:	3.51.12,5.1	OS:	Microsoft Windows (Windows XP)
Assigned to:	Bugs System	Target Version:	3.51
Tags:	ADO, ODBC5-RC
Triage:	D2 (Serious)

Description:
I'm using MySQL 4.1.11 and MyODBC 3.51.12 and coding in ASP (javascript) with
ADO. I want to update a record through a recordset; however I'm getting the error
"Query-based update failed because the row to update could not be found."
The table has a mediumblob field; after some debugging, I found out that the error appears
even if the field is NOT involved in the update, but removing that field from the SELECT
query makes the error go away.

How to repeat:
Create a table with a record:

CREATE TABLE `tst` (
  `ID` int(11) NOT NULL auto_increment,
  `Name` varchar(50) default NULL,
  `File` mediumblob,
  PRIMARY KEY  (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `tst` VALUES (1, 'blah', NULL);

And run this code:

<%@Language=JavaScript%>
<!--METADATA TYPE="typelib" uuid="00000205-0000-0010-8000-00AA006D2EA4" -->
<%
var cnn=Server.CreateObject("ADODB.Connection");
cnn.Open("DSN=sysmysql");
cnn.Execute("USE test");
var rst=Server.CreateObject("ADODB.Recordset");
rst.Open("SELECT * FROM tst WHERE ID=1", cnn, adOpenForwardOnly, adLockPessimistic);
rst("Name")="asd";
rst.Update();
Response.Write("ok");
%>

The result is:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
Query-based update failed because the row to update could not be found.

Change the query to:
rst.Open("SELECT ID, Name FROM tst WHERE ID=1", ...
(no File field included)
And this time it says "ok" as expected.

Suggested fix:
Obviously, the recordset update functionality is seriously BORKED. It desperately needs to
be fixed urgently.
The right fix is to allow updates for any kind of fields (for which an update is supposed
to work), including blobs, binaries, etc.
If somehow, due to some sudden unexpected cataclysm, you find yourself unable to do that,
then at least make the update work when blob fields are not updated.

Hi. Thanks for your problem report. Using pure ODBC calls does not reproduce this problem.
Since I already had troubles with ADO 2.0 can you please post small but complete (VS
preferrable) project demonstrating this problem along with all relevant data regarding
versions used.

Also, can you please try with adOpenStatic as the CursorType (ss or cs).

I don't know what kind of project I could post. I don't have VB, just VC++.Net 2003, and I
don't really know how to use ADO from C++.
The MDAC Component Checker says I'm using "MDAC 2.8 SP1 ON WINDOWS XP SP2". If you need
more details, I can upload a report with file versions.
If you can't run ASP, then you could create a file called "test.js" and put this in it
(basically the same code):

var cnn=new ActiveXObject("ADODB.Connection");
cnn.Open("DSN=sysmysql");
cnn.Execute("USE test");
var rst=new ActiveXObject("ADODB.Recordset");
rst.Open("SELECT * FROM tst WHERE ID=1", cnn, 0, 2);
rst("Name")="asd";
rst.Update();
WScript.Echo("ok");

Then you can double-click the file to run it, or execute "wscript test.js" in a command
prompt.

I tried all cursor types (adOpenDynamic, adOpenForwardOnly, adOpenKeyset, adOpenStatic and
adOpenUnspecified) and the result is exactly the same.

Hi. Not all combinations fail. This one goes without exception:
var cnn=new ActiveXObject("ADODB.Connection");
cnn.Open("DSN=myodbc1");
WScript.Echo("connected");
cnn.Execute("USE test");
WScript.Echo("using");
var rst=new ActiveXObject("ADODB.Recordset");
rst.Open("SELECT * FROM tst WHERE ID=1", cnn, 3, 4);
WScript.Echo("open");

// this works also rst.Update("Name","asd"); and (1,"asd") .. like nothing is //done in
Recordset.Update ...
 
rst("Name")="asd";
rst.Update();

WScript.Echo("ok");
rst.Close();

But no update is done! 
Since I succeded to update table via ODBC I have a log of commandes that succeded. I will
need more time to compare it with the log from your example. Hopefully I'll figure out
what's wrong. For now, it looks like we're missing something with ADO.

You used locktype 4 which is adLockBatchOptimistic; I think that's supposed to work with
batch updates (rst.UpdateBatch()), but anyway it's outside the scope of this problem.

My comment is still the same:
"Since I succeded to update table via ODBC I have a log of commandes that
succeded. I will need more time to compare it with the log from your example.
Hopefully I'll figure out what's wrong. For now, it looks like we're missing
something with ADO." Logs are being analysed.

Hi. Can you please post the value of max_allowed_packet variable?

If you tell me how to find it, I will post it. I never heard of it before.

Hi. Read the value by typing "show variables" in mysql client. I suspect this value to be
too low, so locate my.ini file, it's either in mysqlxxx folder or in windows folder. The
location can also be written in Services/Properties/Path to executable like this:
C:\mysqlxxx\bin\mysqld-max-nt --defaults-file=C:\mysqlxxx\bin\my.ini MySQL5.0
Inside my.ini file there's section
# The MySQL server
[mysqld]
and in it there should be:
max_allowed_packet = XXM
If not, add it.
If this value is lower or equal to your BLOB size increase it and test again. It's quite
safe to increase. Set it to, like 64-128M but it should be bigger than your biggest blob
filed data.

mysql says:  | max_allowed_packet              | 1048576
I get the same value from ASP.
The value is not specified in my.ini

I would like to emphasize that:
- The largest BLOB specified in my testcase is NULL. It's pretty hard to get smaller than
that.
- In my testcase I never try to add or change a BLOB value.
- My actual project is designed to accept files up to 50KB only. Also, I successfully
updated BLOB fields using direct SQL queries of the form: UPDATE tst SET File=x'...'
(using a string of hex digits). I tried files up to 20KB (40K hex digits) and I had no
problems that way. Of course, this is only a workaround.

Did you find that changing the max_allowed_packet value fixes the problem reported in this
bug? If not, I wonder why you are suggesting it.

Anyway, thanks for working on this thing.

Sandor, this is a part of the problem I've been analyzing together with your post in
#19052 and some other reports. We will inform you of our findings soon. Thanks.

Well, if I can help you with other bugs, I'll be happy to do that. And please call me
Adrian.

Adrian, so far I was unable to get updateable recordset with any combination of
CursorLocation and CursorType no matter if there's blob field in the table or not.
Strange.
-myRecordset	{ADODB.RecordsetClass}	ADODB.Recordset
-	ADODB.RecordsetClass	{ADODB.RecordsetClass}	ADODB.RecordsetClass
		BOF	True	Boolean
		CursorLocation	adUseClient {3}	ADODB.CursorLocationEnum
		CursorType	adOpenStatic {3}	ADODB.CursorTypeEnum
		DataMember	Nothing	String
+		DataSource	{ADODB.RecordsetClass}	Object
	>>   EditMode	adEditNone {0}	ADODB.EditModeEnum

Don't use a client cursor, that opens a whole different set of nasty bugs (or you may want
to do it for testing...)
The essential thing is the lock type - make sure it is not adLockReadOnly.

You can use the code that I posted before; as I said, if I change the query to:
rst.Open("SELECT ID, Name FROM tst WHERE ID=1", ...
then it works perfectly.

Hi Adrian. I'm not so good at scripting but this VB code works and is close to yours in
syntax:

        Const DSN = "Uid=root;Pwd=;Driver={MySQL ODBC 3.51
Driver};Server=munja;Database=test;OPTION=3"
        Dim cnxDatabase As ADODB.Connection

        Dim strSQL As String

        ' connecting database
        cnxDatabase = CreateObject("ADODB.Connection")
        cnxDatabase.ConnectionTimeout = 500
        cnxDatabase.Mode = ConnectModeEnum.adModeReadWrite

        cnxDatabase.Open(DSN)
        cnxDatabase.Execute("USE test")

        ' querying data
        strSQL = ""
        strSQL = strSQL & "SELECT * "
        strSQL = strSQL & "FROM tst"

        Dim rs As ADODB.Recordset

        rs = New ADODB.Recordset
        With rs
            .ActiveConnection = cnxDatabase
            .LockType = LockTypeEnum.adLockOptimistic
            .CursorType = CursorTypeEnum.adOpenKeyset
            .CursorLocation = CursorLocationEnum.adUseClient
            .Open(strSQL)
            .Update("Name", "nesto drugo")
        End With

Please inform me of the results.

Well, you used a client cursor, although I specifically told you not to do that.
I took your code, adjusted it a bit and made a VBScript ASP page, here it is:

<%@Language=VBScript%>
<!--METADATA TYPE="typelib" uuid="00000205-0000-0010-8000-00AA006D2EA4" -->
<%
Option Explicit
Const DSN = "DSN=sysmysql"
Dim cnxDatabase
Dim strSQL

' connecting database
Set cnxDatabase = CreateObject("ADODB.Connection")
cnxDatabase.Open(DSN)
cnxDatabase.Execute("USE test")

' querying data
strSQL = "SELECT * FROM tst WHERE ID=1"

Dim rs
Set rs = CreateObject("ADODB.Recordset")
With rs
    .ActiveConnection = cnxDatabase
    .LockType = adLockOptimistic
    .CursorType = adOpenKeyset
    .Open(strSQL)
    .Update "Name", "nesto drugo"
End With
%>

It works very well if the table has no blob field.
I kept your preferred syntax for working with the recordset.

Adrian,  what did you mean by "It works very well if the table has no blob field."? It
should work with blob field too.
As for client side cursor you can test server side cursor now with the code that works.
Does this solution works for 19052 too? If it does and you feel that problem is solved
then we can close bug reports.

LOL, do you think I am wasting your time with fake bug reports ?!!
What I mean is that the code works well when the "tst" table has only the ID (int) and
Name (varchar) fields.
As soon as I do "ALTER TABLE `tst` ADD `File` MEDIUMBLOB;" the exact same code fails
with:

Microsoft OLE DB Provider for ODBC Drivers error '80004005'
Query-based update failed because the row to update could not be found.

Exactly as I said in the bug report.

As for bug 19052, that is a different problem. Do you want to talk about it here?

Adrian, please try this:
ALTER TABLE tst CHANGE File File1 MEDIUMBLOB;
... try the script again. It may be it fails because File is reserved word somewhere.

It's not that. I renamed it to both `File1` and `sdjfvdhfjh`, I'm getting the same error.
Are you still not able to test on your own system?

Adrian, I made project in VB and for me it works with no problem. Can you send me new
script you made so I can test with it?

Are you saying that you can update successfully even though there is a blob field??
You can try any of the scripts I posted. The latest one is 6 messages above. I have
nothing newer than that.

Adrian, I am unable to run vbs script as you posted:
<%@Language=VBScript%>
<!--METADATA TYPE="typelib" uuid="00000205-0000-0010-8000-00AA006D2EA4" -->
syntax error at the begining.
However I'm able to run this:
Option Explicit
Const DSN = "Uid=root;Pwd=;Driver={MySQL ODBC 3.51
Driver};Server=munja;Database=test;OPTION=3"

Dim cnxDatabase
Dim strSQL

' connecting database
Set cnxDatabase = CreateObject("ADODB.Connection")
cnxDatabase.Open(DSN)
cnxDatabase.Execute("USE test")

' querying data
strSQL = "SELECT * FROM tst WHERE ID=1"

Dim rs
Set rs = CreateObject("ADODB.Recordset")
With rs
    .ActiveConnection = cnxDatabase
    '.LockType = adLockOptimistic
    '.CursorType = adOpenKeyset
    .Open(strSQL)
    .Update "Name", "nesto_drugo"
End With
but key things are remarked (LockType,CursorType) because wscript complains they are not
defined. If you can provide me with the VBS script that I can run in wscript and that
contains LockType,CursorType as I defined them, I'll try again.
So far, I was unable to repeat your error in VB project in visual studio which means it
probably isn't MyODBC problem.

That was a VBScript ASP page. You can use the js script I posted earlier, but anyway
here's a corresponding vbs script for this code:

Option Explicit
Const DSN = "DSN=sysmysql"
Dim cnxDatabase
Dim strSQL

' connecting database
Set cnxDatabase = CreateObject("ADODB.Connection")
cnxDatabase.Open(DSN)
cnxDatabase.Execute("USE test")

' querying data
strSQL = "SELECT * FROM tst WHERE ID=1"

Dim rs
Set rs = CreateObject("ADODB.Recordset")
With rs
    .ActiveConnection = cnxDatabase
    .LockType = 3
    .CursorType = 1
    .Open(strSQL)
    .Update "Name", "nesto drugo"
End With

I don't know how to get the constant names into vbs (or js) files so I used their numeric
values.

I understand now that you never managed to reproduce the error?! In your first replies you
said that you were analysing logs, etc. I thought you already tried my code (which
failed), made logs and were just checking the differences. I'm very confused about your
later responses too.

Adrian, sorry I misslead you. I confirmed that your first script (and the last VBS script)
produce the error you describe. Also, I said I have a log of succesfull update through
MyODBC client program not using ADO. Then I made VB program with ADO, DB structure and
data you provided following your query and update statements as close as possible. VB
program is working, VBS is not working. Therefore it is my conclusion that something is
broken in scripting host rather than in MyODBC. 
I don't see what else I can do to prove this is not a MyODBC problem. I can't go debugging
every library MS has included in this problem.

> VB program is working, VBS is not working.

Hm, that is very strange.

> Therefore it is my conclusion that something is broken in scripting host rather than in
MyODBC.

I don't really agree. It's very unlikely that something is broken in the scripting host.
Just think about all the database systems that don't have these problems. I rather suspect
that:
1. MyODBC is not compatible with all legal ways of calling functions, passing parameters
and returning results
and
2. There is a small difference between the ODBC calls generated from VB and VBS
and possibly
3. There is a small difference between the VB and VBS code you are using - e.g. "new"
instead of "CreateObject" - which leads to point 2

By the way, what did you find out from analysing logs? And what kind of logs were they?
Can you find out at which step the operation fails?

Hi, Adrian.
I have one idea for you to test. Try upgrading to, at least, 4.1.13.
Quote from MyODBC changes file, ver. 3.51.12: "binaries made using MySQL 4.1.13"
Inform me of results. In the meantime, I'm setting up IIS.

Test case, execution log, and MyODCB trace.

Attachment: MyODBC_trace.txt (text/plain), 49.64 KiB.

Hi Adrian.
I've included a lot of developers on this one but with no luck. This is what we've found
so far:
ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.WIN32COM.v10.en/ado270/htm/mdcondatasection.htm
*Managing Pending Changes*
A Recordset can be opened in immediate or batch update mode. <cut>
An update always contains the *entire original row data* followed by the changed row data.
The changed row may contain all of the columns or only those columns that have actually
changed. 

Why ADO stopped calling " SQLPrimaryKeys" or "SQLStatistics" functions to get
primary/unique keys for update is beyond me. All I see from our logs is that it doesn't
get called and MyODBC driver gets parametized UPDATE statement with *ALL* fields
included...

Obviously scripting host is following rules exactly and fails to update while VB behaves
smarter and avoids BLOB fields thus succeding. This is the best example of MS
inconsistency...

You mentioned other servers ... Don't know about them but, maybe, they send more metadata
or other people write pages differently...

Also, you can check http://bugs.mysql.com/bug.php?id=15277. People there complain about
similar things after applying newest MS SP...

> This is what we've found so far:
> ms-help://MS.VSCC.v80/MS.MSDN.v80/MS.WIN32COM.v10.en/ado270/htm/mdcondatasection.htm

I think a much better link would be
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarado/html/xmlintegrati
oninado.asp
but anyway, I'm not sure why it is relevant

> All I see from our logs is that it
> doesn't get called and MyODBC driver gets parametized UPDATE
> statement with *ALL* fields included...
> Obviously scripting host is following rules exactly and fails to update
> while VB behaves smarter and avoids BLOB fields thus succeding.
> This is the best example of MS inconsistency...

Well, you kinda lost me here. Hope you know what you're doing.

> You mentioned other servers ... Don't know about them but, maybe,
> they send more metadata or other people write pages differently...

I tried the exact same code with a MS Access database (well, it's not exactly a server,
but close enough). It had no problems. If desired, I could also test it with a MSSQL
database.

> Also, you can check http://bugs.mysql.com/bug.php?id=15277.
> People there complain about similar things after applying newest MS SP...

I don't see any connection at all between my problem and bug 15277.
- First, it's a TOTALLY different problem ("Microsoft Access is no longer able to
connect to a new MYSQL-table", "You don't get any error message.")
- Second, it refers to SP4 for Windows 2000. I am NOT using Windows 2000!
- Third, it refers to msjet40.dll; that file is NOT involved in connecting from
vbscript/javascript to mysql

> Try upgrading to, at least, 4.1.13.

I'm sorry, I don't have the time to upgrade mysql now. But since you have already
reproduced the bug, I don't see the point. And it also happened with older myodbc
versions.

I still believe that this is a problem in myodbc. It may be in a different place than you
expect, it may be caused by poor documentation from MS, but anyway I can't see what other
software component could cause this bug.

Verified as described by reporter with scripts provided.

I had the same error message when trying to do Update.  Traced it through VB and
eventually found a problem - although I was specifying adOpenDynaset, it always opened a
static set.

Solved it by just specifying a an extra flag in the connection string.  This works for
me:

Public Const CONN_STRING = "Uid=my_username;Pwd=my_password;Driver={MySQL ODBC 3.51
Driver};Server=localhost;Database=my_db;OPTION=35"
Public g_conn As New ADODB.Connection  
Public g_rstSites As New ADODB.Recordset

<snip>
  g_conn.CursorLocation = adUseServer
  g_conn.Mode = adModeReadWrite
  g_conn.Open CONN_STRING
  strSql = "SELECT * FROM sites ORDER BY siteName ASC;"
  g_rstSites.Open strSql, g_conn, adOpenDynamic, adLockOptimistic

<MySQL documentation for OPTION value>
Value Description 
1 The client can't handle that MyODBC returns the real width of a column. 
2 The client can't handle that MySQL returns the true value of affected rows. If this flag
is set, MySQL returns “found rows” instead. You must have MySQL 3.21.14 or newer to
get this to work. 
4 Make a debug log in c:\myodbc.log. This is the same as putting
MYSQL_DEBUG=d:t:O,c::\myodbc.log in AUTOEXEC.BAT. (On Unix, the file is /tmp/myodbc.log.)

8 Don't set any packet limit for results and parameters. 
16 Don't prompt for questions even if driver would like to prompt. 
32 Enable or disable the dynamic cursor support. (Not allowed in MyODBC 2.50.) 
...

I am specifying 1, 2 and 32.  Adding flag 32 solved it.

I don't know if this will solve the issue described above with VBScript, but notice that
they are using OPTION=3, which doesn't work for me.

What made this so frustrating was that the error message is misleading.  "the row to
update could not be found" suggests that the problem is elsewhere.  I spent a good 3 hours
checking that everything was OK with my table, that I had the primary keys right etc etc. 
A better error message would have been something like: "Cannot carry out update operation
on static set".  Is there any chance of changing this?

WinXP SP2, MyODBC 3.51.12, VB6.0.8169

I would like to set this report to "Fixed in source trees" if Jess and Georg agree.
However, for MyODBC 5 it's "Verified" (doesn't work at all). So, should we open another
bug report for v5 or modify this one?

Introduction:
  ADO updates are governed by adCriteriaUpdCols (see
http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q190727&GSSNB=1). Default value is

  adCriteriaUpdCols = 2  (Default)
    Uses only the columns in the recordset that have been modified

MyODBC 3.51 works with and without setting .Properties("Update Criteria").Value :
*with*
070206 14:18:10	     43 Connect     root@localhost on test
		     43 Query       SET SQL_AUTO_IS_NULL=0
		     43 Query       select database()
		     43 Query       USE test
		     43 Query       SELECT * FROM tst WHERE ID=1
		     43 Query       UPDATE `test`.`tst` SET `Name`='nesto_drugoxx' WHERE `ID`=1
070206 14:18:12	     43 Quit       

*without*
070206 14:18:31	     44 Connect     root@localhost on test
		     44 Query       SET SQL_AUTO_IS_NULL=0
		     44 Query       select database()
		     44 Query       USE test
		     44 Query       SELECT * FROM tst WHERE ID=1
		     44 Query       UPDATE `test`.`tst` SET `Name`='nesto_drugo' WHERE `ID`=1 AND
`Name`='nesto_drugoxx'
070206 14:18:32	     44 Quit       

Test case:
Option Explicit
Const DSN = "Driver={MySQL ODBC 3.51
Driver};Uid=root;Pwd=;Server=localhost;Database=test;OPTION=1 + 2 + 8 + 16384"
'Const DSN = "Driver={MySQL connector/ODBC
v5};Uid=root;Pwd=;Server=localhost;Database=test;OPTION=1 + 2 + 32"
'Const DSN = "DSN=myodbc1"

Const adOpenForwardOnly = 0
Const adOpenKeyset = 1
Const adOpenDynamic = 2
Const adOpenStatic = 3
Const adOpenUnspecified = -1

Const adUseNone = 1
Const adUseServer = 2
Const adUseClient = 3

Const adLockReadOnly = 1
Const adLockPessimistic = 2
Const adLockOptimistic = 3

'---- ParameterDirectionEnum Values ----
Const adParamUnknown = &H0000
Const adParamInput = &H0001
Const adParamOutput = &H0002
Const adParamInputOutput = &H0003
Const adParamReturnValue = &H0004

'---- CommandTypeEnum Values ----
Const adCmdUnknown = &H0008
Const adCmdText = &H0001
Const adCmdTable = &H0002
Const adCmdStoredProc = &H0004
Const adCmdFile = &H0100
Const adCmdTableDirect = &H0200

Const adInteger=3
Const adDate=7
Const adVarChar=200

Dim cnxDatabase
Dim strSQL

' connecting database
Set cnxDatabase = CreateObject("ADODB.Connection")
cnxDatabase.Open(DSN)
cnxDatabase.Execute("USE test")

' querying data
strSQL = "SELECT * FROM tst WHERE ID=1"

Dim rs
Set rs = CreateObject("ADODB.Recordset")
With rs
    .ActiveConnection = cnxDatabase
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .CursorType = adOpenForwardOnly
    .Properties("Update Criteria").Value = 0
    .Open(strSQL)
    .Update "Name", "nesto_drugo"
End With

WScript.Echo "UPDATE Executed.."

Set cnxDatabase = nothing
Set rs = nothing

Im with the same problem, but, when i changed the * from the query by the field names,
removing the "mediumtext" type fields, the update work fine.

I've run into this problem while writing ADO compliance test -
TestExecuteValidBatchServer() 

It is happening pretty much just as described.  To clarify, it is only happening on SS
side cursors.  At least this is the only time I've seen it.  Here is a simple VB6 function
to reproduce the problem.

mysql> create table foo (C1 INT PRIMARY KEY, C2 BLOB, C3 CHAR(1));
mysql> INSERT INTO foo VALUES (1, 'FOO', 'A'), (2, 'BAR', 'B');

VB CODE:
Private Sub binfieldTest()
    On Error GoTo EH
    Dim strSQL As String
    Dim strConn As String
    Dim connection1 As ADODB.Connection
    Dim recset1 As ADODB.Recordset
    Dim fld As Field
    Dim fldName As String

    Set connection1 = New ADODB.Connection
    Set recset1 = New ADODB.Recordset
    
'    strSQL = "select C1, C3 FROM foo"
    strSQL = "select * from foo"
    
    strConn = "DRIVER={MySQL ODBC 3.51 Driver};" & _
        "SERVER=localhost;DATABASE=ado" & _
        ";USER=root;PASSWORD=mypass;OPTION=3;"

'   strConn = "DRIVER={MySQL Connector/ODBC v5};" & _
        "SERVER=localhost;DATABASE=ado" & _
        ";UID=root;PWD=mypass;OPTION=3;"

    ' open connection
    connection1.ConnectionString = strConn
    connection1.CursorLocation = adUseServer
    connection1.Open
   ' open recordset
    recset1.Open strSQL, connection1, adOpenForwardOnly, _
             adLockOptimistic, adCmdText
             
    If Not recset1.EOF Then
        recset1.MoveFirst
        While Not recset1.EOF
            For Each fld In recset1.Fields
                fldName = fld.Name
                If fldName = "C3" Then
                    fld.Value = "X"
                End If
            Next
            recset1.UpdateBatch
            recset1.MoveNext
        Wend
    End If
    recset1.Close
    Set recset1 = Nothing
    connection1.Close
    Set connection1 = Nothing
    Exit Sub
EH:
    MsgBox Err.Number & ":" & Err.Description
    MsgBox "failed on column " & fldName
End Sub

When run this way, this is the query sent to the server:
1018 Query       UPDATE foo SET C3='X' WHERE (C1=1 AND C2='' AND C3='A' )

NOTE the attempt to match a binary field as ''  I'm not sure that it's even possible to
match a byte array this way.  It fails with the error described in the bug
"-2147467259:Query-based update failed because the row to update could not be found." 
I've seen other errors though depending on what other columns are present in the record
and what order they are in.

If I change the query from Select * to Select [columns] (not including the binary field)
it does this:
1019 Query       UPDATE foo SET C3='X' WHERE (C1=1 AND C3='A' )

When I use the original query,  but change it to adUseClient I get this, basically the
same as above:
 1020 Query       UPDATE `ado`.`foo` SET `C3`='X' WHERE `C1`=1 AND `C3`='A'

So the workaround as stated above is to specify the fields you want to update.  This could
be a pretty cumbersome workaround if you have a lot of columns in the table.  This should
be fixed.

ODBC trace of eric's code

Attachment: SQL.LOG (application/octet-stream, text), 73.74 KiB.

Bug #5838 was closed as a duplicate of this bug.

second trace with blob field set to NULL

Attachment: SQL.LOG (application/octet-stream, text), 146.81 KiB.

As you can see from Eric's trace files, the problem is not within SQLSetPos(), which is
never called in the problematic cases. These UPDATE queries are being generated entirely
within ADO, with the BLOB field being a bound parameter whose value is apparently not
being handled properly, possibly on the ADO side in the case where it is NULL. (It is
still generating a query containing "blobfield = ?", and that will never match even if
that parameter is bound to NULL because of how NULL works in SQL.)

But Eric's case actually has a value for the BLOB field, so it should work. More debugging
will be required, mainly tracing what the parameters are actually bound to at the time of
the UPDATE query execution, and how those are being handled within the driver.

As has been noted, one known workaround is to use dynamic cursors (which must be enabled
with a driver option). There is also an ADO "Update Criteria" option that, if set to
adCriteriaKey, should cause a correct WHERE clause to be generated.

ADO.NET does not have an 'Update Criteria' property, but users of ADO.NET should be using
Connector/NET, not Connector/ODBC.

MySQL Server Version 5.0.22
ASP - VBScript
Remote Host: Hostek.com - Windows Server 2003
Re: Failure updating an existing mediumblob field AND consequent updates of other fields
once mediumblob field is assigned data.

1) When updating a recordset medium blob, I get the same error message that the row cannot
be found.

2) This bug/feature has roots as far back as 2004. It should be worked through by now.
I've tried everything imaginable.

3) Some of the consts don't work as parameters to recordset.Open() as suggested, and
create errors/conflicts of their own. adOpenDynamic works, but the next (lock) parameter
does not accept a const. I have to use integer values. Go figure.

4) As an update work around:  AddNew, copy original record, delete original record, then
update new record to update the medium blob. Messy/Slow.

5) Note:

*** All other type queries, data types, and updates work fine (with various cursors and
locks). ***
Everything goes haywire once the blob field is assigned data. No consequent updates
allowed to that record.
... have tried various cursors and locks and many options: 3, 35, etc., as suggested.
... have used various primary keys, as suggested.
... have specified (one/many) fieldnames in SQL instead of *, as suggested.
All db fields are defined Not NULL.
Explicit connection object not necessary, having used recordset alone for all other
queries and updates to date. And they all work fine.

6) The following code (typed from memory as I now use a work-around) works for Access
(with Access Driver and appropriate cursor/locktype, of course), but not MySQL. This is
the simplest test I can imagine that should work. And it does, provided that a mediumblob
is not in the picture.

const db="DRIVER=MySQL ODBC 3.51
Driver;SERVER=localhost;OPTION=35;USER=user;Password=pass;DATABASE=mydb"
Dim r
Set r=Server.CreateObject("ADODB.Recordset")
r.Open "SELECT * FROM mytable WHERE ID=2",db,adOpenDynamic,3    'Does not accept a const
for locktype
If Not r.EOF Then
	r("nastyblob").AppendChunk killerblob
End If
r.Update
r.Close
Set r=Nothing

Has anyone gotten a VBScript version of the suggested forum work around(s) to function
properly?

Thanks to all that have rolled up their sleeves on this one to date.

Regards,
DW

I will have EricM add an ADO bug test for this. Patch below:

Index: driver/utility.c
===================================================================
--- driver/utility.c    (revision 1010)
+++ driver/utility.c    (working copy)
@@ -228,7 +228,16 @@
       break;
     }
     irrec->schema_name= (SQLCHAR *) "";
-    irrec->searchable= SQL_SEARCHABLE;
+    switch (irrec->concise_type)
+    {
+      case SQL_LONGVARBINARY:
+      case SQL_LONGVARCHAR:
+        irrec->searchable= SQL_PRED_CHAR;
+        break;
+      default:
+        irrec->searchable= SQL_SEARCHABLE;
+        break;
+    }
     irrec->unnamed= SQL_NAMED;
     if (field->flags & UNSIGNED_FLAG)
       irrec->is_unsigned= SQL_TRUE;

Test Case added to ADO conformance tests
/bugs/19065.vbs

Committed into 3.51 as rev 1042, will be released in 3.51.24.
Committed into 5.1 as rev 1043, will be released in 5.1.3.

A note has been added to the 3.51.24 and 5.1.3 changelogs: 

Updating a RecordSet when the query involves a BLOB field would fail.