MySQL Bugs: #31308: MyODBC 5.1.0 alpha shows #deleted

Bug #31308	MyODBC 5.1.0 alpha shows #deleted
Submitted:	30 Sep 2007 21:08	Modified:	4 Dec 2007 5:42
Reporter:	Chaitanya Kulkarni
Status:	Closed
Category:	Connector/ODBC	Severity:	S2 (Serious)
Version:	5.1.0 alpha	OS:	Microsoft Windows (XP SP 2)
Assigned to:	Bugs System	Target Version:
Tags:	ODBC, ODBC5-RC

Description:
When using MyODBC 5.1.0 alpha to connect to MySQL 5.0.45 on the same PC having Windows Xp
SP 2 having MDAC 2.7 installed with all requisite updates and using MS access 2003 to
update the data in the tables, for updated record it shows #deleted. I have taken care
for necessary actions for the same bug in MyODBC 3.51 and using the connectors from 3.51
family successfully and without any problem. The actions taken are, 1)added an timestamp
field in all tables with default value., 2)using only Int columns for numeric, not long
int, 3) MDAC is updated, 4) option number for MyODBC is 16386 while connecting. I think
this is a small bug and cane be removed. 
Also one another point is when I refresh linked table dat in datasheet view it doesn't
removes #deleted but when I do the sorting on any field in that table, #deleted in
removed and correct unicode data is shown. Why?
I need to use MyODBC 5.1.0 for Unicode support as it is not properly implemented in
MyODBC till 3.51.20.
Thanks

How to repeat:
Use settings as above and set database charset to UTF8 and collation to unicode_ci. link
tables from this database to a Access file using MyODBC 5.1.0 and update data through
datasheet view. Use unicode supported languages for data on Windows XP SP2. I am using
MARATHI in unicode.

2 files, one MDB and one SQL script with small data

Attachment: myodbc_test.zip (application/zip, text), 194.05 KiB.

Are you aware of this documentation
http://dev.mysql.com/doc/refman/5.0/en/myodbc-errors.html#qandaitem-25-1-6-3-6

(though it looks like it coiuld need an update with its references to Access2!)

in particular this "For all versions of Access, you should enable the Connector/ODBC
Return matching rows option."

check the 'advanced' tab of the DSN properties!

Peter
(not a MySQL person)

Could you please see documentation commented by Mr. Peter. Thanks.

Yes, I have enabled all the required options while creating a DSN on windows. options
enabled are - Change BIGINT columns to INT, Allow big results, Change BIGINT columns to
INT, Enable Auto Reconnect etc., but it still gives error.

Hi Chaitanya and thanks for your report. True, Access will still return #DELETED from time
to time but I fail to see how this is S2? #DELETED means Access can't handle returned
value not that this particular field has value of #DELETED... It is mainly connected to
several areas we're working on:
 - Access uses UTF-16 internally (as Windows does), we don't
 - MySQL data types are outside of what MS SQL server can handle
 - Error in type mapping of MyODBC
I will check on this even though 5.1 is ALPHA but I'll need more info. Please attach
my.ini/cnf file used to start MySQL server and DDL script suitable for import via MySQL
cl client containing table structure and some data for table that's causing this error to
appear.

My apologies, I just noticed DDL script is attached.

I would like to comment on the same issue:

I use Windows XP SP2 (so Jet SP and MDAC are ok), Access 2003/2007 and MyODBC 5.1 alpha.
All options set as recommended. I created a table in MySQL in utf8 (Primary key called ID
with auto_increment, Name varchar(20) and timestamp ts).

When I link this table in Access and try to add new records by modifying field 'Name' it
adds a record with all fields marked as #Deleted. Only when I select "Refresh All" from
Access menu it shows that record was added correctly.
The problem is maybe that Access cannot forecast the auto_increment value.

This was not a problem with old 3.51 MyODBC version.
Do you recognize the problem?

This problem happens only when table contains field of type CHAR with length 100 or more.
Varchars seem to work ok.

Exacatly the situation takes place if table contains fields of type CHAR, VARCHAR with
length > 85 or fields of type TEXT

Bug #24535 is a duplicate of this bug here

I can reproduce this with MyODBC version 5.1 and MS Access 2007.

When looking into a Mysql database table all cols in all rows just show #DELETED.

When sorting the values, they will shown as they are. But when you try to update one of
it, immediately it switch to #DELETED.

Works fine against svn rev 912. Please re-test after 5.1.1 is released and let us know if
you have any additional problems.

I will test th MyODBC 5.1 with other applications using ODBC driver for data transfer.
Also as per my knowledge MyODBC 3.51 does not have support for Unicode.