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
• Status: Verified
• Category: MySQL Cluster: Cluster (NDB) storage engine
• Severity: S2 (Serious)
• Version: 5.1.34-ndb-7.0.6-cluster-gpl
• OS: Linux (x86_64)
• 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.