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:Microsoft Windows
Assigned to: CPU Architecture:Any
Tags: #DELETED, Alphanumeric, Microsoft Access, MS Access 2003, ODBC, primary key

[24 May 2007 3:26] Jackie Chin
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)
[24 May 2007 3:28] Jackie Chin
SQL and Access DB

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

[24 May 2007 3:30] Jackie Chin
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.

[24 May 2007 3:35] Jackie Chin
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.
[25 May 2007 1:08] Jackie Chin
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
[11 Jun 2007 12:13] Tonci Grgin
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?
[11 Jun 2007 12:14] Tonci Grgin
Image of table in MS Access with changed values in PK field.

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

[11 Jul 2007 23:00] Bugs System
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".
[22 Oct 2007 16:49] Susanne Ebrecht
This is a duplicate of bug #24535