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:
Category:Connector / ODBC Severity:S1 (Critical)
Version:5.00.12 20070307 OS:Microsoft Windows (Windows XP)
Assigned to: Assigned Account CPU Architecture:Any
Tags: #DELETED, creating, new, record

[13 Feb 2007 21:01] Ross Ivantsov
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,
  UNIQUE KEY `Barcode` (`Barcode`),
  UNIQUE KEY `NumCode` (`NumCode`),
  KEY `fkOrder_OrderStatus` (`OrderStatus`),
  KEY `fkOrder_Employee` (`Responsible`),
  KEY `fkOrder_Requisition` (`Requisition`)

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.
[7 Mar 2007 18:20] Tonci Grgin
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.
[8 Mar 2007 8:56] Ross Ivantsov

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.
[8 Mar 2007 9:46] Tonci Grgin
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.
[30 May 2007 20:09] david huhncke
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
[19 Oct 2007 12:56] Susanne Ebrecht
This is a duplicate of bug #24535