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 22:01	Modified:	19 Oct 2007 14:56
Reporter:	Ross Ivantsov
Status:	Duplicate
Category:	Connector/ODBC	Severity:	S1 (Critical)
Version:	5.00.12 20070307	OS:	Microsoft Windows (Windows XP)
Assigned to:	Bugs System	Target Version:
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