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 3:26	Modified:	22 Oct 2007 16:49
Reporter:	Jackie Chin	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.00.11	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	#DELETED, Alphanumeric, Microsoft Access, MS Access 2003, ODBC, primary key

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