MySQL Bugs: #10910: Access shows #Deleted for records inserted with NULL values

Bug #10910	Access shows #Deleted for records inserted with NULL values
Submitted:	27 May 2005 9:55	Modified:	23 Jan 2007 14:05
Reporter:	Martin King-Turner	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.11-2	OS:	Windows (Win XP SP2)
Assigned to:	Peter Harvey	CPU Architecture:	Any

Description:
When using MS Access to insert records into a table with an auto_increment primary key, Access shows #Deleted after the record is written if the data contains explicit null values.

I'm using MySQL 4.1.12 on Linux with MyODBC 3.51.11-2 on Win XP SP2.  I have the same problem on both MS Access 2002 and 2003.

How to repeat:
Create a table in MySQL such as:

CREATE TABLE `Test` (
`ID` INT NOT NULL AUTO_INCREMENT ,
`Field1` VARCHAR( 25 ) ,
`Field2` VARCHAR( 25 ) ,
`Timestamp` TIMESTAMP ,
PRIMARY KEY ( `ID` )
) TYPE = MYISAM ;

Use this table as an ODBC datasource for an MS Access linked table.

Create an Access form for this table.  Make the ID field read-only in the form, and add the following event procedure:

Private Sub Form_BeforeUpdate(Cancel As Integer)   
    Dim Message As String
    Dim ANull As Boolean
    
    Message = "About to create new record..."
    ANull = False
    
    If IsNull(Me.Field1) Then
        Me.Field1 = Null  ' Need this line to cause the problem
        Message = Message + " Field1 set to NULL."
        ANull = True
    End If
    If IsNull(Me.Field2) Then
        Me.Field2 = Null  ' Need this line to cause the problem
        Message = Message + " Field2 set to NULL."
        ANull = True
    End If
    
    If Not (ANull) Then Message = Message + "  No NULLS in data."
    
    MsgBox (Message)
End Sub

Open the form and use it to create a new record.  Leave either Field1 or Field2 balnk in the form when you save the record.  You should see a message box informing you that the blank field has been set to null.  Click OK to actually write the record, and you will see the form is filled with #Deleted, although the record is successfully written to MySQL.

The attached bug.mdb file has this form & code already created.

Interestingly, this problem only occurs if the fields are explicitly set to null in the above procedure.  If the lines "Me.Field1 = Null" and "Me.Field2 = Null" are omitted, the problem does not occur.

Suggested fix:
Inspecting MySQL's query log shows what I believe to be the problem:

SET AUTOCOMMIT=0
INSERT INTO  `Test`  (`Field1`,`Field2`) VALUES ('data',NULL)
SELECT `ID`,`Field1`,`Field2`,`Timestamp`  FROM `Test`  WHERE `ID` IS NULL
COMMIT
SET AUTOCOMMIT=1
SELECT `ID`,`Field1`,`Field2`,`Timestamp`  FROM `Test`  WHERE `ID` IS NULL
SELECT `Test`.`ID` FROM `Test` WHERE `Field1` = 'data' AND `Field2` = NULL

The INSERT INTO.... statement looks fine, but the last SELECT statement would seem incorrect.  The WHERE clause reads "....`Field2` = NULL", but I believe it should read "....`Field2` IS NULL".

If you'd like a MyODBC trace, please let me know where I can obtain the debug dll for MyODBC 3.51.11-2 and I'll happily supply the trace file.

Sample Access database for test case

Attachment: bug.mdb (application/msaccess, text), 176.00 KiB.

I tested inserting new records in several ways: directly in
the linked table object. using a form with not event on before
update without any problems. Actually adding that event I
got the behavior reported and  for this reason I am editing
the bug report as verified.

The bug was caused by default setting SQL_AUTO_IS_NULL=1. This had been done for compatibility reasons as older versions of MDAC and Access retrieved the new records by "SELECT * FROM `my_table` WHERE ID IS NULL;".
By default MyODBC driver sends "SET SQL_AUTO_IS_NULL=0" with the initial statement, so "SELECT * FROM `my_table` WHERE ID IS NULL;" returns empty result set. In order to fix the bug we added a new option for MyODBC driver: FLAG_AUTO_IS_NULL = 8388608.
When FLAG_AUTO_IS_NULL is set, the default behavior or MySQL is being restored. However, I am not aware about any programs/ODBC clients that this options is useful for.

I've updated the documentation with this option in the list of potential parameters. A note on this change has been added to the changelog for 3.5.14.

I am using:

Access 2003 SP3 (Microsoft Jet Engine 4.0.9511.0)
MySQL-Client-Version: 5.0.32
Server Version: 5.0.67
and MySQL ODBC 5.01.05.00 Driver

I have the same problem when I INSERT into linked tables and specifically set a field to NULL in a bound Access Form. Can I somehow select the option FLAG_AUTO_IS_NULL = 8388608 in the Connector/ODBC connection screen? Or do I have to link the tables with my own connection string to set this option value?