SHOW CREATE TABLE doesn"t display all TIMESTAMP options

MySQL 4.0.10 or 4.1.0

You can specify a DEFAULT value for a TIMESTAMP column other than the
first one in a table, but SHOW CREATE TABLE doesn't display it.

How-To-Repeat:

Create a table with a couple of TIMESTAMP columns, then insert a record
in which all values are set to their defaults:

mysql> CREATE TABLE t (
-> i TIMESTAMP DEFAULT 20020101121213,
-> j TIMESTAMP DEFAULT 20020101121213
-> );
Query OK, 0 rows affected (0.01 sec)

mysql> INSERT t () VALUES();
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM t;
+----------------+----------------+
| i | j |
+----------------+----------------+
| 20030212103710 | 20020101121213 |
+----------------+----------------+
1 row in set (0.00 sec)

That shows that the first TIMESTAMP's default is ignored (which I
expected) and that the second TIMESTAMP's default is *not* ignored
(which is actually what I was testing).

However, SHOW CREATE TABLE doesn't show the second column's DEFAULT
value:

mysql> SHOW CREATE TABLE t;
+-------+--------------------------------------------------- ------------------------------------------+
| Table | Create Table
|
+-------+--------------------------------------------------- ------------------------------------------+
| t | CREATE TABLE `t` (
`i` timestamp(14) NOT NULL,
`j` timestamp(14) NOT NULL
) TYPE=MyISAM |
+-------+--------------------------------------------------- ------------------------------------------+
1 row in set (0.01 sec)

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13759@lists.mysql.com
To unsubscribe, e-mail

Re: SHOW CREATE TABLE doesn"t display all TIMESTAMP options

Paul DuBois writes:
> MySQL 4.0.10 or 4.1.0
>
> You can specify a DEFAULT value for a TIMESTAMP column other than the
> first one in a table, but SHOW CREATE TABLE doesn't display it.
>
> How-To-Repeat:
>
> Create a table with a couple of TIMESTAMP columns, then insert a record
> in which all values are set to their defaults:
>

[skip]

>
> mysql> SHOW CREATE TABLE t;
> +-------+--------------------------------------------------- ------------------------------------------+
> | Table | Create Table
> |
> +-------+--------------------------------------------------- ------------------------------------------+
> | t | CREATE TABLE `t` (
> `i` timestamp(14) NOT NULL,
> `j` timestamp(14) NOT NULL
> ) TYPE=MyISAM |
> +-------+--------------------------------------------------- ------------------------------------------+
> 1 row in set (0.01 sec)
>

Hi!

I have taken a look and fixing it is very easy.

But I am not sure whether it should be fixed at all, because comment
in the source is more then clear : // Null as default

--
__ ___ ___ ____ __
/ |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
<___/ www.mysql.com

Join MySQL Users Conference and Expo:
http://www.mysql.com/events/uc2003/


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13765@lists.mysql.com
To unsubscribe, e-mail

Re: SHOW CREATE TABLE doesn"t display all TIMESTAMP options

At 20:50 +0200 2/12/03, Sinisa Milivojevic wrote:
>Paul DuBois writes:
>> MySQL 4.0.10 or 4.1.0
>>
>> You can specify a DEFAULT value for a TIMESTAMP column other than the
>> first one in a table, but SHOW CREATE TABLE doesn't display it.
>>
>> How-To-Repeat:
>>
>> Create a table with a couple of TIMESTAMP columns, then insert a record
>> in which all values are set to their defaults:
>>
>
>[skip]
>
>>
>> mysql> SHOW CREATE TABLE t;
>>
>>+-------+------------------------------------------------- --------------------------------------------+
>> | Table | Create Table
>> |
>>
>>+-------+------------------------------------------------- --------------------------------------------+
>> | t | CREATE TABLE `t` (
>> `i` timestamp(14) NOT NULL,
>> `j` timestamp(14) NOT NULL
>> ) TYPE=MyISAM |
>>
>>+-------+------------------------------------------------- --------------------------------------------+
>> 1 row in set (0.01 sec)
>>
>
>Hi!
>
>I have taken a look and fixing it is very easy.
>
>But I am not sure whether it should be fixed at all, because comment
>in the source is more then clear : // Null as default


Sinisa,

Okay, but:

1) If that's what the comment says, it doesn't match MySQL's behavior,
so it's wrong. The default specified for TIMESTAMP columns other than
the first *is* used.

2) Comments in source don't really count as documentation.

3) It seems to me that if you can specify a column option, and MySQL *uses*
that option (which it does in this case), then SHOW CREATE TABLE probably
out to spit out a CREATE TABLE statement that really is an accurate
representation of the table definition. Currently, SHOW CREATE TABLE
"loses" information in this case.

I'd suggest that either SHOW CREATE TABLE be fixed (as you indicate, it
would be easy), or that the behavior of CREATE TABLE be changed so that
any DEFAULT option be ignored for all TIMESTAMP columns, not just the
first TIMESTAMP column.

Thanks.

>
>--
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Sinisa Milivojevic
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer
>/_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus
> <___/ www.mysql.com
>
>Join MySQL Users Conference and Expo:
>http://www.mysql.com/events/uc2003/
>
>
>----------------------------------------------------------- ----------
>Before posting, please check:
> http://www.mysql.com/manual.php (the manual)
> http://lists.mysql.com/ (the list archive)
>
>To request this thread, e-mail bugs-thread13765@lists.mysql.com
>To unsubscribe, e-mail


------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13766@lists.mysql.com
To unsubscribe, e-mail

Re: SHOW CREATE TABLE doesn"t display all TIMESTAMP options

At 23:26 +0200 2/12/03, Alexander Keremidarski wrote:
>Sinisa Milivojevic wrote:
>>Alexander Keremidarski writes:
>>
>>>Paul,
>>>
>>>Paul DuBois wrote:
>>>
>>>
>>>If so then THIS is bug. :)
>>>
>>>Second TIMESTAMP column should get current system time as value
>>>upon INSERT not "default".
>>>
>>
>>
>>Nope ...
>>
>>Read a manual .....
>
>That is exactly what I am refering to. Read in manual chapter 6.2.2.2
>
>
>
>
>6.2.2.2 The DATETIME, DATE, and TIMESTAMP Types
>
>...
>
>The TIMESTAMP column type provides a type that you can use to
>automatically mark INSERT or UPDATE operations with the current date
>and time. If you have multiple TIMESTAMP columns, only the first one
>is updated automatically.
> ^^^^^^^
>
>....
>
>TIMESTAMP columns other than the first may also be set to the
>current date and time. Just set the column to NULL or to NOW().
>
>
>
>Reading all of the above I can only interpret it as: *ALL*
>timestamps get current system time upon INSERT, but only first is
>automatically changed later with UPDATEs

No, that isn't correct. The current date and time is the default only
for the first TIMESTAMP column in a table. The default for any other
TIMESTAMP columns is 0. (Doorstop II, p781)

In the MySQL Reference Manual, the default value for TIMESTAMP is given
in the CREATE TABLE section. (Printed O'Reilly manual, p533). It says:

@item
For date and time types other than @code{TIMESTAMP}, the default is the
appropriate zero value for the type. For the first @code{TIMESTAMP}
column in a table, the default value is the current date and time.
@xref{Date and time types}.

(The zero value for TIMESTAMP is 00000000000000)

>
>
>More of that. You can test it by yourself. When you have more than 1
>TIMESTAMP columns and you do INSERT without specifying them into
>columns list *ALL* of them get value of current system time .... yes
>if not DEFAULT specified as Paul said :)

No, they don't all get the current system time. Try these queries:

DROP TABLE IF EXISTS t;
CREATE TABLE t (ts1 TIMESTAMP, ts2 TIMESTAMP);
INSERT INTO t () VALUES();
SELECT * FROM t;

The result from the SELECT is:

+----------------+----------------+
| ts1 | ts2 |
+----------------+----------------+
| 20030212154517 | 00000000000000 |
+----------------+----------------+

Only the first TIMESTAMP column gets the current date and time by default.
Others get 0.



>
>So we have problem here and one of following MUST be done:
>
>1. Prevent setting DEFAULT for TIMESTAMP
>2. Fix output of SHOW CREATE TABLE + Manual.

3. Manual is okay, just fix SHOW CREATE TABLE.

>
>1. will make things consistent with my understanding and current Manual
>2. will do it consistent for Paul test case
>
>We can't leave things as is as it fits in category "Table created in
>a way which is impossible to be used for restoring after mysqldump"
>
>Best regards
>
>--
> MySQL Conference and Expo 2003 http://www.mysql.com/events/uc2003/index.html
> For technical support contracts, visit https://order.mysql.com/?ref=msal
> __ ___ ___ ____ __
> / |/ /_ __/ __/ __ \/ / Mr. Alexander Keremidarski
> / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer
> /_/ /_/\_, /___/\___\_\___/ Sofia, Bulgaria
> <___/ www.mysql.com

How-To-Repeat:

------------------------------------------------------------ ---------
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)

To request this thread, e-mail bugs-thread13768@lists.mysql.com
To unsubscribe, e-mail