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

Bug #31308	MyODBC 5.1.0 alpha shows #deleted
Submitted:	30 Sep 2007 19:08	Modified:	4 Dec 2007 4:42
Reporter:	c k	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1.0 alpha	OS:	Windows (XP SP 2)
Assigned to:	Jess Balint	CPU Architecture:	Any
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.

I solve with the following step :
1. disable all primary key in mysql table
2. link all mysql table in msaccess without assign primary key.
3. restore all primary key in mysql table.

Thank you Tony for sharing this with us. If you feel up to it, I'd like to see ODBC trace of the failure and successful linking.