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

Bug #19065	Recordset-based update fails if blob field is queried
Submitted:	12 Apr 2006 23:01	Modified:	26 Feb 2008 12:31
Reporter:	Adrian Sandor	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.12,5.1	OS:	Windows (Windows XP)
Assigned to:	Jess Balint	CPU Architecture:	Any
Tags:	ADO, ODBC5-RC

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