multiple current_timestamp

[Arnold Daniels]

Hi all,

Why can there be only one timestamp field with current_timestamp in
default or on update?

I've been wondered about this for a long time. A table often has a field
`date_added`, with the current timestamp only as default, and a field
`date_changed` with a changing timestamp upon updating. Basically it is
irritating me, since now I have to either solve this in code or use a
trigger. Also because I do not understand why this limitation is necessary.

Is this something that is subjected to change in MySQL 6? I haven't seen
any plans for it.

Best regards,
Arnold

--
MySQL Internals Mailing List
For list archives: http://lists.mysql.com/internals
To unsubscribe: http://lists.mysql.com/internals?unsub=mysql-i...@freebsd.csie.nctu.edu.tw

[Eric Bergen]

It makes sense to only allow one field to have current_timestamp
because having more than one field with current_timestamp would mean
both columns are storing the same data. Is it really that difficult to
have date_updated be current_timestamp and date_added set to null in
the insert query? I don't understand the need for triggers. What
problem are you trying to solve with code or triggers?

> To unsubscribe: http://lists.mysql.com/internals?unsub=eric....@provenscaling.com
>
>

--
high performance mysql consulting.
http://provenscaling.com

[Arnold Daniels]

Hi,

I see that I haven't made myself clear. Please take the statement to
create a table below as example.

CREATE TABLE `mytable` (
`id` INT UNSIGNED NOT NULL ,
`description` VARCHAR( 255 ) NOT NULL ,
`date_insert` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ,
`date_update` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL DEFAULT
CURRENT_TIMESTAMP ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM;

This triggers the error:

#1293 - Incorrect table definition; there can be only one TIMESTAMP
column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

Let's say this table does not trigger an error and I would do the
following queries:

INSERT INTO `mytable` (`description`) VALUES ("My first item");
UPDATE `mytable` SET `description` = "Changed my first item" WHERE
`id`=1;

The fields would not hold the same data.

At this moment you cannot use 'ON UPDATE CURRENT_TIMESTAMP' if you also
have a field like `date_insert`. Instead you need to create a trigger to
do this behavior.

I hope I managed to make my issue clear with this example.

Best regards,
Arnold

--

[Eric Bergen]

That's a much better example. It does seem very strange that the
timestamp functionality isn't more flexible. I suspect there are other
things wrong in that area too. While playing around with timestamp
definitions I found that the on update timestamp column still has to
be listed before any other timestamps.

I can't find anything in the manual that says the "magic" timestamp
column still needs to be first like it was in 4.0.

This is either a documentation bug or a server code bug.

Using your example with DEFAULT CURRENT_TIMESTAMP removed from date_insert:

mysql> CREATE TABLE `mytable` (
-> `id` INT UNSIGNED NOT NULL ,
-> `description` VARCHAR( 255 ) NOT NULL ,
-> `date_insert` TIMESTAMP NOT NULL,
-> `date_update` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ,
-> PRIMARY KEY ( `id` )
-> ) ENGINE = MYISAM;
ERROR 1293 (HY000): Incorrect table definition; there can be only one

TIMESTAMP column with CURRENT_TIMESTAMP in DEFAULT or ON UPDATE clause

mysql> CREATE TABLE `mytable` (
-> `id` INT UNSIGNED NOT NULL ,
-> `description` VARCHAR( 255 ) NOT NULL ,
-> `date_update` TIMESTAMP ON UPDATE CURRENT_TIMESTAMP NOT NULL
DEFAULT CURRENT_TIMESTAMP ,
-> `date_insert` TIMESTAMP NOT NULL,
-> PRIMARY KEY ( `id` )
-> ) ENGINE = MYISAM;
Query OK, 0 rows affected (0.00 sec)

> To unsubscribe: http://lists.mysql.com/internals?unsub=eric....@provenscaling.com
>
>

--
high performance mysql consulting.
http://provenscaling.com

--

[Jim Winstead]

On Sun, Jul 29, 2007 at 07:02:19PM -0700, Eric Bergen wrote:
> That's a much better example. It does seem very strange that the
> timestamp functionality isn't more flexible. I suspect there are other
> things wrong in that area too. While playing around with timestamp
> definitions I found that the on update timestamp column still has to
> be listed before any other timestamps.
>
> I can't find anything in the manual that says the "magic" timestamp
> column still needs to be first like it was in 4.0.

It doesn't.

mysql> create table t1 (a timestamp default 0, b timestamp
-> on update current_timestamp);
Query OK, 0 rows affected (0.01 sec)
mysql> show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`a` timestamp NOT NULL default '0000-00-00 00:00:00',
`b` timestamp NOT NULL default '0000-00-00 00:00:00' on update
CURRENT_TIMESTAMP
) ENGINE=MyISAM DEFAULT CHARSET=latin1

I don't know if there is a strong technical reason for only allowing a
single timestamp field to be automatically set or automatically updated,
or the limitation was just a carry-over from when only the first
timestamp field was auto-set/update. I wouldn't expect anyone at MySQL
to jump at implementing this, but a patch would be looked at. (But since
you can get the same effect with a trigger, it may not have a shot.)

Jim Winstead
MySQL Inc.

[Tobias Asplund]

On Sun, 29 Jul 2007, Jim Winstead wrote:

> I don't know if there is a strong technical reason for only allowing
> a single timestamp field to be automatically set or automatically updated,
> or the limitation was just a carry-over from when only the first timestamp
> field was auto-set/update. I wouldn't expect anyone at MySQL to jump at
> implementing this, but a patch would be looked at. (But since you can get
> the same effect with
> a trigger, it may not
> have a shot.)

The effect can be the same, but the performance is far from there in a trigger
implementation.
We tested this for one of the User Conference sessions this year and found
that timestamp is quite efficient.

We tested:
1. Letting the timestamp take the value automatically
2. Having a BEFORE INSERT trigger set the column value into a DATETIME column
3. Forcing the function NOW() into a DATETIME column

http://www.flupps.org/pics/timestamp_benchmark.015.jpg

We found that if you use NEW. or OLD. in a trigger a second pass of
"Opening tables" is executed, the high concurrency to the table_cache
slowed it down a bit in this case (at least, that's what we could
see from limited testing under time strain).

--
Tobias "flupps" Asplund <tob...@mysql.com>
Trainer and Consultant, Sweden
MySQL AB http://www.mysql.com

[Dmitri Lenev]

Hello Arnold!

* Arnold Daniels <in...@adaniels.nl> [07/07/29 10:45]:

> Why can there be only one timestamp field with current_timestamp in
> default or on update?
>
> I've been wondered about this for a long time. A table often has a field
> `date_added`, with the current timestamp only as default, and a field
> `date_changed` with a changing timestamp upon updating. Basically it is
> irritating me, since now I have to either solve this in code or use a
> trigger. Also because I do not understand why this limitation is necessary.

AFAIU this limitation stems only from the way in which this feature is
currently implemented in the server and there are no other reasons for
its existence. SQL standard does not mention such limitation. Moreover
in addition to CURRENT_TIMESTAMP SQL standard allows several other
niladic functions as default values (e.g. CURRENT_USER).

> Is this something that is subjected to change in MySQL 6? I haven't seen
> any plans for it.

AFAIK this item is on our TODO list but not very high. So it is not
planned for any particular version. Actually rather than implementing
support for multiply TIMESTAMP fields with DEFAULT CURRENT_TIMESTAMP
values it makes sense to implement support for more general expressions
in DEFAULT clause. And the latter feature strongly depends on switch
to new text-based .FRM format (for which there are no fixed plans as
well). Having said that I should mention that, of course, we will
consider a community contributed patch solving only the former problem.

I also should mention that if someone from community wants to work on
removing this limitation he should be aware that one of prerequisites
for such a change is moving logic responsible for auto-magical setting
of TIMESTAMP values from handlers (e.g. ha_myisam.cc) to SQL-layer.

Best regards,
--
Dmitri Lenev, Software Developer
MySQL AB, www.mysql.com

Are you MySQL certified? http://www.mysql.com/certification