Bug #46329 update query returns incorrect number of rows matched when no fields are updated
Submitted: 22 Jul 2009 2:27 Modified: 22 Jul 2009 21:47
Reporter: Sean Fitzgerald Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Cluster: Cluster (NDB) storage engine Severity:S2 (Serious)
Version:5.1.34-ndb-7.0.6-cluster-gpl OS:Linux (x86_64)
Assigned to: CPU Architecture:Any
Tags: count, regression, rows matched, UPDATE

[22 Jul 2009 2:27] Sean Fitzgerald
Description:
When updating a row matched on primary key, if the data in the query is identical to the data already in the row, the server will report 0 rows matched, 0 rows affected, as opposed to 1 row matched, 0 rows modified.

This is a problem with hibernate, which depends on updates returning the correct number of rows matched.

How to repeat:
create table `user` (
  `user_id` int(11) NOT NULL auto_increment,
  `first_name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`user_id`)
) ENGINE=NDB DEFAULT CHARSET=utf8;

insert into user set first_name='Sean';
update user set first_name='Sean' where user_id=1;
[22 Jul 2009 2:35] Sean Fitzgerald
sorry, it appears my simplified table to recreate doesn't recreate the issue!

create table `user` (
  `user_id` int(11) NOT NULL auto_increment,
  `first_name` varchar(100) DEFAULT NULL,
  `last_name` varchar(100) DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT 0,
  `modified` timestamp DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `status` tinyint DEFAULT 0,
  PRIMARY KEY (`user_id`)
) ENGINE=NDB DEFAULT CHARSET=utf8;

With a little further investigation, the problem appears to lie somewhere in the `created` field.
[22 Jul 2009 2:35] Sean Fitzgerald
update user set first_name='Sean', created='2009-07-22 03:05:03' where user_id=1;
[22 Jul 2009 21:47] Sveta Smirnova
Thank you for the report.

Verified as described.