ON DUPLICATE KEY UPDATE - Same Query Different Results
Posted by: Onur Okyay
Date: March 02, 2016 04:08AM

In my test app; Successive execution of the following query always returns positive row count, even when there is no change.

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

The query works as expected in MySQL Console and MySQL Workbench. I get 1 for the first insert and 0 for the successive calls.

However when I try in my C# test app I always get 2 even when nothing changes inside the row.

Any ideas? Am I missing something while setting up the connection?

This is my 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;


And this is my tiny 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
}

NOTES:

- I am using MySql .Net Connector 6.9.8
- All database charset/collation settings are utf8

Options: ReplyQuote


Subject
Written By
Posted
ON DUPLICATE KEY UPDATE - Same Query Different Results
March 02, 2016 04:08AM


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.