MySQL Bugs: #70297: Foreign characters in primary key show #deleted in Access table through ODBC

Bug #70297	Foreign characters in primary key show #deleted in Access table through ODBC
Submitted:	11 Sep 2013 12:05	Modified:	14 Apr 2023 17:33
Reporter:	Emmanuel Renauld	Email Updates:
Status:	Verified	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	ODBC 6.3 and before, 8.0.32	OS:	Windows (Server 2008, 2012 R2)
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	#DELETED, Access 2002, Access 2010, Access 2016, foreign characters, mysql 5.5, MySQL 5.7, ODBC Connector, primary key, Special Characters

Description:
Hello,

I have a table in MySQL 5.0.96 called "Book_publishers". The name of the publisher (text field) is the primary key. Some publishers are e.g. Japaneese. When looking at the table in Access 2002 through ODBC 5.2.5 (and previous versions), the lines containing foreign characters display #deleted. Same for any special character like the 3dots, etc.
I have another table with the same #deleted display when I put special characters in the primary key, and many other tables with fields (not primary key) where the special characters display alright in Access.

Thank you for your help.

How to repeat:
In MySQL I take any of my tables with a text field in the primary key. I paste the 3dots character somewhere in this field. In Access, the table line immediately displays #deleted.

Suggested fix:
Allow special characters in primary keys.

Please provide the output of: show create table your_table_name; . Thanks.

CREATE TABLE `rt_book_publisher` (
  `Publisher` varchar(150) NOT NULL,
  `Inprint` varchar(100) default NULL,
  `Country` varchar(30) default NULL,
  PRIMARY KEY  (`Publisher`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Please read the instructions at:

http://dev.mysql.com/doc/refman/5.5/en/connector-odbc-errors.html

#DELETED# Records Reported by Access

Access reports records as #DELETED# when inserting or updating records in linked table...........

I have already seen and studied the page you mention but couldn't make any improvement from it unfortunately.
Related to the problem, I read on this page:

"Also, get and apply the Microsoft Jet 4.0 Service Pack 5 (SP5), which can be found at http://support.microsoft.com/default.aspx?scid=kb;EN-US;q239114. This fixes some cases where columns are marked as #DELETED# in Access."

When I follow this link, I read:
For computers that are running Windows Server 2008 and later versions:
If you are running Windows Server 2008 or a later version, you have a later version of Jet 4.0 than the version that is included with Jet 4.0 SP8.

The version I use is indeed 4.00.9756.0.

On the page you mention, I also read:
For all versions of Access, enable the Connector/ODBC Return matching rows option. For Access 2.0, also enable the Simulate ODBC 1.0 option.
I don't know if that is important but I don't see where to enable the Simulate ODBC 1.0 option. (Return matching rows option I allowed in the DSN).

Test adding a dummy timestamp column that was the reason to ask you the create table (lack of the timestamp column). Thanks.

I have added a timestamp field with some data in it.
Whenever I insert the 3dots character in the primary key, the record still displays #deleted in the table seen in Access through the ODBC connector. When I remove the 3dots character, the value of the primary key is displayed correctly again in Access.

Then after adding the timestamp field now the issue happens with text that has 3 dot?. If yes please print an example of it. Thanks.

The problem will occur with any special character inserted into the primary key text field.
The 3dots character is an example: (…)
Any foreign character will create the problem as well e.g. 学校への手紙

Access display

Attachment: access_deleted.png (image/png, text), 61.16 KiB.

I couldn't repeat on Access 2010 (Unlucky I don't have Access 2008). Please see picture attached.

OK, thanks a lot for trying in Access 2010.

What is weird though, is that if I convert my Access 2002 database (.mdb) into an Access 2010 database (.accdb), I still see the #deleted problem.
So the problem doesn't seem to be related to the Access version.

We use MySQL 5.0.96 and Windows server 2008. Could the problem come from there?

If you use WorkBench 6.0.7 how are displayed these characters?. Thanks.

Unfortunately, I know nothing about Workbench...

WorkBench

Attachment: wb_showing_row.png (image/png, text), 70.71 KiB.

I asked you to use WorkBench just to check if the issue is the text was inserted in wrong way so it can discard Connector issue (see prior attached picture showing WorkBench the same row). You can get WorkBench: http://dev.mysql.com/downloads/tools/workbench/ . Thanks.

Hum! Installing Workbench on the database server will rely on the server administrator and this can take a long while I am afraid. Is there any other track to follow?

Hi Emmanuel,

are you using ANSI or UNICODE version of Connector/ODBC 5.2.5?

We use the UNICODE version, but we also tried the ANSI version and the problem is the same in both cases.

Miguel, I finally understood what you wanted, sorry.
I don't use WorkBench but I use Navicat.
And yes, when I look at the MySQL table directly through Navicat, I see the foreign or special characters in the primary key alright. It is when those characters go through the ODBC connector that they turn up as #deleted in each field of the record in Access.
I emphasize that the text field is a primary key. When I use the same characters in a text field that is not a primary key, they display alright in Access.

Emmanuel,

This looks like a problem in your MS Access 2002.
My Access 2007 displays foreign characters without any problems.
Also, please note that MS Access 2002 is no longer supported by Microsoft since 2011. You can check its life cycle here:

http://support.microsoft.com/gp/lifeSelectOff

It looks like you should consider upgrading your Access.

Hello Bogdan,

What is weird though, is that if I convert my Access 2002 database (.mdb) into an Access 2010 database (.accdb), I still see the #deleted problem.
So the problem doesn't seem to be related to the Access version.

We use MySQL 5.0.96. Maybe that is what we should upgrade to MySQL 5.6.

Error in MS Access

Attachment: mysql_5_0_91_error.jpg (image/jpeg, text), 54.64 KiB.

Emmanuel,

Thank you for your comment.
Finally I was able to repeat the bug. However, it is a bit different from what you observed: I tried adding another column at the end of the table and it resulted in an error "-- ODBC call failed" and the data as displayed on a screenshot previously posted.

Setting bug to verified.

Finally, we upgraded to MySQL 5.5
But the bug remains, whether we use ODBC connector 5.1 or ODBC connector 5.2.5, and whether we use Access 2.0 or Access 2010.
Special characters in a primary key of a table in MySQL have the fields display "#deleted" in Access.

We'll soon upgrade to MySQL 5.6 but with little hope!

I have a similar problem with these characters. Does anyone know's an answer?
(Of where to look?)

We still have the problem now that we have migrated to MS Windows Server 2012 R2, MySQL 5.3, ODBC 6.3 and MS Access 2016. We modified the database so that foreign or special characters do not appear in primary keys but that was not always possible. Some records show as #deleted in the Access tables but actually the record is still there in the MySQL tables.

I meant MySQL 5.7 now.

Bug #110274 marked as duplicate of this one

I have a similar problem with these special characters.(Japanese)

MySQL 8.0.31
MySQL ODBC Connector 8.0.32
MS Access 2016(not 365)

I tried follows.

1) create new table
 CREATE TABLE `pk_test` (
  `key1` int NOT NULL,
  `key2` varchar(10) COLLATE utf8mb4_bin NOT NULL,
  `val1` varchar(10) COLLATE utf8mb4_bin DEFAULT NULL,
  PRIMARY KEY (`key1`,`key2`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;

2) insert data, primary key contains foreign character.(Japanese)
 insert into pk_test values (1, 'a', 'あ');
 insert into pk_test values (2, 'あ', 'あ');

 On Workbench 8.0.31,
 select * from pk_test
 returns below.
 1,a,あ
 2,あ,あ

3) Create DSN with ODBC(Unicode driver) Configuration options, Character Set set to [utf8mb4].
   Looking at the linked table in Access 2016 through ODBC 8.0.32(Unicode driver),

1,a,あ
#Deleted,#Deleted,#Deleted

4) create DSN with ODBC(ANSI driver) Configuration options, Character Set set to [cp932].
   Looking at the linked table in Access 2016 through ODBC 8.0.32(ANSI driver),

1,a,あ
2,あ,あ

But obviously, after follow data(non cp932 character, "𠮷") insert,
 insert into pk_test values (3, 'b', '𠮷');

Case1. Unicode driver
1,a,あ
#Deleted,#Deleted,#Deleted
3,b,𠮷

Case2. ANSI Driver (ODBC driver call failed.)
#Name?,#Name?,#Name?
#Name?,#Name?,#Name?
#Name?,#Name?,#Name?

 On Workbench 8.0.31,
 select * from pk_test
 returns below.
 1,a,あ
 2,あ,あ
 3,b,𠮷

Due to circumstances, it is unavoidable that Japanese is set in the primary key.
Is there any workaround?

*Note that, create report, export, convert to local table are no problems.

MySQL 8.0.32
MySQL ODBC Connector 8.0.32
MS Access 2016

After so many years and updates, the problem remains. Foreign or special characters are not possible in primary keys. This seems to be a Microsoft Access problem because in some circumstances (i.e. a drop down list in an Access form), the foreign characters will make it through the ODBC driver and the record will display properly (not as #deleted like in the Access table view).