MySQL Bugs: #59968: MS Access ODBC Driver 5.1.8 #DELETED on Insert after Changing field to NULL

Bug #59968	MS Access ODBC Driver 5.1.8 #DELETED on Insert after Changing field to NULL
Submitted:	5 Feb 2011 20:35	Modified:	29 May 2013 12:53
Reporter:	Tim Gold	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1.8	OS:	Any
Assigned to:	Bogdan Degtyariov	CPU Architecture:	Any
Tags:	ODBC 5.1.8 Access #DELETED on insert record

Description:
I have been developing a MS Access 2010 Application using MySQL 5.1 as the backend. I have about 30 Linked tables and have had extremely good success with it until a problem presented itself that I finally tracked to a specific action. My connection strings are built according to all the recommendations and using Option=3 in the string to make sure FLAG_FOUND_ROWS are implemented.

I know in earlier version this has been a problem because MS Jet engine loses Cursor position under some circumstances.

The problem arises when a new record is started and all the NOT NULL fields are added but before the insert is complete a Default Null field is changed from NULL and then back to NULL again. The record does get added with all the NOT NULL fields but shows up ad #DELETED.

I tried to add additional Flags ie. FLAG_DYNAMIC_CURSOR to work around the issue but no matter what I try the same thing happens. If I follow through will all the initial changes and don't ever set the "Default NULL" fields back to NULL even if I change them to "" then the record succeeds and the cursor follows fine.

Are there any recommendations?

How to repeat:
Create a linked table to MS ACCESS with all the recommended field ie. ID - Primary Key and some NOT NULL Fields and some Default NULL fields including at TIME_STAMP field.

Connection String -"Driver={MySQL ODBC 5.1 Driver};Server=IP;Port=3306;Database=DB;User=User;Password=Pass;Option=3"

The issue was tested with default NULL VARCHAR() and DATETIME fields being changed in a new record from NULL to valid value then back to NULL.

Tim,

I could not repeat the problem in Access 2007 and 2010.
Both times NULL values assigned correctly to the columns.

Here is the table I used:

tabnull | CREATE TABLE `tabnull` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `vcf` varchar(45) DEFAULT NULL,
  `dtf` datetime DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1;

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".

I'm closing this bug because I can not continue without feedback from the reporter. If you have new info, please reopen the report.