MySQL Bugs: #46761: "Write conflict" when undoing changes manually (Frontend MS Access)

Bug #46761	"Write conflict" when undoing changes manually (Frontend MS Access)
Submitted:	17 Aug 2009 16:52	Modified:	21 Oct 2009 23:43
Reporter:	Benjamin Udiljak	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S2 (Serious)
Version:	5.1.5 / 3.51.27	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any

Description:
When I change a field value in a linked table and undo the change manually (delete a arbitrary letter and retype it) I receive the error "Write Conflict: This record has been changed by another user since you started editing it...." 

MySQL-Server Version 5.1.36

How to repeat:
Seems to occur in all linked tables in the MS Access Frontend. 

MySQL-Server

The error vanished.... Sorry for the entry....

The bug is definetely a problem.

Benjamin, so what am I to do with your "It does not work"???

Please attach ODBC trace at the time error occurs (see MySQL or Windows manual on how to obtain trace from DM). Also, attach portion of general query log from MySQL server starting at the time Access connects and finishing with getting error in Access (see manual on how to obtain MySQL server general query log).

Presumably, you are trying to update table with FLOAT/DOUBLE field in default adCriteriaAllCols mode. You should add TIMESTAMP field to your table (to use adCriteriaTimeStamp) or switch to adCriteriaKey mode as per http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q190727&GSSNB=1:

I do not think there's bug here...

Unfortunately it seems to be a quite costly task to enable debugging. I can not afford to do that at the moment. 

The error is very easy to reproduce. Again: 
- Link any MySQL table into Access.
- Edit a value, but change it back before you save the record. 
- When you save now the error occurs. 

The type of the field is not important. 

I think it's not a matter of any configuration option. Please tell me if I'm wrong.

No Benjamin, I can not work with this...

There could be a problem with the way Access updates record (see http://support.microsoft.com/default.aspx?scid=kb;EN-GB;q190727&GSSNB=1:), or you could have AUTOCOMMIT set to ON, or it can be any other reason I can't think of right now.

Please prepare small standalone test case (could be from your development machine, not the production one!), ODBC trace and a portion of general query log from MySQL server relevant to this.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

The ODBC Trace while trying to save a recordset with a new value that is like the old value is uploaded in the "Files"-section. 
Obviously the AUTOCOMMIT-Option is turned off automatically and the row is identified correctly by the unique ID. (The field "AdID" is the primary key of the table. )

ODBC Debug trace

Attachment: update_same_value.txt (text/plain), 8.89 KiB.

Benjamin, I still have no info on exact table structure and engine. I know nothing of your SQL_MODE used on server and there is no explicit error on ODBC trace. What is present in ODBC trace is what I already suggested:
MSACCESS        6c4-a74 EXIT  SQLExecDirectW  with return code 0 (SQL_SUCCESS)
    HSTMT               09202790
    WCHAR *             0x0AB82CB0 [      -3] "UPDATE `d_adressen` SET `AdLand`=?  WHERE `AdID` = ? AND `AdLand` = ?\ 0"
    SDWORD                    -3

*All* fields from table are used in generated UPDATE suggesting you need to check Access documentation on how to control them! Please read again the page I pointed you at. You have numerous options to bypass this stupid behavior (notice that UPDATE statement is formed in Access not in c/ODBC!) like controlling updates via primary key only, via timestamp filed and so on. Leaving things as they are will only lead to your next bug report which would be "why can't I update floats" and then "why can't I update blobs" and so on for all the types that can't be matched in this update mode (UPDATE ... WHERE ... floatfld=floatval, blobfld=blobval)...

Although it didn't solve my problem, the "Update Criteria"-Property is certainly an important hint. Luckily I'm not using any Float or Blob fields. 

After some more hours of research I finally found the solution on this page: http://dev.mysql.com/doc/refman/5.1/en/connector-odbc-usagenotes-apptips.html#connector-od...

The option "Return matching rows" (Connector 3.51) or "Return matched rows instead of affected rows" (Connector 5.1) has to be activated for all Microsoft products. The option can be found in the advanced settings of the DSN. 

I'm sorry for the effort I caused by this false alarm. Still I suggest enabling this option by default since it seems to be the expected behavior of many standard odbc clients. 

Thanks for MySQL (and the Connector)! 
Ben

Benjamin, do not worry, there's nothing "false" about this report and each one is, unavoidable, an alarm in it self :-)
Main thing is code's working now.

Connecting MySql via ODBC to an MS-Access Front-end requires per table the following steps.
Add a timestamp field if it does not yet exist.
Don't forget to give the timestamp field a valid value. Default will be '0000-00-00 00:00:00' This can not be interpreted by ODBC.
I used:
ALTER TABLE tablename ADD COLUMN last_change TIMESTAMP;
UPDATE tablename SET last_change '2010-01-01 10:00:00';
now I got rid of the "write conflict"
Do this for each connected table.

I was having the same problem and my solution turned out to be the default value for BIT(1) fields. Access does not like these to be null. Make sure you use either 0 or 1 in mysql for these fields.

Removed a "bit" column with no default value, and for the moment my problem has gone away.

thx Greg when I set the default value for the bit column to 0 or "False" and changed the empty values from "Null" in sql server to "0" then all worked as expected no longer get error 3197. 

Greg's Comments: 
[19 Apr 2011 18:17] Greg Gilles 
Removed a "bit" column with no default value, and for the moment my problem has gone away.