MySQL Bugs: #60489: Update returnd god result but CURRENT

Bug #60489	Update returnd god result but CURRENT_TIMESTAMP field not updated
Submitted:	16 Mar 2011 13:44	Modified:	5 Jul 2011 16:36
Reporter:	David Poor	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	5.5.10	OS:	Windows (XP)
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	current_timestamp, timestamp, update query

Description:
When doing an INSERT .. ON DUPLICATE KEY UPDATE ... query or an UPDATE query with field a TIMESTAMP defined as NOT NULL, DEFAULT CURRENT_TIMESTAMP...

If a record exists and the fields provided are the same as existing data, a prepared statement execute update reports a value of "1" indicating one record changed, but no data are changed in the database and the TIMESTAMP field is not updated to the time of the query.

COMMENT: It does make sense to not update a record with exactly the same data thereby using resources to result in no changes.

However, the presence of a TIMESTAMP field with CURRENT_TIMESTAMP changes the context of the query. If the query is successful, then the TIMESTAMP field always be set to the time of the query. In this case, it is not.

BUG OR DOCUMENTATION? I can see arguments to describe this as acceptable behavior, in which case it should be added to the documentation. On the other hand, I believe that any successful UPDATE query *should* update a CURRENT_TIME TIMESTAMP field.

Is this the appropriate place for this discussion?

How to repeat:
Issue any UPDATE query with the same data as in the database.

(If this is determined to be a bug that should be fixed, I will create and post a sample database to easily replicate the behavior.)

Suggested fix:
CHANGE THE BEHAVIOR OF MySQL:
Treat the presence of CURRENT_TIMESTAMP TIMESTAMP field as a special context that forces an actual database update even if other fields remain the same.

OR...

CHANGE THE DOCUMENTATION:
Expand the documentation for CURRENT_TIMESTAMP and NON-NULL TIMESTAMP fields to explain the conditions under which a successful UPDATE QUERY will not update the TIMESTAMP field.

This should be test-case enough:

CREATE TABLE `tsonuptest` (
  `id` INT(11) NOT NULL AUTO_INCREMENT,
  `txt` VARCHAR(10) DEFAULT NULL,
  `ts` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=INNODB DEFAULT CHARSET=utf8

INSERT INTO `tsonuptest` (txt) VALUES ('b')

SELECT * FROM `tsonuptest`;
/*   id  txt                    ts  
------  ------  -------------------
     1  b       2011-03-16 15:16:30
*/ 

-- wait at least a second!

UPDATE `test`.`tsonuptest` SET `txt`='b' WHERE `id`='1'; -- note that this is NO CHANGE

SELECT * FROM `tsonuptest`;
/*   id  txt                    ts  
------  ------  -------------------
     1  b       2011-03-16 15:16:30
*/

Whether this is expected or if docs are not clear about such case I do not know. But it tells that if *no other column* in table changes the TIMESTAMP will not either.

Peter
(not a MySQL person)

Looks similar to: http://bugs.mysql.com/bug.php?id=28904?.

I think this manual page, http://dev.mysql.com/doc/refman/5.5/en/timestamp.html, should explain this behavior in details. As soon as zero rows are reported as "Changed", timestamp column is not updated.

Thank you Peter for the example.  This shows reasonable results when commands are run directly in MySql.  To better test my issues, I added a key so that I would get a DUPLICATE KEY error.  

alter table `test`.`tsonuptest` add unique `txt` (`txt`);

Then I tried INSERT, UPDATE, and INSERT.. ON DUPLICATE KEY. 

UPDATE `test`.`tsonuptest` SET `txt`='b' WHERE `id`='1';
returns 0 records changed so it is reasonable that the Timestamp is not changed

INSERT `test`.`tsonuptest` SET `txt`='b';
returns a DUPLICATE KEY error

INSERT `test`.`tsonuptest` SET `txt`='b' ON DUPLICATE KEY UPDATE `txt`='b';
returns 0 records changed so it is reasonable that the Timestamp is not changed

HOWEVER:  My problem was discovered with Prepared Statements, not with direct MySql commands. 
...
If a record exists and the fields provided are the same as existing data, a prepared statement execute update reports a value of "1" indicating one record changed, but no data are changed in the database and the TIMESTAMP field is not updated to the time of the query. 
...
So I wrote a test jsp page using the same test database (tsonuptest), and issued the same queries using both STATEMENT and PREPARED STATEMENT to execute the queries.  In all cases, the java.sql classes reported 1 record changed instead of the 0 records changed that I got from directly entering the queries. 

Results from processing... (I will upload the source file)
OPEN DATABASE
... database open

SHOW TIMESTAMP
... timestamp set to 2011-03-17 07:38:59.0

STATEMENT: UPDATE record to same values.
... query is "UPDATE `test`.`tsonuptest` SET `txt`='b' WHERE `id`='1';"
... st.executeUpdate returned 1, timestamp set to 2011-03-17 07:38:59.0

PREPARED STATEMENT: UPDATE record to same values.
... query is "UPDATE `test`.`tsonuptest` SET `txt`='b' WHERE `id`='1';"
... build prepared statement
... st.executeUpdate returned 1, timestamp set to 2011-03-17 07:38:59.0

STATEMENT: INSERT ... ON DUPLICATE KEY UPDATE ... record to same values.
... query is "INSERT `test`.`tsonuptest` SET `txt`='b' ON DUPLICATE KEY UPDATE `txt`='b';"
... st.executeUpdate returned 1, timestamp set to 2011-03-17 07:38:59.0

PREPARED STATEMENT: INSERT ... ON DUPLICATE KEY UPDATE ... record to same values.
... query is "INSERT `test`.`tsonuptest` SET `txt`='b' ON DUPLICATE KEY UPDATE `txt`='b';"
... st.executeUpdate returned 1, timestamp set to 2011-03-17 07:38:59.0

It appears as if the root problem is that the same query gives the number of records changed as 0 if a direct MySql command, but gives the number of records changed as 1 if through JAVA.  (1 is supposed to be an insert, 2 an update, 0 for no records changed, and <0 for errors.)  

If the UPDATE or INSERT..ON DUPLICATE KEY UPDATE... gave a return of 0, then one could decide whether or not to issue a second query to force the timestamp to change.  

SO, perhaps I should close this and set the problem as a bug in the JAVA.sql or possibly in com.mysql.jdbc.Driver? 

In the interim, the workaround is to explicitly set the timestamp value to NOW()

WORKAROUND: Set the timestamp value before the callSTATEMENT: INSERT ... ON DUPLICATE KEY UPDATE ... record to same values.
... query is "INSERT `test`.`tsonuptest` SET `txt`='b',ts=NOW() ON DUPLICATE KEY UPDATE `txt`='b',ts=NOW();"
... st.executeUpdate returned 2, timestamp set to 2011-03-17 09:29:07.0

PREPARED STATEMENT: INSERT ... ON DUPLICATE KEY UPDATE ... record to same values.
... query is "INSERT `test`.`tsonuptest` SET `txt`='b',ts=NOW() ON DUPLICATE KEY UPDATE `txt`='b',ts=NOW();"
... st.executeUpdate returned 2, timestamp set to 2011-03-17 09:29:08.0

Note that these queries properly returned a value of "2" showing that a single record was updated.  (I still don't understand the logic of that, but ok.)

Test file with STATEMENT and PREPARED STATEMENT queries

Attachment: INSERTTEST.jsp (script/javascript, text), 5.35 KiB.

The auto-update TIMESTAMP column is intended to change only when some other column is set to a *different* value. An update that sets columns to their current value will not cause an update of the TIMESTAMP column.

http://dev.mysql.com/doc/refman/5.5/en/timestamp.html

"
The auto-update TIMESTAMP column, if there is one, is automatically updated to the current timestamp when the value of any other column in the row is changed from its current value, unless the TIMESTAMP column explicitly is assigned a value other than NULL.
"

Regarding the differences you find for number of rows affected between entering queries directly and executing them through Java, I suspect this is due to a difference in default settings.

For the C API, the setting is controlled by the CLIENT_FOUND_ROWS flag at connect time. Normally, updates return the number of *changed* (not *matched*) rows, but this can be changed:

The value of client_flag is usually 0, but can be set to a combination of the following flags to enable certain features.

Flag Name	Flag Description
CLIENT_FOUND_ROWS	Return the number of found (matched) rows, not the number of changed rows.

http://dev.mysql.com/doc/refman/5.5/en/mysql-real-connect.html
http://dev.mysql.com/doc/refman/5.5/en/mysql-affected-rows.html

For Java, the setting is:

useAffectedRows
Don't set the CLIENT_FOUND_ROWS flag when connecting to the server (not JDBC-compliant, will break most applications that rely on "found" rows vs. "affected rows" for DML statements), but does cause "correct" update counts from "INSERT ... ON DUPLICATE KEY UPDATE" statements to be returned by the server.
false

http://dev.mysql.com/doc/refman/5.5/en/connector-j-reference-configuration-properties.html:

So for the C API, CLIENT_FOUND_ROWS is not set by default, but for Java it is. I.e., by default, Java returns the number of matched, not changed, rows.

You might try setting useAffectedRows to true to see what happens.