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: | |
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
[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.