MySQL Bugs: #5427: ADO Recordset update for binary data fails

Bug #5427	ADO Recordset update for binary data fails
Submitted:	6 Sep 2004 5:02	Modified:	31 May 2013 8:00
Reporter:	[ name withheld ]	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.23.55	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any

Description:
I'm trying to save bunary data into an EXISTING record with no luck

I keep getting the error:
****************************************
Microsoft OLE DB Provider for ODBC Drivers error '80004005' 

Query-based update failed because the row to update could not be found. 
****************************************
All the help i can find is about adding NEW records using rs.AddNew, which wont do here as i'm trying to update existing records.

ANY help at all would be great.

I read somewhere thou that MyOBDC doesn't handle updates to well from record sets.

Oh yeah, update sql statements wont work with binary data as far as i know.

How to repeat:
sub SaveBlob(sbdatabase, sbtable, sbWhere, sbfield, sbdata)
  Set bprs = Server.CreateObject("ADODB.Recordset")
  bprs.open "SELECT * FROM `" & sbdatabase & "`.`" & sbtable & "`", cn, adOpenKeyset, adLockPessimistic, adCmdText
  bprs.Filter = sbWhere
  ic123 = 0
  while not bprs.EOF
	ic123 = ic123 + 1
	bprs.MoveNext
  wend
  if ic123 = 1 then
	bprs.MoveFirst
'	bprs.Update
	bprs(sbfield).AppendChunk = sbdata
	bprs.Update
  end if
  bprs.close
  set bprs = nothing
end sub

This bug has been around for a while. I am not sure why it has been listed as "Medium" "Non-Critical" when it has to do with updating a database record. This is an everyday occurrance and is needed to be working correctly. Workarounds are a pain in most cases.

I would like to see this escalated to be fixed as soon as possible instead of continously sitting on the back burner.

Thank you.

Mike

Could you please provide a complete test case, table schema and
how you calling sub SaveBlob(...) with their arguments.

Thanks you in advance.

i'm also having same problem as well,

at first ,couldn't not addnew() .. then I saw from somewhere that in connection command add "option=3"  ,  and it worked...but now I'm trying to update()  but doesn't work.. i have tired everything

also it's so hard to find the solution,  is there anyone know how to solve this, i would really really appreciate it.  I'm doing the final year project,and this is killing me :(

Tan
t_srikaew@hotmail.com

oh.. i have done it working now..

apparently, after my experiment and tried in eveyway.. I found (guess) that after using .addNew()   and some of the field was not added anything in it, not even NULL. Therefore when Update() it doesnt' find the table feild..

I started by , go to DOS command line ,and update through that. make sure all feilds of  a table has some value in it.. even NULL ..   then I can then update via update()..after that i changed code on register.asp , add "" NULL value to those empty on Addnew() and Update()

also, my sql command when I update .. i select only the field i need to update , not * .. and it works..   

dont' know what's going on much, just though I'd share .. hope it helps, it works with me.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I'm also receiving error 80004005 in VB6, SP6, MDAC 2.5 up to 2.8, MySQL server 4.1.11, MySQL ODBC driver 3.51.11, at the end (so it seems) of some select queries.

After the first errors, I tried to debug by running a sequence of 3 to 4 test queries with the following template (changing only the table name in the 'from' clause):

   Set oadoRs = New ADODB.Recordset
   With oadoRs
      .Open "select * from tFOR;", oadoConnection, adOpenStatic, adLockReadOnly
      If .RecordCount > 0 Then
         Stop
      End If
      .Close
   End With
   Set oadoRs = Nothing

If I repeat this sequence 3 or 4 times without changing the table name, then if it works the first time, it always works.
If I repeat this sequence 3 or 4 times changing the table name in each sequence, I get error 80004005 somewhere, depending on the used tables.

All the tables used in the queries have only 'int', 'tinyint', 'date' and 'char' column types.

The error seems to be related with table size... Error frequency seems to get higher as the used tables have more records, but it can occur with tables with as low as 10 or 20 records.

I am also having a problem with this. It has been occurred consistantly on MySQL server 3.23.58 on linux and also by our customer on version 4.0 on Linux. The client is MyOBDC installed from MyODBC-3.51.11-2-win.msi.

Table defintion is as follows:
CREATE TABLE `WGrawUsers` (
  `Code` int(11) NOT NULL auto_increment,
  `UserName` varchar(255) default NULL,
  `FullName` varchar(255) default NULL,
  `Domain` varchar(255) default NULL,
  `Locator` varchar(255) default NULL,
  `Entity` varchar(64) default NULL,
  `Comment` varchar(255) default NULL,
  `BillRate` double default NULL,
  `CostRate` double default NULL,
  PRIMARY KEY  (`Code`)
) TYPE=MyISAM |

Table contains one row as follows:
+------+----------+----------+-----------+-------------------+--------+---------+----------+----------+
| Code | UserName | FullName | Domain    | Locator           | Entity | Comment | BillRate | CostRate |
+------+----------+----------+-----------+-------------------+--------+---------+----------+----------+
|    1 | richard  |          | MOONRAKER | MOONRAKER\richard | ST.1.1 |         |        0 |        0 |
+------+----------+----------+-----------+-------------------+--------+---------+----------+----------+

Application is written in C and is using ADO to connect to MySQL server through a DSN. The following query is issued on a record set (adOpenForwardOnly,adLockPessimistic):
SELECT * FROM `WGrawUsers` WHERE `Locator` = 'MOONRAKER\\richard';

One row is returned. C code uses rs.SetValue to write the value 'richard' to the field UserName. rs.Update() returns the error:
Query-based update failed because the row to update could not be found.

Any help suggestions on fixes or workarounds would be greatly appreciated.

Regards,
Richard Cartwright,
Spherical Technology

Sorry.
i'm the original requestor, but have lost my login details so couldn't reply.

further investigating has led to this conclusion.

Table:

CREATE TABLE `employees` (
  `employee_id` int(11) NOT NULL auto_increment,
  `employee_company_id` int(11) NOT NULL default '0',
  `employee_name` varchar(100) NOT NULL default '',
  `employee_rc4_login` mediumblob,
  `employee_login` varchar(100) default NULL,
  `employee_password` varchar(100) default NULL,
  `employee_email` varchar(100) default NULL,
  `employee_email_mail_server` varchar(100) default NULL,
  `employee_email_username` varchar(100) default NULL,
  `employee_email_password` varchar(100) default NULL,
  `employee_email_check` int(1) NOT NULL default '0',
  `employee_security_level` int(11) NOT NULL default '0',
  `employee_password_expiry` date NOT NULL default '2003-12-31',
  `employee_timeout` int(3) NOT NULL default '15',
  `employee_products_access` int(1) NOT NULL default '0',
  `employee_shop_access` int(1) NOT NULL default '0',
  `employee_webaccess_access` int(1) NOT NULL default '0',
  `employee_clients_access` int(1) NOT NULL default '0',
  `employee_projects_access` int(1) NOT NULL default '0',
  `employee_tasks_access` int(1) NOT NULL default '0',
  `employee_staff_access` int(1) NOT NULL default '0',
  `employee_settings_access` int(1) NOT NULL default '0',
  PRIMARY KEY  (`employee_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 COMMENT='Employees - Login control and task assignment'; 

Connection String:

  strConn = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=driver={MySQL ODBC 3.51 Driver};server=servername;uid=username;pwd=password;database=database;option=18439"

ASP Code:

<%
Set RS = Server.CreateObject("ADODB.Recordset")
userid = 4

RS.Open "SELECT employee_id, employee_company_id, employee_name, employee_login, employee_password, employee_email, employee_email_mail_server, employee_email_username, employee_email_password, employee_email_check, employee_security_level, employee_password_expiry, employee_timeout, employee_products_access, employee_shop_access, employee_webaccess_access, employee_clients_access, employee_projects_access, employee_tasks_access, employee_staff_access, employee_settings_access from employees", cn, adOpenKeyset, adLockPessimistic, adCmdText
RS.Filter = "employee_id = " & ToSQL(userid, "Number")
  for each fld in rs.Fields
	RS(fld.name) = fld.value
  next
  RS.Update
RS.close
set RS = nothing
%>

Yes this is just a loop that restores the current value of the field, and it works, no problems... but what... theres more...

change the open query to:
RS.Open "SELECT employee_id, employee_company_id, employee_name, employee_rc4_login, employee_login, employee_password, employee_email, employee_email_mail_server, employee_email_username, employee_email_password, employee_email_check, employee_security_level, employee_password_expiry, employee_timeout, employee_products_access, employee_shop_access, employee_webaccess_access, employee_clients_access, employee_projects_access, employee_tasks_access, employee_staff_access, employee_settings_access from employees", cn, adOpenKeyset, adLockPessimistic, adCmdText

and do the same query... it fails with the "80004005" error.
so what changed you ask... I added the binary field to the query.

interestingly, if you dont use (either specify in the filter or update the value) all the fields you can sometimes get a different error, but thats just an interesting fact.

So it looks like, what we've always known, MyOBDC doesn't like updating binary.

Developer guys... any chance of re-opening the investigations????

Hi all,
Even we are facing this issue, and unfortunately this has hit us when we have completed over 70% of the development. We have the requirement where a user can add a field to an existing table, but when we try to modify its contents we get  "Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
Query-based update failed because the row to update could not be found.".

Its is proving to be a hugh stumbling block and there isn’t much help on this error on most sites which we have visited. Would highly appreciate if someone could point us to forums or sites where people have faced similar issue and successfully resolved it.

Thanks in advance.

Heres the solution.

http://www.aspfaq.com/show.asp?id=2009
You are using MySQL, have opened a recordset that does not include a primary key, and have issued rs.update() in an attempt to modify a single row. Use an UPDATE statement. 

In a nut shell, you need to have a primary key defined on the table which is being updated. It worked for us.

I think I have found a viable workaround to the bug.

I found this bug report while searching for information when the very same was happening in ONE of my computers (the same error message, in the same circumstances - there was even a TEXT field in the table). That was the strange thing - the very same binary, accessing to the very same database, worked on my laptop but failed with the "the row to update could not be found" error on my development computer. After reading all the comments (which weren't of any help), I realized that there should any difference between my two machines, and after checking the versions of MySQL-connector and ADO, I looked at the ODBC data source options. Bingo! The first two chekboxes in the Advanced->Flags 1 tab were checked in the laptop, but not on the development machine. I checked them, and it started working flawlessly! By testing, I have came to the conclusion that only the second box has to be checked.

So the proposed workaround is to edit the ODBC data source, go to the Advanced->Flags 1 tab (in MySQL/Connector 3.51.10 or newer) or to the Options dialog (in MyODBC 3.51.9 or older), check the second checkbox, labeled "Return Matching Rows", and save the changes. That's all.

Can anyone confirm it's working outside my computers?

I can confirm that checking the Return matching rows does indeed fix the bug!!

Many, many Thanks !!!!!! :)

None of the suggestions work. Has anyone else found a solution?

In My case the same bug causes the ODBC and IIS 5.0 to crash.
I am using same method exactly as yours.
Windows XP SP2 and tried in vain with ODBC 3.5 connector, 5.1 Alpha and 5.1 beta.
used MySQL community Edition 5.0.45 as server.
If i run the page 2 times i get two different random errors.
If same database table with same fields is connected to Ms Access 2000 database it works fine.. all fields are of type Long integer in access (int (10)in my SQL including the primary key I used)

 the same odbc connector behaves differently if used in Windows 2003 server
 with SP1 and with SP2 both in R2 release and non r2 release too.
 I get this ODBC errors only on Server or with IIS in high isolation mode.
Else i need to reboot the PC to get any ODBC code working anymore.

reboot is the only option
It's really a pain making work a rounds for this.

Thanks
Signed 
Mohnkhan
http://www.mohitech.com

Still cannot repeat the problem.