MySQL Bugs: #28640: MySQL ODBC Connection MS Access shows #Deleted if Primary key alphanumeric

Bug #28640	MySQL ODBC Connection MS Access shows #Deleted if Primary key alphanumeric
Submitted:	24 May 2007 5:26	Modified:	22 Oct 2007 18:49
Reporter:	Jackie Chin
Status:	Duplicate
Category:	Connector/ODBC	Severity:	S2 (Serious)
Version:	5.00.11	OS:	Microsoft Windows
Assigned to:	Tonci Grgin	Target Version:
Tags:	Alphanumeric, primary key, #DELETED, MS Access 2003, Microsoft Access, ODBC

Description:
When connecting from Microsoft Access 2003 to MySQL 5.0.37 (on Solaris) through ODBC,
records appear as #Deleted if the Primary Key is a combination of Alphanumeric characters
with a length divisible by 4 (this occurs randomly as some combinatons do not trigger this
to happen, and on initial tests sequence of records also affects the results of records
displayed but not related to updates).

When affected records are changed to have an id value of '-<someId.>' note the negative
sign at the front, it appears to make the record appear normally. This problem occurs
with both records updated/created in Access as well as Query Browser.

All previous steps to ensure a TIME STAMP, Primary Key field, latest drivers, BIG INT to
INT, etc followed. Attached to this bug report are the SQLs to create a Test Schema with
a single table and 2 records.

There is also an attached copy of the Access Database. ODBC was created with name 'MySQL
ODBC Test'.

How to repeat:
- Install MySQL 5.0.37 for Solaris on Solaris box
- Run attached SQL to create 'Test' Schema
- Install ODBC 5.00.11 Driver for Windows
- Create ODBC called MySQL ODBC Test pointing to Solaris Box with MySQL
- Open MDB file in Microsoft Access 2003
- Open 'contact' form
- 1 Record appear as #Deleted (id '280n'), 1 Record appear to be normal (id '2')
- Update record '280n' to have id '1'
- Refresh in access 'contact' Form (restart Access if you want to)
- Update record '1' to id '280m' and record '2' to id '280n'
- Refresh in access 'contact' Form (on my machine this appears as fine but not sure if it
will replicate on different computers)

SQL and Access DB

Attachment: MySQL_Bug_Report_ODBC_5_00_11.zip (application/x-zip-compressed, text), 224.79 KiB.

SQL and Access DB - Updated Zip with compacted and repaird Access DB

Attachment: MySQL_Bug_Report_ODBC_5_00_11_v2.zip (application/x-zip-compressed, text), 9.11 KiB.

After revisiting the Access file, I am able to now see the '280n' record, HOWEVER when I
attempt to modify it, it changes the record to #Deleted. Changing the record with id '2'
is fine. Extremely weird.

To work around this I added a temporary Auto Incrementing Id in addition to the existing
Id and made the new Auto Incrementing Id the Primary Key. I then had to add this Id to
Access and set the default to =[KeyPreview] and hide the column from users. Obviously
this is not ideal considering that I'm modifying an existing table with data. I have also
created a Unique Index for my existing Id to ensure integrity. Having 2 indexes vs 1 (for
a unique record identifier) is not a long term solution and modifying existing enterprise
tables is not a long term solution either (of which I backed up and will reverse changes
if/when the ODBC is fixed). I hope someone can respond to my report soon before I am
forced to add this 'workaround' to more tables than critically required at the moment.
Thanks

Hi Jackie and thanks for your report. I was unable to repeat the problem using MySQL
server 5.0.44BK on WinXP Pro SP2 localhost with c/ODBC 5.0.12 (as can be seen in attached
picture). Can you please try latest snapshot from
http://downloads.mysql.com/snapshots/mysql-connector-odbc-5.0/ and inform me of result?

Image of table in MS Access with changed values in PK field.

Attachment: 28640.jpg (image/jpeg, text), 16.53 KiB.

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

This is a duplicate of bug #24535