MySQL Bugs: #5634: Recordset UPDATE with ADODB

Bug #5634	Recordset UPDATE with ADODB
Submitted:	17 Sep 2004 19:54	Modified:	13 Dec 2004 17:50
Reporter:	Simon Wesche	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	3.51.06.00	OS:	Windows (Windows 2000)
Assigned to:		CPU Architecture:	Any

Description:
I am running MySQL 4.0.17 on Windows 2000 professional. The database is interfaced to a web site running on Internet Information Server 5.0 using MyODBC 3.51.06.00.

The problem is updating data using the ADODB.Recordset object in ASP. It appears the problem has been reported earlier with ID 647 - but neither feedback nor solution was provided.

When selecting only non-key columns from a single record (using primary key), and subsequently updating these columns, the result is fatal: ALL records holding the old values from the selected columns gets updated to the new value - irrespective of the Primary Key.

How to repeat:
Assume the following table:

CREATE TABLE Table1 (
  PrimaryKeyField int NOT NULL,
  Description varchar(100) default NULL,
  PRIMARY KEY  (PrimaryKeyField)
) TYPE=MyISAM;

Also assume Table1 contains records as follows:
1, "Description text"
2, "Description text"
3, "Description text"
...
n, "Description text"

Then consider the following code (assume the ADO-objects are declared and initialized already):

StrSQL = "SELECT Description FROM Table1 WHERE PrimaryKeyfield=1"
RecSet.open StrSQL, DBConn, 1, 3, 1
RecSet("Description") = "New description"
RecSet.Update
RecSet.Close

One would expect the MySQL server to excute a DML statement in line with:

UPDATE Table1
SET Description='New Description'
WHERE PrimaryKeyfield=1;

but when extracting the binary log, I discovered to my horror that the executed DML was:

UPDATE Table1
SET Description='New Description'
WHERE (Description='Description text');

Shocking! The Primary Key is clearly used for the initial SELECTion of the record, but in the subsequent UPDATE it is omitted. The result is clearly undesirable.

Suggested fix:
As a quick fix, I have changed the initial SELECT statement to include the Primary Key Column, like:

StrSQL = "SELECT PrimaryKeyfield, Description FROM Table1 WHERE PrimaryKeyfield=1"

Inspecting the binary log, this has the desired effect:

UPDATE Table1
SET Description='New Description'
WHERE (PrimaryKeyfield=1 AND Description='Description text');

This solves the problem... But I have been running in production for 8 months without this patch... Doh!

I tested your test case I got the expected behavior.
I used the driver 3.51.10 against the server 4.0.22.

OK, well, and I still have the undesired behaviour!

Since it is 3 months since I posted the original bug-report, I assume you guys are pretty busy. If you are interested in trying to nail the problem, let me know. I would be proud to be able to help you.

Meanwhile I will try upgrading the ODBC connector to 3.51.10 and/or the database to a more recent version, and see it that solves the problem.

For your information.
I was experiencing the same problem when trying to update one record in a table which I had migrated from MSAccess to MySQL using the MySQL Migration Tool.
However, when I tried the same on a copy of the table which I had created manually, the error did not occur.