ON DUPLICATE KEY UPDATE - Always returns positive integer
Posted by: Onur Okyay
Date: February 05, 2017 02:18AM

- Running "on duplicate key update" query with .Net Connector always returns positive integer even when the row does not change.
- However same query works as expected in MySQL Console and MySQL Workbench and returns "0 rows affected" when there is no change

This seems like a bug to me. Any ideas?

Sample Query:
insert into test (k,v) values ('testkey',123) on duplicate key update v=values(v);

Test Table:
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`k` varchar(45) DEFAULT NULL,
`v` varchar(45) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `icol_UNIQUE` (`k`)
) ENGINE=MyISAM AUTO_INCREMENT=7 DEFAULT CHARSET=utf8;


C# Test Code:
using (var con = new MySqlConnection("Database=mydb;Data Source=localhost;User Id=root;Password=mypass;CharSet=utf8;"))
{
con.Open();
var cmd = new MySqlCommand("insert into test (k,v) values ('testkey',123) on duplicate key update v=values(v)", con);
var rows = cmd.ExecuteNonQuery();

Console.WriteLine(rows); // rows is always 2 OR 1
}

NOTES:

- I am using MySql .Net Connector 6.9.9. Same issue exists in 6.9.8
- All database/connection charset/collation settings are utf8

Options: ReplyQuote


Subject
Written By
Posted
ON DUPLICATE KEY UPDATE - Always returns positive integer
February 05, 2017 02:18AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.