MySQL Bugs: #29466: Update() method doesn't always work.

Bug #29466	Update() method doesn't always work.
Submitted:	1 Jul 2007 4:55	Modified:	26 Aug 2007 7:57
Reporter:	Jay Bourgeois	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.0.7	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	Connector/Net Update MySqlDataAdapter

Description:
The MySqlDataAdapter.Update([DataTable]) method does not always pass an UPDATE command to MySQL on rows with the Modified{16} row state.
I have two seperate routnes that call the same instace of this method depending on what the user wants to do.
In one method a SELECT is called, DataTable is filled. I loop through the table and change some values. I pass the table back to the adapater using the .Update([datatable]) method. The table is updated and everything is great.
The second method loads schema into a new datatable. It then reads an XML file which loads rows into the table. It calls .acceptchanges() on the table, then each row has the rowstate = modified. [I know these rows already exsits, and one or more items in the row have changed from the source data]. This datatable is then passed to the same adapter using the update() method. The method returns the correct number of rows effected, just as it did before. However, a subsequent SELECT statement shows the actual data was not modified. MySQL logs do not show the command being passed to the server. No errors are produced. 

How to repeat:
Follow the directions above.
Setup some test data.
Write two routines that loads data from a select statement, the other from an XML file. Modify the rows. execute the .UPDATE method.

I'm using MySQL server 5.0.2?
and Connector/Net 5.0.6/5.0.7/5.1.2 [all three produce the same results]

Suggested fix:
I don't know.
Using a watcher. Both datatables have the EXACT same data. 
Table name, rows, row data, rowstate, row errors, # of columns, primary keys, etc..

After looking into this a little further,
Each Row(x).item(y) has three property values:
current
default
origonal
[something else..]

The problem with the .update command is uses these values to generate the UPDATE. When a table is created from the XML file it does not contain current and origial values like it did when a table is loaded and changed.

I would suggest correcting this issue with the following logic:
If a row is flagged as 'modified' but the orignal and current values are the same, perhaps the UPDATE command could include all the Key columns in the WHERE clause and all the rest of the values in the SET clause.

I've written some routiens that do this, however, I have to call this explicitly and seperatly from the .Update routine.

Cheers.

Also having a problem with the 5.0.7 .Net connector not updating an auto_increment column when the .Update() method is called.  1.0.7 .Net connector would update auto_increment columns properly.

Not enough information was provided for us to be able to handle this bug. Please re-read the instructions at http://bugs.mysql.com/how-to-report.php

If you can provide more information, feel free to add it to this bug and change the status back to 'Open'.

Thank you for your interest in MySQL.

Explanation:
Hi Jay and thanks for your report. Calling AcceptChanges essentially clears the dirty bit on a datatable thus it should be used after an update has brought back fresh data. The failure in sequence might be causing your problem (as I see it with info provided). Please attach to this report:
 - MySQL server version, my.ini/cnf file
 - DDL script suitable for import with mysql cl client
 - Small but complete test case showing this problem every time it is run

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".