MySQL Bugs: #4666: MySqlDataAdapter.Update does not update Database

Bug #4666	MySqlDataAdapter.Update does not update Database
Submitted:	21 Jul 2004 3:30	Modified:	6 Aug 2004 16:14
Reporter:	Brandon Schenz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	0.7.6.15073	OS:	Windows (Win XP Pro)
Assigned to:	Matthew Lord	CPU Architecture:	Any

Description:
I have the following code:

Imports ByteFX.Data.MySqlClient
Imports System.Diagnostics

Public Class MSS_Data_Helper

    Private myConnStr As String = "Data Source={host};database={db};user ID={UID};password={pass};pooling=false"
    Private itw As New Tracing

    Public Function FillProducts(ByVal ds As MSSDataSet) As MSSDataSet
        Trace.WriteLineIf(itw.TraceVerbose, "Filling Products Table", "MSS.Data")
        Trace.Flush()
        Dim myConn As New MySqlConnection(myConnStr)
        Dim myAdapter As New MySqlDataAdapter
        Dim myCommand As New MySqlCommand

        myCommand.Connection = myConn
        myCommand.CommandText = "SELECT * From Products Where SoldOut = 0"

        myAdapter.SelectCommand = myCommand

        myAdapter.Fill(ds, "Products")

        Return ds
    End Function

    Public Function Update(ByVal ds As MSSDataSet) As MSSDataSet
        ' Check for customers that exist in database
        CheckCustomers(ds)
        ' Update Customers Table
        updateCustomers(ds)
        ' Update Orders Table

        ' Update OrderDetails Table

        ' Update Payments Table

    End Function

    Private Sub CheckCustomers(ByVal ds As MSSDataSet)
        ' Write SQL to Check to see if customer exists in DB
        ' Unique index is FirstName, LastName, Address ,City, StateOrProvince

        Trace.WriteLineIf(itw.TraceVerbose, "Checking to see if Customers Exist in DB", "MSS.Data")
        Trace.Flush()
        Dim myConn As New MySqlConnection(myConnStr)
        Dim myCommand As New MySqlCommand
        Dim myRow As MSSDataSet.CustomersRow
        Dim isRowHere As Boolean
        Dim custID As Integer
        Dim oldCustID As Integer

        myCommand.Connection = myConn
        myCommand.CommandText = "SELECT CustomerID From Customers Where FirstName = @FirstName and " & _
            "LastName = @LastName and Address = @Address and City = @City and StateOrProvince = " & _
            "@StateOrProvince"

        myConn.Open()

        For Each myRow In ds.Customers
            oldCustID = myRow.CustomerID
            myCommand.Parameters.Add("@FirstName", MySqlDbType.VarChar).Value = myRow.FirstName.ToString
            myCommand.Parameters.Add("@LastName", MySqlDbType.VarChar).Value = myRow.LastName.ToString
            myCommand.Parameters.Add("@Address", MySqlDbType.VarChar).Value = myRow.Address.ToString
            myCommand.Parameters.Add("@City", MySqlDbType.VarChar).Value = myRow.City.ToString
            myCommand.Parameters.Add("@StateOrProvince", MySqlDbType.VarChar).Value = myRow.StateOrProvince.ToString
            custID = CType(myCommand.ExecuteScalar, Integer)
            isRowHere = CType(custID, Boolean)
            If isRowHere Then
                myRow.CustomerID = custID
                myRow.AcceptChanges()
            Else
                myRow.CustomerID = oldCustID
            End If
            myCommand.Parameters.Clear()
        Next
        myConn.Close()
    End Sub

    Private Sub updateCustomers(ByVal ds As MSSDataSet)
        Dim myconn As New MySqlConnection(myConnStr)
        Dim myDbAdapter As New MySqlDataAdapter

        myDbAdapter.SelectCommand = New mySqlCommand("Select CustomerID, CompanyName, FirstName, LastName, Address, City, StateOrProvince, PostalCode, Country, Phone, Fax, Email, Notes, BillName, BillAddress, BillCity, BillState, BillZip, CellorPager, InternetCustomerNo from Customers", myconn)

        myconn.Open()

        myDbAdapter.Update(ds, "Customers")

        myconn.Close()
    End Sub
End Class

This is a ClassLibrary that is called from my Main Application.  In the Sub Main of the app the FillProducts Function is called.

After my app loads the data from Order Text Files from my webserver, I attempt to call the Update Function above.  Everything seems to execute as I expect up to the point where " myDbAdapter.Update(ds, "Customers")" is supposed to execute, but nothing happens.  No exceptions, no updates or inserts in the database.

How to repeat:
At this point I am not sure, but I will make a test project and see if it works there....

Correction for updateCustomers Sub.  It is as follows:

Private Sub updateCustomers(ByVal ds As MSSDataSet)
        Dim myconn As New MySqlConnection(myConnStr)
        Dim myDbAdapter As New MySqlDataAdapter
        Dim custCb As MySqlCommandBuilder

        myDbAdapter.SelectCommand = New MySqlCommand("Select CustomerID, CompanyName, FirstName, LastName, Address, City, StateOrProvince, PostalCode, Country, Phone, Fax, Email, Notes, BillName, BillAddress, BillCity, BillState, BillZip, CellorPager, InternetCustomerNo from Customers", myconn)

        custCb = New MySqlCommandBuilder(myDbAdapter)
        myconn.Open()

        myDbAdapter.Update(ds, "Customers")

        myconn.Close()
    End Sub

I used a StreamWriter to write to a file that contains the Insert, Update, and Delete Commands created by the commandbuilder

Attachment: MyCommandBuilderQueries.txt (text/xls), 4.73 KiB.

I am now working on the project at home and do get an exception on the myDbAdapter.Update(ds, "Customers") line:

An unhandled exception of type 'System.Data.DBConcurrencyException' occurred in system.data.dll

Additional information: Concurrency violation: the UpdateCommand affected 0 records.

This is the update statement generated by CommandBuilder(I reformated it so it was easier to read), and what fails:

UPDATE 
	Customers 
SET 
	CompanyName=@CompanyName, 
	FirstName=@FirstName, 
	LastName=@LastName, 
	Address=@Address, 
	City=@City, 
	StateOrProvince=@StateOrProvince, 
	PostalCode=@PostalCode, 
	Country=@Country, 
	Phone=@Phone, 
	Fax=@Fax, 
	Email=@Email, 
	Notes=@Notes, 
	BillName=@BillName, 
	BillAddress=@BillAddress, 
	BillCity=@BillCity, 
	BillState=@BillState, 
	BillZip=@BillZip, 
	CellorPager=@CellorPager, 
	InternetCustomerNo=@InternetCustomerNo 
WHERE 
	(CustomerID=@Original_CustomerID) AND 
	(CompanyName=@Original_CompanyName or (CompanyName IS NULL and @Original_CompanyName IS NULL)) AND 
	(FirstName=@Original_FirstName or (FirstName IS NULL and @Original_FirstName IS NULL)) AND 
	(LastName=@Original_LastName or (LastName IS NULL and @Original_LastName IS NULL)) AND 
	(Address=@Original_Address or (Address IS NULL and @Original_Address IS NULL)) AND 
	(City=@Original_City or (City IS NULL and @Original_City IS NULL)) AND 
	(StateOrProvince=@Original_StateOrProvince or (StateOrProvince IS NULL and @Original_StateOrProvince IS NULL)) AND 
	(PostalCode=@Original_PostalCode or (PostalCode IS NULL and @Original_PostalCode IS NULL)) AND 
	(Country=@Original_Country or (Country IS NULL and @Original_Country IS NULL)) AND 
	(Phone=@Original_Phone or (Phone IS NULL and @Original_Phone IS NULL)) AND 
	(Fax=@Original_Fax or (Fax IS NULL and @Original_Fax IS NULL)) AND 
	(Email=@Original_Email or (Email IS NULL and @Original_Email IS NULL)) AND 
	(Notes=@Original_Notes or (Notes IS NULL and @Original_Notes IS NULL)) AND 
	(BillName=@Original_BillName) AND 
	(BillAddress=@Original_BillAddress or (BillAddress IS NULL and @Original_BillAddress IS NULL)) AND 
	(BillCity=@Original_BillCity or (BillCity IS NULL and @Original_BillCity IS NULL)) AND 
	(BillState=@Original_BillState or (BillState IS NULL and @Original_BillState IS NULL)) AND 
	(BillZip=@Original_BillZip or (BillZip IS NULL and @Original_BillZip IS NULL)) AND 
	(CellorPager=@Original_CellorPager or (CellorPager IS NULL and @Original_CellorPager IS NULL)) AND 
	(InternetCustomerNo=@Original_InternetCustomerNo or (InternetCustomerNo IS NULL and @Original_InternetCustomerNo IS NULL)); 

SELECT 
	CustomerID, 
	CompanyName, 
	FirstName, 
	LastName, 
	Address, 
	City, 
	StateOrProvince, 
	PostalCode, 
	Country, 
	Phone, 
	Fax, 
	Email, 
	Notes, 
	BillName, 
	BillAddress, 
	BillCity, 
	BillState, 
	BillZip, 
	CellorPager, 
	InternetCustomerNo 
FROM 
	Customers 
WHERE 	
	(CustomerID=@Original_CustomerID)

I have made sure to test this with clean data, so after I was able to successfully add the record I have been working with, I changed only the CompanyName in the DataBase and restarted the application.  This should now cause an update since I verified thatr the record was in the Database and I only changed one field.

This part of the WHERE Clause is not valid:
(CompanyName=@Original_CompanyName or (CompanyName IS NULL and @Original_CompanyName IS NULL))

I know this because they are not equal (I changed it) CompanyName (I assume this is the new value) is not null, and @Original_CompanyName is not null (I assume this was the value as it was in the DB)

I am trying to provide as much info for you on this as I can.  I would be happy to send a copy of my APP as well if that will help.

Dear Sir,

Would it be possible for you to provide a dump of the database that the app is using?  This would 
allow me to easily try and reproduce the problem.  If the dump would be too large to attach to 
the bug report you can put it on ftp.mysql.com/pub/mysql/upload using the anonymous account 
and just let me know the filename.

Best Regards

I am attaching a file with a very small subset of the data in the table.  I created it by doing a backup with the MySql Administrator Tool.

Zipped file with small subset of data in table created with MySql Administrator

Attachment: Customer.zip (application/x-zip-compressed, text), 6.60 KiB.

Brandon,

Can you look at this bug:
http://bugs.mysql.com/bug.php?id=4658

I think you may be running into the same thing and this would be a duplicate.

Best Regards,

I looked over that one before I submitted mine, and I do not get that error.

I get:

"An unhandled exception of type 'System.Data.DBConcurrencyException' occurred in system.data.dll

Additional information: Concurrency violation: the UpdateCommand affected 0
records."

And the reason for this is because the Update Command generated is not correct as I stated before:

"This part of the WHERE Clause is not valid:
(CompanyName=@Original_CompanyName or (CompanyName IS NULL and
@Original_CompanyName IS NULL))

I know this because they are not equal (I changed it) CompanyName (I assume this is the new value) is not null, and @Original_CompanyName is not null (I assume this was the value as it was in the DB)"

From what I was reading in the other bug report they are getting a different error (unless the same Update command problem can give two different errors based on circumstance).

Brandon

Dear Sir,

Can you confirm the version of mysql that you are using?

Have you been able to confirm that this statement exectues using the mysql text client?

Turn on the general with "log" line in the [mysqld] section of your my.ini or my.cnf file or just
start mysqld with the --log option.  You will then have a hostname.log file in your datadir that
will contain all the activity for the server.   This way we can see what the actual statement is 
that's sent to the server and we can both try and execute the query using the text client to make
sure that  it's exectuing OK.  From what I've seen so far I'm not sure the Driver is not performing 
correctly.  This way we can also see if we are getting the exception before the statment is even 
sent to the server.

Best Regards

I am using version 4.0.17 on WinXP Pro.

I turned on the log and this is the query with the problems:

UPDATE 
   Customers 
SET 
   CompanyName='Jerry Suttons Used Cars', 
   FirstName='Jerald', 
   LastName='Sutton', 
   Address='502 S. 1St Street', 
   City='Selah', 
   StateOrProvince='WA', 
   PostalCode='98942', 
   Country='US', 
   Phone='5096978989', 
   Fax='', 
   Email='jsutton033@yahoo.com', 
   Notes='', 
   BillName='Jerald L Sutton Jr', 
   BillAddress='502 S. 1St St.', 
   BillCity='Selah', 
   BillState='WA', 
   BillZip='98942', 
   CellorPager='', 
   InternetCustomerNo='36743' 
WHERE 
   (CustomerID=84415) AND 
   (CompanyName='Jerry Suttons Cars' or (CompanyName IS NULL and 'Jerry Suttons Cars' IS NULL)) AND 
   (FirstName='Jerald' or (FirstName IS NULL and 'Jerald' IS NULL)) AND 
   (LastName='Sutton' or (LastName IS NULL and 'Sutton' IS NULL)) AND 
   (Address='502 S. 1St Street' or (Address IS NULL and '502 S. 1St Street' IS NULL)) AND 
   (City='Selah' or (City IS NULL and 'Selah' IS NULL)) AND 
   (StateOrProvince='WA' or (StateOrProvince IS NULL and 'WA' IS NULL)) AND 
   (PostalCode='98942' or (PostalCode IS NULL and '98942' IS NULL)) AND 
   (Country='US' or (Country IS NULL and 'US' IS NULL)) AND 
   (Phone='5096978989' or (Phone IS NULL and '5096978989' IS NULL)) AND 
   (Fax='' or (Fax IS NULL and '' IS NULL)) AND 
   (Email='jsutton033@yahoo.com' or (Email IS NULL and 'jsutton033@yahoo.com' IS NULL)) AND 
   (Notes='' or (Notes IS NULL and '' IS NULL)) AND 
   (BillName='Jerald L Sutton Jr') AND 
   (BillAddress='502 S. 1St St.' or (BillAddress IS NULL and '502 S. 1St St.' IS NULL)) AND 
   (BillCity='Selah' or (BillCity IS NULL and 'Selah' IS NULL)) AND 
   (BillState='WA' or (BillState IS NULL and 'WA' IS NULL)) AND 
   (BillZip='98942' or (BillZip IS NULL and '98942' IS NULL)) AND 
   (CellorPager='' or (CellorPager IS NULL and '' IS NULL)) AND 
   (InternetCustomerNo='36743' or (InternetCustomerNo IS NULL and '36743' IS NULL))

When I run the query in the mysql text client this is the result:
Query OK, 0 rows affected (0.02 sec)
Rows matched: 0  Changed: 0  Warnings: 0

Below is the result of this query to see what is actually in the Database:
SELECT CustomerID, CompanyName, FirstName, LastName, Address, City, StateOrProvince, PostalCode, Country, Phone, Fax, Email, Notes, BillName, BillAddress, BillCity, BillState, BillZip, CellorPager, InternetCustomerNo FROM Customers WHERE (CustomerID=84415);

+------------+--------------------+-----------+----------+-------------------+--------+-----------------+------------+---------+------------+--------+----------------------+--------+--------------------+----------------+----------+-----------+---------+-------------+--------------------+
| CustomerID | CompanyName        | FirstName | LastName | Address           | City   | StateOrProvince | PostalCode | Country | Phone      | Fax    | Email                | Notes  | BillName           | BillAddress    | BillCity | BillState | BillZip | CellorPager | InternetCustomerNo |
+------------+--------------------+-----------+----------+-------------------+--------+-----------------+------------+---------+------------+--------+----------------------+--------+--------------------+----------------+----------+-----------+---------+-------------+--------------------+
|      84415 | Jerry Suttons Cars | Jerald    | Sutton   | 502 S. 1St Street | Selah  | WA              | 98942      | US      | 5096978989 | [NULL] | jsutton033@yahoo.com |        | Jerald L Sutton Jr | 502 S. 1St St. | Selah    | WA        | 98942   | [NULL]      | 36743              |
+------------+--------------------+-----------+----------+-------------------+--------+-----------------+------------+---------+------------+--------+----------------------+--------+--------------------+----------------+----------+-----------+---------+-------------+--------------------+

The Update query should change the CompanyName to: Jerry Suttons Used Cars, but it does not.  I do not see the reason for this.

Possible reason..... 

(Fax='' or (Fax IS NULL and '' IS NULL))

And

(CellorPager='' or (CellorPager IS NULL and '' IS NULL))

Original Record as these fields as null so the first part if false.
Second part first statement is true (It is null in DB)
Second part second statement is false (I do not think that '' will evaluate as null)

So the actual query does not update any rows either.

The problem is just the exception?

Best Regards

The exception is not the problem.  It is the expected result when an update does not return any changed rows.  

The problem is that the update command is not coorect, and it was generated by the mySqlCommandBuilder.

I think the where clauses need to be looked at in how the CommandBuilder generates its commands.

Hi Brandon,

Would you be able to provide the application that is allowing you to repeat the problem?
This would allow Reggie to look at this further.  I think I am too unfamiliar with .NET and
the driver but I will look at it first.

Best Regards

File was too large to be added to bug report.  I used ftp.mysql.com/pub/mysql/upload to upload the project.

Hi Brandon,

Great!  What's the filename(s)?

Thanks

Project for Bug Report 4666.zip

Brandon

I am not sure there is a bug here.  It looks like your underlying data changed between retrieval of the dataset and update of the dataset.  

As you know, the command builder builds a where clause that includes all fields and their original values.  If any of the values have changed, the update fails citing zero rows changed.

In the SQL you gave, you are assigning Fax='' and from looking at the rest of the SQL, the original value of Fax was ''.  However, when looking at the data row you included, fax is NULL.  This will cause an update failure since fax has a value different than the original value.

You are correct that '' is null will fail, however if the value of the underlying column hasn't changed it will still work.  consider the following:

fax has value ''

you do an update of a different field and the sql generated would be something like this:

where
....
(fax = '') or (fax IS NULL AND '' IS NULL)

the AND '' IS NULL will not get executed since the underlying value of fax is still ''.  However, if I did a fill and then used a different program to set the value of fax to NULL and then do my update, it will fail with a zero rows updated.  This is expected behavior.

Please review your process and make sure the underlying table is not being changed during the update and let me know what you find.

Thanks

I guess this is not a bug.  I changed the offending fields to not accept null values (used a space as default character) and it works as expected.

Sorry, but the bug system is not the appropriate forum for asking
support questions. Your problem is not the result of a bug.
For a list of more appropriate places to ask for help using MySQL
products, please visit http://www.mysql.com/support/

Thank you for your interest in MySQL.

Additional info:

User was manually updating data in the database during update cycle.