MySQL Bugs: #26651: MyODBC / ADO Set data type is frequently failing to update when no Primary Key

Bug #26651	MyODBC / ADO Set data type is frequently failing to update when no Primary Key
Submitted:	26 Feb 2007 23:12	Modified:	26 Feb 2007 23:20
Reporter:	Erica Moss	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.12	OS:	Windows (win xp)
Assigned to:	Assigned Account	CPU Architecture:	Any

Description:
This was discovered while running the fldupdate.vbs ADO compliance test.  I've attached a smaller vbs script setbug.vbs to this report.

When run against 3.51 this script will occasionally pass for a few records but will generally fail within 10 record updates.  If you manual select from mysql after the running the test, it is evident that the script is reporting correctly that the updates stop after a few records.  Often it will fail on the very first record.  When run against myODBC 5.0 it never passes.  This will be filed in a separate report for tracking purposes.

How to repeat:
Create test DB ado:
mysql>CREATE DATABASE ado;

To run the attached files in the ADO/script-unit test framework, drop the attached script file in the 'level 0' dir, and the data file in the common dir.  Open script-unit, drag the script file to the work area, highlight and run it.

What the test does:
1. Creates a test table as:  
CREATE TABLE set_test (C1 SET ('MySQL', 'is', 'number', '1'), C2 Timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP)

2. Adds 100 records to the new table
3. Opens a new connection and record set as: SELECT * FROM ado_test
4. Calls Update on each field giving it a new value
5. Compares the field.OriginalValue property with the newly assigned value to make sure that the new value was updated in the DB, and fails if they don't match.

script unit test fle

Attachment: setbug.vbs (application/octet-stream, text), 3.61 KiB.

data file

Attachment: setTest.csv (application/vnd.ms-excel, text), 442 bytes.

Additional Information:
It appears that if a Primary Key is added to the data set and the
necessary small changes are made to the script file, then this test will
complete successfully against the 3.51 drivers, so with this as a workaround I'm
lowering the priority of this bug, however the question remains why isn't it
able to handle the case where there is no primary key?  The worst that should
happen here is that the update might not have the desired results because there
was more than one match.  It shouldn't fail to update at all.

Eric, I think this is either
 a) SQLPos problem
or 
 b) you fail to control your updates depending on schema: http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q190727&GSSNB=1

"Update Criteria" property: The available constants for this property are as follows:
   adCriteriaKey = 0      Uses only the primary key
   adCriteriaAllCols = 1  Uses all columns in the recordset
   adCriteriaUpdCols = 2  (Default)  Uses only the columns in the recordset that have been modified
   adCriteriaTimeStamp = 3 Uses the timestamp column (if available) in the recordset
				
NOTE: Specifying adCriteriaTimeStamp may actually use adCriteriaAllCols method to execute the Update if there is not a valid TimeStamp field in the table. Also, the timestamp field does not need to be in the recordset itself.

What do you think?

I had the test (setBug.vbs) successfully passing, but after 3-rd time of sequential re-running the test started to always fail with Connector/ODBC 3.51.27. With 5.1.8 it never passed.