MySQL Bugs: #36454: ProfileProvider doesn't Save or SetPropertyValue as Update

Bug #36454	ProfileProvider doesn't Save or SetPropertyValue as Update
Submitted:	1 May 2008 14:23	Modified:	2 May 2008 16:19
Reporter:	Ken Grant	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S2 (Serious)
Version:	5.2.1	OS:	Windows
Assigned to:		CPU Architecture:	Any
Tags:	MySQL.Web.ProfileProvider

Description:
When using the ProfileProvider any attempt to update a previously saved peropery fails. 

How to repeat:
Add a ProfileProvider in web.config in a web application that is already using the mebership provider. Add and save a property and then try to update in witha new value. It will then fail.

Suggested fix:
From the documentation :

If you specify ON DUPLICATE KEY UPDATE, and a row is inserted that would cause a duplicate value in a UNIQUE index or PRIMARY KEY, an UPDATE of the old row is performed. is performed. The affected-rows value per row is 1 if the row is inserted as a new row and 2 if an existing row is updated. See Section 12.2.4.3, “INSERT ... ON DUPLICATE KEY UPDATE Syntax”. ON DUPLICATE KEY UPDATE was added in MySQL 4.1.0. 

The count needs to test for a return of "2" if the existing entry is updated. At the moment it only saves the record on return of "1" which is an insert only.

MySqlCommand cmd = new MySqlCommand(
                            @"INSERT INTO my_aspnet_Profiles  
                            VALUES (@userId, @index, @stringData, @binaryData, NULL) ON DUPLICATE KEY UPDATE
                            valueindex=VALUES(valueindex), stringdata=VALUES(stringdata),
                            binarydata=VALUES(binarydata)", connection);
                        cmd.Parameters.Clear();
                        cmd.Parameters.AddWithValue("@userId", userId);
                        cmd.Parameters.AddWithValue("@index", index);
                        cmd.Parameters.AddWithValue("@stringData", stringData);
                        cmd.Parameters.AddWithValue("@binaryData", binaryData);
                        count = cmd.ExecuteNonQuery();
                        if (count != 1)
                            throw new Exception(Resources.ProfileUpdateFailed);
                        ts.Complete();

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

I don't think this bug has been fix yet, am using Connector/NET 5.2.3 and am still having the same problem - can update previously add profile property throw and erroe profile update failed

After my comment above i downloaded the source and stepped through, the TransactionScope is throw up an error @ ts.Complete() and that will rollback the update on the server

Anyone have a way out of this??

Continued in Bug#39330.