| Bug #64301 | Wrong row count returned with 'NO_AUTO_VALUE_ON_ZERO' sql_mode | ||
|---|---|---|---|
| Submitted: | 11 Feb 2012 13:54 | Modified: | 17 Feb 2012 17:51 |
| Reporter: | Peter Laursen (Basic Quality Contributor) | Email Updates: | |
| Status: | Duplicate | Impact on me: | |
| Category: | MySQL Server: DML | Severity: | S3 (Non-critical) |
| Version: | OS: | Any | |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | qc | ||
[11 Feb 2012 14:12]
Peter Laursen
Please replace case 3) with this (copy paste error - in this mode a '0' inserted will become '1')
SET sql_mode = '';
USE somedb;
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(100) NOT
NULL) ENGINE = INNODB;
INSERT INTO `test` (`id`,`name`) VALUES (0,'bif') ON DUPLICATE KEY UPDATE NAME = 'baf';
-- 1 row(s) affected
SELECT * FROM `test`;
/* returns
id name
------ --------
1 bif
*/
INSERT INTO `test` (`id`,`name`) VALUES (0,'bif') ON DUPLICATE KEY UPDATE NAME = 'baf';
-- 1 row(s) affected
[11 Feb 2012 16:10]
Valeriy Kravchuk
IMHO out fine manual, http://dev.mysql.com/doc/refman/5.5/en/insert-on-duplicate.html, explains this: "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." Without NO_AUTO_VALUE_ON_ZERO we have insert of new row with autoincremented value for every INSERT of 0, while with this mode we have update of existing row when when we try to INSERT again. Where is the bug here?
[11 Feb 2012 16:26]
Peter Laursen
The bug is simply that the framing of the message sends a wrong and confusing message to the user. But this is what we have seen and discussed thousands of time before: instead of fixing bugs/issues, an "explanation" is added to the docs. In my world the message "2 row(s) affected" is complete nonsense to send to user. How many rows were affected - 1 or 2? A little common sense, please! The Moon is still made from stone even if MySQL states that it is made from cheese. Just like MySQL docs cannot change the geology of of the universe it cannot either change the meaning of common language.
[17 Feb 2012 17:51]
Sveta Smirnova
Thank you for the feedback. This is actually duplicate of this feature request: bug #24773.
Description: Using 'NO_AUTO_VALUE_ON_ZERO' sql_mode returns wrong row count with "INSERT .. ON DUPLICATE KEY UPDATE .." statement. How to repeat: 1) SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO'; USE somedb; DROP TABLE IF EXISTS `test`; CREATE TABLE `test` (`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(100) NOT NULL) ENGINE = INNODB; INSERT INTO `test` (`id`,`name`) VALUES (0,'bif') ON DUPLICATE KEY UPDATE NAME = 'baf'; -- 1 row(s) affected SELECT * FROM `test`; /* returns id name ------ -------- 0 bif */ INSERT INTO `test` (`id`,`name`) VALUES (0,'bif') ON DUPLICATE KEY UPDATE NAME = 'baf'; -- 2 row(s) affected SELECT * FROM `test`; /* returns id name ------ -------- 0 baf THE POINT HERE: I don't see more than one row affected as there is only one! */ 2) Using a plain UPDATE and not a INSERT .. ON DUPLICATE KEYUPDATE returns correct rowcount. SET sql_mode = 'NO_AUTO_VALUE_ON_ZERO'; USE somedb; DROP TABLE IF EXISTS `test`; CREATE TABLE `test` (`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(100) NOT NULL) ENGINE = INNODB; INSERT INTO `test` (`id`,`name`) VALUES (0,'bif') ON DUPLICATE KEY UPDATE NAME = 'baf'; -- 1 row(s) affected SELECT * FROM `test`; /* returns id name ------ -------- 0 bif */ UPDATE `test` SET `name` ='buf' WHERE id = 0; -- 1 row(s) affected 3) In SQL_mode not including 'NO_AUTO_VALUE_ON_ZERO' this is not a problem: SET sql_mode = ''; USE somedb; DROP TABLE IF EXISTS `test`; CREATE TABLE `test` (`id` INT NOT NULL PRIMARY KEY AUTO_INCREMENT,`name` VARCHAR(100) NOT NULL) ENGINE = INNODB; INSERT INTO `test` (`id`,`name`) VALUES (0,'bif') ON DUPLICATE KEY UPDATE NAME = 'baf'; -- 1 row(s) affected SELECT * FROM `test`; /* returns id name ------ -------- 0 bif */ INSERT INTO `test` (`id`,`name`) VALUES (0,'bif') ON DUPLICATE KEY UPDATE NAME = 'baf'; -- 1 row(s) affected Suggested fix: Return correct row count.