MySQL Bugs: #70851: #Deleted in MS Access query via ODBC

Bug #70851	#Deleted in MS Access query via ODBC
Submitted:	7 Nov 2013 8:50	Modified:	11 Nov 2013 8:10
Reporter:	Sue J	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.2.6	OS:	Windows
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any

Description:
When displaying data from a Microsoft Access query which is based on an ODBC linked table, rows containing a £ sign in one of the table fields are displayed as #Deleted.

This is probably a duplicate of bug 70297, but the steps to reproduce the error are slightly different.

The error manifests under Windows 7 and 8 with both the 32 and 64 bit ODBC Unicode connectors under both Office 2010 and 2013, 32 and 64 bit versions.

How to repeat:
1. Create an Event table with a 3 part key - Organisation Id (Int), Event Date and Event Name (Varchar)
Collation is utf8

2. Create a View in MySQL on the table to limit the records, as shown below (the user login is their Organisation Id) 
where (`event`.`Org_Id` = cast(user() as char charset utf8)) WITH CASCADED CHECK OPTION

3. Add records to the table, one of which contains a £ sign in the Event Name

4. In MSAccess create a link to the Event View using a DSN
[ODBC]
driver={MySQL ODBC 5.2 Unicode Driver}
UID = xxxxxxxx
Pwd = xxxxxxxxxxxxxxxx
OPTION=35
DFLT_BIGINT_BIND_STR = 1
COLUMN_SIZE_S32 = 1
Database = xxxxxxxxxxxxxxxx
SERVER = xxxxxxxxxxxxxxxxxxxxxx
PORT = 3306

5. The Event table at this point displays correctly, but any attempt to modify the data results in the record displaying #deleted and Access displaying the error message saying that another user has already modified the data.

5. In MSAccess create a Query to limit the records displayed to just those for this year, eg
Select * from Event where [Event Date] >= #1/1/2013#

6. The record with the £ in the Event Name now shows up as #Deleted

I tried swapping to the ANSI driver, and did not get the #deleted problem, but earlier posts on the MySQL Forum indicate that one should use the Unicode drivers with MsAccess

Hi Sue,

Thanks for your interest in MySQL software.

This is the expected behavior of Connector/ODBC driver.
If you want data to be returned as UTF8 the ANSI version of Connector/ODBC should be used. This is so because UTF8 is a variable-length encoding that does not include \0 bytes and hence cannot be represented as wide characters (SQLWCHAR type) on the client side. Instead of SQLWCHAR it is represented by single-byte characters SQLCHAR treated as UTF8 stream.

I know, it is confusing because UTF8 is a sort of Unicode, but it is very special and very different from UTF16, UTF32 or UCS2.

This behavior should be documented, so no more users would fall into the same issue.