MySQL Bugs: #68332: Value for mysql_affected_rows() became 1 for ON DUPLICATE KEY after 5.5.15

Bug #68332	Value for mysql_affected_rows() became 1 for ON DUPLICATE KEY after 5.5.15
Submitted:	10 Feb 2013 16:34	Modified:	11 Feb 2013 21:32
Reporter:	Ravi Sarma	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: General	Severity:	S2 (Serious)
Version:	5.5.30,5.6.10	OS:	Any
Assigned to:	Matthew Lord	CPU Architecture:	Any

Description:
Somewhere between MySQL community server 5.5.15 and 5.5.29 the behaviour of affectedRows changed for INSERT ... ON DUPLICATE KEY. I have a simple table with the following fields:

`mydb`.`locations` (
  `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `lat` DECIMAL(8,4) NOT NULL ,
  `long` DECIMAL(8,4) NOT NULL ,
  `zip` CHAR(20) NULL ,
  `updated_on` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
  PRIMARY KEY (`id`) ,
  UNIQUE INDEX `uniq_lat_long` (`lat` ASC, `long` ASC) )
ENGINE = InnoDB;

I am using the 'mysql-node' module with node.js to add rows or update this table.

I am doing the following INSERT:

INSERT INTO mydb.locations (`lat`, `long`, `zip`) VALUES(20.04, 10.43, NULL) ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`)

With server version 5.5.15, the first time I run the INSERT, the affected-rows in the response is 1 and the second time I run the same INSERT it is 2, which is the expected behaviour per documentation.

On server 5.5.29, the second insert returns affected-rows = 1 as well.

This has been tested and found to happen on OS X 10.8 and on Ubuntu GNU/Linux 12.04.1.

How to repeat:
See above. Steps to reproduce:

1. Create a table with an auto-increment primary key and another unique key
2. Insert a row using INSERT...ON DUPLICATE KEY UPDATE
3. Run the same INSERT query again

Step #3 should have affected-rows set to 2. That occurs correctly in 5.5.15. In 5.5.29 the affected-rows value is 1.

Hi Ravi,

Thank you for your interest in MySQL!

Here's what I get in 5.6.10:

mysql> select version();
+------------+
| version()  |
+------------+
| 5.6.10-log |
+------------+
1 row in set (0.00 sec)

mysql> CREATE TABLE `locations` (
    ->   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
    ->   `lat` DECIMAL(8,4) NOT NULL ,
    ->   `long` DECIMAL(8,4) NOT NULL ,
    ->   `zip` CHAR(20) NULL ,
    ->   `updated_on` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
    ->   PRIMARY KEY (`id`) ,
    ->   UNIQUE INDEX `uniq_lat_long` (`lat` ASC, `long` ASC) )
    -> ENGINE = InnoDB;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> INSERT INTO locations (`lat`, `long`, `zip`) VALUES(20.04, 10.43, NULL) ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`);
Query OK, 1 row affected (0.00 sec)

mysql> select * from locations;
+----+---------+---------+------+---------------------+
| id | lat     | long    | zip  | updated_on          |
+----+---------+---------+------+---------------------+
|  1 | 20.0400 | 10.4300 | NULL | 2013-02-11 16:01:07 |
+----+---------+---------+------+---------------------+
1 row in set (0.01 sec)

mysql> INSERT INTO locations (`lat`, `long`, `zip`) VALUES(20.04, 10.43, NULL) ON DUPLICATE KEY UPDATE `id` = 100;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from locations;
+-----+---------+---------+------+---------------------+
| id  | lat     | long    | zip  | updated_on          |
+-----+---------+---------+------+---------------------+
| 100 | 20.0400 | 10.4300 | NULL | 2013-02-11 16:01:07 |
+-----+---------+---------+------+---------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO locations (`lat`, `long`, `zip`) VALUES(20.04, 10.43, NULL) ON DUPLICATE KEY UPDATE `id` = 101;
Query OK, 2 rows affected (0.01 sec)

mysql> select * from locations;
+-----+---------+---------+------+---------------------+
| id  | lat     | long    | zip  | updated_on          |
+-----+---------+---------+------+---------------------+
| 101 | 20.0400 | 10.4300 | NULL | 2013-02-11 16:01:07 |
+-----+---------+---------+------+---------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO locations (`lat`, `long`, `zip`) VALUES(20.04, 10.43, NULL) ON DUPLICATE KEY UPDATE `id` = 101;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from locations;
+-----+---------+---------+------+---------------------+
| id  | lat     | long    | zip  | updated_on          |
+-----+---------+---------+------+---------------------+
| 101 | 20.0400 | 10.4300 | NULL | 2013-02-11 16:01:07 |
+-----+---------+---------+------+---------------------+
1 row in set (0.00 sec)

You're right that when the row is INSERTed, the rows affected is 1, and when the row is UPDATEed, the rows affected is 2. That's how it's documented though:
http://dev.mysql.com/doc/refman/5.6/en/insert-on-duplicate.html

"With ON DUPLICATE KEY UPDATE, 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."

http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html

Can you show me what documentation you are referring to that seems to contradict this?

For now I will mark this as not a bug. Please let me know if you have any other information.

Best Regards,

Matt

Hi Ravi,

I apologize! I realize now that I mis-read your report.

You are saying that 5.5.29 is NOT returning 2 when the UPDATE is done.

I will now run this same test with 5.5.30 and let you know the results.

Best Regards,

Matt

Hi Ravi,

I apologize for the confusion.

I will now set this to cannot repeat as I was unable to repeat the same results using 5.5.30:

mysql> CREATE TABLE `locations` (
    ->   `id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
    ->   `lat` DECIMAL(8,4) NOT NULL ,
    ->   `long` DECIMAL(8,4) NOT NULL ,
    ->   `zip` CHAR(20) NULL ,
    ->   `updated_on` TIMESTAMP NULL DEFAULT CURRENT_TIMESTAMP ,
    ->   PRIMARY KEY (`id`) ,
    ->   UNIQUE INDEX `uniq_lat_long` (`lat` ASC, `long` ASC) )
    -> ENGINE = InnoDB;
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> INSERT INTO locations (`lat`, `long`, `zip`) VALUES(20.04, 10.43, NULL) ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`);
Query OK, 1 row affected (0.00 sec)

mysql> select * from locations;
+----+---------+---------+------+---------------------+
| id | lat     | long    | zip  | updated_on          |
+----+---------+---------+------+---------------------+
|  1 | 20.0400 | 10.4300 | NULL | 2013-02-11 16:12:19 |
+----+---------+---------+------+---------------------+
1 row in set (0.01 sec)

mysql> INSERT INTO locations (`lat`, `long`, `zip`) VALUES(20.04, 10.43, NULL) ON DUPLICATE KEY UPDATE `id` = 100;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from locations;
+-----+---------+---------+------+---------------------+
| id  | lat     | long    | zip  | updated_on          |
+-----+---------+---------+------+---------------------+
| 100 | 20.0400 | 10.4300 | NULL | 2013-02-11 16:12:19 |
+-----+---------+---------+------+---------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO locations (`lat`, `long`, `zip`) VALUES(20.04, 10.43, NULL) ON DUPLICATE KEY UPDATE `id` = 101;
Query OK, 2 rows affected (0.00 sec)

mysql> select * from locations;
+-----+---------+---------+------+---------------------+
| id  | lat     | long    | zip  | updated_on          |
+-----+---------+---------+------+---------------------+
| 101 | 20.0400 | 10.4300 | NULL | 2013-02-11 16:12:19 |
+-----+---------+---------+------+---------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO locations (`lat`, `long`, `zip`) VALUES(20.04, 10.43, NULL) ON DUPLICATE KEY UPDATE `id` = 101;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from locations;
+-----+---------+---------+------+---------------------+
| id  | lat     | long    | zip  | updated_on          |
+-----+---------+---------+------+---------------------+
| 101 | 20.0400 | 10.4300 | NULL | 2013-02-11 16:12:19 |
+-----+---------+---------+------+---------------------+
1 row in set (0.00 sec)

Please let me know if you feel that I'm missing anything here.

Best Regards,

Matt

I missed this output in my previous post:

mysql> select version();
+-------------------------------------------+
| version()                                 |
+-------------------------------------------+
| 5.5.30-enterprise-commercial-advanced-log |
+-------------------------------------------+
1 row in set (0.00 sec)

Hello Matthew, thank you for the detailed comments. There is a small difference between your queries and mine. In my queries, I always do:

INSERT INTO locations (`lat`, `long`) VALUES(10.5, 20.5) ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`);

i.e., I always use LAST_INSERT_ID(`id`). What I notice when I run this using the MySQL command-line client on a 5.6.10 server is that it says: "0 rows affected". I suppose that is because the `id` = LAST_INSERT_ID(`id`) does not really update the table at all?

The same occurs if I were to:

INSERT INTO locations (`lat`, `long`) VALUES(10.5, 20.5) ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`), `lat` = VALUES(`lat`), `long` = VALUES(`long`);

Unfortunately, I do not have the older version of MySQL (5.5.15) to test and see what it displays.

This bug report was filed after I noticed the discrepancy in the affected-rows returned by the node-mysql module for node.js which provides a JavaScript interface to MySQL. I figured the cause of the difference might be on the MySQL side since it started occurring after I upgraded the server.

I am fine with leaving this as not reproducible. In the meantime, I will try to investigate further what is happening underneath the node-mysql module which causes it to return "affectedRows = 2" when working with a 5.5.15 server bot "affectedRows = 1" with later ones.

Hi Ravi,

Correct. If the UPDATE wouldn't actually alter any columns, then the UPDATE is skipped and the rows affected is 0. This is consistent with a normal UPDATE where you'll see 1 row matched but 0 rows affected and 0 rows changed:

mysql> update locations set id = 101 where id = 101;
Query OK, 0 rows affected (0.02 sec)
Rows matched: 1  Changed: 0  Warnings: 0

mysql> update locations set id = 102 where id = 101;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

Please let me know if you find any new information that you feel does demonstrate a bug with 5.5.30.

Best Regards,

Matt