| Bug #44194 | ExecuteNonQuery for update commands does not match actual rows updated | ||
|---|---|---|---|
| Submitted: | 9 Apr 2009 20:15 | Modified: | 17 Apr 2009 15:50 |
| Reporter: | Nick Larsen | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | Connector / NET | Severity: | S3 (Non-critical) |
| Version: | 5.2.5 | OS: | Windows (XP Pro sp3) |
| Assigned to: | Reggie Burnett | CPU Architecture: | Any |
[10 Apr 2009 9:27]
Tonci Grgin
Hi Nick and thanks for your report. Looking into Bug#42087, Bug#39352 ... I think this is a problem with the CLIENT_AFFECTED_ROWS and CLIENT_FOUND_ROWS flags. Can you please state MySQL server version you're using and attach general query log from server starting with you running this code.
[10 Apr 2009 9:28]
Tonci Grgin
Running same queries in mysql command line client might shed some light on this too.
[10 Apr 2009 10:18]
Tonci Grgin
On a second thought, Command.cs, Ln 317 updatedRowCount = reader.RecordsAffected; states: int MySQLDataReader.RecordsAffected: Gets number of rows Changed/Inserted/Deleted by execution of the SQL statement which is obviously not true in this case. Verified as described with test case attached using latest c/NET 5.2 sources.
[10 Apr 2009 10:21]
Tonci Grgin
tonci01@opensol:~# mysql -uroot -p test Enter password: Your MySQL connection id is 139 Server version: 5.1.31-log MySQL Community Server (GPL) mysql> DROP TABLE IF EXISTS `bug44194`; Query OK, 0 rows affected (0.03 sec) mysql> CREATE TABLE IF NOT EXISTS `bug44194` (`TESTKEY` INT UNSIGNED NOT NULL ,`VALUE_1` VARCHAR(3) NOT NULL ,`VALUE_2` INT NOT NULL ,PRIMARY KEY (`TESTKEY`)) ENGINE = InnoDB; Query OK, 0 rows affected (0.03 sec) mysql> INSERT INTO bug44194 (`TESTKEY`,`VALUE_1`,`VALUE_2`) VALUES (1,'A',10), (2,'B',10), (3,'C',10); Query OK, 3 rows affected (0.01 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> UPDATE `bug44194` SET `VALUE_1` = 'C' WHERE `TESTKEY` = 3; Query OK, 0 rows affected (0.01 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> UPDATE `bug44194` SET `VALUE_1` = 'B' WHERE `TESTKEY` = 3; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> UPDATE `bug44194` SET `VALUE_2` = '10' WHERE `TESTKEY` IN (1,2,3); Query OK, 0 rows affected (0.01 sec) Rows matched: 3 Changed: 0 Warnings: 0 mysql> UPDATE `bug44194` SET `VALUE_1` = 'A' WHERE `VALUE_2` = 10; Query OK, 2 rows affected (0.01 sec) Rows matched: 3 Changed: 2 Warnings: 0
[10 Apr 2009 16:41]
Nick Larsen
Hi Tonci, Thank you for looking into this. I should have mentioned the server is MySql 5.1 running on a Debian box. The connector is running on a Windows XP Pro machine with the .net 3.5 sp1 runtime. The issue does seem to just be the .Net 5.2.5 connector. The return is fine on the odbc 3.5 driver, and through a console mysql prompt. Also, the behavior is the same as ExecuteNonQuery() when running "SELECT ROW_COUNT();" through ExecuteScalar() with the .net 5.2.5 connector, that one puzzled me. mysqlCommand.CommandText = "SELECT ROW_COUNT();"; long result = (long)mysqlCommand.ExecuteScalar(); And I think you already mentioned the RecordsAffected property on a DataReader is doing the same. Anyhow, is there another method to retreive the number of rows affected by an update statement through the .net 5.2.5 connector?
[14 Apr 2009 21:24]
Reggie Burnett
This is not a simple issue and is not necessarily a bug. The driver opens the connection by specifying the FOUND_ROWS option. This means that the server will report the number of matched rows as affected rows. This is apparently very common among db vendors. There are also situations where not enablng FOUND_ROWS could be problematic. The ODBC maintainer tells me that his driver also has this default but users will often disable it. It is for this reason that we are creating a connection string option named 'use affected rows'. Setting this to true will cause the provider to return the count of only changed rows instead of found rows.
[14 Apr 2009 21:25]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/72072
[14 Apr 2009 21:26]
Reggie Burnett
This is not really a bug but we added a connection string option to improve it. The option is 'use affected rows'. when true the connection will report actually changed rows instead of found rows.
[15 Apr 2009 6:05]
Tonci Grgin
Reggie, I know where the problem (not bug) is and I verified it solely on the documentation basis being that description of function is misleading. But the way you fixed it is even better.
[17 Apr 2009 15:50]
Tony Bedford
A 'change' entry has been added to the 5.2.6 changelog: A new connection string option has been added: use affected rows. When true the connection will report changed rows instead of found rows. In addition, the main documentation has been updated.
Description: The return value of ExecuteNonQuery on an UPDATE query is the number of rows matched by the where clause of the update statement and not the number of rows actually updated. The same update queries run through a mysql console prompt return different values (Please see the two examples in the How to Repeat Section) How to repeat: Here's a quick little C# function that will demonstrate static void Main(string[] args) { string creatTable = @" CREATE TABLE IF NOT EXISTS `TESTTABLE` ( `TESTKEY` INT UNSIGNED NOT NULL , `VALUE_1` VARCHAR(3) NOT NULL , `VALUE_2` INT NOT NULL , PRIMARY KEY (`TESTKEY`)) ENGINE = InnoDB;"; string insertValues = @" INSERT INTO TESTTABLE (`TESTKEY`,`VALUE_1`,`VALUE_2`) VALUES (1,'A',10), (2,'B',10), (3,'C',10)"; string updateWhereMatchesOneRowNoValuesChange = @" UPDATE `TESTTABLE` SET `VALUE_1` = 'C' WHERE `TESTKEY` = 3"; string updateWhereMatchesOneRowValuesChange = @" UPDATE `TESTTABLE` SET `VALUE_1` = 'B' WHERE `TESTKEY` = 3"; string updateWhereMatchesThreeRowsNoValuesChange = @" UPDATE `TESTTABLE` SET `VALUE_2` = '10' WHERE `TESTKEY` IN (1,2,3)"; string updateWhereMatchesThreeRowsTwoValuesChange = @" UPDATE `TESTTABLE` SET `VALUE_1` = 'A' WHERE `VALUE_2` = 10"; MySql.Data.MySqlClient.MySqlConnection conn = new MySql.Data.MySqlClient.MySqlConnection(connectionString); MySql.Data.MySqlClient.MySqlCommand cmd = new MySql.Data.MySqlClient.MySqlCommand(); int result = 0; conn.Open(); cmd.Connection = conn; cmd.CommandText = creatTable; result = cmd.ExecuteNonQuery(); Console.WriteLine("createTable result = {0}", result); cmd.CommandText = insertValues; result = cmd.ExecuteNonQuery(); Console.WriteLine("insertValues result = {0}", result); cmd.CommandText = updateWhereMatchesOneRowNoValuesChange; result = cmd.ExecuteNonQuery(); Console.WriteLine("1 Row Matched, No Rows Changed; ExecuteNonQuery Return Value = {0}", result); cmd.CommandText = updateWhereMatchesOneRowValuesChange; result = cmd.ExecuteNonQuery(); Console.WriteLine("1 Row Matched, 1 Row Changed; ExecuteNonQuery Return Value = {0}", result); cmd.CommandText = updateWhereMatchesThreeRowsNoValuesChange; result = cmd.ExecuteNonQuery(); Console.WriteLine("3 Rows Matched, No Rows Changed; ExecuteNonQuery Return Value = {0}", result); cmd.CommandText = updateWhereMatchesThreeRowsTwoValuesChange; result = cmd.ExecuteNonQuery(); Console.WriteLine("3 Rows Matched, 2 Rows Changed; ExecuteNonQuery Return Value = {0}", result); conn.Close(); Console.WriteLine("Press \'Enter\' to exit"); Console.ReadLine(); } the output is: createTable result = 0 insertValues result = 3 1 Row Matched, No Rows Changed; ExecuteNonQuery Return Value = 1 1 Row Matched, 1 Row Changed; ExecuteNonQuery Return Value = 1 3 Rows Matched, No Rows Changed; ExecuteNonQuery Return Value = 3 3 Rows Matched, 2 Rows Changed; ExecuteNonQuery Return Value = 3 ***************************************************************** For reference this is the behavior from a mysql console prompt: mysql> CREATE TABLE IF NOT EXISTS `TESTTABLE` ( -> `TESTKEY` INT UNSIGNED NOT NULL , -> `VALUE_1` VARCHAR(3) NOT NULL , -> `VALUE_2` INT NOT NULL , -> PRIMARY KEY (`TESTKEY`)) ENGINE = InnoDB; Query OK, 0 rows affected (0.02 sec) mysql> INSERT INTO TESTTABLE (`TESTKEY`,`VALUE_1`,`VALUE_2`) VALUES -> (1,'A',10), -> (2,'B',10), -> (3,'C',10); Query OK, 3 rows affected (0.27 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> UPDATE `TESTTABLE` SET -> `VALUE_1` = 'C' -> WHERE -> `TESTKEY` = 3; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0 mysql> UPDATE `TESTTABLE` SET -> `VALUE_1` = 'B' -> WHERE -> `TESTKEY` = 3; Query OK, 1 row affected (0.02 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> UPDATE `TESTTABLE` SET -> `VALUE_2` = '10' -> WHERE -> `TESTKEY` IN (1,2,3); Query OK, 0 rows affected (0.00 sec) Rows matched: 3 Changed: 0 Warnings: 0 mysql> UPDATE `TESTTABLE` SET -> `VALUE_1` = 'A' -> WHERE -> `VALUE_2` = 10; Query OK, 2 rows affected (0.00 sec) Rows matched: 3 Changed: 2 Warnings: 0 Suggested fix: Return the actual number of rows changed and not the number or rows matched.