MySQL Bugs: #26343: #DELETED appears in cases where it should not

Bug #26343	#DELETED appears in cases where it should not
Submitted:	13 Feb 2007 21:01	Modified:	19 Oct 2007 12:56
Reporter:	Ross Ivantsov	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	5.00.12 20070307	OS:	Windows (Windows XP)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	#DELETED, creating, new, record

Description:
When creating new record in Access 2003 in a linked ODBC table, #DELETED appears in ALL fields!!!

How to repeat:
1.) Create a sample database with utf8
CREATE DATABASE new_db CHARACTER SET utf8 COLLATE utf8_general_ci;

2.)Create an empty table
CREATE TABLE `wtmorders` (
  `ID` int(11) NOT NULL auto_increment,
  `Barcode` char(100) default NULL,
  `NumCode` char(100) default NULL,
  `Responsible` int(11) default NULL,
  `StartDate` date default NULL,
  `PlanFinishedDate` date default NULL,
  `RealFinishedDate` date default NULL,
  `OrderStatus` int(11) default NULL,
  `Requisition` int(11) default NULL,
  `wtmtimestamp` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,
  PRIMARY KEY  (`ID`),
  UNIQUE KEY `Barcode` (`Barcode`),
  UNIQUE KEY `NumCode` (`NumCode`),
  KEY `fkOrder_OrderStatus` (`OrderStatus`),
  KEY `fkOrder_Employee` (`Responsible`),
  KEY `fkOrder_Requisition` (`Requisition`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

3.) Link this table to a newly created Access 2003 database.

4.) Open the table. Try to enter a value in one field, like Barcode. Go to next record.

OHMYGOD - #DELETED appears in ALL fields!!!

The same database, the same table works correctly in MyODBC 3.51. ID is autoincremented and all not filled fields are left blank. 

With ODBC 5.00.11 all form logic stops working. The value of fields is "#DELETED". Guys, its a disaster. :))

Suggested fix:
Don't know.

Hi Ross and thanks for your report. I was able to verify it as you described. After reopen, all fields have their entered values though. This suggests that you can safely continue inserting records and do a refresh occasionaly.
Tested with 5.00.12.

Hello,

Thank you for paying attention to my report. Yes, sure I can do a refresh when I am editing data through table or simple form, but this behaviour is not correct, and we will not use 5.00.11 in our deployments fo this.

!!! There are many situations where Refresh is not possible to realize, like having a form with many linked subforms, when you run VBA AddNew, and the subforms are expected to refresh immediately (because they have Child - Master fields properties set, you know Access of course :). And because Master field becomes a special nice string "#DELETED", subforms start to spit error messages and stop working. I CANNOT insert refresh between AddNew command and the moment, when subforms start updating. Rewriting tons of code is not my purpose either.

I would really appreciate if you address this issue. Its serious.

Hi Ross. It is even more serious than you noticed...

> Thank you for paying attention to my report. Yes, sure I can do a refresh...and we will not use 5.00.11 in our deployments fo this.

Ofcourse, it is still beta.

> !!! There are many situations...

I know.

> I would really appreciate if you address this issue. Its serious.

It is, I'm raising severity and assigning Jess.

i have the same problem only in MSACCESS XP, everything works great in my production system which is running Mysql 5.0.24a and My
ODBC 3.51.12.   I recently loaded my test system with MySql Enterprise 5.0.40 and connected my front end Production MSACCESS app.   All msaccess forms recordsource is a MSaccess stored query and all tables have a primary key and timestamp.

Did some testing and resolved (I think!) what caused my app to blow up when connecting to 5.0.40.   What led me down this trail was some of my forms allowed me to INSERT a new record and work fine, while others after insert displayed #DELETED#.   I was able to duplicate the issue simply create a test table with a autonumber Primary Key, a varchar field, a date field, a timestamp field.   Link to your access app.  open table and add a record, you will get #deleted#.   
Now modify your TEST table and change the date field to 'datetime' datatype.  Drop and RE-Link your TEST table to the msaccess app.   Now add a new record problem goes away.   As you know MSaccess can't deal with the Mysql 'DATE' datatype very well MSaccess only has a 'datetime' data type so looks like according to my testing that Myslq VERSION 5.0.40 is a little fussier.  I also upgraded to MyODBC 3.51.15 this did not cure the problem, only changing the datatype to 'datetime' fixed it.   dhuhncke@nd.gov

This is a duplicate of bug #24535