Bug #73746 ODBC Connector 5.3.4 MS Access #Deleted on Insert
Submitted: 27 Aug 2014 17:38 Modified: 20 Jun 2016 7:32
Reporter: Steven S Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / ODBC Severity:S2 (Serious)
Version:3.51, 5.1.13, 5.2.7, 5.3.4, 5.3.6 OS:Windows
Assigned to: CPU Architecture:Any
Tags: #DELETED, MS Access, ODBC

[27 Aug 2014 17:38] Steven S
Description:
I'm developing an MS Access 2010 front-end for a MySQL back-end using the ODBC Connector version 5.3.4 and having a very specific issue on insert that I cannot fix.  I have researched all the possible fixes for the #Deleted error and none of them apply to this particular case.

What happens:

Using a Access form bound to an ODBC-linked table in the MySQL database, going to a new record, typing in a value for a field, and then deleting it (Null -> Valid Value -> Null again) will cause Access to lose the record and come up as #Deleted. 

The field is not part of the unique index.  It only happens when you type something in to a bound field that was previously defaulted to null in a new record, then decide to delete it (thus changing it back to null).  Once the #Deleted shows up, the only way to get the record back (it does get inserted properly) is to close the form and reopen it.

Here's my connect string:
ODBC;DRIVER={MySQL ODBC 5.3 Unicode Driver};SERVER=IP;DATABASE=DB;UID=USER;PWD=PASS;FOUND_ROWS=1;DYNAMIC_CURSOR=1;NO_BIGINT=1;AUTO_IS_NULL=1;MULTI_STATEMENTS=1;

And here's a simple table I used to test it:

CREATE TABLE `test` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `TestString` varchar(60) DEFAULT NULL,
 `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1

How to repeat:
Create a new MS Access 2010 database. Link it to a table in a MySQL database similar to the table I posted above.  Create a new form, set the linked table as the RecordSource for the form.  Add a text box and bind it to any field in the table (in my case, TestString).  Go to a new record using the navigation buttons. The text box will start out empty.  Type something in, and do not move focus out of the text box.  Now backspace delete what you just typed in so that the box is empty again.  Now save the record (Ctrl + S).  #Deleted should show up in the textbox (and all other bound fields on the form).
[27 Aug 2014 17:51] Steven S
This bug appears to happen in all available versions of the ODBC connector, though I am using the latest version 5.3.4.
[28 Aug 2014 14:47] MySQL Verification Team
Please attach a mdb file which reproduces the issue. Thanks.
[15 Sep 2014 13:52] Hemant Dangi
odbc trace for MySQLDeleteTest.accdb testcase

Attachment: sql19.log (application/octet-stream, text), 160.77 KiB.

[15 Sep 2014 14:04] Hemant Dangi
Hello Steven,

Thanks for showing interest in MySQL Connector/ODBC.

I have attached odbc trace file sql19.log, which shows nothing is fetched from mysql and Conn/ODBC.

MySQLDeleteTest 2404-1454	EXIT  SQLExecDirectW  with return code 0 (SQL_SUCCESS)
		HSTMT               0x0000000000363520
		WCHAR *             0x000000000E2C44D8 [      -3] "SELECT `test`.`ID` FROM `test` WHERE `TestString` = ?\ 0"
		SDWORD                    -3

MySQLDeleteTest 2404-1454	ENTER SQLFetch 
		HSTMT               0x0000000000363520

MySQLDeleteTest 2404-1454	EXIT  SQLFetch  with return code 100 (SQL_NO_DATA_FOUND)
		HSTMT               0x0000000000363520

May be change TestString to not allow NULL values in mysql database.
Please also check MS Access docs for this.
http://support.microsoft.com/kb/128809 

Let me know if your issue is still not resolved.

Thanks
[15 Sep 2014 20:47] Steven S
Thanks for looking at the issue.  However, there are several problems with the suggested solution.

Firstly, I have already tried this solution before.  Setting the table definition to not allow null values on TestString will result in Access trying to insert null anyway, and then erroring out, saying that the field cannot be null.  There is no way to make Access insert the proper default value (as far as I can tell).  

Secondly, even if this did work, it would not be a solution, but rather a somewhat ugly workaround.  I do not want to have to insert a blank string ("") for every possible varchar field that could be blank. Null should be a possible value.  Because the #DELETED error happens in a VERY specific case, and it normally can update fields to be NULL, I do not think this is a proper solution to the problem.

Finally, the Microsoft KB article does not apply to this problem (I have seen it before).  It states that the problem can happen when you are inserting null values in fields making up the UNIQUE INDEX of the table.  The problem i'm describing is on fields that are NOT in the unique index (I made extra sure this was not the issue), and only happens when you insert fields that had a value and then were changed back to null before the actual insert happened.

I appreciate the suggestions, but this solution does not solve the problem.  Thanks.
[15 Sep 2014 20:47] Steven S
Error message you will see if you set NOT NULL in table

Attachment: Error.PNG (image/png, text), 6.23 KiB.

[16 Sep 2014 8:06] Hemant Dangi
Steven,

You are right and it was not proper workaround for your case. This error is not in Connector/ODBC or MySQL, so I was only suggesting you to look into some solution at MS Access level.

It will return empty result with '#Deleted' or 'NULL' for below query.
"SELECT `test`.`ID` FROM `test` WHERE `TestString` = ?\ 0"

If you still think there is some error in Connector/ODBC or MySQL then let me know and we can discuss further, otherwise we should close this bug as 'Not a Bug', and you can post for help in Connector/ODBC forum.
[16 Sep 2014 16:33] Steven S
I'm inclined to believe that it is at least problem between the ODBC connector and Access, as I have tried using an ODBC connection to a MSSQL Server database set up in the exact same manner as this one, and the exact problem I am having does NOT occur at all.  Using that, when inserting a record that had a value in a field and then got changed to null before the insert happened, Access does not lose the record.

Also, I am attaching two trace files, one called SQLGood, and one called SQLError.  In SQLGood (trace of when I just inserted a row that didn't cause the issue, not adding in a null value), I get that line that you got.  In SQLError (where the null value issue occurred), that line does not appear at all.  So I am getting different results than you.  Does this help at all?
[16 Sep 2014 16:33] Steven S
SQL Trace where a row is normally inserted, no null value issue

Attachment: SQLGood.LOG (application/octet-stream, text), 37.41 KiB.

[16 Sep 2014 16:34] Steven S
SQL trace where the null #Deleted issue occurs

Attachment: SQLError.LOG (application/octet-stream, text), 16.66 KiB.

[16 Sep 2014 16:36] Steven S
Both trace files are everything that happened only at the exact time of saving the record in Access, causing it to insert. SQLGood inserted properly, and SQLError had the #Deleted
[16 Sep 2014 17:33] Hemant Dangi
Steven,

Check line 293 and 187 in SQLError.log, these are the same line (SQL_NO_DATA_FOUND) which I was getting. Although you have some more log data at end. And the link (http://support.microsoft.com/kb/128809) which I posted earlier was to say that #Deleted is getting generated from MS Access and not from MySQL or MyODBC.

If you insert empty value and then try to select that value using below query you would get empty result only, and so MS Access is returning "#Deleted" error message.
SELECT `test`.`ID` FROM `test` WHERE `TestString` = ?

So what is wrong with MyODBC here, I am not able to understand. I can only say you need to check that empty should not get inserted, as you are not generating query here (that is being done by MS Access).
[16 Sep 2014 18:22] Steven S
I do not know how Access communicates with ODBC, and I do not know how the connector communicates with Access.  However, do you not see that if ODBC works with other databases properly, and this bug only occurs through the MySQL ODBC connector, then there must be a problem with the interface between the two?  How is that not a problem with the connector?

Regardless, "checking to make sure empty is not inserted" is an arduous and ugly workaround, and is really not practical.  Null or "" is a possible valid value for most varchars.  I need to allow them.  So I would have to have specific code on every single textbox (there are hundreds) to check for null on insert and instead insert "".  This would fill the database with empty strings instead of nulls, which is very inelegant.  Is there really no other way?
[18 Sep 2014 11:16] Hemant Dangi
trace log for mssql

Attachment: sql_mssql.log (application/octet-stream, text), 52.60 KiB.

[18 Sep 2014 11:16] Hemant Dangi
trace log for mysql

Attachment: sql_mysql.log (application/octet-stream, text), 40.43 KiB.

[18 Sep 2014 15:09] Hemant Dangi
I have added trace logs for for sql server and mysql for comparison. In sql server MS Access is calling "SELECT @@IDENTITY" to fetch last inserted value. But its not calling "SELECT LAST_INSERT_ID()" for mysql, its calling simple select query with TestString = NULL.
So approach is different, and for queries fired against mysql, MyODBC is returning correct output.
[18 Sep 2014 15:21] Hemant Dangi
Anyway this should not be severity S1 bug, S2 may be right.
[18 Sep 2014 18:42] Steven S
Ok, I set it to S1 because it pretty much is stopping me from using MySQL with Access, but I can change it to S2.  But I would appreciate it immensely if some solution can be found.
[12 Feb 2015 1:03] Andreas Skof
Has this ever been resolved? The only workaround I know of is to insert two extra rows in the MySQL table: 1 row with the current time stamp as default value and 1 row with date format but no default value. In MS Access, set the default value of the empty date field to Now(). This will generate a field with a value in the current record but only if the record gets saved.
[20 Jun 2016 7:32] Chiranjeevi Battula
Hello Steven S,

Thank you for your feedback.
Verified this behavior on MySQL Connector / ODBC 5.3.6 and MS Access 2013. 

Thanks,
Chiranjeevi.
[8 Feb 2023 4:56] Bogdan Degtyariov
Posted by developer:
 
Verified against 8.0.33:

Easy way to repeat:

CREATE TABLE `test` (
 `ID` int(11) NOT NULL AUTO_INCREMENT,
 `TestString` varchar(60) DEFAULT NULL,
 `Timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE
CURRENT_TIMESTAMP,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1

Start editing TestString, then remove the content and press "Down" button to write changes in the database.
Access will show #DELETED

Refreshing data for the table removes #Deleted, therefore it is not critical.