Bug #58120 Timestamp field with CURRENT_TIMESTAMP Updates
Submitted: 10 Nov 2010 16:59 Modified: 10 Nov 2010 19:36
Reporter: Scott Weigand Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Workbench: SQL Editor Severity:S3 (Non-critical)
Version:Server: 5.0.77, WB: 5.2.29 CE OS:Any
Assigned to: CPU Architecture:Any

[10 Nov 2010 16:59] Scott Weigand
Description:
When I run an update via MySQL Workbench on a field with timestamp data type with a default value of current_timestamp, the field gets update even though I am not updating the field.

How to repeat:
* Have a field that is timestamp data type, default value of current_timestamp

* Update a row via MySQL Workbench

* the timestamp field gets updated even though you didn't specify that field be updated

Suggested fix:
Timestamp data type field should retain last-written value and not update because of an update. It should only update on a SET command.
[10 Nov 2010 17:40] MySQL Verification Team
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/ and the instructions on
how to report a bug at http://bugs.mysql.com/how-to-report.php
[10 Nov 2010 18:01] Peter Laursen
@Miguel .. you may be wrong here.  A TIMESTAMP can be defined many ways - including

* .. default CURRENT_TIMESTAMP 
* .. default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP 

If the first - and not the second - is the case the TIMESTAMP should not update when other columns are updated.  But most GUI clients have problems handling the difference between the two above (with the sole exception of guess what?) .. so I would not be surprised if also WB has.

Peter
(not a MySQL person(
[10 Nov 2010 18:01] Scott Weigand
I read through the documentation and see what current_timestamp will update when the statement executes. It would be nice for MySQL to have a function that filled in the datetime when Inserted or Set, not just because the row was updated. Using PostgreSQL, current_timestamp fills the date in on Inserts and used to set a field value, not just because a row was updated.
[10 Nov 2010 18:04] Scott Weigand
@Peter

The syntax is just CURRENT_TIMESTAMP.

As for Workbench, that is a pretty good theory. I just updated to 5.2.29 CE and the problem, if it is with WB, is still there.
[10 Nov 2010 18:22] Peter Laursen
@Scott .. I suggest that you change the status of the report to 'open' then.
[10 Nov 2010 18:38] Scott Weigand
Opened back up.
[10 Nov 2010 18:46] Valeriy Kravchuk
For some reason I do not see a problem when mysql command line client is used:

macbook-pro:5.0 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.92-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create table ts(c1 int, c2 timestamp default current_timestamp);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into ts(c1) values (1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from ts;
+------+---------------------+
| c1   | c2                  |
+------+---------------------+
|    1 | 2010-11-10 20:44:30 | 
+------+---------------------+
1 row in set (0.00 sec)

mysql> update ts set c1=2 where c1=1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from ts;
+------+---------------------+
| c1   | c2                  |
+------+---------------------+
|    2 | 2010-11-10 20:44:30 | 
+------+---------------------+
1 row in set (0.00 sec)

mysql> update ts set c1=3 where c1=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from ts;
+------+---------------------+
| c1   | c2                  |
+------+---------------------+
|    3 | 2010-11-10 20:44:30 | 
+------+---------------------+
1 row in set (0.00 sec)

mysql> select current_timestamp();
+---------------------+
| current_timestamp() |
+---------------------+
| 2010-11-10 20:45:18 | 
+---------------------+
1 row in set (0.00 sec)

Do I miss anything? Also, had you tried to check what UPDATE is really generated/sent by Workbench?

I doubt we have any server bug here, sorry.
[10 Nov 2010 18:53] Scott Weigand
@Valeriy

Ran the MySQL Client on the server from Putty and same result happened (the timestamp field with current_timestamp updated when it shouldn't have).
[10 Nov 2010 19:06] Peter Laursen
@Scott .. what does SHOW CREATE TABLE return?

The problem could be that WB create the column with ON UPDATE CURRENT_TIMESTAMP even if you did not specify it,  But note that when specifying a TIMESTAMP NOT NULL (and nothing more) the server will implicitly make it .. DEFAULT CURRENT TIMESTAMP ON UPDATE CURRENT_TIMESTAMP (if the keyword CURRENT_TIMESTAMP was not used for defining a 'lower' column)

Try to understand this mess:
http://dev.mysql.com/doc/refman/5.0/en/timestamp.html
[10 Nov 2010 19:36] Scott Weigand
@ Peter

Thank You! Ran command and saw what you meant. Bit different from other databases.