From: Eric Bergen
Date: July 30 2007 2:02am
Subject: Re: multiple current_timestamp
List-Archive: http://lists.mysql.com/internals/34916
Message-Id: <28fd48f00707291902s488c0028m2032684db8a3198@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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)


On 7/29/07, Arnold Daniels <info@stripped> wrote:
> 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 wrote:
> > 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?
> >
> >
> >
> > On 7/28/07, Arnold Daniels <info@stripped> wrote:
> >
> >> 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=eric.bergen@stripped
> >>
> >>
> >>
> >
> >
> >
>
> --
> MySQL Internals Mailing List
> For list archives: http://lists.mysql.com/internals
> To unsubscribe:    http://lists.mysql.com/internals?unsub=eric.bergen@stripped
>
>


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