MySQL Bugs: #26653: MyODBC / ADO Enum data type is frequently failing to update when no Primary key

Bug #26653	MyODBC / ADO Enum data type is frequently failing to update when no Primary key
Submitted:	26 Feb 2007 23:36	Modified:	31 Jan 2008 18:50
Reporter:	Erica Moss	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	3.51.12	OS:	Windows (win xp)
Assigned to:		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.  It fails with roughly the same frequency as the Set data type which is entered as a separate bug.  If you manually 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 Enum ('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 Enum 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: enumbug.vbs (application/octet-stream, text), 3.60 KiB.

data file

Attachment: enumTest.csv (application/vnd.ms-excel, text), 443 bytes.

have to look into this one a bit more

NEW data file

Attachment: enumTest.csv (application/vnd.ms-excel, text), 967 bytes.

NEW script unit test fle

Attachment: enumbug.vbs (application/octet-stream, text), 3.66 KiB.

There are a few errors in the first set of test files, please use the ones that say "NEW".

Additional Information:
It appears that if a primary key column 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, this should be duplicate of Bug#26651 right? I'll post my findings there.